(Resolved) Multiple Queries One Result SQL Server 2000

[señorbadger]

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)

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))