So what I am trying to do is retrieve all of the orders from the CKDBAUDDP grouped by the KEYVADD(ID). What I then want to do is only return the order if the status is within a certain date range. with the code I currently have, adding another "and" with the STMPADD comparison will just remove those results from the completed query. This is not really what I want to happen as the results just become less clear then.
exec(' select KEYVADD
,min(case when VALUADD=0 then STMPADD else null end) as "Status 0"
,min(case when VALUADD=2 then STMPADD else null end) as "Status 2"
,min(case when VALUADD=4 then STMPADD else null end) as "Status 4"
,min(case when VALUADD=5 then STMPADD else null end) as "Status 5"
,min(case when VALUADD=7 then STMPADD else null end) as "Status 7"
,min(case when VALUADD=8 then STMPADD else null end) as "Status 8"
,min(case when VALUADD=9 then STMPADD else null end) as "Status 9"
,min(nmdoh) as "Customer"
,min(c.scscn) as "Container"
,min(whsoh) as "Warehouse"
,min(preoh) as "Preorder"
from CKDBAUDDP a
left outer join mvpscohp h on left(a.KEYVADD,7) = h.ONHOH
left outer join mvpscnhp c on h.onhoh = c.onhcn
where whsoh in (700,711,721,731)
and IMGTADD = ''A''
and KEYVADD IN (786398200,786417600)
group by KEYVADD
') at nordic
Below is some test data retrieved from the query above.
KEYVADD | Status 0 | Status 2 | Status 4 | Status 5 | Status 7 | Status 8 | Status 9 | Customer | Container | Warehouse | Preorder
786398200 | 2018-04-30 13:07:53.8780000 | 2018-04-30 10:21:41.9330000 | 2018-04-30 13:08:18.8560000 | NULL | NULL | 2018-04-30 16:33:11.8850000 | NULL | test name | 27739742 | 721 | N
786417600 | 2018-04-30 13:07:53.9080000 | 2018-04-30 11:21:15.7810000 | 2018-04-30 13:08:18.8930000 | NULL | NULL | 2018-04-30 18:13:07.7940000 | NULL | test name 2 | 27739744 | 721 | N
The problem would be in this example if I didn't want the second one to show up because the status 2 was after 11 am and I put that in the And statement it would simply remove the time and put a Null in the results which is not what I want. How would I get it to remove that ID all together?
Sorry of this is too much info but hopefully its clear what I am trying to get across. Thanks!
I think you would use having:
having datepart(hour, min(case when VALUADD = 2 then STMPADD end)) >= 11