locked
1 query, 2 counts RRS feed

  • Question

  • Sorry if this has been asked before. All of the similar looking topics either don't seem to be applicable to my problem or the responses are confusing me.

    I want to, in one query, see all of the clients, how many purchases each one made last week, and how many purchases each one made the week before. I can do this in two queries without too much trouble.


    Query 1:

    select Clients.Name, COUNT(Purchases.ClientID) as [purchases7daysago]

    from Purchases

    inner join Clients on

    Purchases.ClientID = Clients.ID

    where Time between DATEADD(d, -7, DateTime()) and DateTime()

    group by Clients.Name

    Query 2:


    select Clients.Name, COUNT(Purchases.ClientID) as [purchases7-14daysago]

    from Purchases

    inner join Clients on

    Purchases.ClientID = Clients.ID

    where Time between DATEADD(d, -14, DateTime()) and DATEADD(d, -7, DateTime())

    group by Clients.Name

    The results:

    Name    purchases7daysago
    Adam    12
    Bob    0
    Chuck    20

    Name    purchases14daysago
    Adam    16
    Bob    0
    Chuck    21

    How can I best have a single query which returns this?

    Name    purchases7daysago    purchases7-14daysago
    Adam    12    16
    Bob    0    0
    Chuck    20    21

    I tried this query, based on similar threads:

    select Clients.Name, ( select COUNT(ClientID) from Purchases where Time between DATEADD(d, -7, DateTime()) and DateTime()

    and ClientID = Purchases.ID

    ) as [7daysago],

    (

    select COUNT(ClientID) from Purchases

    where Time between DATEADD(d, -14, DateTime()) and (DATEADD(d, -7, DateTime())

    and ClientID = Purchases.ID

    ) as [7-14daysago]

    from Purchases

    inner join Clients on Purchases.ClientID = Clients.ID

    group by Clients.name

    but the resulting table looks like this:

    Name    7daysago    7-14daysago
    Adam    32    37
    Bob    32    37
    Chuck    32    37

    It's like the 'and' statement in each subquery isn't doing a thing.

    How do I do what I'm trying to do, in the most efficient manner possible? I also want to be able to intuitively add more fields for more weeks back, and a field for the average purchases per week (for the duration of time specified).


    • Edited by jlimdso Monday, June 25, 2012 10:13 PM
    Monday, June 25, 2012 10:11 PM

Answers

  • Try:

    select Clients.Name, COUNT(case WHEN Purchases.Time between DATEADD(day, -7, CAST(CURRENT_TIMESTAMP as DATE))

    and CAST(CURRENT_TIMESTAMP AS DATE) THEN Purchases.ClientID END) as [purchases7daysago], COUNT(case WHEN Purchases.Time between DATEADD(day, -14, CAST(CURRENT_TIMESTAMP as DATE))

    and dateadd(day,-8,CAST(CURRENT_TIMESTAMP AS DATE)) THEN Purchases.ClientID END) as [purchases14daysago] from Purchases inner join Clients on Purchases.ClientID = Clients.ID where Purchases.Time between DATEADD(day, -14, CAST(CURRENT_TIMESTAMP AS DATE)) and CURRENT_TIMESTAMP group by Clients.Name



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, June 25, 2012 10:23 PM

All replies

  • Try:

    select Clients.Name, COUNT(case WHEN Purchases.Time between DATEADD(day, -7, CAST(CURRENT_TIMESTAMP as DATE))

    and CAST(CURRENT_TIMESTAMP AS DATE) THEN Purchases.ClientID END) as [purchases7daysago], COUNT(case WHEN Purchases.Time between DATEADD(day, -14, CAST(CURRENT_TIMESTAMP as DATE))

    and dateadd(day,-8,CAST(CURRENT_TIMESTAMP AS DATE)) THEN Purchases.ClientID END) as [purchases14daysago] from Purchases inner join Clients on Purchases.ClientID = Clients.ID where Purchases.Time between DATEADD(day, -14, CAST(CURRENT_TIMESTAMP AS DATE)) and CURRENT_TIMESTAMP group by Clients.Name



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, June 25, 2012 10:23 PM
  • with temp as
    (select Clients.ID,Clients.Name, COUNT(Purchases.ClientID) as [purchases7-14daysago]
    from Purchases
    inner join Clients on
    Purchases.ClientID = Clients.ID
    where Time between DATEADD(d, -14, DateTime()) and DATEADD(d, -7, DateTime())
    group by Clients.Name)
    select Clients.Name, COUNT(Purchases.ClientID) as [purchases7daysago],[temp.purchases7-14daysago]

    from Purchases

    inner join Clients on

    Purchases.ClientID = Clients.ID

    inner join temp on

    Purchases.ClientID=temp.ID

    where Time between DATEADD(d, -7, DateTime()) and DateTime()

    group by Clients.Name

    Many Thanks & Best Regards, Hua Min

    Monday, June 25, 2012 10:45 PM
  • hi,

    try this..

    select Clients.Name, COUNT(case when Time between DATEADD(d, -7, DateTime()) and DateTime()

    then Purchases.ClientID

    else 0

    ) as [purchases7daysago],

    COUNT(case when Time between DATEADD(d, -14, DateTime()) and Dateadd(d,-7,datetime())

    then Purchases.ClientID

    else 0

    ) as [purchases7-14daysago]

    from Purchases

    inner join Clients on

    Purchases.ClientID = Clients.ID

    group by Clients.Name




    • Edited by kumara11 Tuesday, June 26, 2012 8:22 AM
    Tuesday, June 26, 2012 7:38 AM