Apologies for the delay in replying the new forum doesn't seem to be notifying me on each response as the old one did.
OK, I have posted some code below with examples so you can see what I mean. In the example below, I would like the records for work order IP-DN-117824 and IP-DN-118287 to be returned as they were both for itemcode 14MC0003, both at the same postcode GU7 1AP, and both were created within a 7 day window, 11th May and 15th May.
In contrast the record for IP-DN-118201 would not be returned because while the sitepostcode is the same and it falls within a 7 day window, the itemcode is different.
I agree it is difficult to decide how the ‘7 day window’ should work, all I’m trying to do is find records where the itemcode and sitepostcode are the same, and they are not more than 7 days apart in terms of date_created. Essentially, I want to highlight where machines on the same site breakdown on more than one occasion in any 7 day period.
Hope that makes sense.
create table worksorderhdr
(worknumber nvarchar(20),
itemcode nvarchar(20),
sitepostcode nvarchar(60),
date_created smalldatetime)
insert into worksorderhdr
(worknumber, itemcode, sitepostcode, date_created)
values
('IP-DN-117824', '14MC0003', 'GU7 1AP','2015-05-11 17:54:00'),
('IP-DN-117870', '17MC0007', 'CO10 2TG', '2015-05-12 11:28:00'),
('IP-DN-118201', '12MC0017', 'GU7 1AP', '2015-05-15 13:07:00'),
('IP-DN-118242', '14MC0022', 'E5 3AS', '2015-05-15 16:15:00'),
('IP-DN-118265', '15MC0019', 'NG12 4BR', '2015-05-15 16:41:00'),
('IP-DN-118287', '14MC0003', 'GU7 1AP', '2015-05-15 17:08:00'),
('IP-DN-118303', '15MC0016', 'GU7 1AP', '2015-05-16 10:12:00')