• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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

0
Leo Torres
Asked:
Leo Torres
  • 6
  • 4
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
are u sure you are in the proper database ? ( i mean the query window )
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
try with a LEFT JOIN instead of an inner join
0
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Current30Days-Previous30Days  
ISNULL(Current30Days) - Previous30Days  
 
0
 
Leo TorresSQL DeveloperAuthor Commented:
Thanks....... I got it............Never done without your help
0
 
Leo TorresSQL DeveloperAuthor Commented:
Excellent!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now