Home SQL group by having clause query
Reply: 0

SQL group by having clause query

user4397
1#
user4397 Published in July 21, 2018, 9:50 am

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
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO