question

MuhammadAnzar-6095 avatar image
0 Votes"
MuhammadAnzar-6095 asked MelissaMa-msft commented

union all and group by

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

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You need to reformat your post to make it readable. (And more precisely, restore the line breaks that supposedly were there originally.)

Also, it seems that most of it is SQL. It probably helps if you add a little more text about what you want help with.

0 Votes 0 ·

Hi @MuhammadAnzar-6095,

Could you please validate and provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MuhammadAnzar-6095,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.