Count if month is X: converting from Access to SQL

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 2008 Forums

Transact-SQL (2008)

Count if month is X: converting from Access to SQL

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-25 : 04:13:51

Hi,

I am converting an Access query to SQL. The query does a monthly count of a date field, running from January to December. Is there a way of telling SQL to count a record if the month is, for example "02" (Feb)? In Access the code is:

"Feb: Sum(IIf(Month([CodeActionDate])=2,1,0))"

How can I reproduce that in SQL please?

Thanks

Jim

Jim

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-25 : 04:20:33

something like below can be done:


SELECT SUM(case when month(CodeActionDate)=2 then 1 else 0 end)
from sometable

Harsh Athalye
http://www.letsgeek.net/

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-25 : 04:31:56

Thanks harsh_athalye, i'll give it a try today. If I wanted to do a full year, am I best doing a sub-query in my code for each month?

Thanks again,

Jim

Jim

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-25 : 19:30:10

quote:
Originally posted by jimtimber

If I wanted to do a full year, am I best doing a sub-query in my code for each month?

No!

SELECT month(CodeActionDate), count(*) CountPerMonth
from sometable
group by month(CodeActionDate)
order by month(CodeActionDate)

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-26 : 05:26:28

Hi Busta,

That works also, however if the month has no data, it ignores the month. Is there a way of saying if there is no data, then return a zero for that month?

Jim

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-26 : 05:30:38

I should add, I've added 'OfficeCode' into the query, so when the query runs, it displays each month there is data for each office.

Thanks
Jim

Jim

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-26 : 05:50:25

Try this:

select m.monthnum, s.OfficeCode, count(*)
from (select [number] as monthnum from master.dbo.spt_values where name is null and [number] between 1 and 12) m
left join sometable s on month(s.CodeActionDate) = m.monthnum
group by m.monthnum, s.OfficeCode
order by m.monthnum, s.OfficeCode

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-09-26 : 07:15:19

This works, thank you. However, if I put a date parameter on it, it garbles it all up?

SELECT TOP (100) PERCENT m.monthnum, s.TeamAgencyCode, COUNT(*) AS Expr1
FROM (SELECT number AS monthnum
FROM master.dbo.spt_values
WHERE (name IS NULL) AND (number BETWEEN 1 AND 12)) AS m LEFT OUTER JOIN
dbo.Tbl_Progress AS s ON MONTH(s.CodeActionDate) = m.monthnum
Where (dbo.Tbl_Progress.CodeActionDate BETWEEN @StartDate AND @EndDate)
GROUP BY s.TeamAgencyCode, m.monthnum
ORDER BY s.TeamAgencyCode, m.monthnum

Also, is there a way of adding years to it? So, if the user select 09/09/13 to 09/09/14, it would run in a smooth order?

Thanks again, sorry this is getting more complicated!!

Jim

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-26 : 14:37:19

quote:

it garbles it all up?

Could you elaborate?

quote:
Also, is there a way of adding years to it?

Yes, GROUP BY code, month and year

quote:
This is getting more complicated!!

Ya think?

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-10-03 : 07:42:04

Hi Bustaz Kool, I've managed to get the query to do what I was wanting now. Thank you for pointing me in the right direct with you help above.

Thanks again

Jamie

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources