Hi Guys, Looks nice to have this forum. Hopfully its easier to use. Just my first time to post a new topic. I have a tough time to consolidate these tables and get the desired result. I’m very grateful if somebody can help me.
--This data is came from a query extracting all Items received for the month of May 2015. I dump the data to a temporary table which this is the result of my query.
Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)
--This Data is a list of Items that will be relate to #MainSample. MainSample data will look up to this table if ItemNum is exist. if exist will be extracted the SerialUsed and RecoveredSearial and recovereddatetime.
Create table #S1
(SearialUsed nvarchar(35),RecoveredSearial nvarchar(35), recovereddatetime datetime)
Insert into #S1(SearialUsed, RecoveredSearial,recovereddatetime) values('M9000000000020510095','990005710331878','2015-05-02 03:10:10.000')
Insert into #S1(SearialUsed, RecoveredSearial,recovereddatetime) values('M9000000000020510094','990005335301686','2015-05-02 17:50:58.000')
Insert into #S1(SearialUsed, RecoveredSearial,recovereddatetime) values('M9000000000020510092','','1900-01-01 00:00:00.000')
--#S1 table will look up to this table if Serialused or RecoveredSerial is exist then get corresponding data. Use SerialUsed and RecoveredSerial to check the records.
Create table #S2
(invNum nvarchar(35), ItemNum nvarchar(35), Locationid nvarchar(35))
Insert into #S2(invNum,ItemNum,Locationid) values('CIM13426568','M9000000000020510095','FG')
Insert into #S2(invNum,ItemNum,Locationid) values('CIM13432602','990005710331878','FG')
Insert into #S2(invNum,ItemNum,Locationid) values('CIM13438719','990005335301686','FG')
Insert into #S2(invNum,ItemNum,Locationid) values('CIM13432557','M9000000000020510094','FG')
Insert into #S2(invNum,ItemNum,Locationid) values('CIM13704089','353852061764483','FG')
Insert into #S2(invNum,ItemNum,Locationid) values('CIM13457717','M9000000000020510092','Scrp')
Insert into #S2(invNum,ItemNum,Locationid) values('CIM13434335','M9000000000020510092','FG')
--Once the 3 table is consolidate i have to evaluate the Invnum if exist the get the desired data for final result.
Create table #S3
(invNum nvarchar(35), Pstatus int, Model nvarchar(35), enddate datetime)
insert into #S3(invNum,Pstatus,Model,enddate)values('CIM13432602','S300-1711-DR-U',7,'2015-05-02 17:51:33.000')
insert into #S3(invNum,Pstatus,Model,enddate)values('CIM13438719','S300-1710-DR-U',7,'2015-05-05 09:07:56.000')
insert into #S3(invNum,Pstatus,Model,enddate)values('CIM13457717','S300-1710-DRX-U',7,'2015-05-07 02:15:52.000')
insert into #S3(invNum,Pstatus,Model,enddate)values('CIM13704089','S300-1710-DRX-U',7,'2015-05-07 02:15:52.000')
--My Initial Query:
;with CTE
AS
(
Select t.ItemDate, t.ItemNum, t.OPType, t2.RecoveredSearial, t2.SearialUsed, t2.recovereddatetime
From #Mainsample t
Left Join #S1 t2
On t2.SearialUsed = t.ItemNum
)
Select t.ItemDate, COalesce(t.ItemNum,t.RecoveredSearial), t.OPType, t.recovereddatetime, t.RecoveredSearial, t2.Locationid, t2.invNum
from CTE t
Left Join #S2 t2
On (t2.ItemNum = t.ItemNum) or (t.RecoveredSearial = t2.ItemNum)
Desired Result:
ItemNum--------------PStatus----Model---------------EndDate-------------LocationID
===================================================================================
990005710331878--------7-----S300-1711-DR-U---2015-05-02 17:51:33.000-------FG
990005335301686--------7-----S300-1710-DR-U---2015-05-05 09:07:56.000-------FG
M9000000000020510092---7-----S300-1710-DRX-U--2015-05-07 02:15:52.000-------Scrp
353852061764483--------7-----S300-1710-DRX-U--2015-05-07 02:15:52.000-------FG