asked on

How to find price if a field is a '1' else use the price from the field where it is a '0'

I am trying to find the price of items. If channel = '1' then that is the NET_PRICE, if not then select the minimum price where the channel is '0'. Only some items will have a channel = '1', but if they do they need to use that NET_PRICE. The way I have it set now this is showing 2 line items, but what I want it to show is just the 11.66 price line.

CORRECT ANSWER

NET_PRICE	part_code	cust_code	channel
11.66	AGA10010	450091	1

Open in new window

CURRENT OUTPUT - Which is wrong

NET_PRICE	part_code	cust_code	channel
9.33	AGA10010	450091	0
11.66	AGA10010	450091	1

Open in new window

SELECT DISTINCT
       MIN(   CASE
                  WHEN pricemtx.price_meth = 'C'
                       AND priceid.channel = '1' THEN
                      ROUND(((pricemtx.part_price * .01) * partmstr.cost_no6), 2)

                  WHEN pricemtx.price_meth = 'R'
                       AND priceid.channel = '1' THEN
                      pricemtx.part_price
                  WHEN pricemtx.price_meth = 'M'
                       AND priceid.channel = '1' THEN
                      ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
                  WHEN pricemtx.price_meth = 'B'
                       AND priceid.channel = '1'
                       AND pricemtx.disc_type = '%' THEN
                      ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
                  WHEN pricemtx.price_meth = 'B'
                       AND priceid.channel = '1'
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id = 'BASELIST' THEN
                      ROUND((partmstr.part_price - pricemtx.part_disc), 2)
                  WHEN pricemtx.price_meth = 'B'
                       AND priceid.channel = '1'
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id <> 'BASELIST' THEN
                      ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
                 
                  WHEN pricemtx.price_meth = 'R' AND (priceid.channel = '0' )  THEN
                      pricemtx.part_price
                  WHEN pricemtx.price_meth = 'M' AND (priceid.channel = '0' )  THEN
                      ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
                  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
                       AND pricemtx.disc_type = '%' THEN
                      ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
                  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id = 'BASELIST' THEN
                      ROUND((partmstr.part_price - pricemtx.part_disc), 2)
                  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id <> 'BASELIST' THEN
                      ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
              END
          ) AS 'NET_PRICE',
       partmstr.part_code,
       custprice.cust_code,
	   priceid.channel

FROM pricemtx
    INNER JOIN partmstr
        ON pricemtx.part_code = partmstr.part_code
    LEFT OUTER JOIN priceid
        ON pricemtx.price_id = priceid.price_id
    LEFT OUTER JOIN vendpart
        ON partmstr.part_code = vendpart.part_code
           AND partmstr.alt_part1 = vendpart.vend_code
    LEFT OUTER JOIN pricemtx AS pricemtx_1
        ON pricemtx.formula_id = pricemtx_1.price_id
           AND pricemtx.part_code = pricemtx_1.part_code
    LEFT OUTER JOIN custprice
        ON pricemtx.price_id = custprice.price_id
WHERE (custprice.cust_code = '450091')
      AND (pricemtx.end_date >= '10/01/18')
      AND (partmstr.avail = 'S')
      AND (partmstr.part_code = 'AGA10010')
      AND (partmstr.price_cat1 <> '100')
      AND ('NET_PRICE' > '0')
      AND (partmstr.part_code <> '1999')
GROUP BY partmstr.part_code,
         custprice.cust_code,
		 priceid.channel

ORDER BY partmstr.part_code,
         custprice.cust_code,
		 priceid.channel

Open in new window

Mark Wills

I think you need to take channel out....

But then, it would show 9.33 because for that customer it seems there is a choice of the 9 or the 11 for the given "WHERE" clause.

What is the relationship to customer as to whether (or not) the channel 1 or 0

I can see there is pricemtx.price_id = custprice.price_id - is there a date / period of being applicable ?

Might need to check some more customer oriented information as part of that big "case" - maybe pricemtx.end_date

Might be interesting to see that in your group by, and selected columns (overlapping dates maybe ?)

t

Aaron Tomosky

You could add a column in the output for channel, then select top 1 from that order by Channel descending

With whatever as (your current thing)
Select top 1 net price, partcode from whatever order by channel desc

ASKER CERTIFIED SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Good suggestions.... I am still thinking that there is something missing and cant stop thinking about that end date.

There is definitely something else we are currently not seeing which gives rise to a customer have both a 0 and 1 at the same time....

Will be a bit of a hit and miss to find which join is giving rise to having two concurrent prices, but would start with

SELECT DISTINCT
       MIN(   CASE
                  WHEN pricemtx.price_meth = 'C'
                       AND priceid.channel = '1' THEN
                      ROUND(((pricemtx.part_price * .01) * partmstr.cost_no6), 2)

                  WHEN pricemtx.price_meth = 'R'
                       AND priceid.channel = '1' THEN
                      pricemtx.part_price
                  WHEN pricemtx.price_meth = 'M'
                       AND priceid.channel = '1' THEN
                      ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
                  WHEN pricemtx.price_meth = 'B'
                       AND priceid.channel = '1'
                       AND pricemtx.disc_type = '%' THEN
                      ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
                  WHEN pricemtx.price_meth = 'B'
                       AND priceid.channel = '1'
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id = 'BASELIST' THEN
                      ROUND((partmstr.part_price - pricemtx.part_disc), 2)
                  WHEN pricemtx.price_meth = 'B'
                       AND priceid.channel = '1'
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id <> 'BASELIST' THEN
                      ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
                 
                  WHEN pricemtx.price_meth = 'R' AND (priceid.channel = '0' )  THEN
                      pricemtx.part_price
                  WHEN pricemtx.price_meth = 'M' AND (priceid.channel = '0' )  THEN
                      ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
                  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
                       AND pricemtx.disc_type = '%' THEN
                      ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
                  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id = 'BASELIST' THEN
                      ROUND((partmstr.part_price - pricemtx.part_disc), 2)
                  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
                       AND pricemtx.disc_type = '$'
                       AND pricemtx.formula_id <> 'BASELIST' THEN
                      ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
              END
          ) AS 'NET_PRICE',
       partmstr.part_code,
       custprice.cust_code,
	   priceid.channel,
pricemtx.end_date

FROM pricemtx
    INNER JOIN partmstr
        ON pricemtx.part_code = partmstr.part_code
    LEFT OUTER JOIN priceid
        ON pricemtx.price_id = priceid.price_id
    LEFT OUTER JOIN vendpart
        ON partmstr.part_code = vendpart.part_code
           AND partmstr.alt_part1 = vendpart.vend_code
    LEFT OUTER JOIN pricemtx AS pricemtx_1
        ON pricemtx.formula_id = pricemtx_1.price_id
           AND pricemtx.part_code = pricemtx_1.part_code
    LEFT OUTER JOIN custprice
        ON pricemtx.price_id = custprice.price_id
WHERE (custprice.cust_code = '450091')
      AND (pricemtx.end_date >= '10/01/18')
      AND (partmstr.avail = 'S')
      AND (partmstr.part_code = 'AGA10010')
      AND (partmstr.price_cat1 <> '100')
      AND ('NET_PRICE' > '0')
      AND (partmstr.part_code <> '1999')
GROUP BY partmstr.part_code,
         custprice.cust_code,
		 priceid.channel,
pricemtx.end_date

ORDER BY partmstr.part_code,
         custprice.cust_code,
		 priceid.channel,
pricemtx.end_date desc

Open in new window

And if we get more than 1 pricemtx.end_date then we can add TOP 1 functionality to the select clause (well, more of a partitioned sort). Are there start dates as well ?

gpsdh

ASKER

Bitsqueezer your solution was spot on. Although it did lead me to the next issue. If I comment out
-- AND (partmstr.part_code = 'AGA10010'), so that it prices all the parts. It brings up nothing. Any ideas?

SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gpsdh

ASKER

Thanks that worked great!!

Mark Wills

instead of using WHERE 1 = ( and a couple of aggregated counts on the derived table )

Using the CTE approach you can also use the row_number() function (see comments in the code) to identify the rows - like a partitioned sort (mentioned above - this is the approach I was going to take with pricemtx.end_date) :

;with CTE as
(
	  SELECT DISTINCT
		   MIN(   CASE
					  WHEN pricemtx.price_meth = 'C'
						   AND priceid.channel = '1' THEN
						  ROUND(((pricemtx.part_price * .01) * partmstr.cost_no6), 2)

					  WHEN pricemtx.price_meth = 'R'
						   AND priceid.channel = '1' THEN
						  pricemtx.part_price
					  WHEN pricemtx.price_meth = 'M'
						   AND priceid.channel = '1' THEN
						  ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
					  WHEN pricemtx.price_meth = 'B'
						   AND priceid.channel = '1'
						   AND pricemtx.disc_type = '%' THEN
						  ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
					  WHEN pricemtx.price_meth = 'B'
						   AND priceid.channel = '1'
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id = 'BASELIST' THEN
						  ROUND((partmstr.part_price - pricemtx.part_disc), 2)
					  WHEN pricemtx.price_meth = 'B'
						   AND priceid.channel = '1'
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id <> 'BASELIST' THEN
						  ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
                 
					  WHEN pricemtx.price_meth = 'R' AND (priceid.channel = '0' )  THEN
						  pricemtx.part_price
					  WHEN pricemtx.price_meth = 'M' AND (priceid.channel = '0' )  THEN
						  ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
					  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
						   AND pricemtx.disc_type = '%' THEN
						  ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
					  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id = 'BASELIST' THEN
						  ROUND((partmstr.part_price - pricemtx.part_disc), 2)
					  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id <> 'BASELIST' THEN
						  ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
				  END
			  ) AS 'NET_PRICE',
		   partmstr.part_code,
		   custprice.cust_code,
		   priceid.channel,
	row_number() over (partition by partmstr.part_code, custprice.cust_code order by priceid.channel desc) as idx
--  we can use row_number() because every we use in the row_number() function is part of the group by
	FROM pricemtx
		INNER JOIN partmstr
			ON pricemtx.part_code = partmstr.part_code
		LEFT OUTER JOIN priceid
			ON pricemtx.price_id = priceid.price_id
		LEFT OUTER JOIN vendpart
			ON partmstr.part_code = vendpart.part_code
			   AND partmstr.alt_part1 = vendpart.vend_code
		LEFT OUTER JOIN pricemtx AS pricemtx_1
			ON pricemtx.formula_id = pricemtx_1.price_id
			   AND pricemtx.part_code = pricemtx_1.part_code
		LEFT OUTER JOIN custprice
			ON pricemtx.price_id = custprice.price_id
	WHERE (custprice.cust_code = '450091')
		  AND (pricemtx.end_date >= '10/01/18')
		  AND (partmstr.avail = 'S')
		  AND (partmstr.part_code = 'AGA10010')
		  AND (partmstr.price_cat1 <> '100')
		  AND ('NET_PRICE' > '0')
		  AND (partmstr.part_code <> '1999')

	GROUP BY partmstr.part_code,
			 custprice.cust_code,
			 priceid.channel

-- order by has been moved to the bottom of the query
 
) Select NET_PRICE,part_code, cust_code, channel
  from CTE
  where idx = 1
  ORDER BY part_code,cust_code,channel

Open in new window

Should also work with whatever WHERE clause you want to use for filtering.... Would you mind testing it out ?

gpsdh

ASKER

This worked as well. Thank you Mark!

Mark Wills

Good to hear.... And thanks very much for trying it out. Much appreciated.

And apologies for harping on about end_date. I have seen multiple prices because a marketing department decides on a promotional price for a specific period and then there are multiple prices appearing.

Still worth considering adding end_date into the select, the group by and in the row_number()

A published price book is never penalised for having a date :)

;with CTE as
(
	  SELECT DISTINCT
		   MIN(   CASE
					  WHEN pricemtx.price_meth = 'C'
						   AND priceid.channel = '1' THEN
						  ROUND(((pricemtx.part_price * .01) * partmstr.cost_no6), 2)

					  WHEN pricemtx.price_meth = 'R'
						   AND priceid.channel = '1' THEN
						  pricemtx.part_price
					  WHEN pricemtx.price_meth = 'M'
						   AND priceid.channel = '1' THEN
						  ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
					  WHEN pricemtx.price_meth = 'B'
						   AND priceid.channel = '1'
						   AND pricemtx.disc_type = '%' THEN
						  ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
					  WHEN pricemtx.price_meth = 'B'
						   AND priceid.channel = '1'
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id = 'BASELIST' THEN
						  ROUND((partmstr.part_price - pricemtx.part_disc), 2)
					  WHEN pricemtx.price_meth = 'B'
						   AND priceid.channel = '1'
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id <> 'BASELIST' THEN
						  ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
                 
					  WHEN pricemtx.price_meth = 'R' AND (priceid.channel = '0' )  THEN
						  pricemtx.part_price
					  WHEN pricemtx.price_meth = 'M' AND (priceid.channel = '0' )  THEN
						  ROUND((partmstr.part_price + (pricemtx.part_price * partmstr.part_price) / 100), 2)
					  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
						   AND pricemtx.disc_type = '%' THEN
						  ROUND(((100 - pricemtx.part_disc) * .01 * partmstr.part_price), 2)
					  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id = 'BASELIST' THEN
						  ROUND((partmstr.part_price - pricemtx.part_disc), 2)
					  WHEN pricemtx.price_meth = 'B' AND (priceid.channel = '0' ) 
						   AND pricemtx.disc_type = '$'
						   AND pricemtx.formula_id <> 'BASELIST' THEN
						  ROUND(((pricemtx_1.part_price * .01) * (partmstr.cost_no6) - pricemtx.part_disc), 2)
				  END
			  ) AS 'NET_PRICE',
		   partmstr.part_code,
		   custprice.cust_code,
		   priceid.channel,
	pricemtx.end_date,
	row_number() over (partition by partmstr.part_code, custprice.cust_code order by pricemtx.end_date,priceid.channel desc) as idx
--  we can use row_number() because every column we use in the row_number() function is also part of the group by
	FROM pricemtx
		INNER JOIN partmstr
			ON pricemtx.part_code = partmstr.part_code
		LEFT OUTER JOIN priceid
			ON pricemtx.price_id = priceid.price_id
		LEFT OUTER JOIN vendpart
			ON partmstr.part_code = vendpart.part_code
			   AND partmstr.alt_part1 = vendpart.vend_code
		LEFT OUTER JOIN pricemtx AS pricemtx_1
			ON pricemtx.formula_id = pricemtx_1.price_id
			   AND pricemtx.part_code = pricemtx_1.part_code
		LEFT OUTER JOIN custprice
			ON pricemtx.price_id = custprice.price_id
	WHERE (custprice.cust_code = '450091')
		  AND (pricemtx.end_date >= '10/01/18')   -- better to use '2018-10-01' or just the plain old getdate()
		  AND (partmstr.avail = 'S')
		  AND (partmstr.part_code = 'AGA10010')
		  AND (partmstr.price_cat1 <> '100')
		  AND ('NET_PRICE' > '0')
		  AND (partmstr.part_code <> '1999')

	GROUP BY partmstr.part_code,
			 custprice.cust_code,
			 priceid.channel,
			 pricemtx.end_date

) Select NET_PRICE,part_code, cust_code, channel, end_date
  from CTE
  where idx = 1
  ORDER BY part_code,cust_code,channel, end_date

Open in new window