CASE statement question - 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

General SQL Server Forums

New to SQL Server Programming

CASE statement question

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-05-11 : 09:44:29

Hi all

Is it possible to have a list in a CASE statement.
E.g.

case cas2.CallSortingGrp
when in ('UNAS', 'ASAP999', 'CGPP12', 'SD12') then sum(cas1.CountOfCallid)
end as gp_symp

I've been Googling all over the place but can't seem to get a definitive answer.

Any help gratefully received.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 09:53:53

Yes, with a slightly different syntax
SUM(CASE WHEN cas2.CallSortingGrp IN ('UNAS', 'ASAP999', 'CGPP12', 'SD12') THEN cas1.CountOfCallid ELSE 0 END

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-11 : 09:55:25

CASE
WHEN cas2.CallSortingGrp in ('UNAS', 'ASAP999', 'CGPP12', 'SD12') THEN sum(cas1.CountOfCallid)
ELSE ??? what should happen here?
END as gp_symp

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-11 : 09:56:56

quote:
Originally posted by jimf

Yes, with a slightly different syntax
SUM(CASE WHEN cas2.CallSortingGrp IN ('UNAS', 'ASAP999', 'CGPP12', 'SD12') THEN cas1.CountOfCallid ELSE 0 END)

Jim

Everyday I learn something that somebody else already knew

Ok that's better

But the last bracket is missing.

No, you're never too old to Yak'n'Roll if you're too young to die.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-05-11 : 09:57:49

Thanks folks.
At the moment there is no ELSE.
I'm using this to group items as otherwise my case statement would be ridiculously long.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 10:03:45

Still use my expression as corrected by webfred.

Jim

Everyday I learn something that somebody else already knew

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-05-11 : 10:20:43

I am doing and this is my query so far:-


declare @start datetime
declare @end datetime
set @start=GETDATE()-2
set @end=GETDATE()
select
	CONVERT(varchar(10),cas1.date,103) as [Date],
	CASE WHEN cas2.CallSortingGrp IN 
		('UNAS', 'ASAP999', 'CGPP12', 'SD12','CGPP4','SGP4','SGP1','SD4','SDWH','CDU',
			'CD1','CD12','CD4','CD24', 'CDNRA24','CDRA','CPN','CDN','CFPC','CGUMC','CHV','CLCMHT',
			'CMH','CMW','COP','CORTH','CCCL','CPOL','CPOLN','CSW','ECON','CPHAR','CWIC','WIC',
			'HOME','CGPP36','SDNWD','RAGP') THEN cas1.CountOfCallid END as gp_symp
--	into #gp_ooh_calls_table
from
	dbo.CAS_sum_daily cas1
		inner join [dbo].[Dispositions table] cas2 on cas1.[Print Group name]=cas2.[Print Group name]
		inner join dbo.[Call Origins] cas3 on cas1.Origin=cas3.[Call Origin]
where
	cas1.date between @start and @end
	and cas3.OOH=1
group by
	CONVERT(varchar(10),cas1.date,103),
	cas2.CallSortingGrp,
	cas1.CountOfCallid

but it's not doing as expected.
I simply want one row with a total and this is giving me 112 rows.

Anyone any ideas?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 10:24:39

That's because you're creating 112 groups of
CONVERT(varchar(10),cas1.date,103),
cas2.CallSortingGrp,
cas1.CountOfCallid
and you're not summing anything up.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources