sum count day each year (SQL)

Question

my example:

 Code            FromDate          ToDate                 
 --              --------           -------        
 101               15/12/2012         15/01/2013   
 101               30/11/2013         20/01/2014

I want to count diff between two date,, how many days in this year , How can i do this with SQL? to be result like this :

 Code    No.day     2012   2013  2014
 --      ------     ----   ----  ----
 101       82        17     46    19

count no. day,2012 what do you mean about count. as per your data from date to date, give logic that in 2012 have 17 etc? — Ajay2707, Feb 07 '17 at 10:01
yes from date to end of year ... and start next year from beginning to 15/01/2013 — ALDHEEB, Feb 07 '17 at 10:03
Possible duplicate of [SQL SERVER: Get total days between two dates](http://stackoverflow.com/questions/6068017/sql-server-get-total-days-between-two-dates) — Pranay Deep, Feb 07 '17 at 10:05
from 15/12/2012 to 15/01/2013 = 17 meaning 15/12/2012 to end 31/12/2012 — ALDHEEB, Feb 07 '17 at 10:12
If any of these answers helped you, please consider [accepting them](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Accepting an answer rewards the contributor and helps others to find a working solution. — David Rushton, Feb 07 '17 at 12:17

Pranay Deep · Answer 1 · 2017-02-07T10:29:47.790

0

Use this:

   SELECT Code,SUM(No.day) AS No.day, SUM(2012) AS 2012, SUM(2013) AS 2013, SUM(2014) AS 2014
    FROM TABLENAME GROUP BY Code

According to your current(Edited) question, you should use

SELECT DATEDIFF(day,'2012-06-05','2012-08-05') AS Year2012 From TABLENAME .

For example:

SELECT DATEDIFF(day, '2014/01/01', '2014/04/28');
Result: 117

SELECT DATEDIFF(hour, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 2

SELECT DATEDIFF(minute, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 165

You can manipulate it based on your requirement.

edited Feb 07 '17 at 10:29

answered Feb 07 '17 at 09:48

Pranay Deep

1,371
4
24
44

2

Careful: You probably will need to escape `2012` so that the RDBMS doesn't mistake it for a number. – Tim Biegeleisen Feb 07 '17 at 09:49

AlbertoCh · Accepted Answer · 2017-02-07T10:41:41.093

Try something like:

select Code,sum([2012])+sum([2013])+sum([2014]) as 'No.days',sum([2012]),sum([2013]),sum([2014]) from(
select Code,
    case when datepart(yy,fromdate)=datepart(yy,todate) and datepart(yy,fromdate)=2012 then  datediff(dd,fromdate, todate)
    when datepart(yy,fromdate)=2012 and  datepart(yy,todate)>2012 then datediff(dd,fromdate,dateadd(yy,1,dateadd(dd,-datepart(dy,DATEADD(yy,1, fromdate)),fromdate)))
    when datepart(yy,fromdate)<2012 and  datepart(yy,todate)=2012 then  DATEPART(dy,todate)
    end as '2012',
    case when datepart(yy,fromdate)=datepart(yy,todate) and datepart(yy,fromdate)=2013 then  datediff(dd,fromdate, todate)
    when datepart(yy,fromdate)=2013 and  datepart(yy,todate)>2013 then  datediff(dd,fromdate,dateadd(yy,1,dateadd(dd,-datepart(dy,DATEADD(yy,1, fromdate)),fromdate)))
    when datepart(yy,fromdate)<2013 and  datepart(yy,todate)=2013 then  DATEPART(dy,todate)
    end as '2013',
    case when datepart(yy,fromdate)=datepart(yy,todate) and datepart(yy,fromdate)=2014 then  datediff(dd,fromdate, todate)
    when datepart(yy,fromdate)=2014 and  datepart(yy,todate)>2014  then  datediff(dd,fromdate,dateadd(yy,1,dateadd(dd,-datepart(dy,DATEADD(yy,1, fromdate)),fromdate)))
    when datepart(yy,fromdate)<2014 and  datepart(yy,todate)=2014 then  DATEPART(dy,todate)
    end as '2014'
    from myTable
    ) as daystable
group by Code

If you need more years you will need to add some more fields in the subquery.

Careful: You probably will need to escape `2012` so that the RDBMS doesn't mistake it for a number. — Tim Biegeleisen, Feb 07 '17 at 09:49

score 0 · Answer 3 · edited May 23 '17 at 12:24

You will need to combine a few different techniques, to get the result you are after.

It helps if you have a calendar table. These are incredibly useful things, well worth researching if you are not familar. My query assumes you don't. Here I've used a recursive CTE to create one on the fly.

Sample Data

TIP: Providing sample data in format we can share, improves the odds of your question receiving an answer.

-- Table variables are a good way to share sample data.
DECLARE @Sample TABLE
    (
        Code        INT,
        FromDate    DATE,
        ToDate      DATE
    )
;

INSERT INTO @Sample
    (
        Code,
        FromDate,
        ToDate
    )
VALUES
    (101, '2012-12-15', '2013-01-15'),
    (101, '2013-11-30', '2014-01-20')
;

The Query

This query joins your source data to the calendar table. One record is returned for each day between the from and to dates. Using the year column in the calendar table, we group the results. Counting the records returns the total days passed. To calculate the year subtotals, we use conditional aggregation. This technique uses a case expression to create new columns that are conditionally populated with a 1 or a 0, based on the year.

/* Returns date counts, split by
 * year.
 */
WITH CalendarTable AS
    (
        /* This CTE returns 1 record for each day 
         * between Jan 1st 2012 and Dec 31st 2014.
         */
            SELECT
                CAST('2012-01-01' AS DATE)  AS [Date],
                2012                        AS [Year]

        UNION ALL

            SELECT
                DATEADD(DAY, 1, [Date])         AS [Date],
                YEAR(DATEADD(DAY, 1, [Date]))   AS [Year]
            FROM
                CalendarTable 
            WHERE
                [Date] < '2014-12-31'
    ) 
SELECT
    s.Code,
    COUNT(*)                                            AS [No.Day],
    SUM(CASE WHEN ct.[Year] = 2012 THEN 1 ELSE 0 END)   AS [2012],
    SUM(CASE WHEN ct.[Year] = 2013 THEN 1 ELSE 0 END)   AS [2013],
    SUM(CASE WHEN ct.[Year] = 2014 THEN 1 ELSE 0 END)   AS [2014]
FROM
    @Sample AS s
        INNER JOIN CalendarTable AS ct      ON  ct.[Date] >= s.FromDate 
                                            AND ct.[Date] < s.ToDate    
GROUP BY
    s.Code
OPTION 
    (MAXRECURSION 1096) 
;

Returns

Code    No.day     2012   2013  2014
--      ------     ----   ----  ----
101       82        17     46    19

If you already have a calendar table you can simplify this query, by removing the CTE and updating the join.

score 0 · Answer 4 · answered Feb 07 '17 at 11:39

SELECT     code,fromdate,todate,datediff(dd,fromdate,todate),
                datediff(dd,case when year(fromdate) < 2012 then '1/1/2012' when year(fromdate) = 2012 then fromdate else '12/31/2012' end,case when year(todate) = 2012 then todate else '12/31/2012'end)'2012',
                datediff(dd,case when year(fromdate) < 2013 then '1/1/2013' when year(fromdate) = 2013 then fromdate else '12/31/2013' end,case when year(todate) = 2013 then todate else '12/31/2013'end)  '2013',
                datediff(dd,case when year(fromdate) < 2014 then '1/1/2014' when year(fromdate) = 2014 then fromdate else '12/31/2014' end,case when year(todate) < 2014 then '1/1/2014' when year(todate) = 2014 then todate else '12/31/2014'end)  '2014' FROM (SELECT     '101' AS code, datefromparts(2012,12,15) AS fromdate, datefromparts(2013,1,15) AS todate
          UNION
          SELECT     '101' AS code, datefromparts(2013,11,30) AS fromdate, datefromparts(2014,1,20) AS todate
          Union 
          SELECT     '101' AS code, datefromparts(2014,11,30) AS fromdate, datefromparts(2016,1,20) AS todate) AS s

Hope This helps

sum count day each year (SQL)

4 Answers4

Sample Data

The Query

Returns