How to find second and max date and compare these two

so I need to find the highest and second highest date from every SKU(number) and compare these 2 dates and see if there is a 3 months difference.

down below is my code I already found the second highest date but I can’t seem to make it work with a highest date in combination anyone for the rescue ?

code :

WITH X0 AS (
   SELECT 
 od.odscheduleid
,lc.shortname as line
,ar.erpcode as SKU
,ar.shortname as Article
,od.[PlannedEndInflow] as PlannedEndInflow
,od.[PlannedStartInflow] as PlannedStartInflow
,st.shortname as 'status'
,od.[ShortName] as 'order'
,od.[ERPCode] as ERPCode
,od.[ASCode] as ASCode
,od.[PlannedQuantity] as PlannedQuantityPC
,cast(od.[PlannedQuantity]*ar.Volume/100 as decimal(12,2)) as PlannedQuantityHL
,od.[PlannedChangeoverTime] as PlannedChangeoverTime
,od.[PlannedSpeed] as PlannedSpeed
,od.[PlannedSpeedRatio] as PlannedSpeedRatio,
         ROW_NUMBER()over(partition by ar.erpcode order by od.PlannedEndInflow desc) as rn 
     FROM (  

       [RM].[TblDatODSchedule] od

          inner join [EM].[TblLstLocation] lc on od.locationid=lc.locationid
          inner join [RM].[TblLstART] ar on od.artid=ar.artid
          inner join [EM].[TblLstStatus] st on od.statusid=st.statusid

           )
            where lc.ShortName = 'fil 3'--replaces lc.shortname in (@line)(reportserver)


), X1 AS (
     SELECT SKU,PlannedEndInflow, RN FROM X0 WHERE RN=2
), X2 AS (
     SELECT SKU, line ,Article, PlannedStartInflow,PlannedEndInflow,PlannedQuantityPC,PlannedQuantityHL,PlannedChangeoverTime,PlannedSpeed,PlannedSpeedRatio,ERPCode,ASCode, PlannedEndInflow as most_recent_date , RN FROM X0 WHERE RN=1
)

SELECT 
 case when DATEDIFF(day,X1.PlannedEndInflow,most_recent_date) >=91 then DATEDIFF(day,X1.PlannedEndInflow,most_recent_date)*0.0328549112  
     -- when  exists( select 1 from x1 where x1.sku = x2.SKU having count(sku) > 1) then 123
else null end as MONTHDIFF,
--SELECT DATEDIFF(day, X1.PlannedEndInflow,most_recent_date) AS MONTHDIFF,

        line,X1.SKU,article,PlannedStartInflow,x2.PlannedEndInflow,x1.PlannedEndInflow,PlannedChangeoverTime,PlannedSpeed,PlannedSpeedRatio,ERPCode,ASCode,PlannedQuantityPC,PlannedQuantityHL
FROM X1
   left JOIN X2 ON X1.SKU=X2.SKU

   where most_recent_date >='20200410' -- replaces most_recent_date > @starDate(reportserver)
   -- add exception where never used SKU's are also shown !

Answer

I’m going to sketch the answer I mean rather than try to make it perfect with your query. This uses three CTEs. X0 assigns the RN value. X1 picks out where RN=1 and X2 picks out where RN=2. Then you join X1 and X2, and do your calculation.

WITH X0 AS (
   SELECT somecolumns,
         ROW_NUMBER()over(partition by ar.erpcode order by od.PlannedEndInflow desc) as rn 
     FROM ( table join table join table )
     WHERE conditions
), X1 AS (
     SELECT somecolumns, RN FROM X0 WHERE RN=1
), X2 AS (
     SELECT somecolumns, RN FROM X0 WHERE RN=2
)
SELECT DATEDIFF(month, X1.datefield, X2.datefield) AS MONTHDIFF,
       X1.othercols, X2.othercols
FROM X1
   INNER JOIN X2 ON X1.something=X2.something

Advertisement

Answer