Find items from multiple records for specific status

Villanuev · January 27, 2016, 6:09am

Hi,

I made a query that gives me all the information that i need for my final report but it i have a difficult time on how to get my required final output. I wanted to pull all those esn1 that has the following op_status as CMA Receive including Open, create order, shipping and finished good specially the CMA Recieve which pertains that this esn is receive it. any help is very much appreciated. thank you.

below is my DDL.. the sample data is the result of my main query the sample desired result.

[code]create table #sample1
(esn1 nvarchar(35), esn2 nvarchar(35), esn3 nvarchar(35), idnum nvarchar(35), trandate datetime, supplier varchar(5), model nvarchar(35), opDesc nvarchar(35), op_status nvarchar(35))
insert into #sample1
select 'B1000000000022422990','058536058570426','058536058570426','PRC006731939','2016-01-06 04:21:56.080','TCS','TALV6000425iPH5C16','CMA Receive','CMA Receive'
union all
select 'B1000000000022422990','058536058570426','058536058570426','PRC006731939','2016-01-06 04:21:57.717','TCS','TALV6000425iPH5C16','CMA shipped','CMA Shipped'
union all
select 'B1000000000022422990','058536058570426','058536058570426','PRC006731939','2016-01-22 23:44:57.000','PROD','N600-0425-IPH5C16-U','Created Order','Creatde Order'
union all
select 'B1000000000022422990','058536058570426','058536058570426','PRC006731939','2016-01-24 00:24:57.000','PROD','N600-0425-IPH5C16-U','Order Complete','Finished good'
union all
select 'B1000000000022422990','058536058570426','058536058570426','PRC006731939','2016-01-24 05:39:08.410','TLK','N600-0412-IPH5C16-U','Shipping','Shipping'
union all
select '013329006770539','513329006770539','NULL','NULL','2016-01-05 04:38:59.620','TLK','N231-0440-IPH5C8-U','Receive','Receive'
union all
select '013329006770539','513329006770539','NULL','NULL','2016-01-05 04:39:14.947','TLK','N231-0440-IPH5C8-U','Pass','Pass'
union all
select 'B1000000000020074152','654452062437950','654452062437950','PRC006708329','2016-01-19 00:07:40.000','PROD','N400-0560-iPH6P128-U','Created Order','Created Order'
union all
select 'B1000000000020074152','654452062437950','654452062437950','PRC006708329','2016-01-22 17:15:16.000','PROD','N400-0560-iPH6P128-U','Order Complete','Finished Good'
union all
select 'B1000000000020074152','654452062437950','654452062437950','PRC006708329','2016-01-23 01:22:52.427','TLK ','SHELL4000430iPH6P64SIL','Shipping','Shipping'
union all
select 'B1000000000022541529','B1000000000022541529','NULL','NULL','2016-01-20 12:19:44.027','EXS','TALV8000460iPH664','CMA Receive','CMA Receive'
union all
select 'B1000000000022541529','B1000000000022541529','NULL','NULL','2016-01-20 12:19:45.820','EXS','TALV8000460iPH664','CMA shipped','CMA shipped'
union all
select 'B1000000000022541529','B1000000000022541529','358373064606949','PRC006730640','2016-01-22 20:13:38.000','PROD','N400-0460-IPH664-U','Create Order','Open'

--Desired Result
result 1

model------------esn1------------------esn2-------------------esn3--------------------------------CMA receiptdate-----Created Order date -----Order Complete date---------shipping date------------op_status
TALV6000425iPH5C16----B1000000000022422990--058536058570426-------058536058570426--2016-01-06 04:21:57.717---2016-01-22 23:44:57.00-- 2016-01-24 00:24:57.00-- 2016-01-24 05:39:08.410----Finished good
TALV8000460iPH664-----B1000000000022541529--B1000000000022541529-------------------2016-01-22 20:13:38.000--- ----------------------------------------------------------------------------Open

result 2

