March 14, 2018 at 1:35 pm
For a crazy calculation I need to do (it's part of a conditional across many fields) I need to see if the date was in the previous month or not.
GOAL: To see if the c.PricingDate was last month or not using Option. wanting to set a flag instead of using a where statement.
I have tried thiscase when C.PricingDate between (dateadd(m, datediff(m, 0, getdate()),0) - 1) and dateadd(year, datediff(month, 0, getdate()),0) then 1 else 0 end as test
but what I'm getting in a return query is almost what i want, I get from 2-16-1018 to current date
Sorry if this seems crazy but I'm trying to reproduce in SQL what someone else wrote in Excel with V lookup's and other things with dates .
Any help would be appreciated.
Thanks
March 14, 2018 at 1:44 pm
Why not just CASE WHEN DATEDIFF(month, getdate(), C.PricingDate) = -1 THEN ...
March 14, 2018 at 1:55 pm
Tried that, when I use that in the case statement the c.PricingDate shows that it does not exist and I get an incorrect syntax error.
March 14, 2018 at 2:00 pm
ZZartin - Wednesday, March 14, 2018 1:44 PMWhy not just CASE WHEN DATEDIFF(month, getdate(), C.PricingDate) = -1 THEN ...
That's not quite the right logic. This requires comparison of both month and year, but not day. Only way I can see to do that efficiently is to pre-compute the 1st day of the previous month as well as the 1st day of this month into a pair of variables, and then use the CASE statement to check to see if the relevant date column is >= the former and < the latter.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 2:08 pm
sgmunson - Wednesday, March 14, 2018 2:00 PMZZartin - Wednesday, March 14, 2018 1:44 PMWhy not just CASE WHEN DATEDIFF(month, getdate(), C.PricingDate) = -1 THEN ...That's not quite the right logic. This requires comparison of both month and year, but not day. Only way I can see to do that efficiently is to pre-compute the 1st day of the previous month as well as the 1st day of this month into a pair of variables, and then use the CASE statement to check to see if the relevant date column is >= the former and < the latter.
Should work fine it's comparing datediff in months not datepart months, although I guess it depends on how the OP is defining previous month.
OP Can you post your entire query, this works for me.
SELECT C.PricingDate, DATEDIFF(month, getdate(), C.PricingDate), CASE WHEN DATEDIFF(month, getdate(), C.PricingDate) = -1 THEN 1 ELSE 0 END
FROM (VALUES(CAST('20180215' AS datetime)), ('20180201'), ('20180228'), ('20180115'), ('20180301'), ('20170201') ) C(PricingDate)
March 14, 2018 at 5:01 pm
To get the first of the previous month:-declare @firstofprevmonth date = datefromparts(year(dateadd(month,-1,getdate())), month(dateadd(month,-1,getdate())), 1)
And the last day:-declare @lastofprevmonth date = eomonth(@firstofprevmonth)
Putting these together is then easy enough:-case when cast(C.PricingDate as date) between @firstofprevmonth and @lastofprevmonth then 1 else 0 end as test
March 15, 2018 at 1:58 am
Mat,
This is probably the worst choice of functions, at least performance wise.
This would be simpler and faster:
Case when C.PricingDate >= dateadd(mm, datediff(mm, 0, getdate() ) -1, 0) -- start of previous month
and C.PricingDate < dateadd(mm, datediff(mm, 0, getdate() ) , 0) -- start of current month
Then ...
_____________
Code for TallyGenerator
March 15, 2018 at 6:28 am
m.richardson.home - Wednesday, March 14, 2018 5:01 PMTo get the first of the previous month:-declare @firstofprevmonth date = datefromparts(year(dateadd(month,-1,getdate())), month(dateadd(month,-1,getdate())), 1)
And the last day:-declare @lastofprevmonth date = eomonth(@firstofprevmonth)Putting these together is then easy enough:-
case when cast(C.PricingDate as date) between @firstofprevmonth and @lastofprevmonth then 1 else 0 end as test
That's exactly what I had in mind...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2018 at 6:32 am
Sergiy - Thursday, March 15, 2018 1:58 AMMat, This is probably the worst choice of functions, at least performance wise.This would be simpler and faster:Case when C.PricingDate >= dateadd(mm, datediff(mm, 0, getdate() ) -1, 0) -- start of previous month and C.PricingDate < dateadd(mm, datediff(mm, 0, getdate() ) , 0) -- start of current month Then ...
Unfortunately, this is also true. Much better performance-wise.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2018 at 6:58 am
Sergiy - Thursday, March 15, 2018 1:58 AMMat, This is probably the worst choice of functions, at least performance wise.This would be simpler and faster:Case when C.PricingDate >= dateadd(mm, datediff(mm, 0, getdate() ) -1, 0) -- start of previous month and C.PricingDate < dateadd(mm, datediff(mm, 0, getdate() ) , 0) -- start of current month Then ...
I wouldn't have thought it would have made that much difference in terms of performance given the variables would already be calculated prior to the SQL statement running. If the calculations were inline I'd understand.
Also much easier to read this way I think.
March 15, 2018 at 7:42 am
m.richardson.home - Thursday, March 15, 2018 6:58 AMSergiy - Thursday, March 15, 2018 1:58 AMMat, This is probably the worst choice of functions, at least performance wise.This would be simpler and faster:Case when CAST(C.PricingDate AS date) >= dateadd(mm, datediff(mm, 0, getdate() ) -1, 0) -- start of previous month and CAST(C.PricingDate AS date) < dateadd(mm, datediff(mm, 0, getdate() ) , 0) -- start of current month Then ...I wouldn't have thought it would have made that much difference in terms of performance given the variables would already be calculated prior to the SQL statement running. If the calculations were inline I'd understand.
Also much easier to read this way I think.
I missed this earlier, but I think Sergiy missed it too. He was reacting to the BOLD ITALICIZED code that I modified to reflect the original post of yours to fit what I thought Sergiy was supposed to be quoting. It's the CAST on the PricingDate column that he (and I) would have concerns with, and NOT the pre-computation of the valid date range.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 16, 2018 at 4:35 am
Here's another slightly different solution
Find the start of month of pricing date, find the start of month of todays date, then if the difference is one month -> flag is true
SELECT c.PricingDate,
CASE WHEN DATEDIFF(MONTH,
DATEADD(MONTH,DATEDIFF(MONTH,0,C.PricingDate),0), /* BOMonth for Pricing date */
DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) /* BOMonth for today */
) = 1
THEN 1 ELSE 0 END as 'PreviousMonthFlag'
FROM (VALUES(CAST('20180101' as date)), ('20180201'), ('20180228'), ('20180301'), ('20180401')) c(PricingDate)
Cheers,
Angus
March 16, 2018 at 4:50 am
Angus.Young - Friday, March 16, 2018 4:35 AMHere's another slightly different solutionFind the start of month of pricing date, find the start of month of todays date, then if the difference is one month -> flag is true
SELECT c.PricingDate,
CASE WHEN DATEDIFF(MONTH,
DATEADD(MONTH,DATEDIFF(MONTH,0,C.PricingDate),0), /* BOMonth for Pricing date */
DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) /* BOMonth for today */
) = 1
THEN 1 ELSE 0 END as 'PreviousMonthFlag'
FROM (VALUES(CAST('20180101' as date)), ('20180201'), ('20180228'), ('20180301'), ('20180401')) c(PricingDate)Cheers,
Angus
Function applied on a table column - goes straight to the rubbish bin.
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply