CODEWITHSUNDEEP
sqlsql-server
KJ3
KJ3

Reputation: 5298

SQL Statement to Get The Minimum DateTime from 2 String FIelds

I've got a bit of a messy table on my hands that has two fields, a date field and a time field that are both strings. What I need to do is get the minimum date from those fields, or just the record itself if there is no date/time attached to it. Here's some sample data:

ID     First     Last     Date         Time
1      Joe       Smith    2013-09-06   04:00
1      Joe       Smith    2013-09-06   02:00
2      Jack      Jones            
3      John      Jack     2013-09-05   06:00
3      John      Jack     2013-09-15   15:00

What I would want from a query is to get the following:

ID     First     Last     Date         Time
1      Joe       Smith    2013-09-06   02:00
2      Jack      Jones            
3      John      Jack     2013-09-05   06:00

The min date/time for ID 1 and 3 and then just ID 2 back because he doesn't have a date/time. I cam up with the following query that gives me ID's 1 and 3 exactly as I would want them:

SELECT *
FROM test as t
where 
cast(t.date + ' ' + t.time as Datetime ) = (select top 1 cast(p.date + ' ' + p.time as Datetime ) as dtime from test as p where t.ID = p.ID order by dtime)

But it doesn't return row number 2 at all. I imagine there's a better way to go about doing this. Any ideas?

Upvotes: 0

Views: 1304

Answers (3)

anon
anon

Reputation:

Assuming [Date] and [Time] are the types I think they are, and not strings:

SELECT ID,[First],[Last],[Date],[Time] FROM 
(
  SELECT ID,[First],[Last],[Date],[Time],rn = ROW_NUMBER() 
    OVER (PARTITION BY ID ORDER BY [Date], [Time])
  FROM dbo.test
) AS t WHERE rn = 1;

Example:

DECLARE @x TABLE
(
  ID INT,
  [First] VARCHAR(32), 
  [Last] VARCHAR(32),
  [Date] DATE,
  [Time] TIME(0)
);

INSERT @x VALUES
(1,'Joe ','Smith','2013-09-06','04:00'),
(1,'Joe ','Smith','2013-09-06','02:00'),
(2,'Jack','Jones',NULL,         NULL  ),
(3,'John','Jack ','2013-09-05','06:00'),
(3,'John','Jack ','2013-09-15','15:00');

SELECT ID,[First],[Last],[Date],[Time] FROM 
(
  SELECT ID, [First],[Last],[Date],[Time],rn = ROW_NUMBER() 
    OVER (PARTITION BY ID ORDER BY [Date], [Time])
  FROM @x
) AS x WHERE rn = 1;

Results:

ID   First   Last    Date          Time
--   -----   -----   ----------    --------
1    Joe     Smith   2013-09-06    02:00:00
2    Jack    Jones   NULL          NULL            
3    John    Jack    2013-09-05    06:00:00

Upvotes: 1

Firoz Ansari
Firoz Ansari

Reputation: 2515

Try:

SELECT 
   *
FROM 
   test as t
WHERE 
   CAST(t.date + ' ' + t.time as Datetime) = 
    (
     select top 1 cast(p.date + ' ' + p.time as Datetime ) as dtime 
     from test as p 
     where t.ID = p.ID 
     order by dtime
    )
OR (t.date='' AND t.time='')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can do this with row_number():

select ID, First, Last, Date, Time
from (select t.*,
             row_number() over (partition by id order by date, time) as seqnum
      from test t
     ) t
where seqnum = 1;

Although storing dates and times as strings is not recommended, you at least do it right. The values use the ISO standard format (or close enough) so alphabetic sorting is the same as date/time sorting.

Upvotes: 2

Related Questions