Multiple table into one result set

Villanuev · July 25, 2015, 2:54pm

Hi Guys,
Basically my requirements is to find how many items where finished good and scrap based on the items received. Let say 100 items was received and 50 was finished good and 30 was scrap the remaining will be a WIP or still not transacted or no history or not yet FG/scrap. The data are came from different tables. Your help is very mich appreciated.

    --drop table #Receiving
    create table #Receiving--Stored all received Item
    (esn nvarchar(35), auditdate datetime, optype int)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019761824','2015-01-03 19:23:53.383',4)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019761824','2015-01-03 19:23:54.980',5)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019802529','2015-01-10 19:23:53.383',4)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019802529','2015-01-10 19:23:54.980',5)
    insert into #Receiving(esn,auditdate,optype ) values('990002462951458','2013-09-04 16:16:24.117',4)
    insert into #Receiving(esn,auditdate,optype ) values('990002462951458','2013-09-04 16:16:26.397',5)
    insert into #Receiving(esn,auditdate,optype ) values('990002462951458','2015-01-22 03:53:38.020',4)
    insert into #Receiving(esn,auditdate,optype ) values('990002462951458','2015-01-22 03:53:40.160',5)
    insert into #Receiving(esn,auditdate,optype ) values('990001216564906','2015-01-26 13:54:51.293',4)
    insert into #Receiving(esn,auditdate,optype ) values('990001216564906','2015-01-26 13:54:53.343',5)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019762159','2015-01-03 23:16:39.993',4)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019762159','2015-01-03 23:16:41.617',5)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019761802','2015-01-03 19:10:10.937',4)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019761802','2015-01-03 19:10:12.447',5)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019921108','2015-01-27 19:43:08.383',4)
    insert into #Receiving(esn,auditdate,optype ) values('T9000000000019921108','2015-01-27 19:43:10.000',5)
    insert into #Receiving(esn,auditdate,optype ) values('990000676842596','2015-01-13 19:43:08.383',4)
    insert into #Receiving(esn,auditdate,optype ) values('990000676842596','2015-01-13 19:43:10.000',5)
    
    
    
    
    --drop table #Recovered
    create table #Recovered--Stored All recovered ESN/TON
    (prodid nvarchar(35), serialid nvarchar(35), recoveredesn nvarchar(35), recovereddate datetime)
    insert into #Recovered(prodid, serialid,recoveredesn, recovereddate) values('TR005065526','T9000000000019761824','354403060637509','2015-02-04 08:19:40.000')
    insert into #Recovered(prodid, serialid,recoveredesn, recovereddate) values('TR005271772','T9000000000019761824','354403060637509','2015-03-19 07:07:44.000')
    insert into #Recovered(prodid, serialid,recoveredesn, recovereddate) values('TR004948393','T9000000000019802529','990002599795240','2015-01-19 07:07:55.000')
    insert into #Recovered(prodid, serialid,recoveredesn, recovereddate) values('TR005106042','T9000000000019762159','354391062163204','2015-02-11 07:37:37.000')
    insert into #Recovered(prodid, serialid,recoveredesn, recovereddate) values('TR005803934','T9000000000019761802','354410061757061','2015-07-11 03:44:54.000')
    insert into #Recovered(prodid, serialid,recoveredesn, recovereddate) values('TR005065591','T9000000000019921108','358755056706937','2015-02-04 08:23:57.000')
    insert into #Recovered(prodid, serialid,recoveredesn, recovereddate) values('TR005075553','T9000000000019921108','358755056706937','2015-02-06 01:19:17.000')
    
    
    
    --drop table #journaldb
    create table #journaldb-- get the finishedtime(posteddate)for the production order
    (prodid nvarchar(35), journalname nvarchar(35), posteddate datetime)
    insert into #journaldb(prodid, journalname, posteddate)values('TR005065526','ProdRAF','2015-02-07 01:50:08.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR005271772','ProdRAF','2015-03-20 08:43:07.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR004948393','ProdRAF','2015-01-19 08:43:07.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR003373712','ProdRAF','2013-09-16 09:16:36.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR005750905','ProdRAF','2015-07-28 16:59:08.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR004831357','ProdRAF','2014-12-15 21:10:32.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR004465783','ProdRAF','2014-09-15 20:21:19.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR005075553','ProdRAF','2015-02-14 03:07:27.000')
    insert into #journaldb(prodid, journalname, posteddate)values('TR005048832','ProdRAF','2015-02-05 04:33:21.000')
    
    
    
    --drop table #Production
    create table #Production--found all finished (ended po/open po)
    (prodid nvarchar(35), datefinished datetime, prodstatus int, invintdimid nvarchar(35), createddate datetime, esnstatus int)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR005065526','2015-02-07 01:50:08.000',7,'DIM12597019','2015-02-04',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR005271772','2015-03-20 08:43:07.000',7,'DIM12597019','2015-03-19',1)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR004948393','2015-01-15 08:43:07.000',7,'DIM12286679','2015-01-14',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR003373712','2013-09-16 08:43:07.000',7,'DIM10049529','2013-09-15',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR005750905','2015-07-29 08:43:07.000',7,'DIM10049529','2015-07-01',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR004831357','2014-12-16 08:43:07.000',7,'DIM10049529','2014-12-14',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR004465783','2014-09-16 20:21:19.000',7,'DIM14206564','2014-09-13',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR005803934','1900-01-01 00:00:00.000',4,'DIM11456551','2015-07-11',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR005075553','2015-02-14 03:07:27.000',4,'DIM12695446','2015-02-06 01:19:15.000',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR005106042','1900-01-01 00:00:00.000',4,'DIM12652450','2015-02-11 07:37:36.000',0)
    insert into #Production(prodid, datefinished, prodstatus, invintdimid,createddate,esnstatus) values('TR005048832','2015-02-05 04:33:21.000',7,'DIM12567933','2015-02-05 04:33:22.000',0)
    
    
    --drop table #inventdim
    create table #inventdim
