Home SQL group by having clause query
Reply: 1

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.297327 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO