Select Case and Count if problem

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

Select Case and Count if problem

Author

Topic

tugsy
Starting Member

10 Posts

Posted - 2009-07-16 : 20:31:04

Hi, I have a farily complex (well I think its complex) query. The outcome I want is a percetage of the all rows that have a date difference of less than 1, returning 1 row with 1 field.

The below code gives me a list as long as my arm.

Can any one please help me? I am fairly new to sql (most of this code I have found elswhere on the interweb.


SELECT 
case when DATEDIFF(dd,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) - datediff(ww,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate)*2 < 2 
then count(DATEDIFF(dd,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) - datediff(ww,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate)*2) / count(*) 
end as '1day'

FROM 
dbo.Status  
RIGHT OUTER JOIN dbo.UnionRequestQuery  
RIGHT OUTER JOIN dbo.UnionActionQuery  ON 
dbo.UnionActionQuery.RequestId = dbo.UnionRequestQuery.RequestId ON 
dbo.Status.StatusId = dbo.UnionRequestQuery.StatusId 

WHERE  
(not dbo.status.RequestTypeId in ('HOL','MYC','NEW1','QUO','REG') and dbo.Status.Title LIKE 'resolved') 
group by
dbo.UnionRequestQuery.DateLogged,
dbo.UnionActionQuery.ActionDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 21:06:29

[code]
SELECT COUNT(
case when DATEDIFF(dd,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate)
- datediff(ww,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) * 2 < 2
then 1
end)
/ COUNT(*) as '1day'
FROM
dbo.Status
RIGHT OUTER JOIN dbo.UnionRequestQuery
RIGHT OUTER JOIN dbo.UnionActionQuery ON
dbo.UnionActionQuery.RequestId = dbo.UnionRequestQuery.RequestId ON
dbo.Status.StatusId = dbo.UnionRequestQuery.StatusId

WHERE
(not dbo.status.RequestTypeId in ('HOL','MYC','NEW1','QUO','REG') and dbo.Status.Title LIKE 'resolved')
group by
dbo.UnionRequestQuery.DateLogged,
dbo.UnionActionQuery.ActionDate[/code]

KH
[spoiler]Time is always against us[/spoiler]

tugsy
Starting Member

10 Posts

Posted - 2009-07-16 : 21:22:40

Thanks for this, I had made the change however I get a returned value of 0 - is this expected due to the number being a percentage?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 21:26:30

multiply the numerator count(*) by 1.0

integer divide by integer will give you back integer. So if you have 5 / 6 it will give you 0. Multiply by 1.0 convert it to decimal, 5.0 / 6 will give you 0.8333

KH
[spoiler]Time is always against us[/spoiler]

tugsy
Starting Member

10 Posts

Posted - 2009-07-16 : 21:34:28

Awesome I get that part.

How do I put it into practise?


COUNT(
case when DATEDIFF(dd,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) 
		- datediff(ww,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) * 2 < 2 
	     then 1
             end) / COUNT(*) * 1.0 as '1day',

thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 21:39:41

Yes. Tried executing it ? OK ?

KH
[spoiler]Time is always against us[/spoiler]

tugsy
Starting Member

10 Posts

Posted - 2009-07-16 : 21:41:04

Not ok, when executed still returned a 0 value.

tugsy
Starting Member

10 Posts

Posted - 2009-07-16 : 21:51:40

I just tested again.. The returned value is 0. which I think means the query is not getting any data.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 21:53:55

remove the count and run the query first see if does return what you wanted


SELECT  dbo.UnionRequestQuery.DateLogged, dbo.UnionActionQuery.ActionDate,
        case when DATEDIFF(dd,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) 
		- datediff(ww,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) * 2 < 2 
	     then 1
             end
FROM 
dbo.Status  
RIGHT OUTER JOIN dbo.UnionRequestQuery  
RIGHT OUTER JOIN dbo.UnionActionQuery  ON 
dbo.UnionActionQuery.RequestId = dbo.UnionRequestQuery.RequestId ON 
dbo.Status.StatusId = dbo.UnionRequestQuery.StatusId 

WHERE  
(not dbo.status.RequestTypeId in ('HOL','MYC','NEW1','QUO','REG') and dbo.Status.Title LIKE 'resolved')

what is the requirement for the upper COUNT() ? What is the datediff() < datediff() * 2 < 2 doing ?

quote:
a date difference of less than 1

you want to count those with this ? just

datediff(day, DateLogged, ActionDate) < 1

KH
[spoiler]Time is always against us[/spoiler]

tugsy
Starting Member

10 Posts

Posted - 2009-07-16 : 22:08:49

Here is my original query.
This query returns the amount of days between the start of a job till when it becomes resolved.


SELECT 
DATEDIFF(dd,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) - datediff(ww,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate)*2

FROM 
	dbo.Status  
	 RIGHT OUTER JOIN dbo.UnionRequestQuery  
	 RIGHT OUTER JOIN dbo.UnionActionQuery  ON 
	dbo.UnionActionQuery.RequestId = dbo.UnionRequestQuery.RequestId ON 
	dbo.Status.StatusId = dbo.UnionRequestQuery.StatusId 
WHERE  
	(not dbo.status.RequestTypeId in ('HOL','MYC','NEW1','QUO','REG') and dbo.Status.Title LIKE 'resolved')

it works and it returns the correct data.

Now I am trying to return for me a percentage of all the jobs resolved within 1 day, 3 days, 5 days, 10 days and + 10days. Or even a count of those days. (I would prefer a percentage though)

quote:

what is the requirement for the upper COUNT() ? What is the datediff() < datediff() * 2 < 2 doing ?

This is counting the weekdays and not weekends.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 22:16:23

[code]
SELECT count(case when days <= 1 then 1 end) * 100.0 / count(*) as [Percentage < 1 day],
count(case when days <= 3 then 1 end) * 100.0 / count(*) as [Percentage < 3 days],
count(case when days <= 5 then 1 end) * 100.0 / count(*) as [Percentage < 5 days]
FROM
(
SELECT days =
DATEDIFF(dd,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate)
- datediff(ww,dbo.UnionRequestQuery.DateLogged,dbo.UnionActionQuery.ActionDate) * 2

FROM
dbo.Status
RIGHT OUTER JOIN dbo.UnionRequestQuery
RIGHT OUTER JOIN dbo.UnionActionQuery ON
dbo.UnionActionQuery.RequestId = dbo.UnionRequestQuery.RequestId ON
dbo.Status.StatusId = dbo.UnionRequestQuery.StatusId
WHERE
(not dbo.status.RequestTypeId in ('HOL','MYC','NEW1','QUO','REG') and dbo.Status.Title LIKE 'resolved')
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

tugsy
Starting Member

10 Posts

Posted - 2009-07-16 : 22:21:31

Thank you so much, You made it look easy. I really appreciate your help. Now I can impress my boss.