2

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.

  • 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 Jul 26 '17 at 21:22
  • 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 – markp Jul 26 '17 at 21:29
  • What DBMS do you use? SQL Server, Oracle, DB2, MySQL, SQLite, PostgreSQL, ...? – ypercubeᵀᴹ Jul 27 '17 at 15:28
  • It would also help to add the CREATE TABLE statement (as code, not image!) and the exact version of your DBMS. – ypercubeᵀᴹ Jul 27 '17 at 15:29
2

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  ;
  • I don't think this is what they want. It will return rows with UID 2 and 3, the rows with same date. – ypercubeᵀᴹ Jul 27 '17 at 15:26
  • 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. – Markh5 Jul 27 '17 at 15:32
  • @Markh5: Check updated version. I think I now understood what is needed. – a1ex07 Jul 27 '17 at 15:44
  • This works correctly and returns the same row count as @ypercube's query below. Thanks for the assistance. – Markh5 Jul 27 '17 at 16:33
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 ;
  • Yes it is MS SQL and this works correctly as well. Thank's for the input. – Markh5 Jul 27 '17 at 16:34
  • Don't get why this is not accepted answer as it was the first correct answer. +1 – paparazzo Jul 27 '17 at 20:23
1

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
  • 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. – hot2use Jul 27 '17 at 20:13
  • 1
    @hot2use It is more straight forward than the other two answers that have no explanation. It just got flagged for being code only. – paparazzo Jul 27 '17 at 20:22
  • 1
    @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. – ypercubeᵀᴹ Jul 27 '17 at 21:25
  • 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. – Markh5 Jul 27 '17 at 22:08
  • Go with what you want. It is not a big deal. – paparazzo Jul 27 '17 at 23:07

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.