April 8, 2015 at 10:25 am
Assuming you did you could end up with something like this to combine your last order with your average order days:
With ordersCTE as (
SELECT ROW_NUMBER() OVER (
PARTITION BY ompCustomerOrganizationID ORDER BY ompcreateddate DESC
) as RN
,s.ompSalesOrderID
,s.ompCustomerOrganizationID
,ompcreateddate
,convert(VARCHAR(10), ompcreateddate, 101) AS order_dat
FROM SalesOrders s
WHERE ompCreatedDate >= '01-01-15'
),
AvgOrderLagCTE as ( --incorporating Dwain's query and adapting to your table and columns
SELECT ompCustomerOrganizationID
,Avg_days_between_orders=DATEDIFF(day, MIN(ompcreateddate), MAX(ompcreateddate))/(COUNT(*)-1.)
FROM SalesOrders
WHERE ompCreatedDate >= '01-01-15'
GROUP BY ompCustomerOrganizationID
HAVING COUNT(*) > 1
)
select o.ompSalesOrderID
,o.ompCustomerOrganizationID
,o.ompcreateddate as last_ORDER_DATE
,o.order_dat
,coalesce(a.Avg_days_between_orders,0) as AverageDays
from orderscte o left join
AvgOrderLagCTE a on o.ompCustomerOrganizationID=a.ompCustomerOrganizationID
where o.rn=1 --this returns only the LAST order from ordersCTE
----------------------------------------------------------------------------------
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?
April 8, 2015 at 4:44 pm
I'm not seeing why you can't just use the code below. I'm sure someone can quickly explain the part of the problem that I'm missing :-).
SELECT cust_id,
DATEDIFF(DAY, MIN(order_date), MAX(order_date)) * 1.0 /
(COUNT(*) - 1) AS avg_days_between_orders
FROM @orders
GROUP BY cust_id
Edit: split calc into multiple lines.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2015 at 5:52 pm
ScottPletcher (4/8/2015)
I'm not seeing why you can't just use the code below. I'm sure someone can quickly explain the part of the problem that I'm missing :-).
SELECT cust_id,
DATEDIFF(DAY, MIN(order_date), MAX(order_date)) * 1.0 /
(COUNT(*) - 1) AS avg_days_between_orders
FROM @orders
GROUP BY cust_id
Edit: split calc into multiple lines.
Looks a lot like the one I posted above, except you forgot to include the HAVING clause to avoid division by zero when a customer only has a single order.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 8, 2015 at 5:56 pm
kat35601 (4/8/2015)
One more question I need to add the last order date to this and then add the AVg_days_between_orders to it so that I get Last_order_Date + Avg_days_between_orders
WITH ordersAS -- add a rownumber to @orders
(
(
SELECT rn = (
ROW_NUMBER() OVER (
PARTITION BY ompCustomerOrganizationID ORDER BY ompcreateddate
)
)
,s.ompSalesOrderID
,s.ompCustomerOrganizationID
,ompcreateddate
,convert(VARCHAR(10), ompcreateddate, 101) AS order_dat
FROM SalesOrders s
WHERE ompCreatedDate >= '01-01-15'
)
)
,prev_orders AS -- get previous orders for that customer
(
SELECT o1.*
,order_date_prev = o2.ompcreateddate
FROM orders o1
JOIN orders o2 ON o1.rn = o2.rn + 1
AND o1.ompCustomerOrganizationID = o2.ompCustomerOrganizationID
)
,days_between
AS -- calculate days between customers' orders
(
SELECT ompCustomerOrganizationID
,Avg_days_between_orders = AVG(DATEDIFF(DD, order_date_prev, ompcreateddate)) OVER (PARTITION BY ompCustomerOrganizationID)
FROM prev_orders
)
-- The max technique should be better than doing a DISTINCT
SELECT ompCustomerOrganizationID
,Avg_days_between_orders = MAX(Avg_days_between_orders)
FROM days_between
GROUP BY ompCustomerOrganizationID;
This is really quite simple.
SELECT cust_id, Avg_days_between_orders, Last_order_date
,Projected_next_order_date=DATEADD(day
,Avg_days_between_orders
,Last_order_date)
FROM
(
SELECT cust_id
,Avg_days_between_orders=DATEDIFF(day, MIN(order_date), MAX(order_date)) /
CASE COUNT(*) WHEN 1 THEN NULL ELSE (COUNT(*)-1.) END
,Last_order_date=MAX(order_date)
FROM @orders
GROUP BY cust_id
) a;
You may want to ROUND(Avg_days_between_orders, 0) where it appears in the DATEADD depending on what suits your needs.
Notice how I've changed the handling of divide by zero errors for the single order case. Add one row to Alan.B's test data:
INSERT INTO @orders VALUES
(1, newid(), '1/1/2014'),
(1, newid(), '1/6/2014'),
(1, newid(), '1/12/2014'),
(1, newid(), '1/15/2014'),
(2, newid(), '1/1/2014'),
(2, newid(), '1/10/2014'),
(2, newid(), '1/21/2014'),
(3, newid(), '1/1/2014'),
(3, newid(), '1/20/2014'),
(3, newid(), '1/30/2014'),
(4, newid(), '1/30/2014');
You'll then get these results:
cust_id Avg_days_between_orders Last_order_dateProjected_next_order_date
1 4.666666666666 2014-01-15 2014-01-19
2 10.000000000000 2014-01-21 2014-01-31
3 14.500000000000 2014-01-30 2014-02-13
4 NULL 2014-01-30 NULL
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 8, 2015 at 10:25 pm
dwain.c (4/8/2015)
ScottPletcher (4/8/2015)
I'm not seeing why you can't just use the code below. I'm sure someone can quickly explain the part of the problem that I'm missing :-).
SELECT cust_id,
DATEDIFF(DAY, MIN(order_date), MAX(order_date)) * 1.0 /
(COUNT(*) - 1) AS avg_days_between_orders
FROM @orders
GROUP BY cust_id
Edit: split calc into multiple lines.
Looks a lot like the one I posted above, except you forgot to include the HAVING clause to avoid division by zero when a customer only has a single order.
Sorry, I was pulled away quite a while working on something, didn't see your subsequent post.
Restricting it may or not be correct, might need to list the custs with only a single order with the "avg" being the single DATEDIFF value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply