dev - 1 year ago 49

SQL Question

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 found this code in SQL - date group by year, month, days

But not work for me...

When I execute my query

`SELECT Item,`

[From date],

[To date]

from ItemDate;

I got

`('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')

and when execute code from this link SQL - date group by year, month, days

I get this:

`Item Year NoOfMonth NoOfDays`

A1 2013 4 -27

A2 2014 10 58

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?

Answer Source

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)
```

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.

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