June 1, 2016 at 1:00 pm
Our weeks run Saturday through Friday. The month the week falls in is determined by Saturday's date.
For example, the week containing 5/2 begins on 4/30. This entire week is in April. The week containing 6/1 begins on 5/28. This entire week is in May.
Our reports always run one week behind.
I'm looking to return the following dates:
1. The first Saturday of the current year given a specific date (StartOfCurrentYear)
2. The Friday before the first Saturday of next year given a specific date (EndOfCurrentYear)
3. The first Saturday of the previous month given a specific date (StartOfPrevMonth)
4. The Friday before the first Saturday of the current month given a specific date (EndOfPrevMonth)
5. The first Saturday of the current month given a specific date (StartOfCurrentMonth)
6. The Friday before the first Saturday of next month given a specific date (EndOfCurrentMonth)
For example, getdate() would return today's date of 6/1/2016.
We'd use a custom function to return Saturday's date of 5/28:
SELECT dbo.fn_Get_LastWeekDay(GETDATE(), 6)
We'd subtract 1 week to get the previous week's beginning date of 5/21:
SELECT DATEADD(ww, - 1, dbo.fn_Get_LastWeekDay(GETDATE(), 6))
5/21 is the date that we would then use to evaluate each of the 6 dates above. I'd expect the returns to be:
1. (StartOfCurrentYear): 1/2/2016
2. (EndOfCurrentYear): 1/6/2017
3. (StartOfPrevMonth): 4/2/2016
4. (EndOfPrevMonth): 5/6/2016
5. (StartOfCurrentMonth): 5/7/2016
6. (EndOfCurrentMonth): 6/3/2016
I keep building longer and longer statements that just keep getting more complicated, less efficient, and that retuen inconsitent results. Any guidance would be appreciated.
June 1, 2016 at 1:04 pm
swansonm (6/1/2016)
Our weeks run Saturday through Friday. The month the week falls in is determined by Saturday's date.For example, the week containing 5/2 begins on 4/30. This entire week is in April. The week containing 6/1 begins on 5/28. This entire week is in May.
Our reports always run one week behind.
I'm looking to return the following dates:
1. The first Saturday of the current year given a specific date (StartOfCurrentYear)
2. The Friday before the first Saturday of next year given a specific date (EndOfCurrentYear)
3. The first Saturday of the previous month given a specific date (StartOfPrevMonth)
4. The Friday before the first Saturday of the current month given a specific date (EndOfPrevMonth)
5. The first Saturday of the current month given a specific date (StartOfCurrentMonth)
6. The Friday before the first Saturday of next month given a specific date (EndOfCurrentMonth)
For example, getdate() would return today's date of 6/1/2016.
We'd use a custom function to return Saturday's date of 5/28:
SELECT dbo.fn_Get_LastWeekDay(GETDATE(), 6)
We'd subtract 1 week to get the previous week's beginning date of 5/21:
SELECT DATEADD(ww, - 1, dbo.fn_Get_LastWeekDay(GETDATE(), 6))
5/21 is the date that we would then use to evaluate each of the 6 dates above. I'd expect the returns to be:
1. (StartOfCurrentYear): 1/2/2016
2. (EndOfCurrentYear): 1/6/2017
3. (StartOfPrevMonth): 4/2/2016
4. (EndOfPrevMonth): 5/6/2016
5. (StartOfCurrentMonth): 5/7/2016
6. (EndOfCurrentMonth): 6/3/2016
I keep building longer and longer statements that just keep getting more complicated, less efficient, and that retuen inconsitent results. Any guidance would be appreciated.
To paraphrase: Each month starts on the first Saturday of the month. Correct?
June 1, 2016 at 1:56 pm
June 1, 2016 at 2:11 pm
Does this help?
d e c l a r e -- spaced to post from work
@TestDate date = dateadd(day,-2,dateadd(week,datediff(week,0,getdate()) - 1,0));
with DayNumbers as (
select
NameOfDay,
DayNumber
from
(values ('Saturday',7),('Friday',6),('Thursday',5),('Wednesday',4),('Tuesday',3),('Monday',2),('Sunday',1))dt(NameOfDay,DayNumber)
where
NameOfDay = datename(weekday,dateadd(year,datediff(year,0,@TestDate),0))
)
select
@TestDate TestDate,
dateadd(day,7 - (select DayNumber from DayNumbers),dateadd(year,datediff(year,0,@TestDate),0)) CurrentYearStart;
with DayNumbers as (
select
NameOfDay,
DayNumber
from
(values ('Saturday',7),('Friday',6),('Thursday',5),('Wednesday',4),('Tuesday',3),('Monday',2),('Sunday',1))dt(NameOfDay,DayNumber)
where
NameOfDay = datename(weekday,dateadd(year,datediff(year,0,@TestDate) + 1,0))
)
select
@TestDate TestDate,
dateadd(day, -1,dateadd(day,7 - (select DayNumber from DayNumbers),dateadd(year,datediff(year,0,@TestDate) + 1,0))) CurrentYearEnd;
with DayNumbers as (
select
NameOfDay,
DayNumber
from
(values ('Saturday',7),('Friday',6),('Thursday',5),('Wednesday',4),('Tuesday',3),('Monday',2),('Sunday',1))dt(NameOfDay,DayNumber)
where
NameOfDay = datename(weekday,dateadd(month,datediff(month,0,@TestDate) - 1,0))
)
select
@TestDate TestDate,
dateadd(day,7 - (select DayNumber from DayNumbers),dateadd(month,datediff(month,0,@TestDate) - 1,0)) PreviousMonthStart;
with DayNumbers as (
select
NameOfDay,
DayNumber
from
(values ('Saturday',7),('Friday',6),('Thursday',5),('Wednesday',4),('Tuesday',3),('Monday',2),('Sunday',1))dt(NameOfDay,DayNumber)
where
NameOfDay = datename(weekday,dateadd(month,datediff(month,0,@TestDate),0))
)
select
@TestDate TestDate,
dateadd(day, -1,dateadd(day,7 - (select DayNumber from DayNumbers),dateadd(month,datediff(month,0,@TestDate),0))) PreviousMonthEnd;
with DayNumbers as (
select
NameOfDay,
DayNumber
from
(values ('Saturday',7),('Friday',6),('Thursday',5),('Wednesday',4),('Tuesday',3),('Monday',2),('Sunday',1))dt(NameOfDay,DayNumber)
where
NameOfDay = datename(weekday,dateadd(month,datediff(month,0,@TestDate),0))
)
select
@TestDate TestDate,
dateadd(day,7 - (select DayNumber from DayNumbers),dateadd(month,datediff(month,0,@TestDate),0)) CurrentMonthStart;
with DayNumbers as (
select
NameOfDay,
DayNumber
from
(values ('Saturday',7),('Friday',6),('Thursday',5),('Wednesday',4),('Tuesday',3),('Monday',2),('Sunday',1))dt(NameOfDay,DayNumber)
where
NameOfDay = datename(weekday,dateadd(month,datediff(month,0,@TestDate) + 1,0))
)
select
@TestDate TestDate,
dateadd(day, -1, dateadd(day,7 - (select DayNumber from DayNumbers),dateadd(month,datediff(month,0,@TestDate) + 1,0))) CurrentMonthEnd;
I have not tested any edge cases at this time.
June 1, 2016 at 2:12 pm
Zidar (6/1/2016)
It seems that a Calendar table would help big time. To learn more, you may want to check this post:🙂
Make it work for everyone:
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/
June 1, 2016 at 2:22 pm
Here are four out of six formulas. You need to understand those, then create the 2 missing ones. Avoid scalar functions and use inline table functions or use the scalar functions to assign values to variables (in my experience, best for getting adequate plans).
Don't forget to test them under different scenarios and that they are dependent on the datefirst setting.
DECLARE @Date datetime = DATEADD( YY, 0, GETDATE());
SET DATEFIRST 7;
SELECT DATEADD( DD, 7-DATEPART(DW, YearStart), YearStart) AS StartOfCurrentYear
FROM (SELECT DATEADD( YY, DATEDIFF(YY, 0, @Date), 0) AS YearStart)x;
SELECT DATEADD( DD, 7-DATEPART(DW, YearStart)-1, YearStart) AS EndOfCurrentYear
FROM (SELECT DATEADD( YY, DATEDIFF(YY, -1, @Date), 0) AS YearStart)x;
SELECT DATEADD( DD, 7-DATEPART(DW, MonthStart), MonthStart) AS StartOfPrevMonth
FROM (SELECT DATEADD( MM, DATEDIFF(MM, 0, @Date) -1, 0) AS MonthStart)x;
SELECT DATEADD( DD, 7-DATEPART(DW, MonthStart)-1, MonthStart) AS EndOfPrevMonth
FROM (SELECT DATEADD( MM, DATEDIFF(MM, 0, @Date), 0) AS MonthStart)x;
June 2, 2016 at 6:30 am
Luis Cazares (6/1/2016)
Here are four out of six formulas. You need to understand those, then create the 2 missing ones. Avoid scalar functions and use inline table functions or use the scalar functions to assign values to variables (in my experience, best for getting adequate plans).Don't forget to test them under different scenarios and that they are dependent on the datefirst setting.
DECLARE @Date datetime = DATEADD( YY, 0, GETDATE());
SET DATEFIRST 7;
SELECT DATEADD( DD, 7-DATEPART(DW, YearStart), YearStart) AS StartOfCurrentYear
FROM (SELECT DATEADD( YY, DATEDIFF(YY, 0, @Date), 0) AS YearStart)x;
SELECT DATEADD( DD, 7-DATEPART(DW, YearStart)-1, YearStart) AS EndOfCurrentYear
FROM (SELECT DATEADD( YY, DATEDIFF(YY, -1, @Date), 0) AS YearStart)x;
SELECT DATEADD( DD, 7-DATEPART(DW, MonthStart), MonthStart) AS StartOfPrevMonth
FROM (SELECT DATEADD( MM, DATEDIFF(MM, 0, @Date) -1, 0) AS MonthStart)x;
SELECT DATEADD( DD, 7-DATEPART(DW, MonthStart)-1, MonthStart) AS EndOfPrevMonth
FROM (SELECT DATEADD( MM, DATEDIFF(MM, 0, @Date), 0) AS MonthStart)x;
I must apologize, these have some errors when giving the first days of a month before the first Saturday.
June 2, 2016 at 2:09 pm
Thanks for the assistance - I think I'm in good shape now!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply