Choosing price selecting from 2 customers

I have to find a price by choosing the correct price out 3 options in a hierarchy

from highest to lowest

  1. if trtype is Quot than choose this price over all from the [price] column
  2. if Alvl is greater than 0 then choose this price.(so if alvl is 6 then choose blvl = 6 and customer = lvlholder and latest date (start_date) for that price.
  3. last if both trtype is blank and Alvl is 0 then choose this price. (for this customer the default customer level is 8, so looking for blvl = 8 and customer = lvlholder)

All the “level prices” from 1-8 are stored in a lvlholder customer but have multiple price records so I need to pull the latest date for those. blvl is the level for the prices 1-8 stored in this lvlholder customer. (not sure why its setup like this but that’s how it is).

It’s a weird query because I’m choosing one customer (“test”) to look at for [trtype] and [alvl] and if [alvl] > 0 or both [trtype] is blank and [alvl] is 0 then I have to choose a price with customer being lvlholder and [alvl] has to match with [blvl] for those.

below is the outcome I would like from the query, when customer is “test”

itemcode uom price start_date FinalResult
ABC1234 EA 0.00 1/15/2024 29.57
X123456 CT 144.10 5/15/2024 144.10
BR23456 PC 0.00 1/1/2024 15.56
CX23456 PNL 0.00 1/1/2024 15.36

Below is the table and it’s data for [sample data][1]

create table pricing(Customer,itemcode,uom,trtype,Alvl,Blvl,price,start_date)as values
 ('test','ABC1234','EA','',6,0,0.0,'5/27/2024'::date)
,('test','X123456','CT','Quot',0,0,144.1,'5/15/2024')-----------
,('test','BR23456','PC','',0,0,0,'4/21/2024')
,('test','CX23456','PNL','',0,0,0,'4/21/2024')
,('lvlholder','ABC1234','EA','',0,3,44.9,'07/11/23')
,('lvlholder','ABC1234','EA','',0,3,44.9,'11/30/2023')
,('lvlholder','ABC1234','EA','',0,5,41.38,'07/11/23')
,('lvlholder','ABC1234','EA','',0,5,41.38,'11/30/2023')
,('lvlholder','ABC1234','EA','',0,2,34.81,'1/15/2024')
,('lvlholder','ABC1234','EA','',0,5,30.49,'1/15/2024')
,('lvlholder','ABC1234','EA','',0,8,28.39,'1/15/2024')
,('lvlholder','ABC1234','EA','',0,2,49.89,'07/11/23')
,('lvlholder','ABC1234','EA','',0,2,49.89,'11/30/2023')
,('lvlholder','ABC1234','EA','',0,7,37.24,'07/11/23')
,('lvlholder','ABC1234','EA','',0,7,37.24,'11/30/2023')
,('lvlholder','ABC1234','EA','',0,4,31.43,'1/15/2024')
,('lvlholder','ABC1234','EA','',0,7,28.97,'1/15/2024')
,('lvlholder','ABC1234','EA','',0,1,62.36,'07/11/23')
,('lvlholder','ABC1234','EA','',0,1,62.36,'11/30/2023')
,('lvlholder','ABC1234','EA','',0,8,36.5,'07/11/23')
,('lvlholder','ABC1234','EA','',0,8,36.5,'11/30/2023')
,('lvlholder','ABC1234','EA','',0,1,38.65,'1/15/2024')
,('lvlholder','ABC1234','EA','',0,3,33.05,'1/15/2024')
,('lvlholder','ABC1234','EA','',0,6,29.57,'1/15/2024')------------
,('lvlholder','ABC1234','EA','',0,4,42.66,'07/11/23')
,('lvlholder','ABC1234','EA','',0,4,42.66,'11/30/2023')
,('lvlholder','ABC1234','EA','',0,6,40.13,'07/11/23')
,('lvlholder','ABC1234','EA','',0,6,40.13,'11/30/2023')
,('lvlholder','X123456','CT','',0,1,251.68,'01/01/53')
,('lvlholder','X123456','CT','',0,2,201.52,'01/01/53')
,('lvlholder','X123456','CT','',0,3,191.4,'01/01/53')
,('lvlholder','X123456','CT','',0,4,181.72,'01/01/53')
,('lvlholder','X123456','CT','',0,5,176.22,'01/01/53')
,('lvlholder','X123456','CT','',0,6,170.94,'01/01/53')
,('lvlholder','X123456','CT','',0,7,165.88,'01/01/53')
,('lvlholder','X123456','CT','',0,8,156.63,'01/01/53')
,('lvlholder','BR23456','PC','',0,5,16.23,'07/05/23')
,('lvlholder','BR23456','PC','',0,5,16.23,'11/30/2023')
,('lvlholder','BR23456','PC','',0,8,14.82,'07/05/23')
,('lvlholder','BR23456','PC','',0,8,14.82,'11/30/2023')
,('lvlholder','BR23456','PC','',0,1,23.51,'07/05/23')
,('lvlholder','BR23456','PC','',0,1,23.51,'11/30/2023')
,('lvlholder','BR23456','PC','',0,3,17.99,'07/05/23')
,('lvlholder','BR23456','PC','',0,3,17.99,'11/30/2023')
,('lvlholder','BR23456','PC','',0,4,17.09,'07/05/23')
,('lvlholder','BR23456','PC','',0,4,17.09,'11/30/2023')
,('lvlholder','BR23456','PC','',0,3,18.89,'01/01/24')
,('lvlholder','BR23456','PC','',0,4,17.94,'01/01/24')
,('lvlholder','BR23456','PC','',0,5,17.04,'01/01/24')
,('lvlholder','BR23456','PC','',0,6,16.54,'01/01/24')
,('lvlholder','BR23456','PC','',0,7,16.04,'01/01/24')
,('lvlholder','BR23456','PC','',0,8,15.56,'01/01/24')--------
,('lvlholder','BR23456','PC','',0,2,19.99,'07/05/23')
,('lvlholder','BR23456','PC','',0,2,19.99,'11/30/2023')
,('lvlholder','BR23456','PC','',0,7,15.28,'07/05/23')
,('lvlholder','BR23456','PC','',0,7,15.28,'11/30/2023')
,('lvlholder','BR23456','PC','',0,1,24.69,'01/01/24')
,('lvlholder','BR23456','PC','',0,2,20.99,'01/01/24')
,('lvlholder','BR23456','PC','',0,6,15.75,'07/05/23')
,('lvlholder','BR23456','PC','',0,6,15.75,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,1,24.38,'01/01/24')
,('lvlholder','CX23456','PNL','',0,2,19.74,'07/05/23')
,('lvlholder','CX23456','PNL','',0,2,19.74,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,5,16.03,'07/05/23')
,('lvlholder','CX23456','PNL','',0,5,16.03,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,7,15.09,'07/05/23')
,('lvlholder','CX23456','PNL','',0,7,15.09,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,2,20.73,'01/01/24')
,('lvlholder','CX23456','PNL','',0,3,18.66,'01/01/24')
,('lvlholder','CX23456','PNL','',0,4,17.72,'01/01/24')
,('lvlholder','CX23456','PNL','',0,5,16.83,'01/01/24')
,('lvlholder','CX23456','PNL','',0,6,16.33,'01/01/24')
,('lvlholder','CX23456','PNL','',0,7,15.84,'01/01/24')
,('lvlholder','CX23456','PNL','',0,8,15.36,'01/01/24')------
,('lvlholder','CX23456','PNL','',0,8,14.63,'07/05/23')
,('lvlholder','CX23456','PNL','',0,8,14.63,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,1,23.22,'07/05/23')
,('lvlholder','CX23456','PNL','',0,1,23.22,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,3,17.77,'07/05/23')
,('lvlholder','CX23456','PNL','',0,3,17.77,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,4,16.88,'07/05/23')
,('lvlholder','CX23456','PNL','',0,4,16.88,'11/30/2023')
,('lvlholder','CX23456','PNL','',0,6,15.55,'07/05/23')
,('lvlholder','CX23456','PNL','',0,6,15.55,'11/30/2023')
;

I had to include all the data for these 4 items to show it has to choose the latest date for the ones that involve the first 2 options not being the correct price. There are lots of older prices being stored for each item.

SELECT [itemcode],[uom],[trtype], [alvl],[blvl],[start_date],
  CASE 
    WHEN [trtype] <> '' THEN [price] 
    WHEN  [alvl] > 0 THEN XXXXXX
  ELSE 0 
  END AS LevelResult 
FROM [test].[testtable].[pricing] 
WHERE [start_cust_or_group] ='test'  
   and [RowDeleted] = 0
   and [item_or_pricecode] in ('ABC1234','X123456','BR23456','CX23456')
ORDER BY [start_date] desc

Tried a select where I put XXXXXXX but not sure how to pass the [alvl] to look for matching [blvl] where customer is lvlholder

  • sql
  • sql-server

New contributor

Siege is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

3

You can use a OUTER APPLY (SELECT TOP 1 ...) pattern to handle price lookups for the second and third cases. With some NULLIF()/COALESCE() trickery, those two lookup cases can be combined.

Since you did not provide a data source for the default customer level, I have just defined a hardcoded value below.

DECLARE @DefaultCustomerLevel INT = 8 -- This should come from a customer table

SELECT
    P.itemcode, P.uom, P.price,
    CASE P.trtype
        WHEN 'Quot' THEN P.start_date
        WHEN '' THEN PLookup.start_date
        END AS start_date,
    CASE P.trtype
        WHEN 'Quot' THEN P.price
        WHEN '' THEN PLookup.price
        END AS FinalResult
FROM pricing P
OUTER APPLY (
    SELECT TOP 1 *
    FROM pricing P1
    WHERE P.trtype = ''
    AND P1.customer = 'lvlholder'
    AND P1.itemcode = P.itemcode  -- Maybe
    AND P1.Blvl = COALESCE(NULLIF(P.Alvl, 0),  @DefaultCustomerLevel)
    --AND P1.start_date <= P.start_date  -- Maybe
    ORDER BY P1.start_date DESC
) PLookup
WHERE P.Customer = 'test'

You did not specify whether or not itemcode must be a match in the lookup logic, but I included that condition. Also, when looking up some latest value, that lookup is often relative to some reference date. If you want the latest price at or prior to the original row start_date, you can uncomment that condition above.

The P.trtype = '' condition is included in the lookup logic to exclude unnecessary lookups when the result is not needed.

Results:

itemcode uom price start_date FinalResult
ABC1234 EA 0 2024-01-15 29.57
X123456 CT 144.1 2024-05-15 144.1
BR23456 PC 0 2024-01-01 15.56
CX23456 PNL 0 2024-01-01 15.36

See this db<>fiddle for a demo.

I think sub queries within the CASE THEN parts will get the info you want

SELECT p.[itemcode],
       p.[uom],
       p.[trtype], 
       p.[alvl],
       p.[blvl],
       CASE WHEN  p.[alvl]>0  THEN (SELECT top 1 x.start_date FROM pricing x WHERE x.itemcode=p.itemcode AND x.blvl=p.alvl AND customer = 'lvlholder' ORDER BY start_date desc)
       WHEN p.[trtype] = '' THEN (SELECT top 1 x.start_date FROM pricing x WHERE x.itemcode=p.itemcode AND x.blvl=8 AND customer = 'lvlholder' ORDER BY start_date desc)
      ELSE p.[start_date] END as start_date,
  CASE 
  WHEN p.[trtype] = 'Quot' THEN p.[price]
  WHEN p.[alvl]>0  THEN (SELECT top 1 x.price FROM pricing x WHERE x.itemcode=p.itemcode AND x.blvl=p.alvl AND customer = 'lvlholder' ORDER BY start_date desc)
  WHEN p.[trtype] = '' THEN (SELECT top 1 x.price FROM pricing x WHERE x.itemcode=p.itemcode AND x.blvl=8 AND customer = 'lvlholder' ORDER BY start_date desc)
  ELSE 0
  END AS LevelResult ,
  p.price
FROM pricing p
WHERE p.[Customer] ='test'  
   --and [RowDeleted] = 0
   and p.[itemcode] in ('ABC1234','X123456','BR23456','CX23456')
ORDER BY p.[start_date] desc

fiddle

itemcode uom trtype alvl blvl start_date LevelResult price
ABC1234 EA 6 0 2024-01-15 29.57 0
X123456 CT Quot 0 0 2024-05-15 144.1 144.1
BR23456 PC 0 0 2024-01-01 15.56 0
CX23456 PNL 0 0 2024-01-01 15.36 0

5

This solution uses joins instead of CASE THEN subqueries

WITH a as
(
  --This query returns all of the lvlholder records. 
  --Each blvl/itemcode combination is provided with a row numbers starting with the most recent.
  SELECT row_number() OVER (Partition by blvl,itemcode ORDER BY start_date desc) as rn,x.price,start_date,itemcode,blvl FROM pricing x WHERE  x.customer = 'lvlholder'
), t as
(
  --This query returns all of the lvlholder records where blvl=8.
  --Each itemcode is provided with a row number starting with the most recent.
  SELECT row_number() OVER (Partition by itemcode ORDER BY start_date desc) as rn, x.price,start_date,itemcode FROM pricing x WHERE x.blvl=8 AND customer = 'lvlholder'
)
SELECT p.[itemcode],
       p.[uom],
       p.[trtype], 
       p.[alvl],
       p.[blvl],
       CASE WHEN  p.[alvl]>0  THEN a.start_date
       WHEN p.[trtype] = '' THEN t.start_date
      ELSE p.[start_date] END as start_date,
  CASE 
  WHEN p.[trtype] = 'Quot' THEN p.[price]
  WHEN p.[alvl]>0  THEN a.price
  WHEN p.[trtype] = '' THEN t.price
  ELSE 0
  END AS LevelResult ,
  p.price
FROM pricing p
LEFT JOIN a ON a.itemcode=p.itemcode AND a.rn=1 AND a.blvl=p.alvl
LEFT JOIN t ON t.itemcode=p.itemcode AND t.rn=1
WHERE AND p.[Customer] ='test'  
      AND p.[itemcode] in ('ABC1234','X123456','BR23456','CX23456')
ORDER BY p.[start_date] desc

fiddle

Here is the query that fulfils your all three requirements, you can test it for values other than testcases it still works :

with cte as 
(
    select Blvl, max(start_date) as changed_date, Customer, itemcode
    from [pricing] 
    where Customer = 'lvlholder'
    group by Customer, itemcode, Blvl
)
select itemcode, uom, price, start_date, price as FinalResult
from [pricing] 
where [trtype] = 'Quot'

union

select a.itemcode, uom, price, changed_date as start_date, FinalResult
from [pricing] as a
left join cte on a.Alvl = cte.Blvl and a.itemcode = cte.itemcode
left join (select price as FinalResult, start_date, Blvl from [pricing]) as b
    on b.start_date = cte.changed_date and b.Blvl = cte.Blvl
where Alvl > 0

union

select a.itemcode, uom, price, changed_date as start_date, FinalResult
from [pricing] as a  
left join cte on a.itemcode = cte.itemcode 
left join (
    select price as FinalResult, start_date as new_date, itemcode from [pricing]
    where Blvl = 8 and Customer = 'lvlholder'
) as b
    on b.new_date = cte.changed_date and b.itemcode= a.itemcode
where a.Alvl = 0 and  a.trtype = '' and cte.Blvl = 8

New contributor

Saurabh Bhalerao is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

2

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị

Filed under: Kiến thức lập trình - @ 14:38

Thẻ:

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật