VAAA VAAA - 4 months ago 8
SQL Question

How to get in same table information from previous period

I have the following SQL Query:

DECLARE @startdate datetime
DECLARE @enddate datetime

/* HARD CODE 1 WEEK DATA */
SET @startdate = '2016-07-10 00:00:00'
SET @enddate = DATEADD(DAY, 7, @startdate)
SET @enddate = DATEADD(MINUTE, -1, @enddate)
/* HARD CODE 1 WEEK DATA */

SELECT COUNT(0) as registered
FROM member_request ma
WHERE ma.createddate BETWEEN @startdate AND @enddate


This return the total records registered in that periord of time, I would like to get in the same result another columns that will give me the total records but for the previous week.

I know I can do extra select and
UNION ALL
them but maybe there is a better way to do it.

Any clue?

Answer

Just subtract one week:

SELECT COUNT(*) as registered
FROM member_request ma
WHERE ma.createddate BETWEEN DATEADD(week, -1, @startdate) AND DATEADD(week, -1, @enddate);

If you want both in the same query, the use conditional aggregation

select sum(case when ma.createddate BETWEEN @startdate AND @enddate then 1 else 0
           end) as thisweek,
       sum(case when ma.createddate BETWEEN DATEADD(week, -1, @startdate) AND DATEADD(week, -1, @enddate) then 1 else 0
           end) as lastweek
from member_request ma
where ma_createddate >= DATEADD(week, -1, @startdate);

Note: Using between with date/time value. Aaron Bertrand has an informative blog on why not to do it, What do BETWEEN and the devil have in common?