Return records not inserted last 7 days.

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

Return records not inserted last 7 days.

Author

Topic

pcpham
Starting Member

10 Posts

Posted - 2011-07-26 : 02:37:21

Hi...

I have a table with equipmentname and usagedate columns. Both columns create unique key.

I need to be able to query equipmentname that has no usagedate entries recorded in the last 7 days.

Thanks in advanced.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-26 : 02:45:42

SELECT EquipmentName FROM dbo.Table1
GROUP BY EquipmentName
HAVING MAX(UsageDate) < DATEADD(DAY, -7, GETDATE())

N 56°04'39.26"
E 12°55'05.63"

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-26 : 02:56:08

It's typically customary to post at least your basic query, but I've definitely been there! So assuming your data looks something like this:

create table EquipmentStatus (Id int primary key identity not null, EquipmentName varchar(10), UsageDate datetime);
insert into EquipmentStatus (EquipmentName, UsageDate) values ('machine', '01/01/2011'), ('tractor', '07/25/2011'), ('rake', null)

Select *
From EquipmentStatus
where DATEDIFF(dd, coalesce(usagedate,'01/01/1900'), getdate())<=7

And I'm sure you'll get a lot more elegant queries from other folks, but this worked if I understand the requirements correctly.

pcpham
Starting Member

10 Posts

Posted - 2011-07-26 : 02:56:14

Thanks....

Is there a way to specifiy which date out of the last 7 days where the record was not recorded? Sometimes they would record just 1 or 2 days out of the last 7 days so I need to know which specif days have no records out of the 7.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-26 : 02:59:37

Oops, you wanted "NO" usagedate. For my solution, you'd use:

Select *
From EquipmentStatus
where DATEDIFF(dd, coalesce(usagedate,'01/01/1900'), getdate())>=7
instead

pcpham
Starting Member

10 Posts

Posted - 2011-07-26 : 03:00:20

Flamblaster:

create table EquipmentStatus (Id int primary key identity not null, EquipmentName varchar(10), UsageDate datetime);
insert into EquipmentStatus (EquipmentName, UsageDate) values ('machine', '20/07/2011'), ('machine', '21/07/2011'), ('rake', '22/07/2011')

The result should be Machine and the dates 22/7 to 26/7.
and rake 20/7, 21/7, 23/7 - 26/7.

pcpham
Starting Member

10 Posts

Posted - 2011-07-26 : 03:03:26

Sorry....not sure if the create table is what i've got as there would be no entry for the equipment if it was not used on the date. I need to tell users to enter equipment usage date and hours.

create table EquipmentStatus (EquipmentName varchar(10), UsageDate datetime, UsageHours int);

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-26 : 03:13:54

Ok, is the equipment coming from another lookup table? Or is it in the status table?

So for example, does your data look more like this:

create table Equipment (Equipmentid int primary key identity not null, EquipmentName varchar(10));
create table EquipmentStatus (EquipmentStatusid int primary key identity not null, EquipmentId int, UsageDate datetime, UsageHours int);

insert into Equipment (EquipmentName) Values ('Machine'), ('Rake'), ('Tractor');
insert into EquipmentStatus (EquipmentId, UsageDate, UsageHours)
Values (1, '07/21/2011', 10), (3, '07/25/2011', 13)

select *
from Equipment E
left outer join EquipmentStatus ES on ES.EquipmentId=E.EquipmentId
where (es.Equipmentid is null or DATEDIFF(dd, coalesce(usagedate,'01/01/1900'), getdate())>=7)

edit: Added the datediff to the where clause

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-26 : 03:33:18

quote:
Originally posted by pcpham

Thanks....

Is there a way to specifiy which date out of the last 7 days where the record was not recorded? Sometimes they would record just 1 or 2 days out of the last 7 days so I need to know which specif days have no records out of the 7.

This is a completely new requirement...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources