Is there an elegant way to calculate proportion of a year that lies within a date range in SQL Server 2014?

Question

As stated above, I am looking to create a column in my dataset that says what percentage of a year is within a date range.

This was already being done in excel but I now have to reproduce this in SQL. Here are the column data types:

Yr1 int
Yr2 float
Yr3 float
Yr4 float
Period Start datetime
Period End datetime
Period Indicator varchar
Leap Year Indicator varchar
Length of Period float
Yr1 Earn % int
Yr2 Earn % float
Yr3 Earn % float
Yr4 Earn % float
Total Earn % float

Here is the SQL used to generate the columns. Yr1 Earn % unfortunately does not work. I receive an error saying: "Operand data type datetime is invalid for divide operator."

Is there any way to calculate this in SQL in the same fashion as below??

      ,[Yr1]+1 as 'Yr2'
      ,[Yr2]+1 as 'Yr3'
      ,[Yr3]+1 as 'Yr4'
      ,dateadd(d,[INSRD PERIOD START],'1899-12-30') as 'Period Start'
      ,dateadd(d,[INSRD PERIOD END],'1899-12-30') as 'Period End'
      ,CASE WHEN MONTH([PERIOD START]) = MONTH([PERIOD END]) AND DAY([PERIOD START]) = DAY([PERIOD END])
            THEN 'Y'
            ELSE 'N'
       END AS 'Period Indicator'
      ,CASE WHEN ISDATE(CAST(YEAR([Period Start]) AS char(4)) + '0229') = 1 
                 OR 
                 ISDATE(CAST(YEAR([Period End]) AS char(4)) + '0229') = 1
            THEN 'Y' 
            ELSE 'N' 
       END as 'Leap Year Indicator'
      ,(([Period End]-([Period Start]-CASE WHEN [Period Indicator]='Y' 
                                          THEN 0 
                                          ELSE 1 
                                          END)))/(CASE WHEN [Leap Year Indicator]='N' 
                                                       THEN 365 
                                                       ELSE 366 
                                                       END) as 'Length of Period'
      ,(SELECT MAX(v) 
       FROM (VALUES (0),
        (((SELECT MIN(w) 
         FROM (VALUES ('2019-06-30'),
                      (CASE WHEN [Period Indicator]='N' 
                            THEN DATEADD(d,[Period End],'1899-12-30')
                            ELSE (DATEADD(DAY,-1,[Period End]))
                            END)
                       ,
                      (DATEFROMPARTS([Yr1],'12','31'))
              ) as value(w))-
                      (SELECT MAX(x) 
                       FROM (VALUES (DATEFROMPARTS([Yr1],'12','31')),
                                    (DATEADD(DAY,-1,[Period Start]))
                            ) as value(x)
                      )
        )/((CASE WHEN [Period Indicator]='N' 
                THEN dateadd(DAY,[Period End],'1899-12-30')
                ELSE DATEADD(DAY,-1,[Period End]) 
                END) 
                - (DATEADD(DAY,-1,[Period Start]))))
       ) as value(v)
       )  as 'Yr1 Earn %'
      ,[Yr2 Earn %] as 'Yr2 Earn %'
      ,[Yr3 Earn %] as 'Yr3 Earn %'
      ,[Yr4 Earn %] as 'Yr4 Earn %'
      ,[Yr1 Earn %]+[Yr2 Earn %]+[Yr3 Earn %]+[Yr4 Earn %] as 'Total Earn %'

EDIT: Here is a copy of the excel formula used to generate the Yr1 Earn % column in the screenshot. z_eval='2019-06-30'

=MAX(0,(MIN(z_eval,IF($G2="N",$F2,$F2-1),DATE(A2,12,31))-MAX(DATE(A2->1,12,31),$E2-1))/(IF($G2="N",$F2,$F2-1)-($E2-1)))

EDIT: Thank you to everyone for your helpful comments so far. I converted all dates to ints with this block of code for Yr1 Earn %. All values are defaulting to zero. Is the outer MAX(v) function struggling to parse the inner Max/Min functions?

,(SELECT MAX(v) 
 FROM (VALUES (0),
  (((SELECT MIN(w) 
   FROM (VALUES (DATEDIFF(d,'1899-12-30','2019-06-30')),
                (CASE WHEN [Period Indicator]='N' 
                      THEN DATEDIFF(d,'1899-12-30',[Period End])
                      ELSE (DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period End])))
                      END)
                 ,
                (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr1],'12','31')))
        ) as value(w))-
                (SELECT MAX(x) 
                 FROM (VALUES (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr1],'12','31'))),
                              (DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period Start])))
                      ) as value(x)
                )
  )/((CASE WHEN [Period Indicator]='N' 
           THEN DATEDIFF(d,'1899-12-30',DATEADD(DAY,[Period End],'1899-12-30'))
           ELSE DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period End])) 
           END) 
           - (DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period Start])))))
 ) as value(v)
 ) as 'Yr1 Earn %'

`DATEDIFF` is your friend.This can also be used to determine the number of days in the year, incidentally (and hence indirectly if it's a leap year, rather than the other way around). — Jeroen Mostert, Jan 15 '20 at 13:43
That's a simple percentage calculatin if you use [a Calendar table](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/). Join with the Calendar table on Date, and calculate the number of dates in the range divided by the number of days in the year. — Panagiotis Kanavos, Jan 15 '20 at 13:44
@Jeroen DATEDIFF would be great but my formula requires division. SQL does not allow me to divide dates. I've tried casting as int to no avail. — Delete_System_32, Jan 15 '20 at 13:51
Indeed, it can't divide dates, but `DATEDIFF` gives you the difference between two dates in any desired period as an `INT`, and those do divide. Excel's treatment of dates as numbers that represent days plus a fraction is a bit wonky and can be difficult to translate to difference semantics, but it's always doable. (Just don't ask me to do it on the spot.) — Jeroen Mostert, Jan 15 '20 at 13:55
Alternate approach just for per cent complete in year: `=A1-DATE(YEAR(A1),1,0)` would show the day# within the year, which divided by 365 (or 366 for leaps) gives the per cent complete of the year. — Cyril, Jan 15 '20 at 13:59
Keep in mind that `/` is truncated integer division in SQL Server, so `3 / 6` is `0`. Multiply either operand by `1.0` to force conversion to `DECIMAL` or by `1e` for floating-point (or you could toss in an explicit `CONVERT`, of course). — Jeroen Mostert, Jan 15 '20 at 14:50

score 0 · Answer 1 · answered Jan 15 '20 at 18:14

SQL does not play nicely when Select Max(v) FROM VALUES(....) as value(v) is nested within a select statement like I did above. I unfortunately had to break these out into individual pieces and join them in temp tables. This is not the most elegant solution, but it may very well be the only solution without joins. See the code below for column generation.

Also, there is a 2 day adjustment when converting dates from Excel to SQL.

This is explained further here:

select *,
        (1.000000000000000*(SELECT MIN(w) 
        FROM (VALUES (DATEDIFF(d,'1899-12-30',[EVAL])),
                    (CASE WHEN [Period Indicator]='N' 
                          THEN DATEDIFF(d,'1899-12-30',[Period End])
                          ELSE (DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period End])))
                          END),
                    (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr1],'12','31')))
        ) as value(w))) as 'Yr1 Earn % a',
        (1.000000000000000*(SELECT MAX(x) 
            FROM (VALUES (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr1]-1,'12','31'))),
                         (DATEDIFF(d,'1900-01-01',DATEADD(DAY,-1,[Period Start])))) 
            as value(x))) as 'Yr1 Earn % b',
        (1.000000000000000*(SELECT MIN(w) 
        FROM (VALUES (DATEDIFF(d,'1899-12-30',[EVAL])),
                    (CASE WHEN [Period Indicator]='N' 
                          THEN DATEDIFF(d,'1899-12-30',[Period End])
                          ELSE (DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period End])))
                          END),
                    (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr2],'12','31')))
        ) as value(w))) as 'Yr2 Earn % a',
        (1.000000000000000*(SELECT MAX(x) 
            FROM (VALUES (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr2]-1,'12','31'))),
                         (DATEDIFF(d,'1900-01-01',DATEADD(DAY,-1,[Period Start])))) 
            as value(x))) as 'Yr2 Earn % b',
        (1.000000000000000*(SELECT MIN(w) 
        FROM (VALUES (DATEDIFF(d,'1899-12-30',[EVAL])),
                    (CASE WHEN [Period Indicator]='N' 
                          THEN DATEDIFF(d,'1899-12-30',[Period End])
                          ELSE (DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period End])))
                          END),
                    (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr3],'12','31')))
        ) as value(w))) as 'Yr3 Earn % a',
        (1.000000000000000*(SELECT MAX(x) 
            FROM (VALUES (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr3]-1,'12','31'))),
                         (DATEDIFF(d,'1900-01-01',DATEADD(DAY,-1,[Period Start])))) 
            as value(x))) as 'Yr3 Earn % b' ,
        (1.000000000000000*(SELECT MIN(w) 
        FROM (VALUES (DATEDIFF(d,'1899-12-30',[EVAL])),
                    (CASE WHEN [Period Indicator]='N' 
                          THEN DATEDIFF(d,'1899-12-30',[Period End])
                          ELSE (DATEDIFF(d,'1899-12-30',DATEADD(DAY,-1,[Period End])))
                          END),
                    (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr4],'12','31')))
        ) as value(w))) as 'Yr4 Earn % a',
        (1.000000000000000*(SELECT MAX(x) 
            FROM (VALUES (DATEDIFF(d,'1899-12-30',DATEFROMPARTS([Yr4]-1,'12','31'))),
                         (DATEDIFF(d,'1900-01-01',DATEADD(DAY,-1,[Period Start])))) 
            as value(x))) as 'Yr4 Earn % b',
        (1.000000000000000*(CASE WHEN [Period Indicator]='N' 
                 THEN DATEDIFF(d,'1900-01-01',[Period End])
                 ELSE DATEDIFF(d,'1900-01-01',DATEADD(DAY,-1,[Period End])) 
                 END)) as 'Earn % c',
        (1.000000000000000*(DATEDIFF(d,'1900-01-01',DATEADD(DAY,-1,[Period Start]))))as 'Earn % d'
into #temptemp2
from #temptemp1

Is there an elegant way to calculate proportion of a year that lies within a date range in SQL Server 2014?

1 Answers1