freshgrill
asked on
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.....
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.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
awking00
Your question is rather confusing to me. Can you provide an explanation of how each of the values in your expected results are derived?
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).
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;ASKER
@ 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.
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.
Has my code been checked?
ASKER
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_
But everything appears to be working correctly.
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_
But everything appears to be working correctly.
You are very gracious. Thank you.
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;