0

I want to check to see if two orders were placed for one person within an hour of one another.

Example data

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.

1

Based on what you have shared, this should work:

Table and data

CREATE TABLE #Orders (Personid int, proc_code int, time datetime)

INSERT INTO #Orders VALUES (123, 1,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (123, 2,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (123, 2,  '4/25/2016 00:02:00')
INSERT INTO #Orders VALUES (111, 1,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (111, 1,  '4/25/2016 00:03:00')
INSERT INTO #Orders VALUES (111, 2,  '4/25/2016 00:01:00')

Query

select a.PersonId, a.proc_code, 
    CASE WHEN b.proc_code = 2 THEN 'Y' ELSE 'N' END AS 'Y/N'
FROM #Orders a
LEFT JOIN #Orders b 
    ON a.time = b.time 
    AND a.Personid = b.Personid 
    AND a.proc_code != b.proc_code
WHERE a.proc_code = 1

DROP TABLE #Orders
| improve this answer | |
  • Thanks for the response. I edited my question as this wouldn't work given the large amount of records I work with. – alwayslearning5280 Apr 26 '16 at 13:00
  • I'm not sure why the number of rows has anything to do with why this would work or not work? Post your schema, and what you expect as output. Based on the data you have given, my solution works...I used the data you provided as an example, I could put 1M+ sample rows in, and it would still work. I think we are missing some requirements or something. – Chad Mattox Apr 26 '16 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.