Summing data across multiple columns based on common parameters

Transact-SQL
1

I am trying to generate a set of data sums from multiple tables based on a common set of parameters (dates) as a single row with the first column being one of the parameters. Typically, this would be for a day, a week, a month, ... and would be regularly run adding the row to the end of an excel file. Typical data would be Sales Book, Sales Bill, PO issued, materials received, etc
At the moment I am strugling with the first part - just getting started on the row of data up to the UNION ALL.

SELECT 
'EndDate' AS Caption, 
SUM(SOPInvoiceCreditLine.LineTotalValue) AS InvSum
FROM SOPInvoiceCreditLine InvoiceCreditLine 
WHERE 
SOPInvoiceCreditLine.InvoiceCreditDate>= [StartDate] and SOPInvoiceCreditLine.InvoiceCreditDate <=[EndDate] 
UNION ALL
SELECT Sum(SOPOrderReturnLine.LineTotalValue) AS OrdSum
WHERE 
SOPOrderReturnLine.InvoiceCreditDate>= [StartDate] and SOPOrderReturnLine.InvoiceCreditDate <=[EndDate]

The error message says "The multi-part identifier " SOPInvoiceCreditLine.InvoiceCreditDate" could not be bound", and similarly for LineTotalValue.

In the long term, there would be data from 4 - 10 tables as columns after the data.

2

There are (at least) two problems that I see with your query.

First, in the part before the UNION ALL, you are giving the alias "InvoiceCreditLine" to table SOPInvoiceCreditLine. Once you do that, everywhere else in that query, you have to use that alias to refer to that table. For example, see below, where I use the alias "i" for SOPInvoiceCreditLine

SELECT  'EndDate' AS Caption ,
        SUM(i.LineTotalValue) AS InvSum
FROM    SOPInvoiceCreditLine AS i -- Alias for SOPInvoiceCreditLine is now "i".
WHERE   i.InvoiceCreditDate >= [StartDate]
        AND i.InvoiceCreditDate <= [EndDate]

The second issue is the part after the UNION ALL. Each part in a UNION ALL query has to be able to stand on its own, and each part should have the same number and type of columns in the SELECT clause. So the second part perhaps should be something like this (just an educated guess).

SELECT  'ReturnDate' AS Caption ,
        SUM(r.LineTotalValue) AS OrdSum
FROM    SomeTable AS r
WHERE   r.InvoiceCreditDate >= [StartDate]
        AND r.InvoiceCreditDate <= [EndDate]

So the whole query would be something like this:

SELECT  'EndDate' AS Caption ,
        SUM(i.LineTotalValue) AS InvSum
FROM    SOPInvoiceCreditLine AS i -- Alias for SOPInvoiceCreditLine is now "i".
WHERE   i.InvoiceCreditDate >= [StartDate]
        AND i.InvoiceCreditDate <= [EndDate]
        
UNION ALL

SELECT  'ReturnDate' AS Caption ,
        SUM(r.LineTotalValue) AS OrdSum
FROM    SomeTable AS r
WHERE   r.InvoiceCreditDate >= [StartDate]
        AND r.InvoiceCreditDate <= [EndDate]
1 Like
3

James
Thank you for the quick reply. As you may have guessed, I am new to SQL. I also think that I did not properly describe my target.
I have a series of tables (T1, T2, etc), with multiple columns including a date and a value in each
I also have 2 parameters that will be passed to the query from Excel: [StartDate] and [EndDate].
These may be the same or different.
The output will be a single row as follows:
@EndDate, SUM(T1.Value), SUM(T2.Value), etc wher @EndDate is the value of the parameter [EndDate]
The SUM is the sum of all values inclusively within the range [StartDate] and [EndDate].
I hope that this helps & thanks again