(invetdimid nvarchar(35), location nvarchar(35), inventserialid nvarchar(35))
insert into #inventdim(invetdimid, location, inventserialid) values('DIM12597019','IPHFG','354403060637509')
insert into #inventdim(invetdimid, location, inventserialid) values('DIM12286679','IPHFG','990002599795240')
insert into #inventdim(invetdimid, location, inventserialid) values('DIM10049529','IPHFG','990002462951458')
insert into #inventdim(invetdimid, location, inventserialid) values('DIM11456551','IPHFG','990001216564906')
insert into #inventdim(invetdimid, location, inventserialid) values('DIM12695446','IPHSO','358755056706937')
insert into #inventdim(invetdimid, location, inventserialid) values('DIM12652450','IPHFG','T9000000000019762159')
insert into #inventdim(invetdimid, location, inventserialid) values('DIM12567933','SCRP','990000676842596')
insert into #inventdim(invetdimid, location, inventserialid) values('DIM10049529','SCRP','990002462951458')

bitsmed · July 25, 2015, 5:29pm

Your create statements doesn't work

Kristen · July 27, 2015, 8:34am

Create Table names different to Insert Into table names ...

Villanuev · August 7, 2015, 12:59am

Sorry for my late reply. I already modified the sample data and my query. The result of my query will pull all the items received from Warehouse and match it with the first production id. as you can see in the result i have a multiple receipts or production order for the same esn_no and get the first transaction with an esn status as zero. however, I get the multiple ESN records. Also, this esn no 990000676842596, was already finished/FG but the prodid was not appear in the column and the esn status . there are some ESN no that did not transacted or perform the ESN recovered. it was received as ESN no label not with letter T. Mostly transacted or perform recovered are those esn no that start with letter 'T' because they received this with TON label.

Herewith is my query and result. indicate also the desired result.

Query and result:

    -- Variables
    declare  @beg_date datetime
    declare  @end_date datetime
    declare  @model varchar(20)
    set      @beg_date = '2015-1-1'
    set      @end_date = cast(getdate() - 1 as date)
    ;
    
    select	distinct
    		'TLS' as 'asia db'
    		,tb.esn as tlcs_no
    		,rs.serialid as esn_no
    		,rs.recoveredesn 
    		,rs.prodid
    		,tb.auditdate as Received_Date
    		,pt.datefinished
    		,case when optype=4 then 'Received'
    		      when optype=5 then 'shipping'
    		 end as op_desc     
    		 ,pt.esnstatus
    from	#Receiving tb  with(nolock)
    		 left join #Recovered rs 
    			 on rs.serialid = tb.esn COLLATE Chinese_Taiwan_Stroke_CI_AS
    		 left join #Production  pt
    			on rs.PRODID = pt.prodid and  pt.esnstatus =0
    		 left join #inventdim id
    			 on pt.invintdimid = id.invetdimid 
    where	tb.optype IN (5)
    		and tb.auditdate >= @beg_date and tb.auditdate <@end_date
    order by tlcs_no

