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;