WILCARD / LIKE if's and elses - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

WILCARD / LIKE if's and elses

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-08-30 : 08:24:31

I have the following statement

SELECT s.term as 'clients', cast(sum(case when type = 'I' then valuenet when type = 'C' then (-1 * valuenet) else 0 end) as decimal(9,2)) as 'turnover', sum(p.rebate) as 'rebate'
FROM tblSalesInvoice t
JOIN (SELECT item AS term from tbltemp_report) s ON t.jobname collate Latin1_General_CI_AS LIKE '%' + s.term + '%'
join tblpurchquote p on p.est_id = t.est_id
where t.invoicedate <= '20050201' and t.invoicedate >= '20050101'
and t.cl_code = 'ABC_COMPANY' and p.ordered = 'y'
GROUP BY s.term

This works fine if one of my clients is in the table tbltemp_report.

So for example if I have in table tbltemp_report the following : -

Company_1
Company_2
Company_3

Then the select statement is returning

Company_1 | 9999.99 | 0.00
Company_2 | 1111.11 | 2.99
Company_3 | 2222.22 | 103.99

This works perfectly for what I am wanting to do as far as report generation is concerned. However, I need to catch all those that don't meet this criteria within the selection. so if there are companies Company_4 and / or Company_5 at present they will not show up.

What I need for the code to do is to capture these a misc so that its represented as follows : -

Company_1 | 9999.99 | 0.00
Company_2 | 1111.11 | 2.99
Company_3 | 2222.22 | 103.99
Misc | 333.33 | 0.00

Obviously select statements are designed to exclude those that don't meet certain criteria so I really need to setup an if else statment or something so I would appreciate your guidance.

thanks in advance

James

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-30 : 08:41:29

I THINK you need to change this
"SELECT s.term as 'clients', cast(sum(case when type = 'I' then valuenet when type = 'C' then (-1 * valuenet) else 0 end) as decimal(9,2)) as 'turnover', sum(p.rebate) as 'rebate'
FROM tblSalesInvoice t
JOIN (SELECT item AS term from tbltemp_report) s ON t.jobname collate Latin1_General_CI_AS LIKE '%' + s.term + '%'
join tblpurchquote p on p.est_id = t.est_id
where t.invoicedate <= '20050201' and t.invoicedate >= '20050101'
and t.cl_code = 'ABC_COMPANY' and p.ordered = 'y'
GROUP BY s.term"

into something along the lines of

"SELECT case when s.term is not null then s.term else 'misc' end as 'clients', cast(sum(case when type = 'I' then valuenet when type = 'C' then (-1 * valuenet) else 0 end) as decimal(9,2)) as 'turnover', sum(p.rebate) as 'rebate'
FROM tblSalesInvoice t
LEFT JOIN (SELECT item AS term from tbltemp_report) s ON t.jobname collate Latin1_General_CI_AS LIKE '%' + s.term + '%'
join tblpurchquote p on p.est_id = t.est_id
where t.invoicedate <= '20050201' and t.invoicedate >= '20050101'
and t.cl_code = 'ABC_COMPANY' and p.ordered = 'y'
GROUP BY case when s.term is not null then s.term else 'misc' end"

jparker
Posting Yak Master

118 Posts

Posted - 2005-08-30 : 08:45:08

Spot On :-)

Cheers Dude

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-30 : 09:04:40

Spot check the results!!!
All information provided as "with no warranty"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources