Skip to main content
  1. About
  3. For Teams
Asked
Modified 8 months ago
Viewed 97 times
0

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

from highest to lowest

  1. if "tran_type" is Quot than choose this price over all from the [price] column and put it as "LevelResult"
  2. if there is no "Quot" in the "tran_type" then look for this: if "default_level" is greater than 0 then choose this price.(so if "default_level" is 5 then choose level_ = 5 and "start_cust_or_group = '' "and newest date "(start_date)" for that price.
  3. last if both "tran_type" is blank and "default_level" is 0 then choose the default level for this customer which for this case the default customer level is 5, so looking for level_ = 5 and customer = '' reason why is below.

All the "level prices" (level_) from 1-8 are stored in a blank customer but have multiple price records so I need to pull the newest date for those. level_ is the level for the prices 1-8 stored in this blank 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 ('7001364') to look at to see if "tran_type" is 'Quote' if not then next check if "default_level" has a value greater than 0 if not then choose the Customer default which in this customers case is 6.

My problem is with the query I currently have is it runs for every instance of the item code, I need it to search the hierarchy and choose the one that is correct so I only get 1 result per item code.

Below is what I get with the current query

item_or_pricecode uom_code price start_date LevelResult
37rgtf08ab PC 11.00 2024-11-15 11.00
37rgtf08ab PC 11.00 2025-01-01 11.00
37rgtf08ab PC 11.00 2025-01-01 11.00
37rgtf08ab PC 11.00 2024-11-15 11.00
37rgtf08ab PC 11.00 2024-11-15 11.00
37rgtf08ab PC 11.00 2024-11-15 11.00
37rgtf08ab PC 11.00 2025-01-01 11.00
37rgtf08ab PC 11.00 2025-01-01 11.00
22rgr45dhs CT 0.00 2025-01-02 170.73
37rgtf08ab PC 0.00 2025-01-02 14.01
35rg4pcpab PC 15.50 2025-01-02 21.41
35rg4pcpab PC 15.50 2025-01-02 21.41
37rgdf10bs PC 0.00 2025-01-01 16.27
22rgr45dhs CT 156.63 2024-12-31 156.63
27miete11k EA 0.00 2024-01-15 16.86
35rg4pcpab PC 15.50 2024-02-31 15.50

below is the query

 DECLARE @DefaultCustomerLevel INT = 5
 SELECT
 I.[item_or_pricecode],
 COALESCE(PLookup.[uom_code], P.[uom_code]) AS [uom_code],
 ISNULL(P.[price], 0) AS [price],
 COALESCE(PLookup.start_date, P.start_date) AS start_date,
 COALESCE(PLookup.[price], P.[price]) AS LevelResult
 FROM (
 VALUES ('22rgr45dhs'),('35rg4pcpab'),('37rgtf08ab'),('37RGDF10BS'),('27MIETE11K')) 
 I([item_or_pricecode])
 LEFT JOIN [P2S_DMSI].[Agility].[pricing] P
 ON P.[start_cust_or_group] = '7001364' AND [cust_shipto_num] = '2' AND P.[item_or_pricecode] 
 = I.[item_or_pricecode]

 OUTER APPLY (
 SELECT TOP 1 * FROM [P2S_DMSI].[Agility].[pricing] P1 WHERE ISNULL(P.[tran_type], '') = '' 
 AND P1.[start_cust_or_group] = ''  AND P1.[item_or_pricecode] = I.[item_or_pricecode] AND 
 P1.[level_] = COALESCE(NULLIF(P.[default_level], 0),  @DefaultCustomerLevel)

  ORDER BY [start_date] desc
   ) PLookup

Above is the query that someone made for me but the problem is it shows all the instances in the hierarchy, I need it to go down the hierarchy list and when it find the highest match for the item in values only display that one.

Below is what I would like it to output

item_or_pricecode uom_code price start_date LevelResult
22rgr45dhs CT 156.63 2024-12-31 156.63
35rg4pcpab PC 15.50 2024-12-31 15.50
37rgtf08ab PC 11.00 2024-11-15 11.00
37rgdf10bs PC 16.27 2025-01-02 16.27
27miete11k EA 30.49 2024-01-15 30.49

below is the data

start_cust_or_group item_or_pricecode uom_code tran_type default_level level_ price start_date
7001364 22RGR45DHS 8 0 0 2024/01/22
7001364 22RGR45DHS CT Quot 0 0 156.63 2024/12/31
7001364 35RG4PCPAB PC 0 0 15.5 2024/01/22
7001364 35RG4PCPAB PC 0 0 15.5 2024/01/22
7001364 35RG4PCPAB PC Quot 0 0 15.5 2024/12/31
7001364 37RGTF08AB 8 0 0 2024/01/22
7001364 37RGTF08AB PC Quot 0 0 11 2024/11/15
7001364 37RGTF08AB PC Quot 0 0 11 2024/11/15
7001364 37RGTF08AB PC Quot 0 0 11 2024/11/15
7001364 37RGTF08AB PC Quot 0 0 11 2025/01/01
7001364 37RGTF08AB PC Quot 0 0 11 2025/01/01
7001364 37RGDF10BS 8 0 0 2024/01/22
27MIETE11K EA 0 1 62.36 2023/07/11
27MIETE11K EA 0 2 49.89 2023/07/11
27MIETE11K EA 0 3 44.9 2023/07/11
27MIETE11K EA 0 4 42.66 2023/07/11
27MIETE11K EA 0 5 41.38 2023/07/11
27MIETE11K EA 0 6 40.13 2023/07/11
27MIETE11K EA 0 7 37.24 2023/07/11
27MIETE11K EA 0 8 36.5 2023/07/11
37RGTF08AB CT 0 1 463.2 2023/07/12
37RGTF08AB CT 0 2 370.6 2023/07/12
37RGTF08AB CT 0 3 315 2023/07/12
37RGTF08AB CT 0 4 283.4 2023/07/12
37RGTF08AB CT 0 5 269.2 2023/07/12
37RGTF08AB CT 0 6 261.2 2023/07/12
37RGTF08AB CT 0 7 253.4 2023/07/12
37RGTF08AB CT 0 8 245.8 2023/07/12
37RGTF08AB PC 0 1 23.16 2023/07/11
37RGTF08AB PC 0 2 18.53 2023/07/11
37RGTF08AB PC 0 3 15.75 2023/07/11
37RGTF08AB PC 0 4 14.17 2023/07/11
37RGTF08AB PC 0 5 13.46 2023/07/11
37RGTF08AB PC 0 6 13.06 2023/07/11
37RGTF08AB PC 0 7 12.67 2023/07/11
37RGTF08AB PC 0 8 12.29 2023/07/11
22RGR45DHS PNL 0 3 9.16 2024/01/01
35RG4PCPAB PC 0 3 35 2024/01/01
22RGR45DHS PNL 0 6 8.01 2024/01/01
35RG4PCPAB PC 0 6 18.78 2024/01/01
22RGR45DHS PNL 0 2 11.44 2023/07/08
35RG4PCPAB PC 0 2 40 2023/06/15
22RGR45DHS CT 0 1 314.82 2023/11/30
35RG4PCPAB PC 0 1 47.84 2023/11/30
27MIETE11K EA 0 1 62.36 2023/11/30
22RGR45DHS CT 0 4 191.4 2023/11/30
35RG4PCPAB PC 0 4 24 2023/11/30
27MIETE11K EA 0 6 40.13 2023/11/30
22RGR45DHS CT 0 1 314.82 2023/11/30
35RG4PCPAB PC 0 1 47.84 2023/11/30
27MIETE11K EA 0 1 62.36 2023/11/30
22RGR45DHS CT 0 4 191.4 2023/11/30
35RG4PCPAB PC 0 4 24 2023/11/30
27MIETE11K EA 0 4 42.66 2023/11/30
35RG4PCPAB PC 0 2 40 2023/06/01
37RGTF08AB PC 0 2 0.0001 2023/06/01
37RGTF08AB PC 0 4 14.17 2024/01/01
35RG4PCPAB PC 0 4 19.42 2024/01/01
37RGTF08AB PC 0 5 13.46 2024/01/01
35RG4PCPAB PC 0 5 18.78 2024/01/01
37RGTF08AB PC 0 6 13.06 2024/01/01
35RG4PCPAB PC 0 6 17.66 2024/01/01
37RGTF08AB PC 0 7 12.55 2024/01/01
27MIETE11K EA 0 1 62.36 2023/07/11
27MIETE11K EA 0 2 49.89 2023/07/11
27MIETE11K EA 0 3 44.9 2023/07/11
27MIETE11K EA 0 4 42.66 2023/07/11
27MIETE11K EA 0 5 41.38 2023/07/11
27MIETE11K EA 0 6 40.13 2023/07/11
27MIETE11K EA 0 7 37.24 2023/07/11
27MIETE11K EA 0 8 36.5 2023/07/11
27MIETE11K EA 0 1 38.65 2024/01/15
22RGR45DHS CT 0 1 251.68 2024/01/15
37RGTF08AB PC 0 1 23.16 2024/01/15
35RG4PCPAB PC 0 1 26.6 2024/01/15
27MIETE11K EA 0 4 17.39 2024/01/15
22RGR45DHS CT 0 4 181.72 2024/01/15
37RGTF08AB PC 0 4 14.17 2024/01/15
35RG4PCPAB PC 0 4 19.42 2024/01/15
27MIETE11K EA 0 7 16.03 2024/01/15
22RGR45DHS CT 0 7 165.88 2024/01/15
37RGTF08AB PC 0 7 12.55 2024/01/15
35RG4PCPAB PC 0 7 16.61 2024/01/15
27MIETE11K EA 0 3 33.05 2024/01/15
22RGR45DHS CT 0 3 191.4 2024/01/15
37RGTF08AB PC 0 3 15.75 2024/01/15
35RG4PCPAB PC 0 3 24 2024/01/15
27MIETE11K EA 0 6 29.57 2024/01/15
22RGR45DHS CT 0 6 170.94 2024/01/15
37RGTF08AB PC 0 6 13.06 2024/01/15
35RG4PCPAB PC 0 6 17.66 2024/01/15
22RGR45DHS CT 0 1 314.82 2023/07/07
22RGR45DHS PNL 0 1 14.31 2023/07/08
35RG4PCPAB PC 0 2 40 2023/06/15
35RG4PCPAB PC 0 2 40 2023/11/30
37RGTF08AB CT 0 2 370.6 2023/07/12
37RGTF08AB PC 0 2 18.53 2023/07/11
27MIETE11K EA 0 4 42.66 2023/07/11
22RGR45DHS PNL 0 1 14.31 2023/07/08
22RGR45DHS PNL 0 2 11.44 2023/07/08
22RGR45DHS PNL 0 3 9.16 2023/07/08
22RGR45DHS PNL 0 4 8.7 2023/07/08
22RGR45DHS PNL 0 5 8.26 2023/07/08
22RGR45DHS PNL 0 6 8.01 2023/07/08
22RGR45DHS PNL 0 7 7.77 2023/07/08
22RGR45DHS PNL 0 8 7.54 2023/07/08
22RGR45DHS CT 0 1 314.82 2023/07/07
22RGR45DHS CT 0 2 251.68 2023/07/07
22RGR45DHS CT 0 3 201.52 2023/07/07
22RGR45DHS CT 0 4 191.4 2023/07/07
22RGR45DHS CT 0 5 181.72 2023/07/07
22RGR45DHS CT 0 6 176.22 2023/07/07
22RGR45DHS CT 0 7 170.94 2023/07/07
22RGR45DHS CT 0 8 165.88 2023/07/07
27MIETE11K EA 0 8 36.5 2023/07/11
27MIETE11K EA 0 7 37.24 2023/07/11
27MIETE11K EA 0 6 40.13 2023/07/11
27MIETE11K EA 0 5 41.38 2023/07/11
27MIETE11K EA 0 4 42.66 2023/07/11
27MIETE11K EA 0 3 44.9 2023/07/11
27MIETE11K EA 0 2 49.89 2023/07/11
27MIETE11K EA 0 1 62.36 2023/07/11
22RGR45DHS PNL 0 1 14.31 2024/01/01
35RG4PCPAB PC 0 1 47.84 2024/01/01
22RGR45DHS PNL 0 4 8.7 2024/01/01
35RG4PCPAB PC 0 4 24 2024/01/01
22RGR45DHS PNL 0 3 9.16 2023/07/08
35RG4PCPAB PC 0 3 35 2023/06/15
22RGR45DHS PNL 0 6 8.01 2023/07/08
35RG4PCPAB PC 0 6 18.78 2023/06/15
22RGR45DHS CT 0 3 201.52 2023/11/30
35RG4PCPAB PC 0 3 35 2023/11/30
27MIETE11K EA 0 3 44.9 2023/11/30
22RGR45DHS CT 0 6 176.22 2023/11/30
35RG4PCPAB PC 0 6 18.78 2023/11/30
22RGR45DHS CT 0 3 201.52 2023/11/30
35RG4PCPAB PC 0 3 35 2023/11/30
27MIETE11K EA 0 3 44.9 2023/11/30
27MIETE11K EA 0 1 62.36 2023/06/01
22RGR45DHS PNL 0 2 0.0001 2023/06/01
27MIETE11K EA 0 3 44.9 2023/06/01
22RGR45DHS CT 0 1 251.68 2024/01/01
22RGR45DHS CT 0 2 201.52 2024/01/01
22RGR45DHS CT 0 3 191.4 2024/01/01
22RGR45DHS CT 0 4 181.72 2024/01/01
35RG4PCPAB PC 0 7 16.61 2024/01/01
37RGTF08AB PC 0 8 12.29 2024/01/01
35RG4PCPAB PC 0 8 15.98 2024/01/01
22RGR45DHS CT 0 3 191.4 2024/01/15
37RGTF08AB PC 0 3 15.75 2024/01/15
35RG4PCPAB PC 0 3 24 2024/01/15
22RGR45DHS CT 0 6 170.94 2024/01/15
37RGTF08AB PC 0 6 13.06 2024/01/15
35RG4PCPAB PC 0 6 17.66 2024/01/15
22RGR45DHS CT 0 2 201.52 2024/01/15
37RGTF08AB PC 0 2 18.53 2024/01/15
35RG4PCPAB PC 0 2 25.27 2024/01/15
27MIETE11K EA 0 5 30.49 2024/01/15
22RGR45DHS CT 0 5 176.22 2024/01/15
37RGTF08AB PC 0 5 13.46 2024/01/15
35RG4PCPAB PC 0 5 18.78 2024/01/15
27MIETE11K EA 0 8 28.39 2024/01/15
22RGR45DHS CT 0 8 156.63 2024/01/15
37RGTF08AB PC 0 8 12.29 2024/01/15
35RG4PCPAB PC 0 8 15.98 2024/01/15
35RG4PCPAB PC 0 1 47.84 2023/06/15
35RG4PCPAB PC 0 1 47.84 2023/11/30
37RGTF08AB CT 0 1 463.2 2023/07/12
37RGTF08AB PC 0 1 23.16 2023/07/11
35RG4PCPAB PC 0 3 35 2023/06/15
35RG4PCPAB PC 0 3 35 2023/11/30
37RGTF08AB CT 0 3 315 2023/07/12
37RGTF08AB PC 0 3 15.75 2023/07/11
27MIETE11K EA 0 5 41.38 2023/07/11
27MIETE11K EA 0 5 41.38 2023/11/30
22RGR45DHS CT 0 7 170.94 2023/07/07
22RGR45DHS PNL 0 7 7.77 2023/07/08
27MIETE11K EA 0 7 37.24 2023/07/11
27MIETE11K EA 0 7 37.24 2023/11/30
22RGR45DHS CT 0 1 274.33 2025/01/02
37RGTF08AB PC 0 2 21.12 2025/01/02
22RGR45DHS CT 0 3 208.63 2025/01/02
35RG4PCPAB PC 0 3 27.36 2025/01/02
37RGTF08AB PC 0 3 17.96 2025/01/02
27MIETE11K EA 0 4 17.39 2024/01/01
27MIETE11K EA 0 5 16.86 2024/01/01
27MIETE11K EA 0 6 16.36 2024/01/01
27MIETE11K EA 0 7 16.03 2024/01/01
27MIETE11K EA 0 8 15.71 2024/01/01
22RGR45DHS PNL 0 7 7.77 2024/01/01
35RG4PCPAB PC 0 7 17.66 2024/01/01
22RGR45DHS PNL 0 1 14.31 2023/07/08
35RG4PCPAB PC 0 1 47.84 2023/06/15
22RGR45DHS PNL 0 5 8.26 2023/07/08
35RG4PCPAB PC 0 5 19.42 2023/06/15
22RGR45DHS PNL 0 7 7.77 2023/07/08
35RG4PCPAB PC 0 7 17.66 2023/06/15
27MIETE11K EA 0 2 49.89 2023/11/30
22RGR45DHS CT 0 5 181.72 2023/11/30
35RG4PCPAB PC 0 5 19.42 2023/11/30
27MIETE11K EA 0 5 41.38 2023/11/30
22RGR45DHS CT 0 8 165.88 2023/11/30
35RG4PCPAB PC 0 8 16.61 2023/11/30
27MIETE11K EA 0 8 36.5 2023/11/30
35RG4PCPAB PC 0 1 47.84 2023/06/01
37RGTF08AB PC 0 1 23.16 2023/06/01
35RG4PCPAB PC 0 3 35 2023/06/01
37RGTF08AB PC 0 3 0.0394 2023/06/01
22RGR45DHS CT 0 8 156.63 2024/01/01
37RGTF08AB PC 0 1 23.16 2024/01/01
35RG4PCPAB PC 0 1 26.6 2024/01/01
37RGTF08AB PC 0 2 18.53 2024/01/01
35RG4PCPAB PC 0 2 25.27 2024/01/01
37RGTF08AB PC 0 3 15.75 2024/01/01
35RG4PCPAB PC 0 3 24 2024/01/01
37RGTF08AB PC 0 2 18.53 2024/01/15
35RG4PCPAB PC 0 2 25.27 2024/01/15
27MIETE11K EA 0 3 18.3 2024/01/15
37RGTF08AB PC 0 5 13.46 2024/01/15
35RG4PCPAB PC 0 5 18.78 2024/01/15
27MIETE11K EA 0 6 16.36 2024/01/15
22RGR45DHS CT 0 8 156.63 2024/01/15
37RGTF08AB PC 0 8 12.29 2024/01/15
35RG4PCPAB PC 0 8 15.98 2024/01/15
27MIETE11K EA 0 2 34.81 2024/01/15
22RGR45DHS CT 0 4 181.72 2024/01/15
37RGTF08AB PC 0 4 14.17 2024/01/15
35RG4PCPAB PC 0 4 19.42 2024/01/15
22RGR45DHS CT 0 7 165.88 2024/01/15
37RGTF08AB PC 0 7 12.55 2024/01/15
35RG4PCPAB PC 0 7 16.61 2024/01/15
22RGR45DHS CT 0 2 251.68 2023/07/07
22RGR45DHS PNL 0 2 11.44 2023/07/08
27MIETE11K EA 0 2 49.89 2023/07/11
27MIETE11K EA 0 2 49.89 2023/11/30
22RGR45DHS CT 0 4 191.4 2023/07/07
22RGR45DHS PNL 0 4 8.7 2023/07/08
35RG4PCPAB PC 0 5 19.42 2023/11/30
35RG4PCPAB PC 0 5 19.42 2023/06/15
37RGTF08AB CT 0 5 269.2 2023/07/12
37RGTF08AB PC 0 5 13.46 2023/07/11
35RG4PCPAB PC 0 7 17.66 2023/06/15
35RG4PCPAB PC 0 7 17.66 2023/11/30
37RGTF08AB CT 0 7 253.4 2023/07/12
37RGTF08AB PC 0 7 12.67 2023/07/11
37RGTF08AB CT 0 8 245.8 2023/07/12
37RGTF08AB PC 0 8 12.29 2023/07/11
35RG4PCPAB PC 0 4 22.14 2025/01/02
37RGTF08AB PC 0 4 16.15 2025/01/02
22RGR45DHS CT 0 5 192.08 2025/01/02
35RG4PCPAB PC 0 5 21.41 2025/01/02
37RGTF08AB PC 0 5 15.34 2025/01/02
35RG4PCPAB PC 0 2 25.27 2024/01/15
37RGTF08AB PC 0 2 18.53 2024/01/15
22RGR45DHS CT 0 3 191.4 2024/01/15
35RG4PCPAB PC 0 3 24 2024/01/15
37RGTF08AB PC 0 3 15.75 2024/01/15
35RG4PCPAB PC 0 8 15.98 2024/01/15
37RGTF08AB PC 0 8 12.29 2024/01/15
22RGR45DHS CT 0 4 198.07 2025/01/02
35RG4PCPAB PC 0 5 18.78 2024/01/15
37RGTF08AB PC 0 5 13.46 2024/01/15
22RGR45DHS CT 0 6 170.94 2024/01/15
35RG4PCPAB PC 0 6 17.66 2024/01/15
37RGTF08AB PC 0 6 13.06 2024/01/15
27MIETE11K EA 0 4 42.66 2023/11/30
22RGR45DHS CT 0 6 176.22 2023/07/07
22RGR45DHS PNL 0 6 8.01 2023/07/08
27MIETE11K EA 0 6 40.13 2023/07/11
27MIETE11K EA 0 6 40.13 2023/11/30
22RGR45DHS CT 0 8 165.88 2023/07/07
22RGR45DHS PNL 0 8 7.54 2023/07/08
35RG4PCPAB PC 0 1 30.32 2025/01/02
37RGTF08AB PC 0 1 26.4 2025/01/02
22RGR45DHS CT 0 2 219.66 2025/01/02
35RG4PCPAB PC 0 2 28.81 2025/01/02
37RGTF08AB PC 0 7 14.31 2025/01/02
22RGR45DHS CT 0 8 170.73 2025/01/02
35RG4PCPAB PC 0 8 18.22 2025/01/02
37RGTF08AB PC 0 8 14.01 2025/01/02
22RGR45DHS CT 0 1 251.68 2024/01/15
35RG4PCPAB PC 0 1 26.6 2024/01/15
37RGTF08AB PC 0 1 23.16 2024/01/15
22RGR45DHS CT 0 2 201.52 2024/01/15
22RGR45DHS CT 0 7 165.88 2024/01/15
35RG4PCPAB PC 0 7 16.61 2024/01/15
37RGTF08AB PC 0 7 12.55 2024/01/15
22RGR45DHS CT 0 8 156.63 2024/01/15
35RG4PCPAB PC 0 1 47.84 2023/06/15
35RG4PCPAB PC 0 2 40 2023/06/15
35RG4PCPAB PC 0 3 35 2023/06/15
35RG4PCPAB PC 0 4 24 2023/06/15
35RG4PCPAB PC 0 5 19.42 2023/06/15
35RG4PCPAB PC 0 6 18.78 2023/06/15
35RG4PCPAB PC 0 7 17.66 2023/06/15
35RG4PCPAB PC 0 8 16.61 2023/06/15
27MIETE11K EA 0 1 21.40531189 2024/01/01
27MIETE11K EA 0 2 19.26 2024/01/01
27MIETE11K EA 0 3 18.3 2024/01/01
22RGR45DHS PNL 0 2 11.44 2024/01/01
35RG4PCPAB PC 0 2 40 2024/01/01
22RGR45DHS PNL 0 5 8.26 2024/01/01
35RG4PCPAB PC 0 5 19.42 2024/01/01
22RGR45DHS PNL 0 8 7.54 2024/01/01
35RG4PCPAB PC 0 8 16.61 2024/01/01
22RGR45DHS PNL 0 4 8.7 2023/07/08
35RG4PCPAB PC 0 4 24 2023/06/15
22RGR45DHS PNL 0 8 7.54 2023/07/08
35RG4PCPAB PC 0 8 16.61 2023/06/15
22RGR45DHS CT 0 2 251.68 2023/11/30
35RG4PCPAB PC 0 2 40 2023/11/30
27MIETE11K EA 0 4 42.66 2023/11/30
22RGR45DHS CT 0 7 170.94 2023/11/30
35RG4PCPAB PC 0 7 17.66 2023/11/30
27MIETE11K EA 0 7 37.24 2023/11/30
22RGR45DHS CT 0 2 251.68 2023/11/30
35RG4PCPAB PC 0 2 40 2023/11/30
27MIETE11K EA 0 2 49.89 2023/11/30
22RGR45DHS PNL 0 1 14.31 2023/06/01
27MIETE11K EA 0 2 49.89 2023/06/01
22RGR45DHS PNL 0 3 0.0189 2023/06/01
22RGR45DHS CT 0 5 176.22 2024/01/01
22RGR45DHS CT 0 6 170.94 2024/01/01
22RGR45DHS CT 0 7 165.88 2024/01/01
35RG4PCPAB PC 0 1 47.84 2023/06/15
35RG4PCPAB PC 0 2 40 2023/06/15
35RG4PCPAB PC 0 3 35 2023/06/15
35RG4PCPAB PC 0 4 24 2023/06/15
35RG4PCPAB PC 0 5 19.42 2023/06/15
35RG4PCPAB PC 0 6 18.78 2023/06/15
35RG4PCPAB PC 0 7 17.66 2023/06/15
35RG4PCPAB PC 0 8 16.61 2023/06/15
27MIETE11K EA 0 2 19.26 2024/01/15
22RGR45DHS CT 0 2 201.52 2024/01/15
27MIETE11K EA 0 5 16.86 2024/01/15
22RGR45DHS CT 0 5 176.22 2024/01/15
27MIETE11K EA 0 8 15.71 2024/01/15
27MIETE11K EA 0 4 31.43 2024/01/15
27MIETE11K EA 0 7 28.97 2024/01/15
27MIETE11K EA 0 1 62.36 2023/07/11
27MIETE11K EA 0 1 62.36 2023/11/30
22RGR45DHS CT 0 3 201.52 2023/07/07
22RGR45DHS PNL 0 3 9.16 2023/07/08
27MIETE11K EA 0 3 44.9 2023/07/11
27MIETE11K EA 0 3 44.9 2023/11/30
35RG4PCPAB PC 0 4 24 2023/06/15
35RG4PCPAB PC 0 4 24 2023/11/30
37RGTF08AB CT 0 4 283.4 2023/07/12
37RGTF08AB PC 0 4 14.17 2023/07/11
22RGR45DHS CT 0 5 181.72 2023/07/07
22RGR45DHS PNL 0 5 8.26 2023/07/08
35RG4PCPAB PC 0 6 18.78 2023/06/15
35RG4PCPAB PC 0 6 18.78 2023/11/30
37RGTF08AB CT 0 6 261.2 2023/07/12
37RGTF08AB PC 0 6 13.06 2023/07/11
27MIETE11K EA 0 8 36.5 2023/07/11
27MIETE11K EA 0 8 36.5 2023/11/30
35RG4PCPAB PC 0 8 16.61 2023/06/15
35RG4PCPAB PC 0 8 16.61 2023/11/30
22RGR45DHS CT 0 6 186.32 2025/01/02
35RG4PCPAB PC 0 6 20.13 2025/01/02
37RGTF08AB PC 0 6 14.89 2025/01/02
22RGR45DHS CT 0 7 180.81 2025/01/02
35RG4PCPAB PC 0 7 18.94 2025/01/02
22RGR45DHS CT 0 4 181.72 2024/01/15
35RG4PCPAB PC 0 4 19.42 2024/01/15
37RGTF08AB PC 0 4 14.17 2024/01/15
22RGR45DHS CT 0 5 176.22 2024/01/15
Share
Improve this question
3
  • The query you shared is not for PostgreSQL. What database are you using?
    Frank Heikens
    –  Frank Heikens
    2025-01-22 17:56:47 +00:00
    Commented Jan 22 at 17:56
  • 1
    Please reduce the data to the minimum that is required to understand the question.
    Jonas Metzler
    –  Jonas Metzler
    2025-01-22 18:28:00 +00:00
    Commented Jan 22 at 18:28
  • @Barmar can you edit it again sorry I need the 6 to be a 5 in the first paragraphs every time I try to edit anything on the page it says my code isn't formatted correctly so I can't submit the change
    Siege
    –  Siege
    2025-01-22 19:31:51 +00:00
    Commented Jan 22 at 19:31

1 Answer 1

Reset to default
0

You may calculate the price rating (the CASE expression) and use distinct on with custom order by to pick the desired price (the one with the highest rating).

select distinct on (item_or_pricecode)
 item_or_pricecode, uom_code, price, start_date, price "LevelResult" 
from the_table
order by
 item_or_pricecode,
 case
    when tran_type = 'Quot' then 3
    when default_level > 0 then 2
    when level_ = 5 and start_cust_or_group = '' then 1
    else 0
 end desc,
 start_date desc;

DB-Fiddle demo
Please note that this is a PostgreSQL query.

Share
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Siege
Siege
This doesn't work for the last when case if tran_type is blank and default_level is 0 then it has to look for the price where the customer is blank and the level_ is the same levels at the defsult level for the customer in this case which is 5 and it has to be the latest date of that price on the list of which there are mutiple.
Stefanov.sm
Stefanov.sm
Fine, you may write an union subquery to implement this requirement as it is quite different from the rest

Your Answer

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.

Morty Proxy This is a proxified and sanitized view of the page, visit original site.