In the query the top part is getting all the years that will run in the stored procedure. Works fine
But at first i just wanted to run the queries for yesterdays date for all the years, but now i realized i want the user to select a date that will be in a parameter @search
Booked <= CONVERT(int,DateAdd(year,
Years.Year - Year(getdate()),
DateAdd(day, DateDiff(day, 2,
getdate()), 1)))
this should be easy because normally it would just be
Booked <= CONVERT(int,@search)
but the problem is i want to do something like a
Booked <= CONVERT(int, (datepart(month, @search)), (datepart(day, @search)), DateAdd(year, Years.Year - (datepart(year, @search)))
would something like that work i dont need to worry about subtracting days but i still need to worry about the years
WITH Years AS (
SELECT DATEPART(year, GETDATE()) [Year]
UNION ALL
SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet
),
q_00 as (
select
DIVISION
, DYYYY
, sum(PARTY) as asofPAX
, sum(APRICE) as asofSales
from dbo.B101BookingsDetails
INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year
where Booked <= CONVERT(int,DateAdd(year, Years.Year - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 1)))
and DYYYY = Years.Year
group by DIVISION, DYYYY, years.year
having DYYYY = years.year
),
