SQL Server – Query to Return Multiple Rows with Same UID and Date

sql server

I've revised the post per MarkP's suggestions. Thanks for the suggestion as I am new to posting.

I have a table with the following data, UID, Order Number,Date In

enter image description here

I need to select the all of the rows where the Date In is the same for all UIDs , as in rows for UIDs 1 and 4, and ignore rows for UIDs 2 and 3 where the same UID has different dates. (Note that those orders may have duplicate dates as well). So far I've come up with some ways to not achieve the desired results. (I removed all of the samples except one). Since they all returned the same data, all orders with multiple entries on the same date, rather than all orders with only entries on the same date.

The results would look like this:

enter image description here



with uidlist as
(select s.uid,s.ordernumber, count(s.uid) as counter, cast(floor(cast(ot.DateIn as float)) as datetime) as ldate
              from service s join ordertask ot on ot.orderuid = s.uid
              where ot.datein > = '2017-04-01'
              group by s.uid,s.ordernumber, cast(floor(cast(ot.DateIn as float)) as datetime)),
uidfinal as (select count(uid) as counter, uid,ordernumber from uidlist group by uid,ordernumber having count(uid) = 1)
select ul.uid,ul.ordernumber 
from uidfinal UL
order by ul.ordernumber;

Thanks for any assistance.

Best Answer

If you need to output rows where the same date appears more than once for a give orderuid, the following should to the job : 

SELECT orderuid, ordernumber, datein 
FROM 
(
  SELECT a.orderuid,a.ordernumber,a.datein, 
   count(1) OVER(PARTITION BY a.orderuid,a.datein) as num_with_same_date
        from  ordertask a
)b WHERE num_with_same_date >1;

Updated. 

SELECT orderuid, ordernumber, datein 
FROM 
(
  SELECT a.orderuid,a.ordernumber,a.datein, 
   count(1) OVER(PARTITION BY a.orderuid,a.datein) as num_with_same_date,
   count(1) OVER(partition by a.orderuid) as total_num_uid 
        from  ordertask a
)b WHERE total_num_uid =num_with_same_date  ;
Related Question