Case Statements - 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 2005 Forums

Transact-SQL (2005)

Case Statements

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 08:19:30

For some reason I didnt pick this up earlier, but in a Case Statement I have I'm trying to group certain results into catagorys, which works well until I have just noticed that it tends to group things in wrong groups

This is the Case Statment
CASE
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 4
THEN 1
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 8
THEN 2
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 13
THEN 3
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 17
THEN 4
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 21
THEN 5
ELSE 6

What I want to do is this
between 0&3 then 1, between 4&7 then 2, between 8&7 then 3, between 13&16 then 4, between 17&20 then 5 else 21+ then 6

How can I get this Case Statement to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-16 : 08:28:27

[code]CASE
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 4 THEN 1
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 8 THEN 2
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 13 THEN 3
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 17 THEN 4
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 21 THEN 5
ELSE 6
END[/code]

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 08:35:26

Thanks, but my problem is something I should have put into Thread1 is that I have ended up with some results in the wrong groups, example I have one record that shows value 6 in Group 3 (< 13) when it should be in Group 2 (< 8) how can I fix that problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-16 : 09:03:42

What is "value 6 in group 3"?

Please post some sample data illustrating your problem.

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 09:13:18

If you look at the Weeks Waiting and the value 6 shows in Weeks Range 3 but the other two values in Weeks Waiting show value 4 and are in the correct Weeks Range.

Waiting List Identifier Waiting List Name Waiting List Service Weeks Waiting Weeks Range Weeks Range Title
10653663 PBCN PHYSIO 4 2 4<8
10640230 PDD PHYSIO 6 3 8<13
10658030 PDD PHYSIO 4 2 4<8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-16 : 09:48:14

This is the RESULT...
Where is your source/sample data?

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 09:56:06

Here is the SQL I use to create the result above

SELECT [Waiting List Identifier], [Patient NHS Identifier], [Patient Name], [Waiting List Name], [Waiting List Description], [Date on Waiting List],
[Referral Archive Flag], [Patient Archive Flag], WL_TYPE, [Referred to Team], [Waiting List Service],
DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7 AS [Weeks Waiting],
CASE
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 4 THEN 1
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 8 THEN 2
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 13 THEN 3
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 17 THEN 4
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 21 THEN 5
ELSE 6
END AS [Weeks Range],
CASE
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 4 THEN '0<4'
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 8 THEN '4<8'
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 13 THEN '8<13'
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 17 THEN '13<17'
WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 21 THEN '17<21'
ELSE '21+'
END AS [Weeks Range Title], [Date Removed from Waiting List]
FROM jez.TEMPJ
WHERE ([Date Removed from Waiting List] IS NULL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 12:51:08

post the source data from which you got earlier query. thats easier than posting code.

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 03:36:26

How do you mean source data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 03:40:07

What you have posted, is the result of your query.
To be able to run your query, you must have some kind of source data, sample data, to produce the output you posted.

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 03:58:39

Ok, this is sample of the original table as comma delimited ...

Waiting List Identifier,Patient NHS Identifier,Patient Name,Waiting List Name,Waiting List Description,Date on Waiting List,Date Removed from Waiting List,Referral Archive Flag,Patient Archive Flag,WL_TYPE,Referred to Team,Weeks Waiting
10603928,6463139122,,PDD,Phy OP Developmental Delay Ass,03-Jul-08,,N,N,,PDD,12.00
10610209,6229042533,,PP,Phy OP Paediatric Ass,10-Jul-08,,N,N,,PCP,11.00
10610577,6229063360,,PP,Phy OP Paediatric Ass,11-Jul-08,,N,N,,PCP,11.00
10615564,6229644592,,PP,Phy OP Paediatric Ass,16-Jul-08,,N,N,,PCP,11.00
10626059,6428334356,,PP,Phy OP Paediatric Ass,30-Jul-08,,N,N,,PDD,9.00
10627156,6367878165,,PP,Phy OP Paediatric Ass,21-Jul-08,,N,N,,PCP,10.00
10629130,6452581589,,PDD,Phy OP Developmental Delay Ass,04-Aug-08,,N,N,,PDD,8.00
10640230,6470429971,,PDD,Phy OP Developmental Delay Ass,15-Aug-08,,N,N,,PCP,6.00
10653663,6471826338,,PBCN,Phy OP Baby Clinic North Ass,03-Sep-08,,N,N,,PBCN,4.00
10658030,6481447569,,PDD,Phy OP Developmental Delay Ass,03-Sep-08,,N,N,,PDD,4.00
10658096,6000199163,,PP,Phy OP Paediatric Ass,05-Sep-08,,N,N,,PCP,3.00
10661415,6483055587,,PDD,Phy OP Developmental Delay Ass,08-Sep-08,,N,N,,PDD,3.00
10661483,6207870476,,PP,Phy OP Paediatric Ass,08-Sep-08,,N,N,,PCP,3.00
10661620,6498793457,,PP,Phy OP Paediatric Ass,08-Sep-08,,N,N,,PCP,3.00
10661682,6455309766,,PGA,Phy OP Gait Anomalies Ass,09-Sep-08,,N,N,,PGA,3.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 04:11:10

Where is the error?

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		[Waiting List Identifier] INT,
		[Patient NHS Identifier] BIGINT,
		[Waiting List Name] VARCHAR(4),
		[Waiting List Description] VARCHAR(30),
		[Date on Waiting List] DATETIME,
		[Referral Archive Flag] CHAR(1),
		[Patient Archive Flag] CHAR(1),
		[Referred to Team] VARCHAR(4),
		[Weeks Waiting] DECIMAL(4, 2)
	)

