Get the latest date and retrieve information

Transact-SQL
1

I am writing a query that pulled year to date and get the data that has optype 4,5,51,55. Then i retrieve only those esn that has an optype 4,5. if this criterea does not meet i will not included in the result.so far my query got the result but i have additional requirement. if an esn has optype 4,5 and the last optype is 51, i will get the information of supplier_ref and create another column for supplier_ref and new optype. below is a sample query and result. thank you.

[code]create table #sample
(esn nvarchar(35), supplier_ref nvarchar(35), ctype int, optype int, transactiondate datetime)
insert into #sample
select '913015092931982','NULL',9,4,'2015-04-24 23:22:29.040' union all
select '913015092931982','NULL',9,5,'2015-04-24 23:22:31.337' union all
select '913015092931982','2015 cleanup',9,51,'2016-01-04 16:01:00.740' union all
select '357670060732870','NULL',5,4,'2016-01-08 20:47:15.520' union all
select '357670060732870','NULL',5,5,'2016-01-08 20:47:17.683' union all
select '357670060733333','2015 Clean up',5,51,'2016-01-15 10:29:20.767' union all
select 'B8000000000022107094','PTA-418 - PH',1,4,'2015-11-14 23:30:22.603' union all
select 'B8000000000022107094','PTA-418 - PH',1,5,'2015-11-14 23:30:24.880' union all
select 'B8000000000022107094','PTA-418 - PH',1,56,'2016-01-10 23:07:02.500' union all
select 'B9000000000022167531','PTA- 418921649 - IPH',5,4,'2016-1-20 12:15:49.610' union all
select 'B9000000000022167531','PTA- 418921649 - IPH',5,5,'2016-1-20 12:15:51.240' union all
select 'B9000000000022167531','JIG for PM January 19, 2016',5,51,'2016-02-8 10:26:35.130' union all
select '990002015919291','NULL',5,55,'2016-01-13 11:24:29.077' union all
select 'B9000000000022167535','NULL',5,4,'2016-1-23 12:15:51.240' union all
select 'B9000000000022167535','NULL',5,5,'2016-1-23 12:15:52.560'

declare @begdate datetime
declare @enddate datetime
set @begdate ='2016-1-1'
set @enddate = getdate()

;With CTE
AS
(
select esn, supplier_ref , ctype, optype, transactiondate
from #sample
where transactiondate between @begdate and @enddate
and optype in (4,5,51,55)
)
select *
from
(
SELECT esn, supplier_ref, ctype, optype, transactiondate,
DENSE_RANK() OVER (PARTITION BY esn ORDER BY CASE WHEN optype IN (4,5) THEN 1 ELSE 2 END) AS RNK
FROM CTE c
WHERE EXISTS (
SELECT 1
FROM CTE
WHERE esn = c.esn and optype IN (4,5)
)) r
where r.RNK =1 and r.optype in (4)--,51, 55)

current result of this query

esn-------------------- supplier_ref----------ctype--optype--transactiondate---------RNK
357670060732870-------- NULL------------------ 5------4----2016-01-08 20:47:15.520---1
B9000000000022167531--- PTA- 418921649 - IPH 5------4----2016-01-20 12:15:49.610---1
B9000000000022167535--- NULL------------------ 5------4----2016-01-23 12:15:51.240---1

New desired result

esn-------------------- supplier_ref----------ctype--optype--transactiondate---------RNK--supplier_ref----------------new optype
357670060732870-------- NULL------------------ 5------4----2016-01-08 20:47:15.520---1---NULL---------------------------4
B9000000000022167531--- PTA- 418921649 - IPH 5------4----2016-01-20 12:15:49.610---1---JIG for PM January 19, 2016----51
B9000000000022167535--- NULL------------------ 5------4----2016-01-23 12:15:51.240---1---NULL---------------------------4[/code]

2

This may help you. Please try and feedback

select *
from
(
SELECT esn, supplier_ref, ctype, optype, transactiondate,   
    DENSE_RANK() OVER (PARTITION BY  esn  ORDER BY CASE WHEN optype IN (4,5)  THEN 1 ELSE 2 END) AS RNK
FROM CTE c
WHERE optype IN (4,5)
) r
LEFT OUTER JOIN cte c1
ON r.esn    = c1.esn    
AND c1.optype    =    51
where r.RNK =1 and r.optype in (4)
3

Thank you very much viggneshwar. working now. :slightly_smiling: