case statement in count?

Question

Post reply

case statement in count?

jbalbo

SSCertifiable

Points: 7583
More actions
March 19, 2013 at 10:55 am

#272363

Hi
trying to do the following
SELECT Date, Year, CASE DATEPART(mm, date) WHEN 07 THEN 6 END AS JulyMaxhours, KindOfDay, Month,
COUNT(CASE DATEPART(mm, date) WHEN 07 )
FROM Auxiliary.Calendar
WHERE (KindOfDay = 'Weekday') AND (Year = 2013) AND (Month = 7)
What I want in the case is the count of days = 07 even better is if I can multiply that count by 6
Thanks
Joe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

Not sure I fully understand, but maybe:

SELECT Date, Year, CASE DATEPART(mm, date) WHEN 07 THEN 6 END AS JulyMaxhours, KindOfDay, Month,

SUM(CASE WHEN DATEPART(mm, date) = 07 THEN 6 ELSE 0 END) AS [Total_Hours?]

FROM Auxiliary.Calendar

WHERE (KindOfDay = 'Weekday') AND (Year = 2013) AND (Month = 7)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Scott

Thanks for the info

I should hav explained more and now that I see what you sent,

When I run

SELECT Date, Year, CASE DATEPART(mm, date) WHEN 07 THEN 6 END AS JulyMaxhours, KindOfDay, Month

FROM Auxiliary.Calendar

WHERE (KindOfDay = 'Weekday') AND (Year = 2013) AND (Month = 7)

I get 22 days of 6 hours

what I need (in this example) is

just the 22*6 or just 22

Thanks

Joe

ScottPletcher SSC Guru Points: 101099 More actions · Answer 3

SELECT

'20130701' AS Date, Year,

6 AS JulyMaxhours,

SUM(6) AS Total_Hours,

KindOfDay, Month

FROM Auxiliary.Calendar

WHERE (KindOfDay = 'Weekday') AND (Year = 2013) AND (Month = 7)

GROUP BY

Year, KindOfDay, Month

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".