(Resolved) Multiple Queries One Result SQL Server 2000

Hello, I have a problem with the following, below is a set of queries that do a similar task, they count up all of the invoices for each project that belong to an organisation (a client).

My question is, i want to display the financial stats for the amount spent tween this year and last year, last year and the year before, and the year before and 2 years ago.

Is it possible to return them as 4 rows of numbers? or columns would be better

Thanks in adv

Sam

(SQL Code listing below)

Code:

---

Declare @OrgID int
declare @StartDate DateTime
declare @EndDate DateTime


declare @Today DateTime
declare @LastYear DateTime
declare @YearBefore DateTime
declare @TwoYearBefore DateTime

set @OrgID = -1


set  @Today  = getdate()
set @LastYear = DATEADD(year, -1, @Today)
set @YearBefore = DATEADD(year, -2, @Today)
set @TwoYearBefore = DATEADD(year, -3, @Today)

SELECT    SUM(InvoiceValue) AS AllTotal FROM    (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices
SELECT    SUM(InvoiceValue) AS LastYear  FROM  (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices WHERE    (InvoiceDate < CONVERT(DateTime, @Today, 103)) AND (InvoiceDate > CONVERT(DateTime, @LastYear, 103))
SELECT    SUM(InvoiceValue) AS YearBefore  FROM (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices WHERE    (InvoiceDate < CONVERT(DateTime, @LastYear, 103)) AND (InvoiceDate > CONVERT(DateTime, @YearBefore, 103))
SELECT SUM(InvoiceValue) AS TwoYearBefore  FROM (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices WHERE    (InvoiceDate < CONVERT(DateTime, @YearBefore, 103)) AND (InvoiceDate > CONVERT(DateTime, @TwoYearBefore, 103))

---

Your WHERE clauses are too complex for me to try to understand...

But the basic idea is:

Code:

---

Select Sum(Case When {....} Then InvoiceValue Else 0 End) "AllTotal"
  ,Sum(Case When {....} Then InvoiceValue Else 0 End) "LastYear"
  ,Sum(Case When {....} Then InvoiceValue Else 0 End) "YearBefore"
  ,Sum(Case When {....} Then InvoiceValue Else 0 End) "TwoYearBefore"
  From ...

---

Within the {....} part of what I just showed you put similar logic to what you have in the WHERE clauses of each sub-query.

You only want to run through the "FROM/WHERE" once.

You are doing it 4 times now - in 4 sub-queries.

Doing it once - with CASE/blocks inside SUM() aggregates works much better.

I do this all the time...

A UNION Query and a few changes to your SQL string should do what you want:

Code:

---

Declare @OrgID int
declare @StartDate DateTime
declare @EndDate DateTime


declare @Today DateTime
declare @LastYear DateTime
declare @YearBefore DateTime
declare @TwoYearBefore DateTime

set @OrgID = -1


set  @Today  = getdate()
set @LastYear = DATEADD(year, -1, @Today)
set @YearBefore = DATEADD(year, -2, @Today)
set @TwoYearBefore = DATEADD(year, -3, @Today)

SELECT  'AllTotal',  SUM(InvoiceValue) AS NVal FROM    (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices
UNION
SELECT 'LastYear',   SUM(InvoiceValue) AS NVal FROM  (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices WHERE    (InvoiceDate < CONVERT(DateTime, @Today, 103)) AND (InvoiceDate > CONVERT(DateTime, @LastYear, 103))
UNION
SELECT  'YearBefore',  SUM(InvoiceValue) AS NVal  FROM (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices WHERE    (InvoiceDate < CONVERT(DateTime, @LastYear, 103)) AND (InvoiceDate > CONVERT(DateTime, @YearBefore, 103))
UNION
SELECT 'TwoYearBefore', SUM(InvoiceValue) AS NVal  FROM (SELECT    InvoiceReference, InvoiceDate, ISNULL(InvoiceValue, 0) InvoiceValue, third_party_margin FROM EXT_Sales_Invoices si inner JOIN project p ON si.project_id = p.project_id inner join (select entity_identifier from entity_organisations where  organisation_role_id = 1 and entity_class_id = 3 and organisation_id = @OrgID) OrgProjects on OrgProjects.entity_identifier = p.project_id inner JOIN project_udf pu ON p.project_id = pu.project_id LEFT JOIN (SELECT    SUM(PurchaseOrderOrInvoiceNetValue) p_value, PurchaseOrderOrInvoiceUniqueSalesInvoiceID FROM          EXT_Purchase_Orders_Or_Invoices WHERE      PurchaseOrderOrInvoiceDelete = 0 AND PurchaseOrderOrPurchaseInvoice = 'PR' GROUP BY PurchaseOrderOrInvoiceUniqueSalesInvoiceID) popi ON si.InvoiceAndAFPsInt1 = popi.PurchaseOrderOrInvoiceUniqueSalesInvoiceID WHERE      InvoiceDelete = 0 AND (InvoiceReference IS NOT NULL)) ProjInvoices WHERE    (InvoiceDate < CONVERT(DateTime, @YearBefore, 103)) AND (InvoiceDate > CONVERT(DateTime, @TwoYearBefore, 103))

---

or you can try using a Scalar Sub-Query

You are a star!!!! Thank you soo much!

Quote:

---

Originally Posted by señorbadger

... or columns would be better

---

I was going for the column option...