Get closest date

Transact-SQL
1

I pulled this data using filtering receipt date from Jan 1, 2015 to Feb 1 2015.
I need to get closest po ended from receipt date. let say this esn 990002036924452 the receipt date is '2015-01-07 17:39:44.660',
so the closest PO ended is '2015-04-23 20:26:55.000'. another example. for this esn R9000000000019761824, the receipt date is 2015-01-03 19:23:54.980
but i will get the latest po ended/last po ended which it should be '2015-03-20 16:43:07.000'. if there is no closest date from po ended still i would include the records.
Any help would be greatly appreciated.

--this is the result from my last cte process
drop table #sample
Create table #sample
(ESN nvarchar(35),	ESN2 nvarchar(35), 	Receipt_Date datetime,	PO_ENDED datetime,  pstatus int)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2015-04-23 20:26:55.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2015-07-14 01:34:34.000',4)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002038378525','990002038378525','2015-01-07 05:29:56.923','2015-05-29 14:50:52.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002038378525','990002038378525','2015-01-07 05:29:56.923','2015-07-30 01:08:51.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002038378525','990002038378525','2015-01-07 05:29:56.923','2015-09-18 04:05:39.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('R9000000000019761824','354403060637509','2015-01-03 19:23:54.980','2015-02-07 09:50:08.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('R9000000000019761824','354403060637509','2015-01-03 19:23:54.980','2015-02-07 09:50:08.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('R9000000000019761824','354403060637509','2015-01-03 19:23:54.980','2015-02-07 09:50:08.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('R9000000000019761824','354403060637509','2015-01-03 19:23:54.980','2015-03-20 16:43:07.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('R9000000000019761824','354403060637509','2015-01-03 19:23:54.980','2015-03-20 16:43:07.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('R9000000000019761824','354403060637509','2015-01-03 19:23:54.980','2015-03-20 16:43:07.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002011652284','990002011652284','2015-01-21 20:34:21.763','2014-10-08 04:44:10.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002011652284','990002011652284','2015-01-21 20:34:21.763','2015-07-24 01:05:30.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002011652284','990002011652284','2015-01-21 20:34:21.763','1900-01-01 08:00:00.000',4)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('R9000000000019925008','354403063739831','2015-01-28 11:59:19.653','2015-06-25 03:18:43.000',7)


select t.ESN, t.ESN2, t.Receipt_Date, t.PO_ENDED, t.pstatus     
from #sample t 


expected result
ESN-------------------ESN2-------------Receipt_Date-------------POENDED---------------
--------------------------------------------------------------------------------------
990002036924452-------990002036924452--2015-01-07 17:39:44.660--2015-04-23 20:26:55.000
990002038378525-------990002038378525--2015-01-07 05:29:56.923--2015-05-29 14:50:52.000
R9000000000019761824--354403060637509--2015-01-03 19:23:54.980--2015-03-20 16:43:07.000
990002011652284-------990002011652284--2015-01-21 20:34:21.763--2015-07-24 01:05:30.000
R9000000000019925008--354403063739831--2015-01-28 11:59:19.653--2015-06-25 03:18:43.000
2 
select     t.ESN, t.ESN2, t.Receipt_Date, p.PO_ENDED  
from     (
        select    distinct ESN, ESN2, Receipt_Date 
        from    #sample 
    ) t 
    cross apply
    (
        select    top 1 x.PO_ENDED
        from    #sample x
        where    x.ESN    = t.ESN
        and    x.ESN2    = t.ESN2
        order by abs(datediff(day, x.Receipt_Date, x.PO_ENDED))
    ) p
order by ESN
3

hi khtan, thanks for the reply. tried the query and i notice the esn 990002011652284 , the po ended is 2014-10-08 04:44:10.000 which should be 2015-07-24 01:05:30.000.

tried also this query and we have he same result.

select *
from (
Select ESN, ESN2, Receipt_Date, PO_ENDED  ,
ROW_NUMBER() OVER (PARTITION BY ESN,Receipt_Date 
ORDER BY Case when left(ESN,1)='R' then PO_ENDED end desc
,  Case when left(ESN,1)<>'R' then  ABS(DATEDIFF(dd, Receipt_Date, PO_ENDED )) end ) AS rowNum
from #sample 
) t
where rowNum =1
4

added condition PO_ENDED >= Receipt_Date and change to OUTER APPLY.

Is that a valid assumption ?

select     t.ESN, t.ESN2, t.Receipt_Date, p.PO_ENDED  
from     (
        select    distinct ESN, ESN2, Receipt_Date 
        from    #sample 
    ) t 
    outer apply
    (
        select    top 1 x.PO_ENDED
        from    #sample x
        where    x.ESN    = t.ESN
        and    x.ESN2    = t.ESN2
        and    x.PO_ENDED >= t.Receipt_Date
        order by abs(datediff(day, x.Receipt_Date, x.PO_ENDED))
    ) p
order by ESN
5

Just tried the query and fixed the problem for this ESN 990002011652284 but it was affected those ESN that start with R. your first query get the exact data which is the last transaction but now it get this date 2015-02-07 09:50:08.000.

This esn that start with R well get the last transaction date. if this can be done on a separate query. thanks.

6

Im trying this approach and those esn start with 'R' is successfully pulledout my worry is this esn 990002011652284 it pulls the previouse year 2014-10-08 04:44:10.000.

Your second query the worry is that we used distinct which some esn have the multiple records with differents data and also it takes time to generate. thanks.

select *
from (
Select ESN, ESN2, Receipt_Date, PO_ENDED  ,
ROW_NUMBER() OVER (PARTITION BY ESN, Receipt_Date ORDER BY ABS(DATEDIFF(dd, Receipt_Date, PO_ENDED ))) AS rowNum 
from #sample 
WHERE left(ESN,1)<>'R'
) t
where rowNum =1
UNION
select *
from (
Select ESN, ESN2, Receipt_Date, PO_ENDED  ,
ROW_NUMBER() OVER (PARTITION BY ESN, Receipt_Date ORDER BY po_ended DESC,  ABS(DATEDIFF(dd, Receipt_Date, PO_ENDED ))) AS rowNum 
from #sample 
WHERE left(ESN,1)='R'
) t
where rowNum =1
7

you can combine both query using CASE WHEN ... on the row_number()

ROW_NUMBER() OVER (
		PARTITION BY ESN,
		             Receipt_Date 
                ORDER BY CASE 
			     WHEN left(ESN, 1) = 'R'
			     THEN po_ended
			     END DESC,
			ABS(DATEDIFF(dd, Receipt_Date, PO_ENDED))
		) AS rowNum
8

Hi kthan, still it display incorrect records. my concern is the esn that start with numbers, like this esn 990002011652284, it capture the previous date.

select *
from (
Select ESN, ESN2, Receipt_Date, PO_ENDED  
,ROW_NUMBER() OVER (
		PARTITION BY ESN,
		             Receipt_Date 
                ORDER BY CASE 
			     WHEN left(ESN, 1) = 'R'
			     THEN po_ended
			     END DESC,
			ABS(DATEDIFF(dd, Receipt_Date, PO_ENDED))
		) AS rowNum
from #sample 
) t
where rowNum =1

ESN ESN2 Receipt_Date PO_ENDED rowNum
990002011652284 990002011652284 2015-01-21 20:34:21.763 2014-10-08 04:44:10.000 1

9

sorry, i have not idea what is the different between esn that start with numbers or the others. Perhaps you can explain the business rules behind these and how do you want to handle it

10

Please see below sample. The receipt date for this ESN records is '2015-01-21 20:34:21.763', using the script in my last reply, the date capture was '2014-10-08 04:44:10.000' which it should be '2015-07-24 01:05:30.000'.

I mean, i have 2 different ESN numbers. the first ESN has a chart in the leftmost while the other is number.
R9000000000019761824
990002011652284

insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002011652284','990002011652284','2015-01-21 20:34:21.763','2014-10-08 04:44:10.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002011652284','990002011652284','2015-01-21 20:34:21.763','2015-07-24 01:05:30.000',7)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED,pstatus)values('990002011652284','990002011652284','2015-01-21 20:34:21.763','1900-01-01 08:00:00.000',4)
11

Based on what information that you decided that the date should be 2015-07-24 and not 2014-10-08 ?

12

Based on receipt date. This PO ended '2014-10-08 04:44:10.000' is prior to receipt date.

13

so you should only consider PO end date that is after the receipt date right ?

just add that to the condition

WHERE PO_ENDED >= Receipt_Date
14

select esn,esn2,receipt_date,PO_ENDED_final from #sample s1 cross apply(select top 1 s2.PO_ENDED PO_ENDED_final from #sample s2 where s1.ESN=s2.ESN and s1.Receipt_Date<s2.PO_ENDED)a
group by esn,esn2,receipt_date,PO_ENDED_final

15

For best performance, you'll very likely want an index on ( ESN, PO_ENDED ):

select 
    t.ESN, t.ESN2, t.Receipt_Date, t.PO_ENDED, t.pstatus,
    oa1.*
from #sample t
outer apply (
    select top (1) *
    from #sample t2
    where t2.ESN = t.ESN and t2.PO_ENDED > t.Receipt_Date
    order by
        case when left(t2.esn, 1) like '[0-9]' then t2.po_ended else CAST('99991212' as datetime) - t2.PO_ENDED end
) oa1
16

thank you guys for the reply.

hi scott, just tried your query and it gives a correct result but it takes time to process. I use the script to process the january records (118,134 records) it takes more time to process.

I just run also this query i notice the first query takes more time while the other one it takes only 1 minue. it gives also correct result.

--first query
    select *
    from (
    Select ESN, ESN2, Receipt_Date, PO_ENDED  ,
    ROW_NUMBER() OVER (PARTITION BY ESN, Receipt_Date ORDER BY ABS(DATEDIFF(dd, Receipt_Date, PO_ENDED ))) AS rowNum 
    from #sample 
    WHERE left(ESN,1)<>'R'
    and  po_PO_ENDED>= Receipt_Date  -- just added this code
    ) t
    where rowNum =1

    UNION

--second query
    select *
    from (
    Select ESN, ESN2, Receipt_Date, PO_ENDED  ,
    ROW_NUMBER() OVER (PARTITION BY ESN, Receipt_Date ORDER BY po_ended DESC,  ABS(DATEDIFF(dd, Receipt_Date, PO_ENDED ))) AS rowNum 
    from #sample 
    WHERE left(ESN,1)='R'
    ) t
    where rowNum =1
17

What indexes do you have?

18

Actually, This is the last result from my CTE where i can pull the records which i obtain the nearest date after the ESN Receiptdate.