Top customers by month - correlated subquery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Top customers by month - correlated subquery?

Author

Topic

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-25 : 07:31:11

Hi,

I have been trying to do a query that will return our top customers each month.

The Invoices table looks something like this:

CompanyName | InvoiceAmount | InvoiceDate
CompanyK | 6943.59 | 14-Sep-05
CompanyA | 6064.55 | 6-Aug-05
CompanyJ | 4160.05 | 18-Aug-05
CompanyI | 3405.84 | 4-Sep-05
CompanyK | 3365.78 | 18-Sep-05
CompanyC | 3250.38 | 12-Aug-05
CompanyD | 2900.56 | 6-Nov-05
CompanyC | 2764.09 | 14-Aug-05
CompanyI | 2460.10 | 8-Oct-05
CompanyJ | 2403.30 | 18-Nov-05
CompanyK | 2399.56 | 4-Oct-05
CompanyA | 2311.88 | 2-Aug-05
CompanyF | 2256.22 | 22-Sep-05
CompanyF | 2150.33 | 20-Oct-05
CompanyH | 1819.26 | 14-Nov-05
CompanyI | 1745.22 | 10-Nov-05
CompanyB | 1580.46 | 6-Oct-05
CompanyF | 1535.92 | 20-Nov-05
CompanyG | 1379.25 | 6-Sep-05

I would like a result set something like

Year | Month | CompanyName | Amount
2005 | 11 | CompanyJ | 2403.30
2005 | 11 | CompanyD | 2900.56
2005 | 10 | CompanyK | 2399.56
2005 | 10 | CompanyI | 2460.10
2005 | 9 | CompanyI | 3405.84
2005 | 9 | CompanyK | 6943.59
2005 | 8 | CompanyJ | 4160.05
2005 | 8 | CompanyA | 6064.55

I tried this:
Select top 2 year(InvoiceDate), Month(InvoiceDate), CompanyName, InvoiceAmount from Invoices
order by year(InvoiceDate), Month(InvoiceDate) desc, InvoiceAmount desc
but that gave me only 2 results, so I tried it in a correlated subquery.

Select year(CreatorTimeStamp), Month(CreatorTimeStamp),
(select top 2 InvoiceID from InvoiceDetails subIDet where year(subIDet.CreatorTimeStamp) = year(IDet.CreatorTimeStamp) and Month(subIDet.CreatorTimeStamp) = Month(IDet.CreatorTimeStamp) ),
(select top 2 TotalTaxAmount from InvoiceDetails subIDet where year(subIDet.CreatorTimeStamp) = year(IDet.CreatorTimeStamp) and Month(subIDet.CreatorTimeStamp) = Month(IDet.CreatorTimeStamp) )
from InvoiceDetails IDet
order by year(CreatorTimeStamp), Month(CreatorTimeStamp) desc

but that returned the error: Subquery returned more than 1 value.

Has anyone done this before and what's the easy way to write this sort of query?

Joshua

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-25 : 07:40:11

[code]
Select year(CreatorTimeStamp),
Month(CreatorTimeStamp),
companyName,
amount
from InvoiceDetails q1
where amount in
(select top 2 amount
from invociedetail q2
where year(q1.CreatorTimeStamp) = year(q2.CreatorTimeStamp)
and Month(q1.CreatorTimeStamp) = Month(q2.CreatorTimeStamp)
order by amount desc)
[/code]

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-25 : 07:52:57

That's spooky simple.

Cool man!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 08:39:24

And use exists than in for better performance


Select year(CreatorTimeStamp), 
       Month(CreatorTimeStamp),
       companyName,
       amount
from InvoiceDetails q1
where exists  
  (select top 2 amount
     from invociedetail q2
    where year(q1.CreatorTimeStamp) = year(q2.CreatorTimeStamp)
      and Month(q1.CreatorTimeStamp) = Month(q2.CreatorTimeStamp)
      where amount=q1.amount
    order by amount desc)

Madhivanan

Failing to plan is Planning to fail

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-25 : 09:04:03

