CODEWITHSUNDEEP
sqlsql-server-2012
Kyle Rickaby
Kyle Rickaby

Reputation: 117

SQL Query CASE WHEN Statement

I am doing a SQL Query to try to figure out production by shift... When I run this query.. it returns things that are in a location of 5-FG as having a shift 3... How is this even possible?

SELECT * FROM (select pt_desc1, pt_desc2, tr_part, tr_loc, tr_time, case when custpart = '' then         tr_part else custpart end as custpart, sum(tr_qty_loc) as 'Total', 
CASE WHEN tr_loc = '6-LDT2' then 'Dakkota'
    WHEN tr_loc = '5-FG' then 'Dexsys' end as 'location',
CASE WHEN tr_time > '14400' AND tr_time < '50400' AND tr_loc = '6-LDT2'Then '1'
    WHEN tr_time > '50400' AND tr_time < '75600' AND tr_loc = '6-LDT2' Then '2' 
    WHEN tr_time > '75600' OR tr_time < '14400' AND tr_loc = '6-LDT2' Then '3'
    WHEN tr_time > '21600' AND tr_time < '59400' AND tr_loc = '5-FG' Then '1'
    WHEN tr_time > '59400' or tr_time < '21600' AND tr_loc = '5-FG' Then '2' end as 'Shift'
from tr_hist_sql
join pt_mstr_sql on tr_part = pt_part
join cp_mstr_sql on pt_part = intpart
where tr_loc IN ('5-FG') 
and tr_type = 'rct-wo' 
group by tr_part, pt_desc1, pt_desc2, tr_loc, tr_time, case when custpart = '' then tr_part else      custpart end, CASE WHEN tr_time > '14400' AND tr_time < '50400' AND tr_loc = '6-LDT2' Then '1'
WHEN tr_time > '50400' AND tr_time < '75600' AND tr_loc = '6-LDT2' Then '2' 
WHEN tr_time > '75600' OR tr_time < '14400' AND tr_loc = '6-LDT2' Then '3'
WHEN tr_time > '21600' AND tr_time < '59400' AND tr_loc = '5-FG' Then '1'
    WHEN tr_time > '59400' or tr_time < '21600' AND tr_loc = '5-FG' Then '2'  end,
CASE WHEN tr_loc = '6-LDT2' then 'Dakkota'
    WHEN tr_loc = '5-FG' then 'Dexsys' end
having sum(tr_qty_loc) > 0) as dt
where Shift = 1 or shift = 2 or shift =3

order by tr_part

enter image description here

Upvotes: 0

Views: 109

Answers (1)

ballBreaker
ballBreaker

Reputation: 722

My guess is that the line:

WHEN tr_time > '75600' or tr_time < '14400' and tr_loc = '6-LDT2' then '3'

is being interpreted as:

WHEN tr_time > '75600' or (tr_time < '14400' and tr_loc = '6-LDT2') then '3'

Then would come to the first clause:

WHEN tr_time > '75600'

Which satisfies the OR statement, giving a value of '3' .

Upvotes: 1

Related Questions