计算两行之间的日期范围

时间：2022-03-19 21:32:17

I have a table where I have materials cost which get change based on the dates and get recorded into the table. Please see below image for the more details. 计算两行之间的日期范围

我有一张桌子,我的材料成本会根据日期变化并记录到表格中。有关详细信息,请参阅下图。

If you see input table, in first row material XYZ cost is 43.14 from '6/29/2018' to'12/31/9999' and in second row again cost of the material get change to 44 and now datefrom is '8/3/2018' and so on, now I want to get the date range(dateto) for the first row based on the second row datefrom which will be less than one day from the second datefrom ('8/2/2018'), highlighted in required out put table.

如果你看到输入表,在第一行材料中,XYZ成本从“6/29/2018”到“12/31/9999”为43.14,在第二行中,材料的成本再次变为44,现在datefrom为'8 / 3/2018'等等,现在我想基于第二行日期得到第一行的日期范围(dateto),距离第二个日期('8/2/2018')不到一天,在所需的输出表中突出显示。

I have tried inner join and other queries but unable to get the required output.

我已尝试内部联接和其他查询但无法获得所需的输出。

1 个解决方案

#1

0  

Can you try with this?

你能尝试一下吗?

WITH TEMP AS
(
    SELECT 
        RANK() OVER ( PARTITION BY MATERIAL ORDER BY DATEFROM ASC) AS ASC_RANK,
        RANK() OVER ( PARTITION BY MATERIAL ORDER BY DATEFROM DESC) AS DES_RANK,
        MATERIAL, COST, DATEFROM, DATETO
    FROM 
)
SELECT MATERIAL, COST, DATEFROM, DATETO
FROM TEMP
WHERE DES_RANK = 1 

UNION ALL 

SELECT T1.MATERIAL, T1.COST, T1.DATEFROM, (T2.DATETO - 1) AS DATETO
FROM TEMP T1
JOIN TEMP T2 ON T1.MATERIAL = T2.MATERIAL AND T1.ASC_RANK = (T2.ASC_RANK + 1)

#1

0  

Can you try with this?

你能尝试一下吗?

WITH TEMP AS
(
    SELECT 
        RANK() OVER ( PARTITION BY MATERIAL ORDER BY DATEFROM ASC) AS ASC_RANK,
        RANK() OVER ( PARTITION BY MATERIAL ORDER BY DATEFROM DESC) AS DES_RANK,
        MATERIAL, COST, DATEFROM, DATETO
    FROM 
)
SELECT MATERIAL, COST, DATEFROM, DATETO
FROM TEMP
WHERE DES_RANK = 1 

UNION ALL 

SELECT T1.MATERIAL, T1.COST, T1.DATEFROM, (T2.DATETO - 1) AS DATETO
FROM TEMP T1
JOIN TEMP T2 ON T1.MATERIAL = T2.MATERIAL AND T1.ASC_RANK = (T2.ASC_RANK + 1)
标签：

相关文章