How to use SQL to do a group by with different dates?

Question

Lets say I have a table with the following columns:

• Qty, INTEGER
• SaleDate, DATETIME

I would now like to see this result:

Sold in the last 7 days | Sold in last 14 days
-----------------------------------------------------
10                      | 20

I can use a where clause to use between, however how would I get the qty sold for 7 and 14 days?

Answer 1

select sum(Qty), datediff(w, getdate(), SaleDate) as Period
from table
group by  datediff(ww, getdate(), SaleDate)

Answer 2

Filter in the WHERE clause to get days 0 to -14. Then aggregate on days 0 to -7 separately.

SELECT
   ...,
   SUM(CASE WHEN SaleDate >= DATEADD(day, -7, GETDATE()) THEN 1 ELSE 0 END) AS 7days,
   COUNT(*) AS 14days
FROM
   MyTable
WHERE
   SaleDate >= DATEADD(day, -14, GETDATE())
GROUP BY
   ...

Answer 3

Tested in MS T-SQL 2003

declare @whatever table(
    qty int,
    saledate datetime
)
insert into @whatever select 1, getdate()
insert into @whatever select 2, dateadd(dd, -1, getdate())
insert into @whatever select 2, dateadd(dd, -2, getdate())
insert into @whatever select 1, dateadd(dd, -3, getdate())
insert into @whatever select 1, dateadd(dd, -4, getdate())
insert into @whatever select 1, dateadd(dd, -5, getdate())
insert into @whatever select 1, dateadd(dd, -6, getdate())
insert into @whatever select 1, dateadd(dd, -7, getdate())
insert into @whatever select 1, dateadd(dd, -8, getdate())
insert into @whatever select 1, dateadd(dd, -9, getdate())
insert into @whatever select 2, dateadd(dd, -10, getdate())
insert into @whatever select 2, dateadd(dd, -11, getdate())

insert into @whatever select 1, dateadd(dd, -15, getdate())
insert into @whatever select 2, dateadd(dd, -16, getdate())

select
        qty,
        sum(
            case
                when datediff(dd, saledate, getdate()) between 0 and 7 then 1
                else 0
            end
        ) as [Sold in last 7 days],
        sum(
            case
                when datediff(dd, saledate, getdate()) between 0 and 14 then 1
                else 0
            end
        ) as [Sold in last 14 days]
    from
        @whatever
    group by
        qty
;