Identifying the column name, the selection of max of date is selected

Question

Hi All,
I have 5-6 Date Columns and I have a script by joining two tables which calculated the Last date of record which is max of the date. Now I need to identity the column name the date is picked up.

In short from the calculation of the columns, if any max or min of columns is measured, how do trace back to the the column which we have as a measure.

Answer 1

Hi @Mythbuster
Please check this:

CREATE TABLE #Test(ID INT IDENTITY(1,1),T1 DATE,T2 DATE,T3 DATE,Supplier1 INT,Supplier2 INT,Supplier3 INT,Supplier4 INT,Supplier5 INT,Supplier6 INT)  
INSERT INTO #Test (T1,T2,T3,Supplier1,Supplier2,Supplier3 ,Supplier4 ,Supplier5 ,Supplier6)VALUES  
('2018/1/23','2018/1/23','2018/1/23',150,193,203,283,349,362),  
('2015/2/15','2015/2/17','2015/2/21',200,234,300,326,345,360),  
('2014/3/12','2014/3/14','2014/3/19',250,317,360,396,423,462)  
--SELECT * FROM #Test  
  
;WITH CTE1 AS  
(  
SELECT ID,T.*,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY T.Date DESC)AS Date_RNum    
FROM #Test CROSS APPLY(VALUES(T1,'T1'),(T2,'T2'),(T3,'T3'))T(Date,Date_Column)  
),CTE2 AS  
(  
SELECT ID,S.*,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY S.Supplier DESC)AS Supplier_RNum   
FROM #Test CROSS APPLY(VALUES(Supplier1, 'Supplier1'),  
		                     (Supplier2, 'Supplier2'),  
							 (Supplier3, 'Supplier3'),  
							 (Supplier4, 'Supplier4'),  
							 (Supplier5, 'Supplier5'),  
							 (Supplier6, 'Supplier6'))S(Supplier,Supplier_Column)  
)  
SELECT C1.ID,C1.Date AS MAX_Date,C1.Date_Column,C2.Supplier AS MAX_Supplier,C2.Supplier_Column   
FROM CTE1 C1 JOIN CTE2 C2 ON C1.ID=C2.ID  
WHERE Date_RNum=1 AND Supplier_RNum=1  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Can you script the above as create table and insert some data and desired result based on that?

That will allow us to provide a tested solution.

Based on the image alone I know you have to use CROSS APPLY from VALUES (...) technique to know the max value, so the sketch of the solution would be:

;with cte as (select T.ID, X.* from Measure t
cross apply (values (t.dATE1, 'Date1', Supplier1, 'Supplier1'), (t.Date2, 'Date2', Supplier2, 'Supplier2'), etc. ) X (dt, DateColumn, Supplier, SupplierColumn))

cte2 as (select *, ROW_NUMBER() over (partition by ID order by Supplier DESC) as RnS, ROW_NUMBER() over (partition by ID order by dt DESC) as RnD from cte)

select ID, dt, Supplier, SupplierColumn as MaxVal from cte2 where RnS = 1 -- max Supplier
UNION ALL
select ID, dt, Supplier, DateColumn as from cte2 where RnD = 1 -- max date

Answer 3

Hi @LiHong-MSFT
Thank you very much. I will try this now.

Answer 4

 ;with mycte  as (
SELECT [KEY] as TheKey, Value as TheValue 
from OpenJson((select * from #Test  FOR JSON AUTO ))
)

  ,mycte2 as ( 
 SELECT TheKey,unpvt.[Key] k1, unpvt.Value v1
  , row_number() Over(Partition by TheKey, Case when unpvt.[Key] Like 'T_' then 'T' else '|' end  Order by unpvt.Value desc) rn1
  , row_number() Over(Partition by TheKey, Case when unpvt.[Key] Like 'Supplier_' then 'S' else '^' end  Order by unpvt.Value desc) rn2
 FROM mycte AS src
 CROSS APPLY OpenJson(src.TheValue) AS unpvt 
 )

 select  
 TheKey+1 as ID,
 max(Case when rn1=1 and k1 Like 'T_' then v1 else null end) MAX_Date,
 max(Case when rn1=1 and k1 Like 'T_' then k1 else null end)  Date_Column,
 max(Case when rn2=1 and k1 Like 'Supplier_' then v1 else null end) Supplier_Column,  
 max(Case when rn2=1 and k1 Like 'Supplier_' then k1 else null end) MAX_Supplier
from mycte2
group by TheKey