时间戳

首页 > 解决方案 > 如何找到第二个和最大日期并比较这两个

sql - 如何找到第二个和最大日期并比较这两个

问题描述

所以我需要从每个 SKU（数字）中找到最高和第二高的日期，并比较这两个日期，看看是否有 3 个月的差异。

下面是我的代码，我已经找到了第二高的日期，但我似乎无法让它与最高日期结合使用任何人进行救援？

代码 ：

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 !

标签： sqlsql-server

解决方案

我将勾勒出我的意思的答案，而不是尝试通过您的查询使其完美。这使用三个 CTE。X0 分配 RN 值。X1 挑选出 RN=1 的地方，X2 挑选出 RN=2 的地方。然后加入 X1 和 X2，并进行计算。

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

推荐阅读