Find the nearest register date from a receipt date

Transact-SQL
1

Hi,

I am working on to find the nearest dt_register from dt_receipt using id and the date itself by could not get the exact result. Thank you.

Declare  @sample table
(id nvarchar (35),
sourceid int,
itemid nvarchar(35),
dt_receipt datetime)


insert @sample values ('52164',3,'P16GLD','2017-06-13 03:59:42.000')
insert @sample values ('52164',3,'P16GLD','2016-08-1 01:59:42.000')
insert @sample values ('52060',3,'P64GRY','2016-08-27 03:32:10.000')
insert @sample values ('52000',3,'SP64GRY','2017-04-10 06:28:07.000')
insert @sample values ('52001',3,'SP64GRY','2017-06-08 04:36:33.000')
insert @sample values ('52001',3,'SP64GRY','2017-01-27 05:37:10.000')
insert @sample values ('52268',3,'LOP16GLD','2017-07-25 06:27:57.000')




Declare  @register table
(id nvarchar (35),
sourceid int,
itemid nvarchar(35),
dt_register datetime)

insert @register values ('52164',1,'SOP16GLD','2017-07-10 06:27:57.000')
insert @register values ('52164',1,'LP16GLD','2016-09-23 03:44:03.000')
insert @register values ('52164',1,'P16GLD','2016-07-14 04:30:40.000')
insert @register values ('52060',1,'P64GRY','2016-09-28 09:32:10.000')
insert @register values ('52000',1,'SP64GRY','2017-02-10 06:28:07.000')
insert @register values ('52001',1,'SP64GRY','2017-07-08 10:36:33.000')
insert @register values ('52001',1,'SP64GRY','2017-03-27 11:37:10.000')



select s.id, s.itemid,s.sourceid, s.dt_receipt,  r.dt_register
from @sample s
left join @register r
on s.id = r.id and r.dt_register >= s.dt_receipt
where s.sourceid=3

Desires Result:

id------sourceid--itemid---dt_receipt----------------dt_registered
====================================================================
52000---3-------SP64GRY----2017-04-10 06:28:07.000---2017-02-10 06:28:07.000
52001---3-------SP64GRY----2017-01-27 05:37:10.000---2017-03-27 11:37:10.000
52001---3-------SP64GRY----2017-06-08 04:36:33.000---2017-07-08 10:36:33.000
52060---3-------P64GRY-----2016-08-27 03:32:10.000---2016-09-28 09:32:10.000
52164---3-------P16GLD-----2016-08-01 01:59:42.000---2016-09-23 03:44:03.000
52164---3-------P16GLD-----2017-06-13 03:59:42.000---2017-07-10 06:27:57.000
52268---3-------LOP16GLD---2017-07-25 06:27:57.000---
2 
DECLARE @sample table
	( id nvarchar (35)
	, sourceid int
	, itemid nvarchar(35)
	, dt_receipt datetime );

INSERT @sample VALUES 
   ('52164',3,'P16GLD','2017-06-13 03:59:42.000')
 , ('52164',3,'P16GLD','2016-08-1 01:59:42.000')
 , ('52060',3,'P64GRY','2016-08-27 03:32:10.000')
 , ('52000',3,'SP64GRY','2017-04-10 06:28:07.000')
 , ('52001',3,'SP64GRY','2017-06-08 04:36:33.000')
 , ('52001',3,'SP64GRY','2017-01-27 05:37:10.000')
 , ('52268',3,'LOP16GLD','2017-07-25 06:27:57.000') ;

DECLARE @register table
	( id nvarchar (35)
	, sourceid int
	, itemid nvarchar(35)
	, dt_register datetime );

INSERT @register VALUES 
   ('52164',1,'SOP16GLD','2017-07-10 06:27:57.000')
 , ('52164',1,'LP16GLD','2016-09-23 03:44:03.000')
 , ('52164',1,'P16GLD','2016-07-14 04:30:40.000')
 , ('52060',1,'P64GRY','2016-09-28 09:32:10.000')
 , ('52000',1,'SP64GRY','2017-02-10 06:28:07.000')
 , ('52001',1,'SP64GRY','2017-07-08 10:36:33.000')
 , ('52001',1,'SP64GRY','2017-03-27 11:37:10.000') ;

SELECT
   s.id
 , s.sourceid
 , s.itemid
 , s.dt_receipt
 ,  r.dt_register
FROM 
	@sample s
LEFT JOIN
	@register r
ON		s.id           = r.id
	AND r.dt_register >= s.dt_receipt
WHERE
	s.sourceid = 3
ORDER BY 
   s.id
 , s.itemid DESC
 , s.dt_receipt
 , r.dt_register ;
/*
Desired Result:

id		sourceid	itemid		dt_receipt					dt_registered
====================================================================
52000	3			SP64GRY		2017-04-10 06:28:07.000		2017-02-10 06:28:07.000 -- dt_registered is null and should be as it is before dt_receipt
52001	3			SP64GRY		2017-01-27 05:37:10.000		2017-03-27 11:37:10.000
52001	3			SP64GRY		2017-06-08 04:36:33.000		2017-07-08 10:36:33.000
52060	3			P64GRY		2016-08-27 03:32:10.000		2016-09-28 09:32:10.000
52164	3			P16GLD		2016-08-01 01:59:42.000		2016-09-23 03:44:03.000
52164	3			P16GLD		2017-06-13 03:59:42.000		2017-07-10 06:27:57.000
52268	3			LOP16GLD	2017-07-25 06:27:57.000

Actual result:
id                                  sourceid    itemid                              dt_receipt              dt_register
----------------------------------- ----------- ----------------------------------- ----------------------- -----------------------
52000                               3           SP64GRY                             2017-04-10 06:28:07.000 NULL
52001                               3           SP64GRY                             2017-01-27 05:37:10.000 2017-03-27 11:37:10.000
52001                               3           SP64GRY                             2017-01-27 05:37:10.000 2017-07-08 10:36:33.000
52001                               3           SP64GRY                             2017-06-08 04:36:33.000 2017-07-08 10:36:33.000
52060                               3           P64GRY                              2016-08-27 03:32:10.000 2016-09-28 09:32:10.000
52164                               3           P16GLD                              2016-08-01 01:59:42.000 2016-09-23 03:44:03.000
52164                               3           P16GLD                              2016-08-01 01:59:42.000 2017-07-10 06:27:57.000
52164                               3           P16GLD                              2017-06-13 03:59:42.000 2017-07-10 06:27:57.000
52268                               3           LOP16GLD                            2017-07-25 06:27:57.000 NULL
*/

image

3

Using the script I created the result is not accurate. the desired result should be like this sample below.
any help is very much appreciated. thanks.

id------sourceid--itemid---dt_receipt----------------dt_registered

52000---3-------SP64GRY----2017-04-10 06:28:07.000---2017-02-10 06:28:07.000
52001---3-------SP64GRY----2017-01-27 05:37:10.000---2017-03-27 11:37:10.000
52001---3-------SP64GRY----2017-06-08 04:36:33.000---2017-07-08 10:36:33.000
52060---3-------P64GRY-----2016-08-27 03:32:10.000---2016-09-28 09:32:10.000
52164---3-------P16GLD-----2016-08-01 01:59:42.000---2016-09-23 03:44:03.000
52164---3-------P16GLD-----2017-06-13 03:59:42.000---2017-07-10 06:27:57.000
52268---3-------LOP16GLD---2017-07-25 06:27:57.000---

4

Try this:

select top(1) with ties
       a.id
      ,a.itemid
      ,a.sourceid
      ,a.dt_receipt
      ,b.dt_register
  from @sample as a
       left outer join @register as b
                    on b.id=a.id
 order by row_number() over(partition by a.id
                                        ,a.itemid
                                        ,a.sourceid
                                        ,a.dt_receipt
                            order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                           )
;

or this:

select id
      ,itemid
      ,sourceid
      ,dt_receipt
      ,dt_register
  from (select a.id
              ,a.itemid
              ,a.sourceid
              ,a.dt_receipt
              ,b.dt_register
              ,row_number() over(partition by a.id
                                             ,a.itemid
                                             ,a.sourceid
                                             ,a.dt_receipt
                                 order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                                )
               as rn
          from @sample as a
               left outer join @register as b
                            on b.id=a.id
       ) as a
 where rn=1
;
5

Hi Bistmed,

Just try the script and made some adjustment in the sample data. This ID# 52164 has multiple receipts (3x) with different receipt date and the first 2 receipts has an equivalent register date but the last receipt date does not have the register date meaning it is still in the process before transferring to production. Running both the query I notice it has the same register date the 2nd and 3rd records which the 3rd records should not have a register date.

Please see sample screen shot:

Capture

modified sample data:

DECLARE @sample table
	( id nvarchar (35)
	, sourceid int
	, itemid nvarchar(35)
	, dt_receipt datetime );

INSERT @sample VALUES 
  ('52164',3,'P16GLD','2016-08-1 01:59:42.000')
 , ('52164',3,'P16GLD','2017-06-13 03:59:42.000')
  , ('52164',3,'P16GLD','2017-08-13 03:59:42.000')
 , ('52060',3,'P64GRY','2016-08-27 03:32:10.000')
 , ('52000',3,'SP64GRY','2017-04-10 06:28:07.000')
 , ('52001',3,'SP64GRY','2017-01-27 05:37:10.000')
 , ('52001',3,'SP64GRY','2017-06-08 04:36:33.000')
 , ('52268',3,'LOP16GLD','2017-07-25 06:27:57.000') ;

DECLARE @register table
	( id nvarchar (35)
	, sourceid int
	, itemid nvarchar(35)
	, dt_register datetime );

INSERT @register VALUES 
 ('52164',1,'LP16GLD','2016-09-23 03:44:03.000')
 , ('52164',1,'SOP16GLD','2017-07-10 06:27:57.000')
 , ('52060',1,'P64GRY','2016-09-28 09:32:10.000')
 , ('52000',1,'SP64GRY','2017-05-10 06:28:07.000')
 , ('52001',1,'SP64GRY','2017-02-08 10:36:33.000')
 , ('52001',1,'SP64GRY','2017-03-27 11:37:10.000') ;
6

My first thought after your latest reply was that register datetime always comes after receipt datetime, but that would mean row 3 from your lates sample screen shot (id 52001, item SP64GRY, receipt 2017-06-08 04:36:33) would also "be in process".

You need to establish exactly what the rules are, otherwise I'm just guessing.

7

Sorry for not giving clear requirements and sample data but this is the exact process the dt_register always comes after the dt_receipt. When there is a dt_receipt means this is already received by warehouse and after sorting all the items this is be forwarded to production and first they will register this which I have the dt_register then they will procees now to prod.

8

So this might do the trick:

select top(1) with ties
       a.id
      ,a.itemid
      ,a.sourceid
      ,a.dt_receipt
      ,b.dt_register
  from @sample as a
       left outer join @register as b
                    on b.id=a.id
                   and b.dt_register>=a.dt_receipt
 order by row_number() over(partition by a.id
                                        ,a.itemid
                                        ,a.sourceid
                                        ,a.dt_receipt
                            order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                           )
;

or this:

select id
      ,itemid
      ,sourceid
      ,dt_receipt
      ,dt_register
  from (select a.id
              ,a.itemid
              ,a.sourceid
              ,a.dt_receipt
              ,b.dt_register
              ,row_number() over(partition by a.id
                                             ,a.itemid
                                             ,a.sourceid
                                             ,a.dt_receipt
                                 order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                                )
               as rn
          from @sample as a
               left outer join @register as b
                            on b.id=a.id
                           and b.dt_register>=a.dt_receipt
       ) as a
 where rn=1
;
1 Like
9

Thank you very much Bistmed. What if I have more columns to be included in the result do I need to add all the columns in this particular code

,row_number() over(partition by a.id
                                         ,a.itemid
                                         ,a.sourceid
                                         ,a.dt_receipt
                             order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                            )
10

No, you have access to all fields from both sample (aliased as a) and register (aliased as b).
If you use the first query, just add your fields to the select section.
If you use the second query, add your fields to the select section in both inner (sub) and outer (main) query.

11

Thanks a lot

12

Hi Bistmed,

Trying the code you have given and I'm giving an error in the result. Please see below sample desired result . I'm getting a duplicate itemnumber for this ID "00000490960583". the first transaction shound have no changes because the register date is not nearest from the cores receipt while the second transaction should have made a changes .

DECLARE @sample table
	( id nvarchar (35)
	, sourceid int
	, itemid nvarchar(35)
	, dt_receipt datetime );

INSERT @sample VALUES 
  ('00000490960583',3,'P16GLD','2017-07-06 00:09:00.000')
 , ('00000490960583',3,'P16GLD','2017-08-18 05:34:00.000')
  , ('0000049091111',3,'P16GLD','2017-08-17 03:34:00.000')
  , ('0000049092222',3,'P16GLD','2017-08-17 03:34:00.000')
;

DECLARE @register table
	( id nvarchar (35)
	, sourceid int
	, itemid nvarchar(35)
	, dt_register datetime );

INSERT @register VALUES 
 ('00000490960583',1,'BRDGLD','2017-08-30 01:51:13.000'),
  ('0000049091111',1,'BRD16GLD','2017-08-30 01:59:14.000')
;



select id
      ,itemid
	  ,itemnumber
      ,sourceid
      ,dt_receipt
      ,dt_register

  from (
    select a.id
              ,a.itemid
			  ,b.itemid as itemnumber
              ,a.sourceid
              ,a.dt_receipt
              ,b.dt_register
              ,row_number() over(partition by a.id
                                             ,a.itemid
                                             ,a.sourceid
                                             ,a.dt_receipt
                                 order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                                )
               as rn
          from @sample as a
               left outer join @register as b
                            on b.id=a.id
                           and b.dt_register> =a.dt_receipt
       ) as a
 where rn=1
;



id--------------itemid---itemnumber--sourceid---dt_receipt-------------dt_register
================================================================================================
0000049091111---P16GLD	BRD16GLD-------3--------2017-08-17 03:34:00.000---2017-08-30 01:59:14.000
0000049092222---P16GLD	NULL	-------3--------2017-08-17 03:34:00.000---NULL
00000490960583--P16GLD	P16GLD	-------3--------2017-07-06 00:09:00.000---NULL
00000490960583--P16GLD	BRDGLD	--------3-------2017-08-18 05:34:00.000---2017-08-30 01:51:13.000
13

Change this:

              ,row_number() over(partition by a.id
                                             ,a.itemid
                                             ,a.sourceid
                                             ,a.dt_receipt
                                 order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                                )
               as rn

to this:

              ,row_number() over(partition by a.id
                                             ,a.itemid
                                             ,a.sourceid
                                             ,b.dt_register
                                 order by abs(datediff(minute,a.dt_receipt,b.dt_register))
                                )
               as rn
14

Thanks bistmed for the reply. I tried the script I have a missing records the for this itemnumber the first transaction was removed which it should still be included the changes should be the itemid.