Using exists will not give the desired result.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 09:07:15

If you use valid where clause then it will

Madhivanan

Failing to plan is Planning to fail

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-28 : 10:06:27

Ok, so this is how I spent monday.. Trying to optimise a query and wishing I had more sleep over the weekend...

It's probably really hopeful to make it simpler, but 38 seconds query time is just way too long. Invoices table has 2000 records, InvoiceDetails 4000 and Companies 500. Is there a way to optimise the query (indexes / views)? What books / websites should I subscribe to take my SQL to the next level?

Select Year(I.tmStamp) 'Yr', Month(I.tmStamp) 'Mnth', IDet.sumTotal, I.Currency, C.CompanyID
from Invoices I
join Companies C on I.CustomerCode = C.AccpacCode and I.CustomerName = C.CompanyName
join (Select sum(case DocumentType when '1' then 1 else -1 end * TotalTaxAmount) 'sumTotal', Year(I.tmStamp) 'Yr', Month(I.tmStamp) 'Mnth', I.Currency, CompanyID
from Invoices I
join Companies C on I.CustomerCode = C.AccpacCode and I.CustomerName = C.CompanyName
join InvoiceDetails IDet on I.InvoiceID = IDet.InvoiceID
group by Year(I.tmStamp), Month(I.tmStamp), I.Currency, CompanyID) IDet on IDet.CompanyID = C.CompanyID
and IDet.Yr = Year(I.tmStamp)
and IDet.Mnth = Month(I.tmStamp)
where C.CompanyID in (Select top 20 CompanyID from Invoices subI
join Companies subC on subI.CustomerCode = subC.AccpacCode and subI.CustomerName = subC.CompanyName
join InvoiceDetails subIDet on subI.InvoiceID = subIDet.InvoiceID
group by Year(subI.tmStamp), Month(subI.tmStamp), subC.CompanyID, subI.Currency
having Year(subI.tmStamp) = Year(I.tmStamp)
and Month(subI.tmStamp) = Month(I.tmStamp)
and subI.Currency = I.Currency
order by sum(case DocumentType when '1' then 1 else -1 end * TotalTaxAmount) desc
)
group by Year(I.tmStamp), Month(I.tmStamp), IDet.sumTotal, I.Currency, C.CompanyID

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-28 : 14:23:52

Joshua,

In terms of taking your performance tuning to the next level a simple place to start is directly in the Query Analyzer. Simply allow the system to show the execution plan for your query(s) and see where the "hotspots" are. You can tell the system to show the Execution Plan as an option you check under the Query menu option. Once you turn that on, just run the query and then a new tab will be displayed in the Results pane at the bottom. Click on the Execution Plan tab and you'll see exactly what SQL Server is doing to return your results. When you see things like "TABLE SCAN" the server is telling you that it couldn't find any indexes that would help it do its work more quickly than simply scanning every row of the table 1 by 1. As you play with that, you will start understanding what is actually going on behind the scenes. If you want to know if a change in your query will be for the better or worse you can copy the query, and then paste it below the original and make the changes to it. Then do a run (which will actually run both of them) and you can see in the execution plan what the percent of time for Query 1 is compared to Query 2. If you Query 2 is much better then you improved it, if it worse then you destroyed it etc.

You'll begin seeing how fast the system can actually do the bulk of the work, but how costly it is to actually look up the data for the selects after the values are found in an index. Or how smart SQL Server can be at times and will ignore your index if you are going to return a ton of rows instead of just a few so that it can avoid doing the lookups after the fact.

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-29 : 04:31:20

Druer,

Thanks for the tips. I checked out the execution plan functionality of query analyser and it seems like adding the collated subquery increases the run time of the statement exponentially.

If I run the first part of the query (without the where statment), I get results within 1 second. When I add the collated where statement, the run time jumps to 48 seconds. Does the collated subquery run once for each record of the result set?

I also tried adding indexes to the columns in the tables that I was using for the various joins and the execution time jumped further to around 1 min 25 seconds.

I prepared two images illustrating the two run times, but couldn't attach them to this post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources