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

[szlamany]

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

But the basic idea is:

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

[Mark Gambo]

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

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

[Mark Gambo]

or you can try using a Scalar Sub-Query

[señorbadger]

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

[szlamany]

... or columns would be better

I was going for the column option...