CODEWITHSUNDEEP
sqlsql-serversql-server-2008reporting-services
a415
a415

Reputation: 369

SQL Server : week end as Friday and prior week as end date

I am creating a report which reports revenue details for Prior Week, Month to Week, Quarter to Week, and Total, all based on the cut off date as prior week.

Assuming the report will always be run on Fridays, the calculation/ cut off dates would be as follows :

Now assuming if I ran this report on Wednesday 08-03-2016, then it should use the current end of week as 08-05-2015 and then go back a week. In that case the Cut off date would be 08-23-2016 until 08-29-2016.

How would I do this?

Upvotes: 0

Views: 1049

Answers (1)

Liesel
Liesel

Reputation: 2979

There are two ways to work out Last Friday, one using DATEFIRST and the other using the coincidence that Day 0 was a Monday.

Set DATEFIRST to 6 (Saturday), then work out the previous end of week day:

SET DATEFIRST 6
SELECT CAST(DATEADD(DAY,  -DATEPART(WEEKDAY, GETDATE()), GETDATE()) AS DATE) 'Last Friday';

Or find the most recent start of week (Monday) and count back three days:

SELECT CAST(DATEADD(DAY, -3 , DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0)) AS DATE) 'Last Friday'

I prefer the former but since you're looking explicitly for Friday, not the locale specific end of the week day, you'll be OK with the latter.

The rest is plugging into standard First/Last DATEADD/DATEDIFF patterns. These should give you what you're looking for:

DECLARE @LastFriday DATE = DATEADD(DAY, -3 , DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0));

SELECT 
    LastFriday = @LastFriday, 
    PriorWeekStart = DATEADD(DAY, -6, @LastFriday),
    MonthStart = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, @LastFriday), 0) AS DATE),
    QuarterStart = CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @LastFriday), 0) AS DATE)

LastFriday PriorWeekStart MonthStart QuarterStart
---------- -------------- ---------- ------------
2016-07-29 2016-07-23     2016-07-01 2016-07-01

Upvotes: 1

Related Questions