asia--tlcs_no--------------	esn_no----------------	recoveredesn-----prodid-------Received_Date-------------datefinished-------------op_desc--	esnstatus
TLS--990000676842596	---	NULL					NULL			--NULL		---2015-01-13 19:43:10.000--NULL					-shipping--	NULL
TLS--990001216564906	---	NULL					NULL			--NULL		---2015-01-26 13:54:53.343--NULL					-shipping--	NULL
TLS--990002462951458	---	NULL					NULL			--NULL		---2015-01-22 03:53:40.160--NULL					-shipping--	NULL
TLS--T9000000000019761802--	T9000000000019761802--	354410061757061	--TR005803934--2015-01-03 19:10:12.447--1900-01-01 00:00:00.000	-shipping--	0
TLS--T9000000000019761824--	T9000000000019761824--	354403060637509	--TR005065526--2015-01-03 19:23:54.980--2015-02-07 01:50:08.000	-shipping--	0
TLS--T9000000000019761824--	T9000000000019761824--	354403060637509	--TR005271772--2015-01-03 19:23:54.980--NULL					-shipping--	NULL
TLS--T9000000000019762159--	T9000000000019762159--	354391062163204	--TR005106042--2015-01-03 23:16:41.617--1900-01-01 00:00:00.000	-shipping--	0
TLS--T9000000000019802529--	T9000000000019802529--	990002599795240	--TR004948393--2015-01-10 19:23:54.980--2015-01-15 08:43:07.000	-shipping--	0
TLS--T9000000000019921108--	T9000000000019921108--	358755056706937	--TR005065591--2015-01-27 19:43:10.000--NULL					-shipping--NULL
TLS--T9000000000019921108--	T9000000000019921108--	358755056706937	--TR005075553--2015-01-27 19:43:10.000--2015-02-14 03:07:27.000	-shipping--	0
         
 

Desired Result:


asia--tlcs_no--------------	esn_no----------------	recoveredesn-----prodid-------Received_Date-------------datefinished-------------op_desc--	esnstatus
TLS--990000676842596	---	NULL					NULL			--NULL		---2015-01-13 19:43:10.000--NULL					-shipping--	NULL
TLS--990001216564906	---	NULL					NULL			--NULL		---2015-01-26 13:54:53.343--NULL					-shipping--	NULL
TLS--990002462951458	---	NULL					NULL			--NULL		---2015-01-22 03:53:40.160--NULL					-shipping--	NULL
TLS--T9000000000019761802--	T9000000000019761802--	354410061757061	--TR005803934--2015-01-03 19:10:12.447--1900-01-01 00:00:00.000	-shipping--	0
TLS--T9000000000019761824--	T9000000000019761824--	354403060637509	--TR005065526--2015-01-03 19:23:54.980--2015-02-07 01:50:08.000	-shipping--	0
TLS--T9000000000019762159--	T9000000000019762159--	354391062163204	--TR005106042--2015-01-03 23:16:41.617--1900-01-01 00:00:00.000	-shipping--	0
TLS--T9000000000019802529--	T9000000000019802529--	990002599795240	--TR004948393--2015-01-10 19:23:54.980--2015-01-15 08:43:07.000	-shipping--	0
TLS--T9000000000019921108--	T9000000000019921108--	358755056706937	--TR005075553--2015-01-27 19:43:10.000--2015-02-14 03:07:27.000	-shipping--	0

Villanuev · August 9, 2015, 10:39am

Hi Guys, anybody can help me to pull the desired result based on the samples data i posted or any idea. Thank you.

bitsmed · August 9, 2015, 12:42pm

Try this:

declare  @beg_date datetime='2015-1-1';
declare  @end_date datetime=dateadd(dd,-1,getdate());

select 'TLS' as [asia db]
      ,esn as tlcs_no
      ,serialid as esn_no
      ,recoveredesn
      ,prodid
      ,auditdate as Received_Date
      ,datefinished
  from (select tb.esn
              ,rs.serialid
              ,rs.recoveredesn
              ,rs.prodid
              ,tb.auditdate
              ,pt.datefinished
              ,case
                  when optype=4 then 'Received'
                  when optype=5 then 'shipping'
               end as op_desc
              ,pt.esnstatus
              ,row_number() over(partition by tb.esn
                                             ,rs.serialid
                                             ,rs.recoveredesn
                                 order by pt.datefinished
                                ) as rn
          from #Receiving as tb
               left outer join #Recovered as rs 
                            on rs.serialid=tb.esn collate Chinese_Taiwan_Stroke_CI_AS
               left outer join #Production as pt
                            on rs.PRODID=pt.prodid
                           and pt.esnstatus=0
               left outer join #inventdim as id
                            on pt.invintdimid=id.invetdimid 
         where tb.optype=5
           and tb.auditdate>=@beg_date
           and tb.auditdate<@end_date
       ) as a
 where rn=1
 order by esn
;

Oh yeah, please read this regarding the use of nolock

Villanuev · August 10, 2015, 2:00am

Thank you bitsmed,

I make an evaluation with the result, i notice there are some data that has a different prodid. like for ESN T9000000000019761802, there was only one receipt(#receiving), so it maches the first production order (#production)TR005065526, TR005015004 doesnt have a record while there is not another receipts before TR005271772. it will be missing the receipt records.

another one, this esn 90002462951458 receipt should have a record prodid TRC005750905. it was ended last july 29 as you can see in #production. but it was displayed a null values.

It possible to pull all asuesntype =0 and then all the transaction that doens't have yet a transaction in #prodtable.

Thank you for your kind help.