Solved

Customer with Largest Growth past 30 days

Posted on 2009-05-15
10
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…

690 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