Solved

Customer with Largest Growth past 30 days

Posted on 2009-05-15
10
347 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
Comment Utility
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
Comment Utility
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
Comment Utility
are u sure you are in the proper database ? ( i mean the query window )
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
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
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75

Expert Comment

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

Author Comment

by:Leo Torres
Comment Utility
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
Comment Utility
Current30Days-Previous30Days  
ISNULL(Current30Days) - Previous30Days  
 
0
 
LVL 8

Author Comment

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

Author Closing Comment

by:Leo Torres
Comment Utility
Excellent!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now