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

gpsdhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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
0
Aaron TomoskySD-WAN SimplifiedCommented:
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
0
BitsqueezerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Mark WillsTopic AdvisorCommented:
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 ?
0
gpsdhAuthor 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?
0
BitsqueezerCommented:
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
0
gpsdhAuthor Commented:
Thanks that worked great!!
0
Mark WillsTopic AdvisorCommented:
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 ?
0
gpsdhAuthor Commented:
This worked as well.  Thank you Mark!
0
Mark WillsTopic AdvisorCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.