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/
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -