April 22, 2008 at 8:07 am
Hello,
I have a table dbo.service_ orders with about 200.000 records.
Some of the fields in those records are service_engineer, ID_code, service_date, repeat_call
We wish to see if there is a repeat call for an ID code, this when the the date difference is <= 30 days. If there is, the field repeat_call must have a value of 1 else 0.
So the service_engineer from the first call causes the repeat call and gets the 1.
Can somebody help me with solving this problem.
.
April 22, 2008 at 3:32 pm
Try this:
Update dbo.service_orders
Set repeat_call = 1
From dbo.service_orders O
Where Repeat_call = 0
And Exists(Select * From dbo.service_orders o2
Where o2.ID_Code = o.ID_Code
And DateDiff(dd, o2.service_date, o.service_date) <= 30 )
Sorry, I cannot tell from your description if you want the the service_engineer field in the new record changed or not.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 23, 2008 at 1:16 am
Hey Barry,
thanks for your quick response, It works but first all repeat_call became 1. I had to add an other line that 02.service_date > o.service_date.
But now it's okay.
BR Wim
😛
April 23, 2008 at 10:03 am
Oops, good point.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply