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

gpsdh
gpsdh used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 TomoskyDirector of Solutions Consulting

Commented:
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
Hi,

you use a "GROUP BY" clause which groups your result for each of the fields in this list. So you get a "MIN" value also for each group if one can be found.

As your "CASE" statements include both possibilities, channel = 1 or channel = 0, you will get one MIN value for channel = 0 and one MIN value for channel = 1. That's the result you got.

I would simply use another outer SELECT where you specify from these two result lines which of them do you want to have as output. If I understand your condition correct, as the output contains one row with channel = 1, this is the preferred value and only if now row with channel = 1 can be found, then the other row would be correct.

So you could write this:

WITH qryPrice AS
(
   <Here your complete SELECT above>
)
SELECT P.part_code,  P.cust_code, P.channel, P.NET_PRICE
FROM qryPrice AS P
WHERE 1 = (CASE 
    WHEN (SELECT COUNT(*) FROM qryPrice AS P1 WHERE P1.channel = 1) = 1 AND P.channel = 1 THEN 1
    WHEN (SELECT COUNT(*) FROM qryPrice AS P2 WHERE P2.channel = 1) = 0 AND P.channel = 0 THEN 1
    ELSE 0
    END)

Open in new window


Cheers,

Christian
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 ?

Author

Commented:
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?
Hi,

of course, because in your initial SELECT you select only one part_code and so my variant counts for exactly one result as you get two result rows in your query, one for channel=1 and one for channel=0. If you remove that you would get in maximum two result rows for each part_code so you would need to specifiy the part_code in the count also.

If you want to extend it to more results you would need to extend the count, something like this:

WITH qryPrice AS
(
   <Here your complete SELECT above>
)
SELECT P.part_code,  P.cust_code, P.channel, P.NET_PRICE
FROM qryPrice AS P
WHERE 1 = (CASE 
    WHEN (SELECT COUNT(*) FROM qryPrice AS P1 WHERE P1.channel = 1 AND P1.part_code = P.part_code) = 1 AND P.channel = 1 THEN 1
    WHEN (SELECT COUNT(*) FROM qryPrice AS P2 WHERE P2.channel = 1 AND P2.part_code = P.part_code) = 0 AND P.channel = 0 THEN 1
    ELSE 0
    END)

Open in new window


Cheers,

Christian

Author

Commented:
Thanks that worked great!!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 ?

Author

Commented:
This worked as well.  Thank you Mark!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial