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

# 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
• 3
• 2
• 2
• +1
1 Solution

Commented:
``````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
``````
0

Commented:
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

Commented:
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;
``````
0

Author 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

Commented:
Has my code been checked?
0

Author 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

Commented:
You are very gracious.  Thank you.
0

Commented:
``````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;
``````
0

## Featured Post

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