I want to check to see if two orders were placed for one person within an hour of one another.
PERSONID | proc_code | TIME 123 | 1 | 4/25/2016 00:01:00 123 | 2 | 4/25/2016 00:01:00 123 | 2 | 4/25/2016 00:02:00 111 | 1 | 4/25/2016 00:01:00 111 | 1 | 4/25/2016 00:03:00 111 | 2 | 4/25/2016 00:01:00
SELECT persons.person_id, order_proc.proc_code, (CASE WHEN order_proc.proc_code='1' then case when order_proc2.proc_code='2' and order_proc.time=order_proc2.time then 'Y' else 'N' end end) as 'Y/N?' from order_proc inner join PERSONS on order_proc.PERSON_ID=PERSONS.PERSON_ID inner join order_proc as order_proc2 on order_proc2.PERSON_ID=PERSONS.PERSON_ID where order_proc.ordertime=order_proc2.ordertime
I then get duplicate rows if a person has had multiple "2" orders.
What I would like to see returned is for every proc_code=1 check to see if a 2 has been ordered at the same time as 1 was ordered (though ideally it will be within an hour of one another but I can figure that out later).
The furthest I've made it is with the above code but it isn't showing me all 1 orders when 2 wasn't ordered. The where statement above limits me from doing this because it is only matching where times are equal so I don't see any values returned as 'N'
What I'd like to see based on the above sample data:
PERSON_ID Proc_code Y/N? 123|1|Y 111|1|Y 111|1|N
Is there a way I can do this with a while loop and without duplicates? I'm guessing my duplicates are coming from the second join where I alias order_proc as order_proc2.
I am dealing with millions of rows and joining to several other tables as well to pull different types of information for the Persons and orders. I need to check a huge table (order_proc) whose foreign key is a order_procedure_id. There are several persons each with multiple orders.