CODEWITHSUNDEEP
sqlsql-servert-sql
VAAA
VAAA

Reputation: 15049

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?

Upvotes: 0

Views: 509

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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?

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176134

You could use conditional aggregation:

SELECT 
   COUNT(CASE WHEN ma.createddate 
          BETWEEN @startdate AND @enddate THEN 1 END) AS current_week
  ,COUNT(CASE WHEN ma.createddate 
          BETWEEN @startdate - 7 AND @enddate - 7 THEN 1 END) AS prev_week
FROM member_request ma
WHERE ma.createddate BETWEEN @startdate - 7 AND @enddate;

WHERE - filters 2 weeks range

COUNT(CASE WHEN ... END) counts only rows in 1 week range

Upvotes: 2

Related Questions