SQLTeam.com | Weblogs | Forums

Repeatedly records


#1

Hi , What is the SQL codes to replicate the records based on serialid and prodid. theres a multiple prodid using one serialid but it distributed to differernt staging.

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. please see below sample data.

[code]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')
insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04493096','Scrap','A160045566','LDEnt','4/20/2016 1:18:58',1,'LCM-03')
insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04493096','Scrap','A160045566','LCDRep','4/20/2016 4:51:36',2,'NULL')
insert into #S(prodid,po_status,serialid,wrkctr,createddatetime,row_num,Staging)values('P04493096','Scrap','A160045566','CBSCP','4/20/2016 16:50:38',3,'NULL')

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
P04493096----Scrap------A160045566--LDEnt--4/20/2016 1:18:58---1----- LCM-03
P04493096----Scrap------A160045566--LCDRep--4/20/2016 4:51:36---2----- LCM-03
P04493096----Scrap------A160045566--CBSCP---4/20/2016 16:50:38--3----- LCM-03[/code]


#2

Can you post desired output? So that can help you.


#3

Please post the queries you have tried so far.