June 18, 2008 at 4:10 pm
Hi all, I have an issue I am thinking has to be some what common, for now however it has me baffled on the method to go about this. (Could be creating too many similar ones in a short time, stuck in one train of thinking).
What I have is a list of customers, and dates of service, what I need to identify is which dates of service for each customer are less than 24 months apart (accurate to day not hours etc). That is less 24 months between consecutive visits. So between 1/1/2000 and 6/1/2000 and 6/1/2004 would be 6 months, and 48 months..
1/1/2000 to 6/1/2000 = 6 months
6/1/2000 to 6/1/2004 = 48 months
and I need the 6/1/2000 to be the item returned, as it is only 6 months from the first date of service.
I have been working on similar date calculations for the last few days, but none were rolling periods. That seems to be where I am getting lost.
I want to work through the details of this one myself, so as to understand it better, I am sure there will come another time in this buisiness I need to do this again. So I would more like a pointer in the right direction, rather than nice running and pretty code. (well, until the deadline gets closer, heh.)
So data such as...
CustomerDOS
A20030812
A20000510
A20010605
A20020722
A20040416
B20020128
B20030317
C19990101
C20080501
Thanks
June 18, 2008 at 4:47 pm
Well, your using SQL Server 2000 so you can't take advantage of a CTE or any of the new ROW_NUMBER() or ranking functions.
You'll need a way to order the values grouped by Customer so I'd start by inserting them into a table variable with an IDENTITY column using an ORDER BY Customer, DOS clause. Once you've got them in there, you can do a self join using the IDENTITY values to offset the 2 recordsets so you're comparing the DOS with the previous DOS for that Customer. Make sense? There may be a better way, but this should get you started.
June 18, 2008 at 4:47 pm
The easiest way is to left join each row to the prior row for the same customer, and do a difference between the dates from the current and prior row to find the ones where the gap is less then 24 months.
The real trick is joining the table to itself to get the prior row, but since you didn’t want code, I’m sure you can work that part out yourself.
June 18, 2008 at 5:10 pm
Michael Valentine Jones (6/18/2008)
The easiest way is to left join each row to the prior row for the same customer, and do a difference between the dates from the current and prior row to find the ones where the gap is less then 24 months.The real trick is joining the table to itself to get the prior row, but since you didn’t want code, I’m sure you can work that part out yourself.
All I can add to that might be to remember what the only thing ORDER BY guarantees during an insert....could be useful in this case....
You did say you wanted to be pointed in the right direction, right?:w00t:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 19, 2008 at 8:18 am
Thanks! I had not thought about using an identity column, that was my sticking point, finding how to match to the prior row.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply