IIF statement query using SQL server

Question

IIF statement query using SQL server

danny_s

SSC Enthusiast

Points: 124
More actions
June 7, 2022 at 3:20 pm

#4045685

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!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

SELECT ...
FROM ...
WHERE DATEADD(WEEK, 4, appl_CreatedDate) <= appl_ConversionDate;

drew.allen SSC Guru Points: 77012 More actions · Answer 2

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

danny_s SSC Enthusiast Points: 124 More actions · Answer 3

thanks Phil! I appreciate the quick response,

This reply was modified 2 years, 11 months ago by danny_s.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 4

danny_s wrote:

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.

IIF statement query using SQL server

Cookies on SQLServerCentral