Search code examples
sqlsql-servert-sqldatediff

SQL Query: Return value starting 28 days from parameter date to 56 days after parameter date

I have a query that allows me to return the number of events by EventID that occur from a parameter date (@STARTDATE) to 28 days previous. I would like to be able to return the number of events that occur in the previous 28 days.

COUNT (DISTINCT (CASE when EventID between  DATEADD(YEAR,-0,DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), -28)) and DATEADD(Year,-0,@STARTDATE) then EVentID END)) TwentyEightDays

I need to be able to run a similar query where the EVENTID count starts at 29 days from @STARTDATE to 56 days after @STARTDATE.

Any help is appreciated.

Solution

  • The numbers were reverse order but this query worked:

    COUNT(DISTINCT CASE WHEN eventdate >= DATEADD(day, -56, @StartDate) AND eventdate <= DATEADD(day, -29, @StartDate) THEN EventId END)

    Thank you to Gordon for this help!