SQL-按年,月,日分组的日期-更新 [英] SQL - date group by year, month, days - update
问题描述
我使用代码按年,月,日来计算两个日期组之间的差异:
I used code to calculate difference between two date group by year, months, date:
;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)
我找到了此代码i n SQL-按年,月,日分组的日期
I found this code in SQL - date group by year, month, days
但不适用于我...
当我执行查询时
SELECT Item,
[From date],
[To date]
from ItemDate;
我知道了
('A1','2013-08-27','2013-09-27'),
('A1','2013-09-28','2013-11-28'),
('A1','2013-11-30','2013-12-03'),
('A1','2013-12-31','2014-03-31'),
('A1','2014-04-01','2014-07-01'),
('A1','2014-07-02','2014-10-02'),
('A1','2014-10-03','2014-12-31')
,并从此链接执行代码 SQL-按年,月,日的日期分组
and when execute code from this link SQL - date group by year, month, days
我明白了:
Item Year NoOfMonth NoOfDays
A1 2013 4 -27
A2 2014 10 58
这不好。...对于2013年,
应该是3个月零4天,对于2014年应该是11个月零28天
This is not good.... It should be 3 months and 4 day for year 2013, and for year 2014 11 month and 28 days
如何更新代码以获得所需的结果?
How to update the code to get the desired result?
推荐答案
C hange最后选择的内容:
Change the last select to:
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
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屋!