Multiple results in query needed

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

General SQL Server Forums

New to SQL Server Programming

Multiple results in query needed

Author

Topic

StarMan
Starting Member

2 Posts

Posted - 2012-02-22 : 16:04:07

Hi,
I have a simple but very time consuming series of queries to do, I hope there is a way to speed this up.

Case:
I need to get an invoice amount for each customer, but I need this amount in various countings

Current month, total of current Quarter, total current Year
and also these amounts for the same period previous year.

Assume that the current month = 10 (october) and year = 2011

The customers change, so i first select all cuurent customers and I store them in memory.
Environment: Visual Basic 6 / SQL Server 2008

For each Table.Customer

'Query1 (Month):
Select sum(Inv_Amount) AS sum_inv_amount
From INVOICES
WHERE INV_Month = 10
AND INV_Year = 2011
AND INV_Customer = Table.Customer

vSUM_INV_AMOUNT_P = sum_inv_amount

'Query2 (Quarter):
Select DISTINCT INV_Customer, sum(Inv_Amount) AS sum_inv_amount
From INVOICES
WHERE INV_Month in (9,10)
AND INV_Year = 2011
AND INV_Customer = Table.Customer

vSUM_INV_AMOUNT_Q = sum_inv_amount

'Query3 (Year):
Select DISTINCT INV_Customer, sum(Inv_Amount) AS sum_inv_amount
From INVOICES
WHERE INV_Month in (1,2,3,4,5,6,7,8,9,10)
AND INV_Year = 2011
AND INV_Customer = Table.Customer

vSUM_INV_AMOUNT_Y = sum_inv_amount

'Query 4,5 and 6 are alike, the INV_Year has the value of 2010.

Can I do this in a single query?

Mahdi Eftekhari Moghaddam
Starting Member

3 Posts

Posted - 2012-02-22 : 16:45:59

from the look of it you might be able to use CASE and might benefit from CTE or correlated subqueries but I am not sure. Could you post the structure of your tables with few sample data so we can have a look.

Regards
Mahdi Eftekhari

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources