Sql-server – Duplicates when checking for two different column values based on a key that is not unique in table

sql serversql-server-2012

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.

Best Answer

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
Related Question