Solved

Customer with Largest Growth past 30 days

Posted on 2009-05-15
10
382 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query - Duplicate dates with different activities counts 10 45
Syntax issue with my Where Clause SQL 2012 20 38
Problem with MySQL query - graph 3 26
Error 438 6 16
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

733 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