How to get a count by month

[luvmy2Js]

I have a list of id's that I derived by creating a temp table. From that list I need to get a count by region for each month from Jan. 2009 - Dec. 2010. I am struggling getting this to work? Here is the start of my code:

Create table totalemp# (empid int,geo nvarchar(50))

EXECUTE adm_Supplimental

Insert into totalemp# (empid, geo)

(
Select
a.sapid,
'Geo' = bs.geo
from

employeeall a
LEFT OUTER JOIN cvt_CoCodetoBusSite bs ON bs.companycode = CONVERT(nvarchar, DecryptByKey(a.[Company Code]))

Where
a.sapid > 500000
and
CONVERT(nvarchar, DecryptByKey(a.[Emp Group Name])) <> 'External'
and
CONVERT(nvarchar, DecryptByKey(a.[DS Term Date])) between '20090101' and '20101231'
)

I get the list I need, now I need to take that list and get a count by month where the startdate < "the end of each month" example: 01-01-2009.

Here is how I start out, but how can I carry this all the way across and it now blow up?

Create table totalemp# (empid int,geo nvarchar(50))

EXECUTE adm_Supplimental

Insert into totalemp# (empid, geo)

(

Select
a.sapid,
'Geo' = bs.geo
--'Country Name' = ISNULL(CONVERT(nvarchar, DecryptByKey(a.[country name])),'')
from

employeeall a
LEFT OUTER JOIN cvt_CoCodetoBusSite bs ON bs.companycode = CONVERT(nvarchar, DecryptByKey(a.[Company Code]))

Where
a.sapid > 500000
and
CONVERT(nvarchar, DecryptByKey(a.[Emp Group Name])) <> 'External'
and
(CONVERT(nvarchar, DecryptByKey(a.[DS Term Date])) > '20090101' or CONVERT(nvarchar, DecryptByKey(a.[DS Term Date])) IS NULL)
)

Select count(sapid) as JanCount, geo

From
totalemp# t
inner join employeeall e on e.sapid = t.empid
where CONVERT(nvarchar, DecryptByKey(e.[DS Last Hire Date])) <= '20090131'

Group by geo

Drop table totalemp#

 

[markros]

Quick unrelated suggestion - always specify size for nvarchar in the CONVERT (and other situations). By default it's 30 characters, but it's much better to always be explicit.

PluralSight Learning Library