CODEWITHSUNDEEP
mysqlsql
Ginni Machamer
Ginni Machamer

Reputation: 23

Can SQL query do this?

I have a table "audit" with a "description" column, a "record_id" column and a "record_date" column. I want to select only those records where the description matches one of two possible strings (say, LIKE "NEW%" OR LIKE "ARCH%") where the record_id in each of those two matches each other. I then need to calculate the difference in days between the record_date of each other.

For instance, my table may contain:

id   description    record_id    record_date
1    New Sub        1000         04/14/13
2    Mod            1000         04/14/13
3    Archived       1000         04/15/13
4    New Sub        1001         04/13/13

I would want to select only rows 1 and 3 and then calculate the number of days between 4/15 and 4/14 to determine how long it took to go from New to Archived for that record (1000). Both a New and an Archived entry must be present for any record for it to be counted (I don't care about ones that haven't been archived). Does this make sense and is it possible to calculate this in a SQL query? I don't know much beyond basic SQL.

I am using MySQL Workbench to do this.

Upvotes: 2

Views: 106

Answers (2)

dazedandconfused
dazedandconfused

Reputation: 3186

My test was in SQL Server so the syntax might need to be tweaked slightly for your (especially the DATEDIFF function) but you can select from the same table twice, one side grabbing the 'new' and one grabbing the 'archived' then linking them by record_id...

SELECT 
    newsub.id, 
    newsub.description, 
    newsub.record_date,
    arc.id, 
    arc.description, 
    arc.record_date,
    DATEDIFF(day, newsub.record_date, arc.record_date) AS DaysBetween
FROM 
    foo1 arc 
    , foo1 newsub
WHERE 
    (newsub.description LIKE 'NEW%') 
    AND 
    (arc.description LIKE 'ARC%')
    AND 
    (newsub.record_id = arc.record_id)

Upvotes: 0

Ronnis
Ronnis

Reputation: 12833

The following is untested, but it should work asuming that any given record_id can only show up once with "New Sub" and "Archived"

select n.id as new_id
      ,a.id as archive_id
      ,record_id
      ,n.record_date as new_date
      ,a.record_date as archive_date
      ,DateDiff(a.record_date, n.record_date) as days_between
  from audit n
  join audit a using(record_id)
 where n.description = 'New Sub'
   and a.description = 'Archieved';

I changed from OR to AND, because I thought you wanted only the nr of days between records that was actually archived.

Upvotes: 1

Related Questions