INSERT	@Sample
SELECT	10603928, 6463139122, 'PDD' , 'Phy OP Developmental Delay Ass', '03-Jul-08', 'N', 'N', 'PDD' , 12.00 UNION ALL
SELECT	10610209, 6229042533, 'PP'  , 'Phy OP Paediatric Ass'         , '10-Jul-08', 'N', 'N', 'PCP' , 11.00 UNION ALL
SELECT	10610577, 6229063360, 'PP'  , 'Phy OP Paediatric Ass'         , '11-Jul-08', 'N', 'N', 'PCP' , 11.00 UNION ALL
SELECT	10615564, 6229644592, 'PP'  , 'Phy OP Paediatric Ass'         , '16-Jul-08', 'N', 'N', 'PCP' , 11.00 UNION ALL
SELECT	10626059, 6428334356, 'PP'  , 'Phy OP Paediatric Ass'         , '30-Jul-08', 'N', 'N', 'PDD' ,  9.00 UNION ALL
SELECT	10627156, 6367878165, 'PP'  , 'Phy OP Paediatric Ass'         , '21-Jul-08', 'N', 'N', 'PCP' , 10.00 UNION ALL
SELECT	10629130, 6452581589, 'PDD' , 'Phy OP Developmental Delay Ass', '04-Aug-08', 'N', 'N', 'PDD' ,  8.00 UNION ALL
SELECT	10640230, 6470429971, 'PDD' , 'Phy OP Developmental Delay Ass', '15-Aug-08', 'N', 'N', 'PCP' ,  6.00 UNION ALL
SELECT	10653663, 6471826338, 'PBCN', 'Phy OP Baby Clinic North Ass'  , '03-Sep-08', 'N', 'N', 'PBCN',  4.00 UNION ALL
SELECT	10658030, 6481447569, 'PDD' , 'Phy OP Developmental Delay Ass', '03-Sep-08', 'N', 'N', 'PDD' ,  4.00 UNION ALL
SELECT	10658096, 6000199163, 'PP'  , 'Phy OP Paediatric Ass'         , '05-Sep-08', 'N', 'N', 'PCP' ,  3.00 UNION ALL
SELECT	10661415, 6483055587, 'PDD' , 'Phy OP Developmental Delay Ass', '08-Sep-08', 'N', 'N', 'PDD' ,  3.00 UNION ALL
SELECT	10661483, 6207870476, 'PP'  , 'Phy OP Paediatric Ass'         , '08-Sep-08', 'N', 'N', 'PCP' ,  3.00 UNION ALL
SELECT	10661620, 6498793457, 'PP'  , 'Phy OP Paediatric Ass'         , '08-Sep-08', 'N', 'N', 'PCP' ,  3.00 UNION ALL
SELECT	10661682, 6455309766, 'PGA' , 'Phy OP Gait Anomalies Ass'     , '09-Sep-08', 'N', 'N', 'PGA' ,  3.00

-- Display output
SELECT	CASE
		WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 4 THEN 1 
		WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 8 THEN 2 
		WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 13 THEN 3 
		WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 17 THEN 4
		WHEN DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 21 THEN 5
		ELSE 6
	END AS theCASE,
	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 04:35:21

Ok this is the results from that above thread...

