CODEWITHSUNDEEP
sqlgroup-by
vikasde
vikasde

Reputation: 5751

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

Lets say I have a table with the following columns:

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?

Upvotes: 2

Views: 533

Answers (3)

baldy
baldy

Reputation: 5541

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

Upvotes: 0

sorpigal
sorpigal

Reputation: 26106

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
;

Upvotes: 0

gbn
gbn

Reputation: 432667

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
   ...

Upvotes: 3

Related Questions