Query to return multiple rows with same UID and entered on the same date

Question

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.

• markp-fuso: curious, what would the results be for a UID that has both different and duplicate dates, eg: (5,5/17/2017) (5,5/17/2017) (5,8/4/2017); display just the 2 duplicate rows? display all 3 rows because there's at least 1 duplicate?
• markp-fuso: also, your queries mention orderuid and ordernumber, but your sample data only contains uid; it's not apparent (to me) if ordernumber is duplicated, too ... or distinct ... ? it would help if your sample data matched your queries; I recommend you take a look at How do I ask a good question? and then edit your question accordingly
• ypercubeᵀᴹ: What DBMS do you use? SQL Server, Oracle, DB2, MySQL, SQLite, PostgreSQL, ...?
• ypercubeᵀᴹ: It would also help to add the CREATE TABLE statement (as code, not image!) and the exact version of your DBMS.

Answer 1

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 ;

• ypercubeᵀᴹ: I don't think this is what they want. It will return rows with UID 2 and 3, the rows with same date.
• user84926: Thanks for the sample however this does the same as my previous attempts. Returns all UIDs with two or more dates that are the same for a distinct UID/Order Number, even if those UID/Order Numbers have another row with a different date, as in UID 2 & 3 from my sample. I'm after those where the UID/Order Number has only one date, UID/Order Number 1& 4, and may have multiple rows but all on the same date. Just saw @ypercube's comment which is correct.
• a1ex07: @Markh5: Check updated version. I think I now understood what is needed.
• user84926: This works correctly and returns the same row count as @ypercube's query below. Thanks for the assistance.

Answer 2

Assuming that you are in SQL Server (otherwise, the DATEDIFF() will need to be adjusted to the respective function for the DBMS you use).

The casts are not needed if the type of datein is date:

SELECT 
 orderuid, ordernumber, datein 
FROM 
 ( SELECT 
 orderuid, ordernumber, datein, 
 diff = DATEDIFF( 
 day,
 CAST(MIN(datein) OVER (PARTITION BY orderuid) AS date),
 CAST(MAX(datein) OVER (PARTITION BY orderuid) AS date)
 ) 
 FROM ordertask
 ) AS ot 
WHERE 
 diff = 0 ;

• user84926: Yes it is MS SQL and this works correctly as well. Thank's for the input.
• paparazzo: Don't get why this is not accepted answer as it was the first correct answer. +1

Answer 3

Using min max

SELECT ot.orderuid, ot.ordernumber, ot.datein 
 FROM ordertask ot
 join ( SELECT orderuid, MIN(datein) as 'min', MAX(datein) as 'max' 
 FROM ordertask
 group by orderuid
 ) AS tt 
 on tt.orderuid = ot.orderuid
 and tt.max = tt.min

• John K. N.: Could you add some explanation to your code to enable the OP to understand your query and why it returns the desired results? It has been flagged as a low-quality answer.
• paparazzo: @hot2use It is more straight forward than the other two answers that have no explanation. It just got flagged for being code only.
• ypercubeᵀᴹ: @Paparazzi I like this answer but it assumes that datein is a date. Reading the OP's code, the floor(cast(ot.DateIn as float)) part, it's more probably a datetime.
• user84926: Yes, Date In is a DateTime, MS SQL DB, and all three examples work. I'm in the dark as to which is the better method and would like to know why one would be preferred over the other.
• paparazzo: Go with what you want. It is not a big deal.