theCASE,Waiting List Identifier,Patient NHS Identifier,Waiting List Name,Waiting List Description,Date on Waiting List,Referral Archive Flag,Patient Archive Flag,Referred to Team,Weeks Waiting
4,10603928,6463139122,PDD,Phy OP Developmental Delay Ass,00:00.0,N,N,PDD,12
4,10610209,6229042533,PP,Phy OP Paediatric Ass,00:00.0,N,N,PCP,11
4,10610577,6229063360,PP,Phy OP Paediatric Ass,00:00.0,N,N,PCP,11
4,10615564,6229644592,PP,Phy OP Paediatric Ass,00:00.0,N,N,PCP,11
3,10626059,6428334356,PP,Phy OP Paediatric Ass,00:00.0,N,N,PDD,9
3,10627156,6367878165,PP,Phy OP Paediatric Ass,00:00.0,N,N,PCP,10
3,10629130,6452581589,PDD,Phy OP Developmental Delay Ass,00:00.0,N,N,PDD,8
3,10640230,6470429971,PDD,Phy OP Developmental Delay Ass,00:00.0,N,N,PCP,6
2,10653663,6471826338,PBCN,Phy OP Baby Clinic North Ass,00:00.0,N,N,PBCN,4
2,10658030,6481447569,PDD,Phy OP Developmental Delay Ass,00:00.0,N,N,PDD,4
2,10658096,6000199163,PP,Phy OP Paediatric Ass,00:00.0,N,N,PCP,3
2,10661415,6483055587,PDD,Phy OP Developmental Delay Ass,00:00.0,N,N,PDD,3
2,10661483,6207870476,PP,Phy OP Paediatric Ass,00:00.0,N,N,PCP,3
2,10661620,6498793457,PP,Phy OP Paediatric Ass,00:00.0,N,N,PCP,3
2,10661682,6455309766,PGA,Phy OP Gait Anomalies Ass,00:00.0,N,N,PGA,3

the problem is that, looking at theCASE field shows from the Case Statement against the Weeks Waiting doesnt match up...
eg the first row shows CASE as 4 and Weeks Waiting as 12 this should be in CASE 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 04:46:28

Well, the [Weeks Waiting] value was calculated three weeks ago...

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 05:04:20

Yes, this is because I need the Weeks Waiting to always measure from the 1st of that current month, so I need th case Statement to do the same

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 05:08:19

quote:
Originally posted by JezLisle

Yes, this is because I need the Weeks Waiting to always measure from the 1st of that current month, so I need th case Statement to do the same

That's new information to us.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 05:11:43

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
[Waiting List Identifier] INT,
[Patient NHS Identifier] BIGINT,
[Waiting List Name] VARCHAR(4),
[Waiting List Description] VARCHAR(30),
[Date on Waiting List] DATETIME,
[Referral Archive Flag] CHAR(1),
[Patient Archive Flag] CHAR(1),
[Referred to Team] VARCHAR(4),
[Weeks Waiting] DECIMAL(4, 2)
)

INSERT @Sample
SELECT 10603928, 6463139122, 'PDD' , 'Phy OP Developmental Delay Ass', '03-Jul-08', 'N', 'N', 'PDD' , 12.00 UNION ALL
SELECT 10610209, 6229042533, 'PP' , 'Phy OP Paediatric Ass' , '10-Jul-08', 'N', 'N', 'PCP' , 11.00 UNION ALL
SELECT 10610577, 6229063360, 'PP' , 'Phy OP Paediatric Ass' , '11-Jul-08', 'N', 'N', 'PCP' , 11.00 UNION ALL
SELECT 10615564, 6229644592, 'PP' , 'Phy OP Paediatric Ass' , '16-Jul-08', 'N', 'N', 'PCP' , 11.00 UNION ALL
SELECT 10626059, 6428334356, 'PP' , 'Phy OP Paediatric Ass' , '30-Jul-08', 'N', 'N', 'PDD' , 9.00 UNION ALL
SELECT 10627156, 6367878165, 'PP' , 'Phy OP Paediatric Ass' , '21-Jul-08', 'N', 'N', 'PCP' , 10.00 UNION ALL
SELECT 10629130, 6452581589, 'PDD' , 'Phy OP Developmental Delay Ass', '04-Aug-08', 'N', 'N', 'PDD' , 8.00 UNION ALL
SELECT 10640230, 6470429971, 'PDD' , 'Phy OP Developmental Delay Ass', '15-Aug-08', 'N', 'N', 'PCP' , 6.00 UNION ALL
SELECT 10653663, 6471826338, 'PBCN', 'Phy OP Baby Clinic North Ass' , '03-Sep-08', 'N', 'N', 'PBCN', 4.00 UNION ALL
SELECT 10658030, 6481447569, 'PDD' , 'Phy OP Developmental Delay Ass', '03-Sep-08', 'N', 'N', 'PDD' , 4.00 UNION ALL
SELECT 10658096, 6000199163, 'PP' , 'Phy OP Paediatric Ass' , '05-Sep-08', 'N', 'N', 'PCP' , 3.00 UNION ALL
SELECT 10661415, 6483055587, 'PDD' , 'Phy OP Developmental Delay Ass', '08-Sep-08', 'N', 'N', 'PDD' , 3.00 UNION ALL
SELECT 10661483, 6207870476, 'PP' , 'Phy OP Paediatric Ass' , '08-Sep-08', 'N', 'N', 'PCP' , 3.00 UNION ALL
SELECT 10661620, 6498793457, 'PP' , 'Phy OP Paediatric Ass' , '08-Sep-08', 'N', 'N', 'PCP' , 3.00 UNION ALL
SELECT 10661682, 6455309766, 'PGA' , 'Phy OP Gait Anomalies Ass' , '09-Sep-08', 'N', 'N', 'PGA' , 3.00

