Counting months based on number of hours in month

[luvtitans50]

I have the below code:

select p.employeeid, sum (actualunits) as actualunits
from ss_hcsscsa.dbo.prchecktc t
inner join ss_hcsscsa.dbo.prcheckheader p (NOLOCK) on p.checkhistid = t.checkhistid
where t.paycodeid in (1,2,3,4,5,6,7) and checkdate between '2009-01-01' and '2009-12-31'
group by employeeid

Now what I need to do is list how many of the months out of the year had 1000 actualunits: count distinct months where sum (actualunits) = 1000

Any suggestions?

Thanks!!

 

[bborissov]

Not sure if I understand you.
Could you please provide some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

1. What is your SQL Server version.

2. You can use Datapart function to find out the month of the CheckDate or you can simply use Month(CheckDate)

e.g.

select p.employeeid, Month(checkDate) as [Month]
sum (actualunits) as actualunits    from ss_hcsscsa.dbo.prchecktc t     inner join ss_hcsscsa.dbo.prcheckheader p (NOLOCK) on p.checkhistid = t.checkhistid    
where t.paycodeid in (1,2,3,4,5,6,7) and checkdate between '2009-01-01' and '2009-12-31' 
   group by p.employeeid, Month(checkDate)
having sum(actualunits) = 1000

PluralSight Learning Library

 

[markros]

I missed comma between [Month] and Sum

PluralSight Learning Library

 

[luvtitans50]

I'm using SQL Server 2005


Sample data is:

Name TotalHours
Joe Smith 1,288
Don King 650
James Doe 875

End result:

Name TotalHours MonthsWorked
Joe Smith 1,288 8
Don King 650 5
James Doe 875 7

I need to know how many months out of the year they worked?

 

[markros]

In your initial query you can add count(Month(CheckDate)) as MonthsWorked

PluralSight Learning Library

 

[bborissov]

Maybe better:

....
count(DISTINCT Month(CheckDate)) as MonthsWorked 
...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Yes, you need DISTINCT, you're right. Thanks.

PluralSight Learning Library