union all and group by

Question

SELECT ProductName Detail, sum(PackQty) PurchaseQty, SUM(Purchase) PurchaseValue, sum(SaleQuantity) SaleQty,--, SUM(Sale) Sale, sum (Received) CasReceive , SUM(paid) CashPaid FROM ( SELECT PurchaseDate AS TransactionDate, ProductName, 0 AS Sale, Total as Purchase, PackQuantity as PackQty, 0 as SaleQuantity, 0 AS Received, 0 as Paid FROM dbo.Pur_OrderDetail UNION ALL SELECT ReciveDate AS TransactionDate, Remarks, Received as CashReceived, 0 AS Purchase, 0 as PackQty , 0 as SaleQuantity, Received AS Received, 0 as Paid FROM dbo.CashReceived UNION ALL SELECT SaleDate AS TransactionDate, ProductName, Total as Sale, 0 AS Purchase, 0 as PackQty , SaleQuantity,-- as SaleQty 0 AS Received, 0 as Paid FROM dbo.[Order Details] UNION ALL SELECT PaymentDate AS TransactionDate, SupplierName, 0 as CashReceived, 0 AS Purchase, 0 as PackQty , 0 as SaleQuantity, 0 AS Received, Paid FROM dbo.CashPayments)as temp1 where TransactionDate between '2021-03-17' and '2021-03-28' GROUP BY ProductName--, TransactionDate --i need TransactionDate for parameter and not in group by clause

Answer 1

Hi @Muhammad Anzar ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is helpful.

;with cte as   
(SELECT PurchaseDate AS TransactionDate, ProductName, 0 AS Sale, Total as Purchase, PackQuantity as PackQty, 0 as SaleQuantity,   
0 AS Received, 0 as Paid FROM dbo.Pur_OrderDetail   
UNION ALL   
SELECT ReciveDate AS TransactionDate, Remarks, Received as CashReceived, 0 AS Purchase, 0 as PackQty , 0 as SaleQuantity,   
Received AS Received, 0 as Paid FROM dbo.CashReceived   
UNION ALL   
SELECT SaleDate AS TransactionDate, ProductName, Total as Sale, 0 AS Purchase, 0 as PackQty , SaleQuantity,  
0 AS Received, 0 as Paid FROM dbo.[Order Details]   
UNION ALL   
SELECT PaymentDate AS TransactionDate, SupplierName, 0 as CashReceived, 0 AS Purchase, 0 as PackQty , 0 as SaleQuantity,   
0 AS Received, Paid FROM dbo.CashPayments)  
,cte1 as (  
select ProductName,Sale, Purchase,PackQty, SaleQuantity, Received,  Paid  
 from cte   
where TransactionDate between '2021-03-17' and '2021-03-28' )  
select ProductName Detail, sum(PackQty) PurchaseQty, SUM(Purchase) PurchaseValue, sum(SaleQuantity) SaleQty,   
SUM(Sale) Sale, sum (Received) CasReceive , SUM(paid) CashPaid   
from cte1  
GROUP BY ProductName  

If above is not working,we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.