CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user1180703
user1180703

Reputation: 23

t-SQL get original order and re-order only if a reorder was within 30 days. Not getting expected results

I created two temp tables, one table is for orders and the other is for re-orders within 30 days from the orders temp table. I would like to see the original order and any re-order within 30 days. I though using two temp tables would work.

The first temp table called #orders has all of the orders for the past year. #orders Fields: RecordID (text), order date, ship date

For the second table I used the dateadd function to get any order within 30 days of the shipddate above, it has the same fields (#reorder)

Select recorded, orderdate, shipdate
Into #reorder
Where shipdate between dateadd(day, 1, p.eff_to) and dateadd(day, 30,p.eff_to)

My query to get the original order and any reorders (to exclude any orders that did not have a reorder within 30 days by joining the two tables is:

Select o.recordid, o.orderdate, o.shipdate, r.recordid, r.orderdate, r.shipdate
From #orders o
Left join #reorder 
Where o.recordid = r.recordid and o.shipdate between dateadd(day, -30,orderdate)    
and dateadd(day, -1, r.orderdate)

I tested some of the results and I am either not getting all of the records returned or redundant records returned.

Sample data 1:

#orders     
RecordID    Orderdate   ShipDate
525     1/8/2011        1/10/2011
525     3/22/2011       3/23/2011
525     4/5/2011        4/6/2011
525     4/12/2011       6/4/2011


#reorder    Orderdate   ShipDate
525     4/5/2011        4/6/2011
525     4/12/2011       6/4/2011


Results:    Orderdate   ShipDate
 525    3/22/2011   3/23/2011
 525    3/22/2011   3/23/2011
 525    4/5/2011    4/6/2011

expected results    
Orderdate   ShipDate
525     3/22/2011       3/23/2011
525     4/5/2011        4/6/2011
525     4/12/2011       6/4/2011

Sample #2:

#orders     
RecordID    Orderdate   ShipDate
101     2/22/2011       3/3/2011
101     5/22/2011       6/6/2011
101     6/27/2011       7/8/2011
101     7/11/2011       7/19/2011
101     11/25/2011      12/1/2011

#reorder    Orderdate   ShipDate
101      6/27/2011      7/8/2011
101      7/11/2011      7/19/2011


Results:    Orderdate   ShipDate
101     6/27/2011       7/8/2011
101     7/11/2011       7/19/2011


Expected results:   
Orderdate   ShipDate
101     5/22/2011       6/6/2011
101     6/27/2011       7/8/2011
101     7/11/2011       7/19/2011

Upvotes: 2

Views: 341

Answers (1)

Pankaj
Pankaj

Reputation: 10115

My query to get the original order and any reorders (to exclude any orders that did not have a reorder within 30 days by joining the two tables is:

Sample - Expected Result - 3 should not come up in resultset => Because it violates the above statement as it is not coming in 30 days.

You can check the query below.

create table #orders
(
    RecordID int,
    orderdate datetime, 
    shipdate datetime
)

create table #reorder
(
    Reorder int,
    orderdate datetime, 
    shipdate datetime
)
insert into #orders(RecordID,orderdate, shipdate)
values(525, '1/8/2011', '1/10/2011')

insert into #orders(RecordID,orderdate, shipdate)
values(525, '3/22/2011', '3/23/2011')


insert into #orders(RecordID,orderdate, shipdate)
values(525, '4/5/2011', '4/6/2011')


insert into #orders(RecordID,orderdate, shipdate)
values(525, '4/12/2011', '6/4/2011')


insert into #reorder(Reorder,orderdate, shipdate)
values(525, '4/5/2011', '4/6/2011')


insert into #reorder(Reorder,orderdate, shipdate)
values(525, '4/12/2011', '6/4/2011')

Select o.recordid, o.orderdate, o.shipdate, r.Reorder, 
r.orderdate, r.shipdate
From #orders o
Inner join #reorder r on o.recordid = r.Reorder
Where  r.shipdate between dateadd(day, -1, o.orderdate)    
and dateadd(day, 30,o.orderdate)

drop table #orders
drop table #reorder

Upvotes: 2

Related Questions