November 21, 2018 at 1:16 pm
Hi Guys,
I need help to get records from the following :
Client_id PU Due_time
100623 11163 PIGGOTT DR,CULVE 05:30
100623 11163 PIGGOTT DR,CULVE 05:40
100623 1919 TORRANCE BLVD,TORRA 15:40
100623 1919 TORRANCE BLVD,TORRA 15:30
100925 11180 ORVILLE ST,CULVE 06:40
100925 11180 ORVILLE ST,CULVE 06:10
100925 11180 ORVILLE ST,CULVE 06:15
100925 11180 ORVILLE ST,CULVE 06:30
100925 11180 ORVILLE ST,CULVE 06:11
100925 11180 ORVILLE ST,CULVE 06:35
100925 11180 ORVILLE ST,CULVE 06:45
101028 1414 W EL SEGUNDO BLVD,GADNA 08:15
101028 9812 WALNUT ST,BELLF 13:30
102485 1610 PLAZA DEL AMO,TORRA 06:40
102485 1610 PLAZA DEL AMO,TORRA 06:35
102485 1210 E 223RD ST,CARSO 12:10
102626 16236 CORNUTA AVE,BELLF 07:15
102626 16236 CORNUTA AVE,BELLF 07:19
102626 4007 PARAMOUNT BLVD,LAKEW 14:15
I would like to select records that have same client_id,PU and multiple due_time.I don't need records that have same client_id,PU and only 1 Due_time
Thanks advance
November 21, 2018 at 2:00 pm
SELECT tn.*
FROM dbo.table_name tn
INNER JOIN (
SELECT client_id,PU
FROM dbo.table_name
GROUP BY client_id,PU
HAVING MIN(Due_time) <> MAX(Due_time)
) AS tn_dups ON tn_dups.client_id = tn.client_id AND tn_dups.PU = tn.PU
ORDER BY client_id, PU, due_time
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 21, 2018 at 2:17 pm
Thank you,
I came up with this which works as well
SELECT client_id, PU
FROM (
SELECT client_id AS CLIENT_ID, PU, Due_time, ROW_NUMBER() OVER (
PARTITION BY (
pu, due_time ORDER BY client_id
) AS r
FROM TABLE_NAME t
) AS t
WHERE t.r > 1
GROUP BY client_id, PU
)
November 23, 2018 at 10:10 am
But that doesn't match your original request. You said you wanted "records" (i.e. rows), which would include the Due_time.
If you need just the client_id and PU, you can use just the inner part of my query, which will be more efficient.
SELECT client_id,PU
FROM dbo.table_name
GROUP BY client_id,PU
HAVING MIN(Due_time) <> MAX(Due_time)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply