Post reply

Getting the biggest spending customers for last 7 days

  • April 9, 2020 at 10:23 am

    #3741712

    Hi all,

    Can anyone help with the below SQL. I'm trying to get the top 5 customers for the last 7 days. I've tried to do it as shown below but it doesn't like my ORDER BY statement in the sub select statement. Any way I can get around this?

    Thanks in advance.

    Paul.

    select top 5 * from
    (select c.companyname as companyname, sum(j.cost) as cost
    from job j
    inner join customer c
    on j.cus_guid = c.guid
    where j.duedate >= dateadd(day,-7,getdate()) and
    j.duedate < dateadd(day,+1,getdate())
    group by c.guid,c.companyname,j.cost
    order by cost desc
    )  as data

  • April 9, 2020 at 10:32 am

    #3741714 

    select top 5 c.companyname as companyname, sum(j.cost) as cost
    from job j
    inner join customer c
    on j.cus_guid = c.guid
    where j.duedate >= dateadd(day,-7,getdate()) and
    j.duedate < dateadd(day,+1,getdate())
    group by c.guid,c.companyname,j.cost
    order by cost desc

  • April 9, 2020 at 10:34 am

    #3741715

    Thank you Frederico.

  • April 9, 2020 at 11:21 am

    #3741722

    I think there's an error in your GROUP BY, Frederico.

    WITH Top5Customers AS (
    SELECT TOP(5)
    cus_guid, WeekCost = SUM(cost)
    FROM job
    WHERE duedate >= dateadd(day,-7,getdate()) 
    AND duedate < dateadd(day,+1,getdate())
    GROUP BY cus_guid
    ORDER BY SUM(cost) DESC
    )
    SELECT c.companyname, j.WeekCost
    FROM Top5Customers j
    INNER JOIN customer c
    ON j.cus_guid = c.[guid]
    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • April 9, 2020 at 11:53 am

    #3741726

    not my group by - I just copied the original code and moved the top 5 to the inner sql - and removed the outer sql as not required

    so if the original inner query with the group by worked it should also work by adding the top 5 to it

  • April 9, 2020 at 3:20 pm

    #3741801

    Ahhhh that's much better for me.

    Thank you Chris.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply