yesterday and today with subscriptions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

yesterday and today with subscriptions

I want to have a subscription for a report that shows activity for a day (and week). how do i put in the parameters at the report server? what i want logically would look something like: begin date: today() – 1
end date: today() AND begin date: today() – 7
end date: today() Joe Janka
I determine the dates in the SP according to the passed in parameter:
DECLARE @MyEndDate SMALLDATETIME IF @Parameter = 1 — yesterday
SET @MyStartDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()-1), 0) ELSE — last week
SET @MyStartDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()-7), 0)
SET @MyEndDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
In this case, you can create two subscriptions. First one pass in 1 as parameter for yesterday, second one pass in 2 as parameter for last week.