In publishing, and Im sure many other industries, we get data at both the daily level and the weekly level. To properly tie these two pieces of data you need to aggregate the daily data and adjust the date to align with the weekly date. Our weekly data is set to the Saturday of the week.
As an example all daily data acquired between 10/21/2018 and 10/27/2018 must have a WeekEndingDate of 10/27/2018. This can be done with a case statement, but a there is an inline formula that will come in handy when doing projections (YTD, previous 6 months, etc). Ill present the formula and then show modifications to handle variations.
select dateadd(dd,7-datepart(dw,'10/21/2018'),'10/21/2018')
The above formula determines how many days away from 7 the current date is and simply adds that many days. We use 7, because Saturday is the 7th day of the week. Ill break it down.
datepart(dw,'10/21/2018')
The above snippet returns a value of 2, because it is a Monday. 7 if it is a Saturday. You subtract 7 from that to determine how many days you have to move forward. In the example, you need to move 5 days from Monday to make it to Saturday. This gives you the number of days to add. Reducing the example:
select dateadd(dd,5,'10/21/2018')
This comes in very handy when you are looking to move backward in time a certain number of days, weeks, months, or years but align to a Saturday. An example is below, which goes back 1 month.
select dateadd(dd,(datepart(dw,7-dateadd(mm,-1,'10/21/2018')),dateadd(mm,-1,'10/21/2018'))
Here is a simple way to remember how to use the formula. Remember you have to move your date first, then push it to Saturday.
declare @var date
select @var=dateadd(mm,-1,'10/2/2018')
select dateadd(dd,7-datepart(dw,@var),@var)