February 2, 2012 at 2:36 pm
Hi-
I have a table that has every order placed.
Something like this
customer_id, order_code_id, order_date, order_fulfill_date, customer_order_number
I want to write something that compares the first order and second order for each customer to see if they were greater than 4 weeks apart.
Ideally, I would then want to update a field I would create in the same table indicating if the time period between 1st and second order was over 4 weeks or not for the second order. .
This must be pretty straightforward expecially since I have a sequential order number to work with but can't wrap my head around it. Been trying to get outer apply to work (seems like the approach I need with the self join?)
Many thanks
Paul
February 2, 2012 at 3:06 pm
Paul,
I know this isn't going to help too much (as this is asked in a 2008 forum) but this seems like an ideal problem for the new LAG and LEAD Analytic functions in SQL Server 2012.
Some sample data would help. Can you elaborate on whether the sequential order number is unique on the table or unique per customer.
Dave
February 2, 2012 at 3:13 pm
Unique per customer..
I think I have it though, kidnof.
I did just upgrade to 2012 RCO (yesterday), but am a little wary of the new functions .
SELECT a.customer_id, datediff("d", a.order_date, f.order_date) as diff1to2
--INTO #A
FROM order_valid a
OUTER Apply (SELECT b.order_date FROM order_valid b WHERE a.customer_id = b.customer_id and b.order_number=2)f
WHERE a.order_number = 1
UPDATE order_valid
SET valid = 0
WHERE customer_id IN (SELECT customer_id FROM #A
WHERE diff1to2 < 32 AND diff1to2 is not null)
AND order_number= 2
Seems to do what I am wanting.
Definitely open to a better way!
Thanks,
Paul
February 2, 2012 at 3:22 pm
Paul,
Take a look at http://msdn.microsoft.com/en-us/library/hh231256(v=sql.110).aspx. Unfortunately, I don't have 2012 installed. However I have used LAG and LEAD with an Oracle database previously.
Dave
February 2, 2012 at 3:27 pm
paulgill (2/2/2012)
Seems to do what I am wanting.Definitely open to a better way!
I'm afraid I have almost no understanding of LAG and LEAD, however, this isn't an uncommon scenario. Your exact code will work wonderfully for orders 1 and 2, but what about 29 and 30? Is that ever a concern? Are the order numbers always ungapped and partitioned by customer ID?
If you provide some sample data with a few customers in it, I can hand you a pretty simple solution that will match each record to its previous one via either self-joins or cross applys (or a subquery MAX(), depending), which from there you'll be able to do your datediff calculations. See the first link in my sig if you'd like a walkthrough on what we're looking for regarding sample schema/data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 2, 2012 at 3:29 pm
Dave Brooking (2/2/2012)
Paul,Take a look at http://msdn.microsoft.com/en-us/library/hh231256(v=sql.110).aspx. Unfortunately, I don't have 2012 installed. However I have used LAG and LEAD with an Oracle database previously.
Dave
Very interesting, thanks for the link Dave. I'll be very curious to see what optimizations exist on that for indexing usage in regards to the forced partitioning.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 2, 2012 at 3:39 pm
Paul,
From the earlier postings and my recollections of LAG and LEAD what does the following produce
select a.customer_id
,a.order_date
,LAG(a.order_date) OVER (PARTITION BY a.customer_id, a.customer_order_number) as previous_order_date
from order_valid a
Dave
February 3, 2012 at 10:19 am
Will this work?
I think I can do this for all orders, but it would be alot of hopefully unnecessary work.
Interested in your method.
Thanks!
paul
customer_idorder_dateorder_numbervalid
192322007-10-08 00:00:00.0001NULL
192322008-06-12 00:00:00.0002NULL
192322009-07-01 00:00:00.0003NULL
380402009-01-14 00:00:00.0001NULL
380402009-03-16 00:00:00.0002NULL
380402009-08-24 00:00:00.0003NULL
399842008-08-29 00:00:00.0001NULL
399842008-11-19 00:00:00.0002NULL
399842010-09-09 00:00:00.0003NULL
401332010-08-13 00:00:00.0001NULL
401332011-08-23 00:00:00.0002NULL
403692011-08-12 00:00:00.0001NULL
403692011-11-08 00:00:00.0002NULL
404752009-03-31 00:00:00.0001NULL
405302008-03-31 00:00:00.0001NULL
405302008-06-02 00:00:00.0002NULL
405302008-10-06 00:00:00.0003NULL
405382011-04-08 00:00:00.0001NULL
405862007-10-30 00:00:00.0001NULL
405862008-01-15 00:00:00.0002NULL
405862008-06-17 00:00:00.0003NULL
406572007-04-17 00:00:00.0001NULL
406572007-06-26 00:00:00.0002NULL
407062010-12-21 00:00:00.0001NULL
407062011-08-16 00:00:00.0002NULL
407492011-08-11 00:00:00.0001NULL
407492011-10-19 00:00:00.0002NULL
408082010-04-07 00:00:00.0001NULL
408292011-08-12 00:00:00.0001NULL
408862007-08-14 00:00:00.0001NULL
408862007-10-30 00:00:00.0002NULL
408862008-10-14 00:00:00.0003NULL
408872007-08-14 00:00:00.0001NULL
408872007-10-30 00:00:00.0002NULL
408872008-10-14 00:00:00.0003NULL
409402010-08-12 00:00:00.0001NULL
409402011-09-08 00:00:00.0002NULL
409542007-08-27 00:00:00.0001NULL
409552007-09-25 00:00:00.0001NULL
409552007-11-29 00:00:00.0002NULL
409552008-05-06 00:00:00.0003NULL
409562010-11-29 00:00:00.0001NULL
409562011-02-16 00:00:00.0002NULL
409562011-06-16 00:00:00.0003NULL
410422010-08-18 00:00:00.0001NULL
410422010-09-28 00:00:00.0002NULL
410422011-02-22 00:00:00.0003NULL
410522010-09-14 00:00:00.0001NULL
410892008-06-10 00:00:00.0001NULL
410892010-03-08 00:00:00.0002NULL
February 3, 2012 at 10:24 am
Hi Dave,
I think your lag is missing an argument, it just errors out.
I will mess with it though as it does seem perfect for this.
Thanks,
Paul
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply