none
TSQL Script for repeatedly records RRS feed

  • Question

  • Hi,

    I haven't across this scenario to replicate records based on serialid and prodid the staging column. please see below a sample result my script and a DLL. Thank you. 

    I want to add if wrkctr is start with PEntry this will fall to LCM-01. if wrkctr is start with LDREnt this will go to LCM-02 while wrkctr start with LDEnt this will go to LCM-03.

    thank you.

    create table #S
    (prodid nvarchar(35), po_status nvarchar(35),	serialid nvarchar(35),
    	wrkctr nvarchar(35),	createddatetime datetime,	row_num int, Staging nvarchar(35))
    	
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04468150','Li-01','A160045566','PEntry','4/17/2016 8:26:54',1,'LCM-01')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04468150','Li-01','A160045566','LMDADS','4/17/2016 11:55:55',2,'NULL')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04468150','Li-01','A160045566','ADiAss','4/18/2016 9:16:42',3,'NULL')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04468150','Li-01','A160045566','Diass','4/18/2016 13:16:55',4,'NULL')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04468150','Li-01','A160045566','Lens','4/18/2016 22:59:59',5,'NULL')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04468150','Li-01','A160045566','LCDVFI','4/18/2016 23:30:44',6,'NULL')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04468150','Li-01','A160045566','LCDRw','4/18/2016 23:46:56',7,'LCM-01')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04493095','Scrap','A160045566','LDREnt','4/19/2016 1:18:58',1,'LCM-02')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04493095','Scrap','A160045566','LCDRep','4/19/2016 4:51:36',2,'NULL')
    insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04493095','Scrap','A160045566','CBSCP','4/19/2016 16:50:38',3,'NULL')
    
    
    Desired result:
    prodid---po_status----	serialid----wrkctr---createddatetime----row_num--Staging
    ============================================================================================================
    P04468150----Li-01------A160045566--PEntry--4/17/2016 8:26:54---1-----	LCM-01
    P04468150----Li-01------A160045566--LMDADS--4/17/2016 11:55:55--2-----	LCM-01
    P04468150----Li-01------A160045566--ADiAss--4/18/2016 9:16:42---3-----	LCM-01
    P04468150----Li-01------A160045566--Diass---4/18/2016 13:16:55--4-----	LCM-01
    P04468150----Li-01------A160045566--Lens----4/18/2016 22:59:59--5-----	LCM-01
    P04468150----Li-01------A160045566--LCDVFI--4/18/2016 23:30:44--6-----	LCM-01
    P04468150----Li-01------A160045566--LCDRw---4/18/2016 23:46:56--7-----	LCM-01
    P04493095----Scrap------A160045566--LDREnt--4/19/2016 1:18:58---1-----	LCM-02
    P04493095----Scrap------A160045566--LCDRep--4/19/2016 4:51:36---2-----	LCM-02
    P04493095----Scrap------A160045566--CBSCP---4/19/2016 16:50:38--3-----	LCM-02



    • Edited by Lenoj Wednesday, April 20, 2016 5:21 AM
    Wednesday, April 20, 2016 4:24 AM

Answers

  • Hi Lenoj,

    According to your description, you may refer to script below.

    ;with cte as 
    (
    	select prodid, po_status, serialid, wrkctr, createddatetime,
    		ROW_NUMBER() OVER(PARTITION BY serialid, prodid ORDER BY createddatetime) AS row_num
    	from #S
    ),
    cte2 as
    (
    	select prodid, serialid,
    		CASE wrkctr
    			WHEN 'PEntry' THEN 'LCM-01'
    			WHEN 'LDREnt' THEN 'LCM-02'
    			WHEN 'LDEnt' THEN 'LCM-03'
    			ELSE NULL
    		END AS Staging 
    	from cte
    	where row_num = 1
    )
    select a.*, b.Staging
    from cte a
    join cte2 b on a.serialid = b.serialid and a.prodid = b.prodid

    Sam Zha
    TechNet Community Support

    • Proposed as answer by Riaon Wednesday, April 20, 2016 6:32 AM
    • Marked as answer by Lenoj Wednesday, April 20, 2016 7:05 AM
    Wednesday, April 20, 2016 5:57 AM
    Moderator

All replies

  • The following should meet your description.

    select *,
    CASE   WHEN wrkctr like 'PEntry%' THEN 'LCM-01'
                  WHEN wrkctr like 'LDREnt%' THEN 'LCM-02'
                  WHEN wrkctr like 'LDEnt%' THEN 'LCM-03' ELSE NULL END AS Result
    from #S
     

    Wednesday, April 20, 2016 5:09 AM
  • The following should answer your question.

    select *,
    CASE   WHEN wrkctr like 'PEntry%' THEN 'LCM-01'
                  WHEN wrkctr like 'LDREnt%' THEN 'LCM-02'
                  WHEN wrkctr like 'LDEnt%' THEN 'LCM-03' ELSE NULL END AS Result
    from #S
     


     


    • Edited by JohnSterrett Wednesday, April 20, 2016 5:11 AM wrong text..
    Wednesday, April 20, 2016 5:10 AM
  • Hi john, it has the same result ..

    below is the desired result

    Desired result:
    prodid---po_status----	serialid----wrkctr---createddatetime----row_num--Staging
    ============================================================================================================
    P04468150----Li-01------A160045566--PEntry--4/17/2016 8:26:54---1-----	LCM-01
    P04468150----Li-01------A160045566--LMDADS--4/17/2016 11:55:55--2-----	LCM-01
    P04468150----Li-01------A160045566--ADiAss--4/18/2016 9:16:42---3-----	LCM-01
    P04468150----Li-01------A160045566--Diass---4/18/2016 13:16:55--4-----	LCM-01
    P04468150----Li-01------A160045566--Lens----4/18/2016 22:59:59--5-----	LCM-01
    P04468150----Li-01------A160045566--LCDVFI--4/18/2016 23:30:44--6-----	LCM-01
    P04468150----Li-01------A160045566--LCDRw---4/18/2016 23:46:56--7-----	LCM-01
    P04493095----Scrap------A160045566--LDREnt--4/19/2016 1:18:58---1-----	LCM-02
    P04493095----Scrap------A160045566--LCDRep--4/19/2016 4:51:36---2-----	LCM-02
    P04493095----Scrap------A160045566--CBSCP---4/19/2016 16:50:38--3-----	LCM-02
    

    • Edited by Lenoj Wednesday, April 20, 2016 5:22 AM
    Wednesday, April 20, 2016 5:20 AM
  • looks like this is what you're after based on the sample data posted

    SELECT prodid,
    po_status,
    serialid,
    workctr,
    createddatetime,
    row_num,
    COALESCE(Staging,MaxStaging) AS Staging
    FROM
    (
    SELECT *,MAX(Staging) OVER (PARTITION BY prodid) AS MaxStaging
    FROM #S
    )t


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 20, 2016 5:26 AM
  • Hi Visakh16,

    right now this is what my records look like..

    prodid---po_status---- serialid----wrkctr---createddatetime----row_num--Staging
    ============================================================================================================
    P04468150----Li-01------A160045566--PEntry--4
    /17/2016 8:26:54---1----- LCM-01
    P04468150----Li-01------A160045566--LMDADS--4
    /17/2016 11:55:55--2-----
    P04468150----Li-01------A160045566--ADiAss--4
    /18/2016 9:16:42---3-----
    P04468150----Li-01------A160045566--Diass---4
    /18/2016 13:16:55--4-----
    P04468150----Li-01------A160045566--Lens----4
    /18/2016 22:59:59--5-----
    P04468150----Li-01------A160045566--LCDVFI--4
    /18/2016 23:30:44--6-----
    P04468150----Li-01------A160045566--LCDRw---4
    /18/2016 23:46:56--7----
    P04493095----Scrap------A160045566--LDREnt--4
    /19/2016 1:18:58---1-----LCM-02
    P04493095----Scrap------A160045566--LCDRep--4
    /19/2016 4:51:36---2-----
    P04493095----Scrap------A160045566--CBSCP---4
    /19/2016 16:50:38--3-----

    should be like this. the staging columns if filled up already.

    ============================================================================================================
    P04468150----Li-01------A160045566--PEntry--4
    /17/2016 8:26:54---1----- LCM-01
    P04468150----Li-01------A160045566--LMDADS--4
    /17/2016 11:55:55--2----LCM-01-
    P04468150----Li-01------A160045566--ADiAss--4
    /18/2016 9:16:42---3----- LCM-01
    P04468150----Li-01------A160045566--Diass---4
    /18/2016 13:16:55--4----- LCM-01
    P04468150----Li-01------A160045566--Lens----4
    /18/2016 22:59:59--5----- LCM-01
    P04468150----Li-01------A160045566--LCDVFI--4
    /18/2016 23:30:44--6-----LCM-01
    P04468150----Li-01------A160045566--LCDRw---4
    /18/2016 23:46:56--7-----LCM-01

    Wednesday, April 20, 2016 5:44 AM
  • Hi Visakh16,

    right now this is what my records look like..

    prodid---po_status---- serialid----wrkctr---createddatetime----row_num--Staging
    ============================================================================================================
    P04468150----Li-01------A160045566--PEntry--4
    /17/2016 8:26:54---1----- LCM-01
    P04468150----Li-01------A160045566--LMDADS--4
    /17/2016 11:55:55--2-----
    P04468150----Li-01------A160045566--ADiAss--4
    /18/2016 9:16:42---3-----
    P04468150----Li-01------A160045566--Diass---4
    /18/2016 13:16:55--4-----
    P04468150----Li-01------A160045566--Lens----4
    /18/2016 22:59:59--5-----
    P04468150----Li-01------A160045566--LCDVFI--4
    /18/2016 23:30:44--6-----
    P04468150----Li-01------A160045566--LCDRw---4
    /18/2016 23:46:56--7----
    P04493095----Scrap------A160045566--LDREnt--4
    /19/2016 1:18:58---1-----LCM-02
    P04493095----Scrap------A160045566--LCDRep--4
    /19/2016 4:51:36---2-----
    P04493095----Scrap------A160045566--CBSCP---4
    /19/2016 16:50:38--3-----

    should be like this. the staging columns if filled up already.

    ============================================================================================================
    P04468150----Li-01------A160045566--PEntry--4
    /17/2016 8:26:54---1----- LCM-01
    P04468150----Li-01------A160045566--LMDADS--4
    /17/2016 11:55:55--2----LCM-01-
    P04468150----Li-01------A160045566--ADiAss--4
    /18/2016 9:16:42---3----- LCM-01
    P04468150----Li-01------A160045566--Diass---4
    /18/2016 13:16:55--4----- LCM-01
    P04468150----Li-01------A160045566--Lens----4
    /18/2016 22:59:59--5----- LCM-01
    P04468150----Li-01------A160045566--LCDVFI--4
    /18/2016 23:30:44--6-----LCM-01
    P04468150----Li-01------A160045566--LCDRw---4
    /18/2016 23:46:56--7-----LCM-01

    Hmm.. This is different from what you posted before

    How did all values become LCM - 01 now? What happened to LCM- 02?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 20, 2016 5:51 AM
  • Hi Lenoj,

    According to your description, you may refer to script below.

    ;with cte as 
    (
    	select prodid, po_status, serialid, wrkctr, createddatetime,
    		ROW_NUMBER() OVER(PARTITION BY serialid, prodid ORDER BY createddatetime) AS row_num
    	from #S
    ),
    cte2 as
    (
    	select prodid, serialid,
    		CASE wrkctr
    			WHEN 'PEntry' THEN 'LCM-01'
    			WHEN 'LDREnt' THEN 'LCM-02'
    			WHEN 'LDEnt' THEN 'LCM-03'
    			ELSE NULL
    		END AS Staging 
    	from cte
    	where row_num = 1
    )
    select a.*, b.Staging
    from cte a
    join cte2 b on a.serialid = b.serialid and a.prodid = b.prodid

    Sam Zha
    TechNet Community Support

    • Proposed as answer by Riaon Wednesday, April 20, 2016 6:32 AM
    • Marked as answer by Lenoj Wednesday, April 20, 2016 7:05 AM
    Wednesday, April 20, 2016 5:57 AM
    Moderator
  • This is the same just removed only the lcm-02. actually there are 3 staging but I only given the 2 staging process.
    Wednesday, April 20, 2016 7:07 AM
  • Hi Sam, I tested your script and so far I'm getting the exact result. I will try this to run using 2 million records if will affect the performance. thank you.
    Wednesday, April 20, 2016 9:55 AM