4

Hi all I'm trying to find a way to select min or max from a range of data based on these conditions:

  • If setuptime and processtime cols are all 0 select MIN(oprNum) (operation hasn't started yet so get first oprnum)
  • If setuptime and process time are not 0, get max oprnum (active operation).

Based on either of these I want ONE row... Please see attached example of data. Thanks! This is part of a much larger query so i need 1 output row per prodid...

+------------+--------+---------+--------------------+--------------------+ | ProdId | OprNum | Company | SetupTime | ProcessTime | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 10 | 12 | 1.3400000000000000 | 1.6100000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 10 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 15 | 12 | 1.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 50 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 60 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 60 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 70 | 12 | 0.0700000000000000 | 0.0400000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 70 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+
Improve this question
4
  • Have you considered to use CASE statement ? Commented Aug 4, 2015 at 16:58
  • i tried a case statement but can't seem to get the right output (1 row) Commented Aug 4, 2015 at 16:59
  • Post what you tried please :D Commented Aug 4, 2015 at 16:59
  • you could try analytic function OVER msdn.microsoft.com/en-us/library/ms189461.aspx Commented Aug 4, 2015 at 17:00

2 Answers 2

Reset to default
4

updated query: based on last comment

if the max record has 0 times, i want to select the last record that has a setuptime or process time. If I add this row to your fiddle ('12M0003381',80,12,0.00,0.00) I get this row when i want the last one with a setuptime or process time

logic used by this query is to simply calculate an additional column weighted_value. In outer query we use min over value and max over weighted value like before.

select t.prodId, case when MAX(t.setuptime+ t.processtime)>0 then MAX(t.weighted_value) else MIN(t._value) end as value from ( select prodID, oprnum as _value, setuptime, processtime, case when setuptime+processtime>0 then oprnum else NULL end as weighted_value from tbl ) t group by t.prodID

updated fiddle link: http://sqlfiddle.com/#!6/b7ecb/20

please try this query

select t1.ProdId, case when exists( select 1 from tbl t2 where t2.setuptime >0 or t2.Processtime>0 and t2.prodId=t1.prodId ) then MAX(t1.oprNum) ELSE MIN(t1.oprNum) END from tbl t1 group by ProdId

sql fiddle link http://sqlfiddle.com/#!6/c52e22/1

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

7 Comments

this does the same as the other one, however what i said in my last comment if the max record has 0 times, i want to select the last record that has a setuptime or process time. If I add this row to your fiddle ('12M0003381',80,12,0.00,0.00) I get this row when i want the last one with a setuptime or process time, thanks for your help.
I just noticed the where clause at bottom is going to filter out any records that have all 0 processtime and 0 setuptime maybe should be... where t1.setuptime >= 0 OR t1.processtime >= 0?
yes that's what i did. I updated your fiddle, changed all times to 0 and then i get 0 rows returned, where in this situation I want the MIN(oprnum)... which would be 10...
thanks so much for your help on this.... it's not that straightforward... can you explain to me the ELSE NULL? why not ELSE 0 or something? How does this affect the results...
I used NULL as all Aggregation functions like MAX and MIN ignore NULL values and we did not want to consider these values. Basically here weighted value is either original value or not. MAX and MIN in this scenario will remain unaffected by 0 value. But lets say we had to work on ranges which included negative values as well, we'd be safer with NULL
|
3

One way:

SELECT ProdId, CASE WHEN SUM(SetupTime) + SUM(ProcessTime) = 0 THEN MIN(oprNum) ELSE MAX(oprNum) END FROM T GROUP BY ProdId
Improve this answer

2 Comments

I think this is what i was after! can't believe i've been so stupid not to think of this... i've been going round the houses trying to come up with a solution...
how could i expand this to show MAX(OprNum) that has a time? so at the max part ideally i'd want to add MAX(oprNum) WHERE setuptime + processtime <> 0...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.