August 6, 2018 at 9:08 am
Hi,
I'm doing this directly in a computed column:
case
when [DUE]<convert([date],getdate(),(0)) then 'OVERDUE'
when datepart(day,[DUE])=datepart(day,convert([date],getdate(),(0))) AND datepart(month,[DUE])=datepart(month,getdate()) AND datepart(year,[DUE])=datepart(year,getdate()) then 'TODAY'
when datepart(week,[DUE])=datepart(week,convert([date],getdate(),(0))) AND datepart(month,[DUE])=datepart(month,getdate()) AND datepart(year,[DUE])=datepart(year,getdate()) then 'WEEK'
when datepart(month,[DUE])=datepart(month,convert([date],getdate(),(0))) AND datepart(year,[DUE])=datepart(year,getdate()) then 'MONTH'
else 'LATER'
end
Only DATEPART with WEEK starts on Sunday, so right now when looking at results today the week ends this coming Saturday, so Sunday's results get given MONTH.
How can I change WEEK to start with a Monday. As it's in a computed column, I can't use DATEFIRST, nor can I put this in a function with DATEFIRST.
Thanks
August 6, 2018 at 9:49 am
lanky_doodle - Monday, August 6, 2018 9:08 AMHi,I'm doing this directly in a computed column:
case
when [DUE]<convert([date],getdate(),(0)) then 'OVERDUE'
when datepart(day,[DUE])=datepart(day,convert([date],getdate(),(0))) AND datepart(month,[DUE])=datepart(month,getdate()) AND datepart(year,[DUE])=datepart(year,getdate()) then 'TODAY'
when datepart(week,[DUE])=datepart(week,convert([date],getdate(),(0))) AND datepart(month,[DUE])=datepart(month,getdate()) AND datepart(year,[DUE])=datepart(year,getdate()) then 'WEEK'
when datepart(month,[DUE])=datepart(month,convert([date],getdate(),(0))) AND datepart(year,[DUE])=datepart(year,getdate()) then 'MONTH'
else 'LATER'
end
Only DATEPART with WEEK starts on Sunday, so right now when looking at results today the week ends this coming Saturday, so Sunday's results get given MONTH.
How can I change WEEK to start with a Monday. As it's in a computed column, I can't use DATEFIRST, nor can I put this in a function with DATEFIRST.
Thanks
You could actually simplify it taking advantage that the CASE expression actually short circuits when it finds a condition that evaluates to true.
SELECT CASE
WHEN x.DUE < CONVERT([DATE], GETDATE(), (0)) THEN 'OVERDUE' --Before today
WHEN x.Due < DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 1) THEN 'TODAY' --Before tomorrow
WHEN x.Due < DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 7) THEN 'WEEK' --Before next Monday
WHEN x.Due < DATEADD(MM, DATEDIFF(MM, -1, GETDATE()), 0) THEN 'MONTH' --Before next month
ELSE 'LATER'
END
FROM
(SELECT c.cal_date AS DUE FROM dbo.Calendar AS c) AS x
WHERE x.DUE
BETWEEN GETDATE() - 15 AND GETDATE() + 30;
Try to understand the formulas taking this into account: the integer values used are converted to dates.
SELECT n, CAST(n AS DATETIME)
FROM (VALUES(-1), (0), (1), (7))x(n)
August 6, 2018 at 9:50 am
Have you considered making use of DATEDIFF(day, DUE, CONVERT(date, GETDATE())) ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 6, 2018 at 9:51 am
depending on your datefirst settings you could try incorporate weekday
select datepart(WEEKDAY,getdate()) -- if select @@DATEFIRST = 7 (default) then today would be 2
alternatlivly
look at setting date first to 1???
set datefirst 1
| Value | First day of the week is |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 (default, U.S. English) | Sunday |
***The first step is always the hardest *******
August 6, 2018 at 9:53 am
SGT_squeequal - Monday, August 6, 2018 9:51 AMdepending on your datefirst settings you could try incorporate weekday
select datepart(WEEKDAY,getdate()) -- if select @@DATEFIRST = 7 (default) then today would be 2alternatlivly
look at setting date first to 1???
set datefirst 1
Value First day of the week is 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 (default, U.S. English) Sunday
This is in a computed column, so DATEFIRST can't be used.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 8, 2018 at 9:53 am
Thanks for the tips.
With Luis' one, that will take the next 7 days. By "this week", I want the calendar week, so when looking at it today, "this week" results should stop on Sunday, NOT 7 days from today, which is what datediff is doing:
2018-08-08 TODAY Wednesday (correct = today)
2018-08-09 WEEK - Thursday (correct = week)
2018-08-10 WEEK - Friday (correct = week)
2018-08-11 WEEK - Saturday (correct = week)
2018-08-12 WEEK - Sunday (correct = week)
2018-08-13 WEEK - Monday (wrong = week, should be month)
2018-08-14 WEEK - Tuesday (wrong = week, should be month)
2018-08-15 MONTH - Wednesday (correct = month)
So I really need week from datepart to be shifted forwards by 1 day (so a week becomes Monday to Sunday and not Sunday to Saturday.)
August 8, 2018 at 10:07 am
lanky_doodle - Wednesday, August 8, 2018 9:53 AMThanks for the tips.With Luis' one, that will take the next 7 days. By "this week", I want the calendar week, so when looking at it today, "this week" results should stop on Sunday, NOT 7 days from today, which is what datediff is doing:
2018-08-08 TODAY Wednesday (correct = today)
2018-08-09 WEEK - Thursday (correct = week)
2018-08-10 WEEK - Friday (correct = week)
2018-08-11 WEEK - Saturday (correct = week)
2018-08-12 WEEK - Sunday (correct = week)
2018-08-13 WEEK - Monday (wrong = week, should be month)
2018-08-14 WEEK - Tuesday (wrong = week, should be month)
2018-08-15 MONTH - Wednesday (correct = month)So I really need week from datepart to be shifted forwards by 1 day (so a week becomes Monday to Sunday and not Sunday to Saturday.)
I'm sorry, I forgot one part on the week calculation.
SELECT CASE
WHEN x.DUE < CONVERT([DATE], GETDATE(), (0)) THEN 'OVERDUE' --Before today
WHEN x.Due < DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 1) THEN 'TODAY' --Before tomorrow
WHEN x.Due < DATEADD(DD, (DATEDIFF(DD, 0, GETDATE()) / 7)*7, 7) THEN 'WEEK' --Before next Monday
WHEN x.Due < DATEADD(MM, DATEDIFF(MM, -1, GETDATE()), 0) THEN 'MONTH' --Before next month
ELSE 'LATER'
END, x.DUE
FROM
(SELECT c.cal_date AS DUE FROM dbo.Calendar AS c) AS x
WHERE x.DUE
BETWEEN GETDATE() - 15 AND GETDATE() + 30;
August 8, 2018 at 11:01 am
That's the ticket, thanks so much 🙂
Can you break down the calculation for me. I need to add a "next week" group and doing this works, so the last number is the number of days forward (14)?. What's the / 7 ) * 7 bit all about?
WHEN x.Due < DATEADD(DD, (DATEDIFF(DD, 0, GETDATE()) / 7) * 7, 14) THEN 'WEEK' --Before next, next Monday
August 8, 2018 at 12:35 pm
lanky_doodle - Wednesday, August 8, 2018 11:01 AMThat's the ticket, thanks so much 🙂Can you break down the calculation for me. I need to add a "next week" group and doing this works, so the last number is the number of days forward (14)?. What's the / 7 ) * 7 bit all about?
WHEN x.Due < DATEADD(DD, (DATEDIFF(DD, 0, GETDATE()) / 7) * 7, 14) THEN 'WEEK' --Before next, next Monday
That's right, you would need to use 14.
The /7*7 is basically taking advantage of integer divisions to get complete weeks from a certain day. 1900-01-01 (date zero) is a Monday, so we have 6188 weeks and 2 days since then. Integer division would just return 6188 instead of 6188.285714. We then add the weeks as days and that's why we need to multiply by seven again. We could also add weeks directly and it might become clearer.
WHEN x.Due < DATEADD(WK, (DATEDIFF(DD, 0, GETDATE()) / 7) + 1, 0) THEN 'WEEK' --Before next Monday
WHEN x.Due < DATEADD(WK, (DATEDIFF(DD, 0, GETDATE()) / 7) + 2, 0) THEN 'NEXT WEEK' --Before next, next Monday
EDIT: If you need to change the start of the week, replace 0 with other numbers from 1 to 6 depending on the day you want (It works with others but I prefer to keep it simple).
August 8, 2018 at 12:44 pm
By the way, what would happen in dates like 2018-08-29? Would you have no rows for month?
August 9, 2018 at 2:52 am
Thanks.
Yes, on dates right at the end of a month that's what I expect. I've changed the label from 'This month' the 'Later this month' to make it clearer.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply