Solved

Customer with Largest Growth past 30 days

Posted on 2009-05-15
10
369 Views
Last Modified: 2012-05-07

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

0
Comment
Question by:Leo Torres
  • 6
  • 4
10 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24398103
Hello ltorres321,



Regards,

Aneesh
SELECT TOP 5 a.CustomerNo, TotalSalesBefore30Days, TotalSales, TotalSalesBefore30Days- TotalSales as ThisMonthSales
FROM (
	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
) A 
INNER JOIN (
	select CustomerNo, sum(TotalSales) as TotalSalesBefore30Days 
	from dbo.V_Commission_Report
	where InvoiceDate < dateadd(day, datediff(day, 0 ,getdate())-30, 0)
	group by CustomerNo
)B on a.CustomerNo = b.CustomerNo
ORDER BY TotalSalesBefore30Days- TotalSales DESC

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24398591
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24398623
are u sure you are in the proper database ? ( i mean the query window )
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Author Comment

by:Leo Torres
ID: 24399116
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

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24399135
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

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24399144
try with a LEFT JOIN instead of an inner join
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24399179
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

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 500 total points
ID: 24399264
Current30Days-Previous30Days  
ISNULL(Current30Days) - Previous30Days  
 
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24399323
Thanks....... I got it............Never done without your help
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 31582026
Excellent!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question