Counting activities within a date range

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

General SQL Server Forums

New to SQL Server Programming

Counting activities within a date range

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-06-01 : 19:43:43

Hi folks,

Just wondering if anyone can help me with a subquery problem

I initially had this query

SELECT [Less Than 6 months] = COUNT(CASE WHEN DATEDIFF(Month, E.EmployDt, A.ActDate) < 6 THEN 1 END),
[6 Months - Year] = COUNT(CASE WHEN DATEDIFF(Month, E.EmployDt, A.ActDate) BETWEEN 6 AND 12 THEN 1 END), [1 Year - 5 Years] = COUNT(CASE WHEN DATEDIFF(Month, E.EmployDt, A.ActDate) BETWEEN 13 AND 60 THEN 1 END),

[5 Years - 10 Years] = COUNT(CASE WHEN DATEDIFF(Month, E.EmployDt, A.ActDate) BETWEEN 61 AND 120 THEN 1 END),

[More than 10 Years] = COUNT(CASE WHEN DATEDIFF(Month, E.EmployDt, A.ActDate) > 120 THEN 1 END)
FROM irActivity AS A INNER JOIN pdEmployee AS E ON A.Comp = E.Comp ";
WHERE (E.Department = '" + StoreNum + "') AND (I.HlthCare = 'Y') AND (A.ActDate >= '" + beginDate + "')
AND (A.ActDate <= '" + endDate + "')

And it produces his result
[>6mos]||[6mos1yr]||[1yr5yr]||[5yr10yrs]||[<10yr]
0||1||2||1||0

Now I'm trying to perform this query in a less efficient way using several massive queries and am now getting a different result

This is a part of my new query

SELECT COUNT(A.ActDate) AS [1 Year - 5 Years]
FROM irActivity AS A INNER JOIN pdEmployee AS E ON I.Comp = E.Comp WHERE (DATEDIFF(MONTH,E.EmployDt,I.IncDate) > 12) AND (DATEDIFF(MONTH, E.EmployDt, A.ActDate) < 60) AND
E.Department = '" + StoreNum + "') AND (A.HlthCare = 'Y') AND (A.IncDate >= '" + beginDate + "')
AND (A.IncDate <= '" + endDate + "')) AS [1 Year - 5 Years] CROSS JOIN

SELECT COUNT(A.ActDate) AS [5 Years - 10 Years]
FROM irActivity AS A INNER JOIN pdEmployee AS E ON I.Comp = E.Comp WHERE (DATEDIFF(MONTH,E.EmployDt,I.IncDate) > 60) AND (DATEDIFF(MONTH, E.EmployDt, A.ActDate) < 120) AND
E.Department = '" + StoreNum + "') AND (A.HlthCare = 'Y') AND (A.IncDate >= '" + beginDate + "')
AND (A.IncDate <= '" + endDate + "')) AS [1 Year - 5 Years]

The query result is

[>6mos]||[6mos1yr]||[1yr5yr]||[5yr10yrs]||[<10yr]
0||0||1||1||0

Is the different result due to my datediff syntax ?

Thanks again for the assistance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-02 : 01:46:25

First there should come up an error because you are going on I.IncDate but there is no table with alias I involved.
So I think you are not showing the really used queries!

Second: In your initially query the datediff() goes on ActDate and the other queries are going on IncDate.

How should we know what is wrong or right?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources