Create view with variable declaration defined by a case statement , generating a dynamic date table-CodePudding

I have static date table and a working query that I use to add references fields for comparison between financial periods, I would like to save as a view however the variable declaration is not permitted.

Query is shown below, the specific issues is with declaring "@this_qtr_month" and calculating "Is QTD". The variable determines [Num of Month in QTR] for today.

declare @this_qtr_month as int  = case when month(getdate()) in (1,4,7,10) then 1 
                                       when month(getdate()) in (2,5,8,11) then 2
                                       when month(getdate()) in (3,6,9,12) then 3
                                   end

select *
       ,DATEDIFF(day,[Date],getdate()) as 'Days Aged'
       ,DATEDIFF(ww,[Date],getdate()) as 'Weeks Aged'
       ,DATEDIFF(qq,[Date],getdate()) as 'QTRs Aged'
       ,DATEDIFF(yy,[Date],getdate()) as 'Years Aged'
       
       ,case when DATEPART(dd,[Date]) <= DATEPART(dd,getdate()) then 'Y' else 'N' end as 'Is MTD'

       ,case when @this_qtr_month > [Num of Month in QTR] then 'Y'
             when @this_qtr_month = [Num of Month in QTR] and DATEPART(dd,[Date]) <= DATEPART(dd,getdate()) then 'Y'
             else 'N'
       end as 'Is QTD'

       ,case when getdate() >= DATEFROMPARTS(year(getdate()), [Month Num], day(date_modified_LeapYear)) then 'Y' else 'N' end as 'Is YTD'

from Date_Table_Static

CodePudding user response：

You could write that without a variable:

SELECT *,
       DATEDIFF(DAY, [Date], GETDATE()) AS 'Days Aged',
       DATEDIFF(ww, [Date], GETDATE()) AS 'Weeks Aged',
       DATEDIFF(qq, [Date], GETDATE()) AS 'QTRs Aged',
       DATEDIFF(yy, [Date], GETDATE()) AS 'Years Aged',
       CASE
           WHEN DATEPART(dd, [Date]) <= DATEPART(dd, GETDATE()) THEN
               'Y'
           ELSE
               'N'
       END AS 'Is MTD',
       CASE
           WHEN (CASE
                     WHEN MONTH(GETDATE()) IN ( 1, 4, 7, 10 ) THEN
                         1
                     WHEN MONTH(GETDATE()) IN ( 2, 5, 8, 11 ) THEN
                         2
                     WHEN MONTH(GETDATE()) IN ( 3, 6, 9, 12 ) THEN
                         3
                 END
                ) > [Num of Month in QTR] THEN
               'Y'
           WHEN (CASE
                     WHEN MONTH(GETDATE()) IN ( 1, 4, 7, 10 ) THEN
                         1
                     WHEN MONTH(GETDATE()) IN ( 2, 5, 8, 11 ) THEN
                         2
                     WHEN MONTH(GETDATE()) IN ( 3, 6, 9, 12 ) THEN
                         3
                 END
                ) = [Num of Month in QTR]
                AND DATEPART(dd, [Date]) <= DATEPART(dd, GETDATE()) THEN
               'Y'
           ELSE
               'N'
       END AS 'Is QTD',
       CASE
           WHEN GETDATE() >= DATEFROMPARTS(YEAR(GETDATE()), [Month Num], DAY(date_modified_LeapYear)) THEN
               'Y'
           ELSE
               'N'
       END AS 'Is YTD'
FROM Date_Table_Static;

Or, since all you need with this view is a SELECT, you could write that as a Stored Procedure or TVF (Table Valued Function).

EDIT: I didn't read your expression before, it doesn't need to be that complex:

SELECT *,
       DATEDIFF(DAY, [Date], GETDATE()) AS 'Days Aged',
       DATEDIFF(ww, [Date], GETDATE()) AS 'Weeks Aged',
       DATEDIFF(qq, [Date], GETDATE()) AS 'QTRs Aged',
       DATEDIFF(yy, [Date], GETDATE()) AS 'Years Aged',
       CASE
           WHEN DATEPART(dd, [Date]) <= DATEPART(dd, GETDATE()) THEN
               'Y'
           ELSE
               'N'
       END AS 'Is MTD',
       CASE
           WHEN (Month(Getdate())-1)%3 1 > [Num of Month in QTR] THEN
               'Y'
           WHEN (Month(Getdate())-1)%3 1 = [Num of Month in QTR]
                AND DATEPART(dd, [Date]) <= DATEPART(dd, GETDATE()) THEN
               'Y'
           ELSE
               'N'
       END AS 'Is QTD',
       CASE
           WHEN GETDATE() >= DATEFROMPARTS(YEAR(GETDATE()), [Month Num], DAY(date_modified_LeapYear)) THEN
               'Y'
           ELSE
               'N'
       END AS 'Is YTD'
FROM Date_Table_Static;