Monday, March 26, 2012

Gather aggregate data from two tables

I have three tables:

1) Contract - columns ContractNo and ContractName

2) SalesTransactions - multiple rows for each contract - relevant columns: ContractNo, InvoiceDate, Value

3) CostOfSaleTransactions - multiple rows for each contract - relevant columns: ContractNo, TransactionDate, Cost

How do I write a SELECT statement to produce rows containing:

ContractNo ContractName Sum of Value for ContractNo between @.FromDate and @.ToDate Sum of Cost for ContractNo between @.FromDate and @.ToDate

Not all Contracts have either Sales or CostOfSales Transactions in the relevant date range and so one or both totals can be zero.

I've written something like:

SELECT CT.ContractNo, CT.ContractName, sum(CT.Value) as TotalValue, Sum(CS.Cost) as TotalCost

FROM Contract CT

INNER JOIN SalesTransactions ST ON CT.ContractNo=ST.ContractNo

INNER JOIN CostOfSaleTransactions CS ON CT.ContractNo=CS.ContractNo

WHERE (ST.InvoiceDate BETWEEN @.FromDate AND @.ToDate) AND (CS.TransactionDate BETWEEN @.FromDate AND @.ToDate)

GROUP BY CT.ContractNo, CT.ContractName

The TotalValue and TotalCost figures I get are much higher than expected. I presume this is something to do with the JOINs or WHERE clause. Please can you advise how I get the correct values?

You can do the following:

SELECT CT.ContractNo, CT.ContractName

, (SELECT sum(ST.Value)

FROM SalesTransactions ST

WHERE CT.ContractNo=ST.ContractNo

AND ST.InvoiceDate BETWEEN @.FromDate AND @.ToDate) as TotalValue

, (SELECT Sum(CS.Cost)

FROM CostOfSaleTransactions CS

WHERE CT.ContractNo=CS.ContractNo

AND CS.TransactionDate BETWEEN @.FromDate AND @.ToDate) as TotalCost

FROM Contract CT

No comments:

Post a Comment