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 the number of activities in a date range

Author  Topic  rc1138 Starting Member 35 Posts Posted - 2010-06-02 : 21:24:48 Hello all I have a massive sql query with multiple subqueries SELECT [1yr5yr].[1yr5yr],[5yr10yr].[5yr10yr] FROM (SELECT COUNT(I.IncDate) AS [1 Year - 5 Years] FROM irIncident AS I INNER JOIN pdEmployee AS E ON I.Comp = E.Comp WHERE (DATEDIFF(MONTH,E.EmployDt,I.IncDate) > 12) AND (DATEDIFF(MONTH, E.EmployDt, I.IncDate) < 60) AND E.Department = '1000') AND (I.HlthCare = 'Y') AND (I.IncDate >= '1/1/1996') AND (I.IncDate <= '1/1/2000')) AS [1 Year - 5 Years] CROSS JOIN (SELECT COUNT(I.IncDate) AS [5yr10yr] FROM irIncident AS I INNER JOIN pdEmployee AS E ON I.Comp = E.Comp WHERE (DATEDIFF(MONTH,E.EmployDt,I.IncDate) > 60) AND (DATEDIFF(MONTH, E.EmployDt, I.IncDate) < 120) AND E.Department = '1000') AND (I.HlthCare = 'Y') AND (I.IncDate >= '1/1/1996') AND (I.IncDate <= '1/1/2000')) AS [5yr10yr] it produces this result [1yr5yr]||[5yr10yrs] 0||1 Problem is If I actually count the items in the database the result produced is incorrect. I guess the question would be are the filters I'm placing on my datediff statements even close to being correct ? How many employees received incidents within the 1 year to 5 years of their employment (SELECT COUNT(I.IncDate) AS [1 Year - 5 Years] FROM irIncident AS I INNER JOIN pdEmployee AS E ON I.Comp = E.Comp WHERE (DATEDIFF(MONTH,E.EmployDt,I.IncDate) > 12) AND (DATEDIFF(MONTH, E.EmployDt, I.IncDate) < 60) AND E.Department = '1000') AND (I.HlthCare = 'Y') AND (I.IncDate >= '1/1/1996') Thanks again for the assist