I am currently stuck with a task which is to find out the number of days remaining in each calendar year with given a start date and an end date. I am able to do for one date but not if I were to calculate for multiple start dates and end dates in a table.

Challenge: given a table with start dates and end dates, find out the days have lapsed in each calendar years.

Expected output:

Table A

CodePudding user response：

First of all, it looks a little like homework... what have you tried so far? However, following an example which might help you finding the solution - if you use pivot on the result, it should look pretty much like your expected result. ;-)

DECLARE @StartDT DATE = '2017-01-05';
DECLARE @EndDT DATE = '2020-03-31';

WITH cteYears AS(
SELECT @StartDT AS StartDT, @EndDT AS EndDT, YEAR(@StartDT) AS y
UNION ALL
SELECT StartDT, EndDT, y 1
  FROM cteYears
  WHERE y < YEAR(EndDT)
)
SELECT *
      ,CASE
         WHEN y = YEAR(StartDT) THEN DATEDIFF(d, StartDT, CAST(CAST(YEAR(StartDT) AS VARCHAR(8))   '-12-31' AS DATE))                     
         WHEN y = YEAR(EndDT) THEN DATEDIFF(d, CAST(CAST(YEAR(EndDT) AS VARCHAR(8))   '-01-01' AS DATE), EndDT)
         ELSE DATEDIFF(d, CAST(CAST(YEAR(y) AS VARCHAR(8))   '-01-01' AS DATE), CAST(CAST(YEAR(y) AS VARCHAR(8))   '-12-31' AS DATE)) 1
       END AS DiffDays
  FROM cteYears

CodePudding user response：

You will need to pivot over the years you want to aggregate for, and in order to determine which years you need to pivot over you must use dynamic SQL.

I defined a table variable called @daterange which contains a list of different start/end dates, including your example dates. I also defined a user defined type (called DateRangeType) so that this table variable can be sent to sp_executesql as a parameter.

Note I also included a PRINT statement, so you can see what SQL is being created and stick it in a query window, to help understand what it is doing with nice formatting (not just a big block of string).

IF NOT EXISTS (   SELECT *
                  FROM   sys.types AS t
                  WHERE  t.name = 'DateRangeType')
    CREATE TYPE DateRangeType AS TABLE (
        startdate DATE NOT NULL
       ,enddate   DATE NOT NULL
    );
GO

DECLARE @daterange DateRangeType;

DECLARE @yearselect NVARCHAR(MAX) = N''
       ,@yearpvt NVARCHAR(MAX) = N''
       ,@sql NVARCHAR(MAX);

INSERT INTO @daterange (startdate
                       ,enddate)
VALUES ('2017-01-05', '2020-03-31')
      ,('2015-04-25', '2018-10-03');

WITH years
AS (SELECT YEAR(d.startdate) AS eachyear
          ,YEAR(d.enddate) AS endyear
    FROM   @daterange AS d
    UNION ALL
    SELECT y.eachyear   1
          ,y.endyear
    FROM   years AS y
    WHERE  y.eachyear < y.endyear)
SELECT   @yearselect  = CONCAT(',ISNULL(pvt.', QUOTENAME(years.eachyear), ',0) AS ', QUOTENAME(years.eachyear))
        ,@yearpvt  = CONCAT(',', QUOTENAME(years.eachyear))
FROM     years
GROUP BY years.eachyear
ORDER BY years.eachyear;

SELECT @yearselect = STUFF(@yearselect, 1, 1, '')
      ,@yearpvt = STUFF(@yearpvt, 1, 1, '');

SELECT @sql = CONCAT(
                  N'WITH years
AS (SELECT d.startdate
          ,d.enddate
          ,YEAR(d.startdate) AS eachyear
          ,YEAR(d.enddate) AS endyear
    FROM   @daterange AS d
    UNION ALL
    SELECT y.startdate
          ,y.enddate
          ,y.eachyear   1
          ,y.endyear
    FROM   years AS y
    WHERE  y.eachyear < y.endyear)
SELECT   pvt.startdate
        ,pvt.enddate
        ,'
                 ,@yearselect
                 ,'
FROM     (   SELECT years.startdate
                   ,years.enddate
                   ,years.eachyear
                   ,DATEDIFF(DAY
                            ,CASE YEAR(years.startdate)
                                 WHEN years.eachyear THEN years.startdate
                                 ELSE DATEFROMPARTS(years.eachyear, 1, 1)
                             END
                            ,CASE YEAR(years.enddate)
                                 WHEN years.eachyear THEN DATEADD(DAY,1,years.enddate)
                                 ELSE DATEFROMPARTS(years.eachyear   1, 1, 1)
                             END) AS dayselapsed
             FROM   years) AS y
PIVOT (   MAX(dayselapsed)
          FOR eachyear IN ('
                 ,@yearpvt
                 ,')) AS pvt
ORDER BY pvt.startdate DESC
        ,pvt.enddate DESC;');

PRINT (@sql);

EXEC sys.sp_executesql @stmt = @sql
                      ,@params = N'@daterange DateRangeType READONLY'
                      ,@daterange = @daterange;
GO

DROP TYPE DateRangeType;
GO

This is not a particularly pretty solution, but it is really not a very pretty problem... Ideally this pivot should be done in the layer that is calling this sql rather than in the sql itself.

Hope it helps though!