SQL - 按年，月，日的日期组 [英] SQL - date group by year, month, days

问题描述

当我执行查询

SELECT Item,
    [From date],
    [To date]
 from ItemDate;

我有这个表：

('A1','2014-03-05','2014-07-21'),
('A1','2014-07-25','2015-03-15'),
('A1','2015-03-17','2016-03-17'),
('B1','2015-04-18','2016-06-16'),
('C1','2015-04-21','2016-02-12'),
('C1','2016-02-14','2016-08-29')

我想计算日期之间的差异 [从日期] 和 [到目前为止] 并获得下一张表：

I want to calculate difference between date [From date] and [To date] and get next table:

Item   Year    NoOfMonth  NoOfDays
A1    2014       9          ...
A1    2015       ...        ...
........

Group by Year and item

• NoOfMonts 是按年份和项目的月份数量


• NoOfDays 是年份和项目的天数。


• NoOfMonts is number of months by year and item
• NoOfDays is number of days by year and item.

任何想法？

推荐答案

您可以使用日历表和一些日期功能：

You can use calendar table and some date functions:

;WITH calendar AS (
SELECT  CAST(MIN([From date]) as datetime) as d,
        MAX([To date]) as e
FROM ItemTable
UNION ALL
SELECT  DATEADD(day,1,d),
        e
FROM calendar
WHERE d < e
), cte AS(
SELECT  i.Item,
        DATEPART(year,c.d) as [Year],
        DATEDIFF(month,MIN(c.d),MAX(c.d)) as NoOfMonth,
        DATEDIFF(day,DATEADD(month,DATEDIFF(month,MIN(c.d),MAX(c.d)),MIN(c.d)),MAX(c.d)) as NoOfDays
FROM ItemTable i
INNER JOIN calendar c
    ON c.d between i.[From date] and i.[To date]
GROUP BY i.Item, DATEPART(year,c.d),[From date],[To date]
)

SELECT  Item,
        [Year],
        SUM(NoOfMonth) as NoOfMonth,
        SUM(NoOfDays) as NoOfDays
FROM cte
GROUP BY Item,[Year]
ORDER BY Item
OPTION (MAXRECURSION 0)

输出：

Item    Year    NoOfMonth   NoOfDays
A1      2014    9           22
A1      2015    11          28
A1      2016    2           16
B1      2015    8           13
B1      2016    5           15
C1      2015    8           10
C1      2016    7           26

编辑

这个问题。

SELECT  Item,
        [Year],
        CASE WHEN SUM(NoOfDays) < 0 THEN SUM(NoOfMonth)-1 
            WHEN SUM(NoOfDays) > 30 THEN SUM(NoOfMonth)+1 
            ELSE SUM(NoOfMonth) END as NoOfMonth,
        CASE WHEN SUM(NoOfDays) >= 30 THEN SUM(NoOfDays)-30 
            WHEN SUM(NoOfDays) < 0 THEN SUM(NoOfDays)+30
            ELSE SUM(NoOfDays) END as NoOfDays
FROM cte
GROUP BY Item,[Year]
ORDER BY Item
OPTION (MAXRECURSION 0)

这样的报告的主要问题 - 很难定义什么是 1月份，DATEDIFF只需要从2个日期开始的数字，并从另一个日期中减去一个。

The main problem of such report - it is hard to define what is 1 month, DATEDIFF just takes number from 2 dates and subtract one from another.

我选择了30个月的日数，现在我比较值的天数为30，所以如果日数低于零或低于30，我们可以将 +1 添加到月份

I have choose 30 as a days count in month, and now I compare values of days with 30 so we can add +1 to month if the day count goes under zero or below 30

这篇关于SQL - 按年，月，日的日期组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！