Home SQL group by having clause query

# SQL group by having clause query

user3266033
1#
user3266033 Published in 2018-01-11 10:36:02Z

I have the below table and query. As you can see from the code when run, it is dividing by MAX(NewBalance) which for receipt number 5 is 155.

## receiptNo 5 example

However I want it to use the NewBalance for the Max(Receiptno2) value i.e 125 instead of the MAX(NewBalance) of 155.

so it should use the NewBalance value when RecieptNo = 5 and receiptNo2 = 5

CREATE TABLE [dbo].[Y](
[Item No_] [nvarchar](20) NULL,
[ReceiptNo] [bigint] NULL,
[ReceiptNo2] [bigint] NULL,
[NewBalance] [decimal](38, 20) NULL,
[InventoryBalance] [decimal](38, 20) NULL,
[PurchaseQty] [decimal](38, 20) NULL,
[IntakeSellingPrice] [decimal](38, 20) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Y] ([Item No_], [ReceiptNo], [ReceiptNo2], [NewBalance], [InventoryBalance], [PurchaseQty], [IntakeSellingPrice])
VALUES (N'1000001', 1, 1, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(0.00000000000000000000 AS Decimal(38, 20)), CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 2, 2, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(5.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 3, 3, CAST(110.00000000000000000000 AS Decimal(38, 20)), CAST(60.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(12.50000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 4, 4, CAST(155.00000000000000000000 AS Decimal(38, 20)), CAST(105.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(15.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 5, 5, CAST(125.00000000000000000000 AS Decimal(38, 20)), CAST(75.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(20.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 5, 4, CAST(155.00000000000000000000 AS Decimal(38, 20)), CAST(25.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(15.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 5, 3, CAST(110.00000000000000000000 AS Decimal(38, 20)), CAST(-25.00000000000000000000 AS Decimal(38, 20)), CAST(25.00000000000000000000 AS Decimal(38, 20)), CAST(12.50000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 5, 2, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(-75.00000000000000000000 AS Decimal(38, 20)), CAST(-25.00000000000000000000 AS Decimal(38, 20)), CAST(5.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 5, 1, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(-175.00000000000000000000 AS Decimal(38, 20)), CAST(-75.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 4, 3, CAST(110.00000000000000000000 AS Decimal(38, 20)), CAST(55.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(12.50000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 4, 2, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(5.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(5.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 4, 1, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(-95.00000000000000000000 AS Decimal(38, 20)), CAST(5.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 3, 2, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(5.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 3, 1, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(-90.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)))
, (N'1000001', 2, 1, CAST(100.00000000000000000000 AS Decimal(38, 20)), CAST(-50.00000000000000000000 AS Decimal(38, 20)), CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)))
GO

SELECT [Item No_],
ReceiptNo,
SUM(CASE WHEN PurchaseQty <= 0 THEN 0 ELSE PurchaseQty  END  * IntakeSellingPrice) / MAX(NewBalance) AS avg_price
FROM Y
GROUP BY [Item No_], ReceiptNo

Justinas Marozas
2#
Justinas Marozas Reply to 2018-01-11 11:46:29Z
 You can get MAX(ReceiptNo2) by grouping in common table expression and then joining on it in SELECT statement to access all values of that row. ;WITH Y_AGGREGATED([Item No_], ReceiptNo, MAX_ReceiptNo2) AS ( SELECT [Item No_], ReceiptNo, MAX(ReceiptNo2) AS MAX_ReceiptNo2 FROM [Y] GROUP BY [Item No_], ReceiptNo ) SELECT Y.* FROM [Y] JOIN [Y_AGGREGATED] AS A ON A.[Item No_] = Y.[Item No_] AND A.ReceiptNo = Y.ReceiptNo AND A.MAX_ReceiptNo2 = Y.ReceiptNo2 
 You need to login account before you can post.
Processed in 0.297327 second(s) , Gzip On .