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.
| Author |
Topic |
|
rc1138
Starting Member
35 Posts |
 Posted - 2010-05-13 : 23:12:20
|
| Hi All,I was wondering if anyone can help me in regards to a query to count the number of instances within certain time periodsthe tables I am working with areFirst table [EmpKey] [int] NULL, [LastName] [char](25) NULL, [FirstName] [char](20) NULL, [EmpId] [char](9) NULL, [EmployDt] [datetime] NULL, The second tableCREATE TABLE [dbo].[irIncident]( [IncNo] [int] NULL, [IncDate] [datetime] NULL, [IncType] [int] NULL, [EmpKey] [int] NULL, [ClaimNo] [char](15) NULL, [LostTime] [char](1) NULL, [HlthCare] [char](1) NULL, [FirstAid] [char](1) NULL, The Query output should be[Less Than 6 months] [6 months - year] [1yr - 5yrs] [5yrs - 10yrs] 0 0 0 0To populate these columns you would need to measure the [EmployDt] Date from the first table to the [IncDate] date on the second tableAny help would be greatly appreciated |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-13 : 23:20:02
|
[code]select [Less Than 6 months] = count(case when datediff(month, f.EmployDt, s.IncDate) < 6 then 1 end), [6 months - year] = count(case when datediff(month, f.EmployDt, s.IncDate) between 6 and 12 then 1 end), [1yr - 5yrs] = count(case when datediff(month, f.EmployDt, s.IncDate) between 13 and 60 then 1 end), [5yr - 10yrs] = count(case when datediff(month, f.EmployDt, s.IncDate) between 61 and 120 then 1 end)from [First table] f inner join [Second table] s on f.EmpKey = s.EmpKey[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
rc1138
Starting Member
35 Posts |
Posted - 2010-05-14 : 03:28:05
|
| Awesome this worked perfectly |
|
|
|
|
|
|
|
|
Query to count instances within dates