• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Find items not ordered this time but order in previous order

ms sql 2008

we have standing orders (standing_item_id), and general orders (item_id) for customers.

We want the last date, qty and prev qty for items - but for standing orders only, if they ordered an item the previous standing order but not the current standing order we want to show current date for standing order with qty of 0 for those items.

Table
custid, date,     qty, standing_item_id, item_id
1       6/30/12    30       200                 null
1       3/31/12    20       200                 null
1       3/31/12    20       250                 null
1       6/30/12    30       null                 400  
1       3/31/12    20       null                 400  
1       3/31/12    20       null                 500

Needed:
1) for standing items, the difference from the previous standing order and when no order this time for an item the qty will be 0, with the difference being -prev_qty
2) for regular order, just the last order and difference from last time the order the same item - not the last time they place any general order  

Results (showing last orders, with 0 for standing orders with item not order this time but ordered in previous standing order):

custid, date,      qty, standing_item_id,  item_id, prev qty , chg
1       6/30/12    30       200                       null,     20         10
1       6/30/12    0         250                      null       20        -10
1       6/30/12    30       null                     400       10           10
1       3/31/12    20       null                      500       0            20

Need a sql query that accomplishes that.....
0
freshgrill
Asked:
freshgrill
  • 3
  • 2
  • 2
  • +1
1 Solution
 
fhlio_adminCommented:
WITH LAST_ORDER_DATES AS (
	SELECT T1.custid, MAX(T1.date) AS [date]
	FROM Table T1
), PRIOR_ORDER_DATES AS (
	SELECT T1.custid, MAX(T2.date) AS [date]
	FROM 
		Table T1
		INNER JOIN Table T2 ON T1.custid = T2.custid AND T1.date <> T2.date
), ORDER_DATES AS (
	SELECT LOD.custid, LOD.date
	FROM LAST_ORDER_DATES LOD
	UNION SELECT POD.custid, POD.date
	FROM PRIOR_ORDER_DATES POD
), STANDING_ITEMS AS (
	SELECT DISTINCT T1.custid, T1.standing_item_id
	FROM 
		Table T1
		INNER JOIN ORDER_DATES OD ON T1.custid = OD.custid AND T1.date = OD.date
	WHERE T1.standing_item_id IS NOT NULL
), REGULAR_ITEMS AS (
	SELECT DISTINCT T1.custid, T1.item_id
	FROM 
		Table T1
		INNER JOIN ORDER_DATES OD ON T1.custid = OD.custid AND T1.date = OD.date
	WHERE T1.item_id IS NOT NULL
), ITEMS AS (
	SELECT SI.custid, SI.standing_item_id, NULL AS item_id
	FROM STANDING_ITEMS SI
	UNION SELECT RI.custid, NULL AS standing_item_id, RI.item_id
	FROM REGULAR_ITEMS RI
)		

SELECT 
	LOD.custid, LOD.date, 
	(CASE WHEN T1.qty IS NULL THEN ISNULL(T2.qty, 0) ELSE T1.qty END) AS qty, 
	T1.standing_item_id, T2.item_id, 
	(CASE WHEN T3.qty IS NULL THEN ISNULL(T4.qty, 0) ELSE T3.qty END) AS [prev qty], 
	(CASE WHEN T1.qty IS NULL THEN ISNULL(T2.qty, 0) ELSE T1.qty END) - 
	(CASE WHEN T3.qty IS NULL THEN ISNULL(T4.qty, 0) ELSE T3.qty END) AS chg 
FROM 
	LAST_ORDER_DATES LOD 
	INNER JOIN ITEMS I ON LOD.custid = I.custid 
	LEFT JOIN Table T1 ON LOD.custid = T1.custid AND LOD.date = T1.date AND I.standing_item_id = T1.standing_item_id
	LEFT JOIN Table T2 ON LOD.custid = T2.custid AND LOD.date = T2.date AND I.item_id = T2.item_id
	LEFT JOIN PRIOR_ORDER_DATES POD ON LOD.custid = POD.custid 
	LEFT JOIN Table T3 ON POD.custid = T3.custid AND POD.date = T3.date AND I.standing_item_id = T3.standing_item_id
	LEFT JOIN Table T4 ON POD.custid = T4.custid AND POD.date = T4.date AND I.item_id = T4.item_id

Open in new window

0
 
awking00Commented:
Your question is rather confusing to me. Can you provide an explanation of how each of the values in your expected results are derived?
0
 
lwadwellCommented:
I am still a little confused as two what exactly you need.  This is perhaps due to your example being over simplified.

I understand this result standing orders ...
    custid, date,      qty, standing_item_id,  item_id, prev qty , chg
    1       6/30/12    30       200                       null,     20         10
    1       6/30/12    0         250                      null       20        -10
these differed from the one order to the next ... either by amount or existence.

I understood this result for regular orders ...
    custid, date,      qty, standing_item_id,  item_id, prev qty , chg
    1       6/30/12    30       null                     400       10           10
it differed by amount from the current to previous.  You mentioned that this should be against the last order for that item ... but as you only had two dates for your example I wasn't sure of the situation.

This one has me confused ... it occurred only in the sample data and is a regular order ... so I am not sure why it is being reported.
    custid, date,      qty, standing_item_id,  item_id, prev qty , chg
    1       3/31/12    20       null                      500       0            20

This is my attempt ... it may help (NOTE: my test data differs a little to try and cover the regular order rule).
IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL
	DROP TABLE #tmp_data

CREATE TABLE #tmp_data (
    custid      int,
    order_date  date,
    qty         int,
    standing_item_id    int,
    item_id     int
);

INSERT INTO #tmp_data (custid, order_date, qty, standing_item_id, item_id)
select 1 as custid, cast('06/30/12' as date) as order_date, 30 as qty, 200  as standing_item_id, null as item_id union all
select 1 as custid, cast('06/30/12' as date) as order_date, 30 as qty, null as standing_item_id,  400 as item_id union all  
select 1 as custid, cast('06/30/12' as date) as order_date, 20 as qty, null as standing_item_id,  500 as item_id union all
select 1 as custid, cast('03/31/12' as date) as order_date, 20 as qty, 200  as standing_item_id, null as item_id union all
select 1 as custid, cast('03/31/12' as date) as order_date, 20 as qty, 250  as standing_item_id, null as item_id union all  
select 1 as custid, cast('03/31/12' as date) as order_date, 80 as qty, null as standing_item_id,  500 as item_id union all   
select 1 as custid, cast('01/31/12' as date) as order_date, 20 as qty, null as standing_item_id,  400 as item_id ;


with standing as (
select custid, order_date, qty, standing_item_id, item_id, dense_rank()over(order by order_date desc) as orderid
  from #tmp_data
 where standing_item_id is not null
), nonstanding as (
select custid, order_date, qty, standing_item_id, item_id, dense_rank()over(partition by custid, item_id order by order_date desc) as orderid
  from #tmp_data
 where item_id is not null
)
select r.custid
     , r.order_date
     , isnull(c.qty,0) as qty
     , isnull(c.standing_item_id,p.standing_item_id) standing_item_id
     , isnull(c.item_id,p.item_id) item_id
     , p.qty           as prev_qty
     , isnull(c.qty,0) - p.qty as chg
  from (select distinct custid, order_date, orderid from standing) r
       join standing p
         on r.orderid = p.orderid-1 
            and r.custid = p.custid
  left join standing c
         on r.orderid = c.orderid 
            and r.custid = c.custid
            and p.standing_item_id = c.standing_item_id
            and p.qty != c.qty
union all
select r.custid
     , r.order_date
     , isnull(c.qty,0) as qty
     , isnull(c.standing_item_id,p.standing_item_id) standing_item_id
     , isnull(c.item_id,p.item_id) item_id
     , p.qty           as prev_qty
     , isnull(c.qty,0) - p.qty as chg
  from (select distinct custid, order_date, orderid from nonstanding) r
       join nonstanding p
         on r.orderid = p.orderid-1 
            and r.custid = p.custid
  left join nonstanding c
         on r.orderid = c.orderid 
            and r.custid = c.custid
            and p.item_id = c.item_id
            and p.qty != c.qty
order by r.custid, r.order_date desc, isnull(c.standing_item_id,p.standing_item_id)

drop table #tmp_data;

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
freshgrillAuthor Commented:
@ lwadwell: Your solution almost works but it gives the wrong answers on standing orders same qty both current and previous.

6/30 qty 30 item A
3/31 qty 30 item A

Result should be Curr qty: 30 , prev qty  30 change 0
From your command I get: Curr Qty: 0 , prev qty 30 change -30

I am still checking the other possible situations.
0
 
fhlio_adminCommented:
Has my code been checked?
0
 
freshgrillAuthor Commented:
I had to make the following changes for it to work:

1) for LOD and POD I had to add group by custid
2) in POD change t1.date <> t2.date to t1.date > t2.date
3) for items that were not ordered this time, but was last time no standing_item_number would show, the qty would (and would be correct). So in the final select list I changed T1.standing_item_id to coalesce(T1.standing_item_id,T3.standing_item_id).

But everything appears to be working correctly.
0
 
fhlio_adminCommented:
You are very gracious.  Thank you.
0
 
lwadwellCommented:
IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL
	DROP TABLE #tmp_data

CREATE TABLE #tmp_data (
    custid      int,
    order_date  date,
    qty         int,
    standing_item_id    int,
    item_id     int
);

INSERT INTO #tmp_data (custid, order_date, qty, standing_item_id, item_id)
select 1 as custid, cast('06/30/12' as date) as order_date, 30 as qty, 200  as standing_item_id, null as item_id union all
select 1 as custid, cast('06/30/12' as date) as order_date, 30 as qty, null as standing_item_id,  400 as item_id union all  
select 1 as custid, cast('06/30/12' as date) as order_date, 20 as qty, null as standing_item_id,  500 as item_id union all
select 1 as custid, cast('06/30/12' as date) as order_date, 22 as qty, null as standing_item_id,  222 as item_id union all
select 1 as custid, cast('03/31/12' as date) as order_date, 20 as qty, 200  as standing_item_id, null as item_id union all
select 1 as custid, cast('03/31/12' as date) as order_date, 20 as qty, 250  as standing_item_id, null as item_id union all  
select 1 as custid, cast('03/31/12' as date) as order_date, 80 as qty, null as standing_item_id,  500 as item_id union all   
select 1 as custid, cast('03/31/12' as date) as order_date, 22 as qty, null as standing_item_id,  222 as item_id union all   
select 1 as custid, cast('01/31/12' as date) as order_date, 20 as qty, null as standing_item_id,  400 as item_id ;


with standing as (
select custid, order_date, qty, standing_item_id, item_id, dense_rank()over(order by order_date desc) as orderid
  from #tmp_data
 where standing_item_id is not null
), nonstanding as (
select custid, order_date, qty, standing_item_id, item_id, dense_rank()over(partition by custid, item_id order by order_date desc) as orderid
  from #tmp_data
 where item_id is not null
)
select r.custid
     , r.order_date
     , isnull(c.qty,0) as qty
     , isnull(c.standing_item_id,p.standing_item_id) standing_item_id
     , isnull(c.item_id,p.item_id) item_id
     , p.qty           as prev_qty
     , isnull(c.qty,0) - p.qty as chg
  from (select distinct custid, order_date, orderid from standing) r
       join standing p
         on r.orderid = p.orderid-1 
            and r.custid = p.custid
  left join standing c
         on r.orderid = c.orderid 
            and r.custid = c.custid
            and p.standing_item_id = c.standing_item_id
--            and p.qty != c.qty
union all
select r.custid
     , r.order_date
     , isnull(c.qty,0) as qty
     , isnull(c.standing_item_id,p.standing_item_id) standing_item_id
     , isnull(c.item_id,p.item_id) item_id
     , p.qty           as prev_qty
     , isnull(c.qty,0) - p.qty as chg
  from (select distinct custid, order_date, orderid from nonstanding) r
       join nonstanding p
         on r.orderid = p.orderid-1 
            and r.custid = p.custid
  left join nonstanding c
         on r.orderid = c.orderid 
            and r.custid = c.custid
            and p.item_id = c.item_id
--            and p.qty != c.qty
order by r.custid, r.order_date desc, isnull(c.standing_item_id,p.standing_item_id)

drop table #tmp_data;

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now