asked on

Customer with Largest Growth past 30 days

I did not think this query was do difficult. I want to select the Top 5 Sales Growth Customer in the last 30 days.
For example
[30 days back total is 25,000 (Today - 4/14/09)] -- [17,000 Prior 30 days(4/14/09-3/14/09)]

this result would be 8,000 then select top 5 results

What I have here I later noticed has a big flaw it only Selects Customer sales in last 30 days. I need to select the ones by biggest diferences.. Help any advice would be apprecited.

select CustomerNo, sum(TotalSales) as TotalSales 
from dbo.V_Commission_Report
where InvoiceDate between dateadd(day, datediff(day, 0 ,getdate())-30, 0) and dateadd(day,datediff(day, 0 ,getdate()), 0)
group by CustomerNo

Open in new window

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leo Torres

ASKER

Wow.. that was fast and makes Sense looks like the logic will work but why does the query not see the dbo.V_Commission view? Says its invalid see picture...

Thanks Again!!
Top5Growth.JPG

Aneesh

are u sure you are in the proper database ? ( i mean the query window )

Leo Torres

ASKER

Sorry you were right.. Now it runs.. You have been of great help I tweaked what you gave me and now it is doing what I want except for one thing.. Those clients that sold in Previous30Days have not Sold in Current30Days they dont appear how could I get those in there showing negative.. And yes I did remove the top 5 for now... Would that be a Union??

SELECT a.CustomerNo, Previous30Days, Current30Days, Current30Days-Previous30Days  as Growth
FROM (
        select CustomerNo, sum(TotalSales) as Previous30Days 
        from dbo.V_Commission_Report
        where InvoiceDate < dateadd(day, datediff(day, 0 ,getdate())-30, 0) and InvoiceDate > dateadd(day, datediff(day, 0 ,getdate())-60, 0)
        group by CustomerNo
) A 
INNER JOIN (
        select CustomerNo, sum(TotalSales) as Current30Days 
        from dbo.V_Commission_Report
        where InvoiceDate between dateadd(day, datediff(day, 0 ,getdate())-30, 0) and dateadd(day,datediff(day, 0 ,getdate()), 0)
        group by CustomerNo
)B on a.CustomerNo = b.CustomerNo
ORDER BY Current30Days-Previous30Days Desc

Open in new window

Leo Torres

ASKER

or Wait couldnt we just fix the join at the bottom that says to join both select? I dont know the syntax??

on a.CustomerNo = b.CustomerNo

Open in new window

Aneesh

try with a LEFT JOIN instead of an inner join

Leo Torres

ASKER

Ok better that was a step in the right Direction.. Now one more issue.. Its not performing the calculation because Current month is NULL-35,000= NULL (What its doing Now)

I would like NULL-35,000= -35,000

SELECT a.CustomerNo,Current30Days, Previous30Days, Current30Days-Previous30Days  as Growth
FROM (
        select CustomerNo, sum(TotalSales) as Previous30Days 
        from dbo.V_Commission_Report
        where InvoiceDate < dateadd(day, datediff(day, 0 ,getdate())-30, 0) and InvoiceDate > dateadd(day, datediff(day, 0 ,getdate())-60, 0)
        group by CustomerNo
) A 
left JOIN (
        select CustomerNo, sum(TotalSales) as Current30Days 
        from dbo.V_Commission_Report
        where InvoiceDate between dateadd(day, datediff(day, 0 ,getdate())-30, 0) and dateadd(day,datediff(day, 0 ,getdate()), 0)
        group by CustomerNo
)B on a.CustomerNo = b.CustomerNo
ORDER BY Current30Days-Previous30Days Desc

Open in new window

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leo Torres

ASKER

Thanks....... I got it............Never done without your help

Leo Torres

ASKER

Excellent!