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