query to count instance since last db update

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

query to count instance since last db update

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-05-14 : 03:34:46

Hi All,

I was wondering if anyone can help me in regards to a query to count the number of days from when a table column was last updated

the tables I am working with are

First table

[EmpKey] [int] NULL,
[LastName] [char](25) NULL,
[FirstName] [char](20) NULL,
[EmpId] [char](9) NULL,
[EmployDt] [datetime] NULL,

The second table

CREATE 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,

Say the IncDate column has this date last

1/1/2010

Then the output table should be

Days

133

I was trying to form the query but kept getting the wrong day values

SELECT COUNT(CASE WHEN DATEDIFF(DAY, GETDATE(), I.IncDate) < 365 THEN 1 END) AS Expr1
FROM irIncident AS I INNER JOIN
pdEmployee AS E ON I.EmpKey = E.EmpKey

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-14 : 03:37:13

this " DATEDIFF(DAY, GETDATE(), I.IncDate) " will give you negative value as the GETDATE() is later than the IncDate

change to

DATEDIFF(DAY, I.IncDate, GETDATE())

KH
[spoiler]Time is always against us[/spoiler]

rc1138
Starting Member

35 Posts

Posted - 2010-05-14 : 03:43:35

Ah I see Thanks!

It is giving me a 0 value now though I think the problem maybe in this section of the query

SELECT COUNT(CASE WHEN DATEDIFF(DAY, GETDATE(), I.IncDate) ***< 365 THEN 1 END****) AS Expr1
FROM irIncident AS I INNER JOIN
pdEmployee AS E ON I.EmpKey = E.EmpKey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources