June 7, 2022 at 3:20 pm
hey,
Using the following statement I can calculate conversions taking place within the same month:
IIF(datepart(wk,appl_createddate) <= datepart(wk,appl_conversiondate), 1, 0)
however, if I want to return values where appl_conversiondate =< 4 weeks after appl_createddate how would I go about achieving this?
Ultimately I'm trying to capture conversions that take place between months as well so the conversion rate isn't skewed, hope this makes sense!
June 7, 2022 at 3:44 pm
SELECT ...
FROM ...
WHERE DATEADD(WEEK, 4, appl_CreatedDate) <= appl_ConversionDate;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 7, 2022 at 3:52 pm
I would use DATEADD() instead. DATEDIFF() and DATEPART() use boundaries instead of full periods.
WHERE wk.appl_createddate <= DATEADD(wk,4,wk.appl_conversiondate)
AND wk.appl_conversiondate <= appl_createddate /* This may not be necessary. */
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 7, 2022 at 4:02 pm
thanks Phil! I appreciate the quick response, please understand if I'm asking on a forum it's because I have tried to resolve it first and then subsequently trolled through Q&A's on various forums trying to find an answer.
No worries. The part of my comment underneath the line is my signature & appears on all of my posts. It was not directed at you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply