Case expression assistance - 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 expression assistance

Author

Topic

TLW
Starting Member

8 Posts

Posted - 2007-12-10 : 14:26:14

I am working on a report that uses the parameter @LastOccurrence. When the user chooses 8 or less it would show all instances that occurred 8 or less hours ago, if they choose 24 or less it would show all instances that occurred 24 or less hours ago, including those that occurred 8 or less hours ago, etc. I understand why it's not working, but I'm struggling with how to get it to work as I described. The code that I have now is below. Thanks in advance for any assistance.

Declare @LastOccurrence char(40)
set @LastOccurrence = ' All'
SELECT DISTINCT wicv.[workitem_number] AS '#',      
            	DATEDIFF(HOUR, [MyField], GETDATE()) AS 'Age(Hours)',          
	DATEDIFF(MINUTE, [MyField], GETDATE()) AS 'Inactive(Mins)',          
	CASE	
		WHEN  DATEDIFF(HOUR, [MyField], GETDATE())<= 8  THEN '8 or less'
		WHEN  DATEDIFF(HOUR, [MyField], GETDATE())<= 24 THEN '24 or less'
		WHEN  DATEDIFF(HOUR, [MyField], GETDATE())<= 48 THEN '48 or less'
		WHEN  DATEDIFF(HOUR, [MyField], GETDATE())> 48 THEN 'Over 48'
	END AS LastOccurrence
		
FROM [MyTable]
WHERE 
((Case WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 8 THEN '8 or less'
    WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 24 THEN '24 or less'
	WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 48 THEN '48 or less'
Else 'more than 48' end ) = @LastOccurrence and @LastOccurrence <> ' All'
OR
 ((Case WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 8 THEN '8 or less'
    WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 24	THEN '24 or less'
	WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 48	THEN '48 or less'
Else 'more than 48' end )  <> @LastOccurrence and @LastOccurrence =  ' All') )

Teresa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 14:49:09

[code]SELECT [#],
[Age(Hours)],
[Inactive(Mins)],
LastOccurrence
FROM (
SELECT DISTINCT wicv.[workitem_number] AS [#],
DATEDIFF(HOUR, [MyField], GETDATE()) AS [Age(Hours)],
DATEDIFF(MINUTE, [MyField], GETDATE()) AS [Inactive(Mins)],
CASE
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 8 THEN '8 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 24 THEN '24 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 48 THEN '48 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) > 48 THEN 'Over 48'
END AS LastOccurrence
FROM [MyTable]
) AS e
WHERE LastOccurrence = @LastOccurrence
OR @LastOccurrence = ' all'[/code]

E 12°55'05.25"
N 56°04'39.16"

TLW
Starting Member

8 Posts

Posted - 2007-12-10 : 14:59:25

Thanks for the response. I tried what you suggested but I am still not getting it to work. I currently have 2 items 8 or less, none for 24 or less and 1 for 48 or less. When I change the parameter vaule to 48 or less it only gives me the one item. I need it to give me the 1 for 48 or less AND the 2 items for 8 or less.

Any other suggestions?

Teresa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 15:05:01

It depends on what you want.
Can you post some proper and accurate sample together with expected output for all possible outcomes?

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-10 : 15:09:07

Can you provide some sample data and expected output? I read both your posts several times and I'm not sure exactly what you want based on the input from the user.

TLW
Starting Member

8 Posts

Posted - 2007-12-10 : 15:30:26

Here's the code again and I listed sample data and expected output

Declare @LastOccurrence char(40)
set @LastOccurrence = 'All'
SELECT DISTINCT wicv.[workitem_number] AS '#',      
            	DATEDIFF(HOUR, [My Field], GETDATE()) AS 'Age(Hours)',          
	CASE	
		WHEN  DATEDIFF(HOUR, [My Field], GETDATE())<= 8  THEN '8 or less'
		WHEN  DATEDIFF(HOUR, [My Field], GETDATE())<= 24 THEN '24 or less'
		WHEN  DATEDIFF(HOUR, [My Field], GETDATE())<= 48 THEN '48 or less'
		WHEN  DATEDIFF(HOUR, [My Field], GETDATE())> 48 THEN 'Over 48'
	END AS LastOccurrence
		
FROM [My Table]
WHERE 
((Case WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 8 THEN '8 or less'
    WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 24 THEN '24 or less'
	WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 48 THEN '48 or less'
Else 'more than 48' end ) = @LastOccurrence and @LastOccurrence <> ' All'
OR
 ((Case WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 8 THEN '8 or less'
    WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 24	THEN '24 or less'
	WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 48	THEN '48 or less'
Else 'more than 48' end )  <> @LastOccurrence and @LastOccurrence =  ' All') )

Sample data
# Age Last Occurrence
1000 5 8 or less
1002 20 24 or less
1004 40 48 or less
1006 100 over 48

Expected output samples:

If the parameter is 'All' it would show
1000 5 8 or less
1002 20 24 or less
1004 40 48 or less
1006 100 over 48

If the parameter is 8 or less
1000 5 8 or less

If the parameter is 24 or less
1000 5 8 or less
1002 20 24 or less

If the parameter is 48 or less
1000 5 8 or less
1002 20 24 or less
1004 40 48 or less

If the parameter is Over 48
1006 100 over 48

Hope this helps clarify the ultimate goal.

Thanks!

Teresa

tm
Posting Yak Master

160 Posts

Posted - 2007-12-10 : 15:45:54

Based on your script try ..

Declare @LastOccurrence char(40)
set @LastOccurrence = 'All'
SELECT DISTINCT wicv.[workitem_number] AS '#',
DATEDIFF(HOUR, [My Field], GETDATE()) AS 'Age(Hours)',
CASE
WHEN @LastOccurrence = 'All' THEN
CASE
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 8 THEN '8 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 24 THEN '24 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 48 THEN '48 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) > 48 THEN 'Over 48'
END
ELSE
CASE
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) > 48 AND @LastOccurrence = 'Over 48' THEN 'Over 48'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 48 AND @LastOccurrence = '48 or less' THEN '48 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 24 AND @LastOccurrence = '24 or less' THEN '24 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 8 AND @LastOccurrence = '8 or less' THEN '8 or less'
END
END AS LastOccurrence

FROM [My Table]
WHERE
((Case WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 8 THEN '8 or less'
WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 24 THEN '24 or less'
WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 48 THEN '48 or less'
Else 'more than 48' end ) = @LastOccurrence and @LastOccurrence <> ' All'
OR
((Case WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 8 THEN '8 or less'
WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 24 THEN '24 or less'
WHEN DATEDIFF(HOUR, [My Field], GETDATE()) <= 48 THEN '48 or less'
Else 'more than 48' end ) <> @LastOccurrence and @LastOccurrence = ' All') )

TLW
Starting Member

8 Posts

Posted - 2007-12-10 : 16:13:08

I tried it but am still getting the same result. Once it falls into the 8 or less "bucket", it won't pull it in for the "24 or less" or "48 or less".

I'm at a loss so any and all suggestions are welcome!

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 16:19:34

[code]SELECT [#],
[Age(Hours)],
[Inactive(Mins)],
REPLACE(LastOccurrence, '0', '') AS LastOccurrence
FROM (
SELECT DISTINCT wicv.[workitem_number] AS [#],
DATEDIFF(HOUR, [MyField], GETDATE()) AS [Age(Hours)],
DATEDIFF(MINUTE, [MyField], GETDATE()) AS [Inactive(Mins)],
CASE
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 8 THEN '08 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 24 THEN '24 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 48 THEN '48 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) > 48 THEN 'Over 48'
END AS LastOccurrence
FROM [MyTable]
) AS e
WHERE LastOccurrence <= @LastOccurrence
OR @LastOccurrence = ' all'[/code]

E 12°55'05.25"
N 56°04'39.16"

TLW
Starting Member

8 Posts

Posted - 2007-12-11 : 09:11:23

Peso, your last post works except for the "Over 48". I think I'm going to just change it to "72 or less" and give them an option for "All".
I'm a bit confused as to how it's working though

(LastOccurrence <= @LastOccurrence
	OR @LastOccurrence = ' all')

For the <=, is it just looking at the first character? For example
08 or less
24 or less
48 or less
72 or less

Is it seeing that 08 is less than 24 so when you use the parameter 24 or less it pulls in the 08 or less also? Hopefully that makes sense. I'm glad it's working but I'd like to be able to say I understand why

Thanks a bunch!

Teresa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 09:13:04

What different values can @LastOccurence parameter hold?
I thought is was the complete string "24 or less"?

E 12°55'05.25"
N 56°04'39.16"

TLW
Starting Member

8 Posts

Posted - 2007-12-11 : 09:51:48

[code]CASE
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 8 THEN '08 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 24 THEN '24 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) <= 48 THEN '48 or less'
WHEN DATEDIFF(HOUR, [MyField], GETDATE()) > 48 THEN 'Over 48'
END AS LastOccurrence[/code]

It can hold any of the above '08 or less', '24 or less', '48 or less', 'Over 48'. I did change it slightly and I'm now using '72 or less' and adding in an 'All' option in place of the 'Over 48'. The code you posted worked except for the 'Over 48', which is why I changed it. That is also why I am thinking it must just compare the 08, 24, and 48.

Teresa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 09:56:13

"08 or less" is less than "Over 48".
So when you pass "Over 48" as value for @LastOccurrence, all three "xx or less" records should be returned.

E 12°55'05.25"
N 56°04'39.16"

TLW
Starting Member

8 Posts

Posted - 2007-12-11 : 10:20:54

It did work as you just described, however, the manager actually wanted Over 48 to show only those items that occurred more than 48 hours ago. So it should not have included the "xx or less" items, only those that were "over 48". I just added in the 72 or less option and will give them an option for all.

So is the <= looking at the beginning two characters? Like 08 is less than 24, 08 and 24 are less than 48, etc?

Teresa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 10:23:53

The check look for entire string.

E 12°55'05.25"
N 56°04'39.16"

TLW
Starting Member

8 Posts

Posted - 2007-12-11 : 10:44:30

OK, Thanks so much!

Teresa