esn1------------------esn2------------------esn3--------------idnum--------trandate----------------supplier--- model----------opDesc---------op_status
B1000000000022422990--058536058570426-------058536058570426--PRC006731934-- 2016-01-06 04:21:56.080--TCS----TALV6000425iPH5C16---CMA Receive-- CMA Receive
B1000000000022422990--058536058570426-------058536058570426--PRC006731934-- 2016-01-06 04:21:57.717--TCS----TALV6000425iPH5C16---CMA Shipping---CMA Shipping
B1000000000022422990--058536058570426-------058536058570426--PRC006731934-- 2016-01-22 23:44:57.000--PROD---N600-0425-IPH5C16----Created Order--Creatde Order
B1000000000022422990--058536058570426-------058536058570426--PRC006731934-- 2016-01-24 00:24:57.000--PROD---N600-0425-IPH5C16----Order Complete--Finished good
B1000000000022422990--058536058570426-------058536058570426--PRC006731934-- 2016-01-24 05:39:08.410--TLK----N600-0412-IPH5C16----Shipping --Shipping
B1000000000022541529--B1000000000022541529--NULL-------------NULL---------- 2016-01-20 12:19:44.027--EXS----TALV8000460iPH664----CMA Receive-- CMA Receive
B1000000000022541529--B1000000000022541529--NULL-------------NULL---------- 2016-01-20 12:19:45.820--EXS----TALV8000460iPH664----CMA Shipping-- CMA Shipping
B1000000000022541529--B1000000000022541529--358373064606949--PRC006730647-- 2016-01-22 20:13:38.000--PROD---N400-0460-IPH664-----Create Order-- Open[/code]

djj55 · January 27, 2016, 11:49am

What does your query look like? Can you not use an OR in the where clause to list the possible values?

ScottPletcher · January 27, 2016, 4:13pm

I don't fully understand what you want, but I think the query below should at least be close to the first query you need. I don't understand enough yet about what's required for the 2nd query to even try writing it yet.

SELECT
    MAX(CASE WHEN opDesc = 'CMA shipped' THEN model END) AS model,
    s1.esn1, s1.esn2, 
    MAX(CASE WHEN opDesc = 'CMA shipped' THEN esn3 END) AS esn3,
    MAX(CASE WHEN opDesc = 'CMA shipped' THEN trandate END) AS [CMA receiptdate],
    MAX(CASE WHEN opDesc = 'Created Order' THEN trandate END) AS [Created Order date],
    MAX(CASE WHEN opDesc = 'Order Complete' THEN trandate END) AS [Order Complete date],
    MAX(CASE WHEN opDesc = 'Shipping' THEN trandate END) AS [shipping date],
    SUBSTRING(
        MAX(CASE WHEN opDesc = 'CMA Receive' THEN '1' + op_status
                 WHEN opDesc IN ( 'Create Order', 'Created Order' ) THEN '2' + op_status
                 WHEN opDesc = 'Order Complete' THEN '3' + op_status
                 WHEN opDesc = 'Shipping' THEN '4' + op_status
                 END)
        , 2, 100) AS op_status
FROM #sample1 s1
GROUP BY s1.esn1, s1.esn2
HAVING MAX(CASE WHEN opDesc = 'CMA Receive' THEN 1 ELSE 0 END) = 1

Villanuev · January 28, 2016, 2:01am

Thank you very much for the promp reply. Just run the script and it gives me close to my requirements. adding new sample data which i found out it display some inconsistency like this esn1 B9000000000019761829 and B9000000000022532176, B9000000000019761800. for this esn1 B9000000000019761829 it has multiple created order and order complete but i will get the latest order complete. for this case it has a CMA Receive once, the first create order and order complete has a problem cannot be shipping, then it returns again in production it perform again a created order and it become order complete and it was shipped the item. for this esn1 B9000000000019761800 this is an outright scrap which it was receive but does not perform PO transaction or does not enter in production.

Btw the way, below is the script that i made. its a little bit close but some data does not display the correct information specially this esn1 B9000000000019761829.

below is the added new sample data and script

select 
		 t1.esn1, t1.esn2, t1.esn3, t1.idnum, t1.trandate as Cores_Receipt, t1.model, t1.opDesc, t1.op_status as C_Status,
		 t2.trandate as PO_created, t3.trandate as PO_ended , t4.trandate as shipping_date, t2.op_status  as PO_Status, 
		 case when t2.op_status ='Open PO' then isnull(t3.op_status, t2.op_status)
		      when t1.op_status='CMA Receive' and t2.trandate is NULL then  isnull(t3.op_status, t1.op_status)
		      when t1.op_status='Scrap' and t2.trandate is NULL then  isnull(t3.op_status, t1.op_status) else t3.op_status 
		 end as Prod_Status
from #sample1  t1
		left outer join  
		(
			select  esn1 , esn2, esn3, idnum ,opDesc ,op_status , trandate     
			from #sample1
			where opDesc  = 'Created order'
		) as t2
		on t1.esn1 = t2.esn1
		left outer join  
		(
			select  esn1, esn2, esn3 , idnum ,opDesc ,op_status , trandate
			from #sample1 
			where opDesc = 'Order Complete'
			) t3
		on t1.esn1  = t3.esn1 
		left outer join  
		(
			select  esn1 , esn2, esn3 , idnum ,opDesc , op_status   , trandate   
			from #sample1
			where opDesc = 'Shipping'
		) as t4
		on t1.esn1  = t4.esn1  
where 
	 t1.opDesc  in ('CMA Receive','Scrap')

create table #sample1
(esn1 nvarchar(35), esn2 nvarchar(35), esn3 nvarchar(35), idnum nvarchar(35), trandate datetime, supplier varchar(5), model nvarchar(35), opDesc nvarchar(35), op_status nvarchar(35)) 
insert into #sample1
select 'B9000000000019761829','B9000000000019761829','0354403060637509','PRC905065526','2015-01-03 19:23:53.383','EXS','NOR6000430iPH66','CMA Receive','CMA Receive'
UNION ALL
select 'B9000000000019761829','B9000000000019761829','0354403060637509','PRC005065526','2015-01-03 19:23:54.980','EXS','NOR6000430iPH66','CMA shipped','CMA shipped'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905065526','2015-02-04 16:19:40.000','PROD','N600-0430-IPH66','Created Order','Created Order'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905065526','2015-02-07 09:50:08.000','PROD','N600-0430-IPH66','Order Complete','Finished Good'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905271772','2015-03-19 15:07:43.000','PROD','N600-0430-IPH66','Created Order','Created Order'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905271772','2015-03-20 16:43:07.000','PROD','N600-0430-IPH66-U','Order Complete','Finished Good'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905271772','2015-03-20 17:40:51.303','TLK','NOR6000430iPH66','Write','Write'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905271772','2015-03-20 18:02:48.467','PRC','NOR6000430iPH66','Pass','Pass e'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905271772','2015-03-27 09:17:12.213','PRC','N600-0430-IPH66','Print','Print'
UNION ALL
select 'B9000000000019761829','0354403060637509','0354403060637509','PRC905271772','2015-03-27 09:27:11.150','PRC','N600-0430-IPH66','Shipping','Shipping'
UNION ALL
select 'B9000000000019761829','T9000000000019761829','NULL','NULL','2016-01-08 17:05:33.790','XCS','NOR6000430iPH664','Write','Write'
union all
select 'B9000000000019761800','T9000000000019761800','NULL','NULL','2016-01-08 21:05:33.700','XCS','NOR6000430iPH664','Scrap','Scrap'
union all
select 'B9000000000022532176','B9000000000022532170','054387066609140','PRC806735084','2016-01-19 16:13:26.360','XCS','KP6000560iPH6','CMA Receive','CMA Receive'
union all
select 'B9000000000022532176','B9000000000022532170','054387066609140','PRC806735084','2016-01-19 16:13:28.390','CCS','KP6000560iPH6','CMA Shipped','CMA Shipped'
union all
select 'B9000000000022532176','054387066609140','054387066609140','PRC806735084','2016-01-23 10:24:10.000','PROD','R600-0560-iPH6','Created Order','Open PO'
union all
select 'B1000000000022541529','B1000000000022541529','NULL','NULL','2016-01-20 12:19:44.027','EXS','TALV8000460iPH664','CMA Receive','CMA Receive'
union all
select 'B1000000000022541529','B1000000000022541529','NULL','NULL','2016-01-20 12:19:45.820','EXS','TALV8000460iPH664','CMA shipped','CMA shipped'

Find items from multiple records for specific status

--Desired Result result 1

result 2

--Desired Result
result 1