SQLTeam.com | Weblogs | Forums

Find records that have same item and postal code in last 7 days rolling


#1

Hi I need to find any work order records from my table in the last 7 days where the itemcode and site postal code are the same. What's the best way to achieve this? My query sql is below:

select wh.worknumber, wh.itemcode, wh.sitepostcode, wh.date_created from worksorderhdr wh
where wh.date_created >GETDATE()-7

Many thanks
Martyn


#2

Try:

[CODE]
SELECT wh.worknumber,
wh.itemcode,
wh.sitepostcode,
wh.date_created

	FROM worksorderhdr wh
	
	WHERE wh.date_created >= DATEADD(d,-7, GETDATE())
	AND wh.itemcode = wh.sitepostcode

[/CODE]


#3

will return a TIME element, so will only select items AFTER that time (7 days ago). More likely the requirement is to include anything during the day, 7 days ago, and at any time after that.

WHERE wh.date_created >= CAST(DATEADD(Day,-7, GETDATE()) AS DATE)

#4

Thanks, but that returns nothing at all (I know there are some there somewhere in the data). I don't understand how wh.itemcode=wh.sitepostcode can work - they would never match. See an exatract of the results below (no matches here but hopefully will help in showing what the data is like:

worknumber itemcode sitepostcode date_created


HU-DN-008933 14MC0019 GU7 1UF 2015-05-14 15:47:00
HU-DN-008934 14MC0047 TA2 6RX 2015-05-14 16:50:00
HU-DN-008935 17MC0003 GU2 7XY 2015-05-15 08:40:00
HU-DN-008936 432A SN14 6ET 2015-05-15 10:12:00
HU-DN-008937 5531A IP3 9BS 2015-05-15 10:58:00

For example, I want to see any records where 14MC0019 at GU7 1UF occurs more than once in the last 7 days.

Thanks
Martyn


#5

Kristen totally agree it will be a time stamp that way without first converting to date, so either CAST or CONVERT will do the trick.

SELECT wh.worknumber,
wh.itemcode,
wh.sitepostcode,
wh.date_created
FROM worksorderhdr wh
WHERE wh.date_created >= CONVERT(DATE, DATEADD(d,-7, GETDATE()))


#6

You mentioned you wanted to return where those fields are the same in your question? If they are not the same fields then of course that wont do, can you elaborate?


#7

It would save us all a lot of time if you could please:

Post sample data - as a CREATE TABLE and INSERT statements

Post expected results

If you just post a table of data then everyone who wants to try to help you has to manually convert that into some code to test their answer. Everyone repeating that means that their time is wasted and/or many/most of the people here won't bother to help you ...

Here is an explanation of what would be helpful:


#8

Agreed.

If you mean though you only want to see records with a greater value than 1 for the criteria then you can use a CTE with ROW_NUMBER().


#9

Still ambiguous though ... "any records" might mean "display one occurrence" (which one??) or "display all occurrences". Ie xpect that Sample Data / Results would clarify


#10

yep sample data would help or the merry go round begins.


#11

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')


#12

Option 1:

select wh.worknumber
      ,wh.itemcode
      ,wh.sitepostcode
      ,wh.date_created
  from (select itemcode
              ,sitepostcode
          from worksorderhdr
         where date_created>=cast(dateadd(dd,-7,getdate()) as date)
         group by itemcode
                 ,sitepostcode
         having count(*)>1
       ) as wh_tmp
       inner join worksorderhdr as wh
               on wh.date_created>=cast(dateadd(dd,-7,getdate()) as date)
              and wh.itemcode=wh_tmp.itemcode
              and wh.sitepostcode=wh_tmp.sitepostcode

Option 2:

select wh.worknumber
      ,wh.itemcode
      ,wh.sitepostcode
      ,wh.date_created
  from worksorderhdr as wh
 where wh.date_created>=cast(dateadd(dd,-7,getdate()) as date)
   and exists (select 1
                 from worksorderhdr as wh_tmp
                where wh_tmp.date_created>=cast(dateadd(dd,-7,getdate()) as date)
                  and wh_tmp.worknumber<>wh.worknumber
                  and wh_tmp.itemcode=wh.itemcode
                  and wh_tmp.sitepostcode=wh.sitepostcode
              )

Option 3:

select worknumber
      ,itemcode
      ,sitepostcode
      ,date_created
  from (select worknumber
              ,itemcode
              ,sitepostcode
              ,date_created
              ,count(*) over(partition by itemcode,sitepostcode) as c
          from worksorderhdr
         where date_created>=cast(dateadd(dd,-7,getdate()) as date)
       ) as wh
 where c>1

#13

I think you only get one notification until you re-visit the forum, then I think (fingers crossed!) you will then get another notification if a further reply is made (but, again, only the one notification).

I would prefer that the text of the Reply was not included in the Notification message - it might well have been changed / fixed / improved after initially being written. You might not bother to read it when you come back again, if you read the text in your notification email ...

And you are right, I think some people get the notification, think "Great, I'll do that" and by not returning to the forum potentially miss out on lots of absolutely brilliant advice :smiley:

If bitsmed's answer is not what you need please post your expected results for your sample data :slight_smile:


#14

Thank you - option 1 worked perfectly for my requirements!

Martyn


#15

Kristen

Your advice, as always, is excellent :grinning:

Many thanks
Martyn