-- Display output
SELECT CASE
WHEN DATEDIFF(DAY, theMonth, GETDATE()) / 7 < 4 THEN 1
WHEN DATEDIFF(DAY, theMonth, GETDATE()) / 7 < 8 THEN 2
WHEN DATEDIFF(DAY, theMonth, GETDATE()) / 7 < 13 THEN 3
WHEN DATEDIFF(DAY, theMonth, GETDATE()) / 7 < 17 THEN 4
WHEN DATEDIFF(DAY, theMonth, GETDATE()) / 7 < 21 THEN 5
ELSE 6
END AS theCASE,
[Waiting List Identifier],
[Patient NHS Identifier],
[Waiting List Name],
[Waiting List Description],
[Date on Waiting List],
[Referral Archive Flag],
[Patient Archive Flag],
[Referred to Team],
[Weeks Waiting]
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [Date on Waiting List]), '19000101') AS theMonth,
[Waiting List Identifier],
[Patient NHS Identifier],
[Waiting List Name],
[Waiting List Description],
[Date on Waiting List],
[Referral Archive Flag],
[Patient Archive Flag],
[Referred to Team],
[Weeks Waiting]
FROM @Sample
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 05:25:57

This still puts the first record which is WeeksWaiting 12 in CASE 4

What does this section mean?
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [Date on Waiting List]), '19000101') AS theMonth,
[Waiting List Identifier],
[Patient NHS Identifier],
[Waiting List Name],
[Waiting List Description],
[Date on Waiting List],
[Referral Archive Flag],
[Patient Archive Flag],
[Referred to Team],
[Weeks Waiting]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 07:01:57

Yes, because it now is over 13 weeks since the first of the month for the record.
There is NO correlation between GETDATE() 20081017 and the value in weeks waiting column.

Why don't you realize this?

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 07:44:25

Ok, I've been looking over and over at this, and think I have found a solution...

It seems such as simple one too.

SELECT [Waiting List Identifier], [Patient NHS Identifier], [Patient Name], [Waiting List Name], [Waiting List Description], [Date on Waiting List],
[Referral Archive Flag], [Patient Archive Flag], WL_TYPE, [Referred to Team], [Waiting List Service],
DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7 AS [Weeks Waiting],
CASE
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 4 THEN 1
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 8 THEN 2
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 13 THEN 3
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 17 THEN 4
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 21 THEN 5
ELSE 6
END AS [Weeks Range],
CASE
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 4 THEN '0<4'
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 8 THEN '4<8'
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 13 THEN '8<13'
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 17 THEN '13<17'
WHEN (DATEDIFF(DAY, [Date on Waiting List], DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) /7) < 21 THEN '17<21'
ELSE '21+'
END AS [Weeks Range Title], [Date Removed from Waiting List]
FROM jez.TEMPJ
WHERE ([Date Removed from Waiting List] IS NULL)

With This the first record in Posted - 10/17/2008 : 05:11:43 shows in CASE 3 just like I was wanting...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 09:39:16

And what is different from what I suggested 10/17/2008 : 05:11:43 ?

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources