0

I am using SQL Server 2008. I need to pull daily prorated amounts from various tables for custom periods.

For example I need to pull values from March 1, 2018 to April 30, 2019. Some years are leap years and, some are not. So, I would use 

SELECT amount / CASE WHEN Year(start_date) % 4 = 0 THEN 366 ELSE 365 END..

Is it possible to declare a variable instead of that CASE statement and I could use it in every query, stored procedure, function, trigger...

SELECT amount / @some_var...

Thank you, Gene

UPDATE: I know that I can not use the formula above for a leap year calculation because the result of it is not always true. This is just an example, sorry, may be not a good one. I am more interested if it is possible to declare some sort of variable, like environment variable in windows, that I can use across queries.

user1706426
  • 387
  • 1
  • 3
  • 12
  • 4
    Why? Variables in all programming languages are meant to hold values inside a single method or script. In any case the leap year calculation would be wrong. – Panagiotis Kanavos Nov 20 '19 at 16:06
  • Possible duplicate of [Check for leap year](https://stackoverflow.com/questions/6534788/check-for-leap-year) – Panagiotis Kanavos Nov 20 '19 at 16:07
  • Why not just do `SELECT amount / DATEDIFF(DAY, start_date, DATEADD(YEAR,1,start_date))`? – Thom A Nov 20 '19 at 16:12
  • I know that I can not use that formula for a leap year as it is not always the true. This is just an example, sorry, may be not a good one. I am more interested if it is possible to declare some sort of variable that I can use across queries. – user1706426 Nov 20 '19 at 16:13
  • 2
    You can store values in tables... – MJH Nov 20 '19 at 16:44
  • 1
    Values in a database are kept in tables. Put values like this there. – JazzmanJim Nov 20 '19 at 16:47

1 Answers1

0

Most data warehouses have a date table that has a row for each date with date attributes as columns like date,FiscalYear,CalendarYear,FQ,CQ,....

You would then use that in your query like the following.

cross apply (
select Count(*) NumOfDays from DimDate
where CalendarYear = YEAR(start_date) ) ca

then you can use ca.NumOfDays wherever you need it.

KeithL
  • 5,348
  • 3
  • 19
  • 25
  • I updated my question. I guess that example was not a good one. I am more interested if there is a possibility to use some sort of globally declared variable that I could use across my queries. – user1706426 Nov 20 '19 at 16:18
  • That is a description of a function then – KeithL Nov 20 '19 at 16:19
  • Functions can be heavy for large data sets. I have been using them a lot and I would like to cut down on the usage. – user1706426 Nov 20 '19 at 16:21