CODEWITHSUNDEEP
sqlselectsqlite
Cocoa Dev
Cocoa Dev

Reputation: 9551

need help creating a SELECT query to select unique rows

I want to create an SQL Query for a SQLite3 DB.

In my table I have the following sample data

7/1/2011   8:02   LOGIN   JOE
7/1/2011   8:15   LOGIN   CAROL
7/1/2011   8:15   LOGIN   JOE
7/1/2011   8:35   LOGIN   SANDY
7/1/2011   13:15  LOGOFF  SANDY
7/1/2011   16:15  LOGIN   SANDY
7/1/2011   18:12  LOGOFF  CAROL
7/1/2011   21:09  LOGOFF  TED
7/1/2011   21:19  LOGIN   TED
7/1/2011   22:10  LOGOFF  JOE
7/1/2011   23:40  LOGOFF  SANDY
7/2/2011   9:00   LOGIN   JOE
7/3/2011   10:00  LOGIN   JOE
7/3/2011   20:00  LOGOFF  SANDY

basically I want to only get the first LOGIN entry per date and the last LOGOFF entry for the same date (if available)

So I want the query to produce

7/1/2011   8:02   LOGIN   JOE
7/1/2011   8:15   LOGIN   CAROL
7/1/2011   8:35   LOGIN   SANDY
7/1/2011   18:12  LOGOFF  CAROL
7/1/2011   21:09  LOGOFF  TED
7/1/2011   21:19  LOGIN   TED
7/1/2011   22:10  LOGOFF  JOE
7/1/2011   23:40  LOGOFF  SANDY
7/2/2011   9:00   LOGIN   JOE
7/3/2011   10:00  LOGIN   JOE
7/3/2011   20:00  LOGOFF  SANDY

As you can see we are interested in the first login and the last logoff for a user.

All fields are separate fields that are stored as a VARCHAR() EXCEPT DATE and TIME The date is stored as DATE The time is stored as TIME Below are the field names and what they represent.

tddate - date
tdtime - time
tdtype - LOGIN/LOGOFF
tdusername - username

Not all login have a corresponding logoff because a person may disconnect from the network without actually logging off.

Here is a query that I was attempting to use

select tddate,
        case tdtype
                   when 'LOGIN' then Min(tdtime)          
                   when 'LOGOFF' then Max(tdtime)        
                   end as tdtype  from TimeData                     
WHERE tdusername LIKE "JOE"

i get 0 rows of data

Upvotes: 1

Views: 144

Answers (3)

Markus Jarderot
Markus Jarderot

Reputation: 89231

What you have almost works like you want. You just need to add a group by clause:

SELECT
    tddate,
    CASE tdtype
        WHEN 'LOGIN' THEN MIN(tdtime)
        WHEN 'LOGOFF' THEN MAX(tdtime)
        ELSE NULL
    END AS tdtime,
    tdtype,
    tdusername
FROM TimeData
GROUP BY tddate, tdtype, tdusername
ORDER BY tddate, tdtime

Upvotes: 2

StevieG
StevieG

Reputation: 8729

select tddate, max(tdtime) time, tdtype, tdusername 
from table 
where tdtype = 'LOGIN' 
group by tddate, tdtype, tdusername

union

select tddate, min(tdtime) time, tdtype, tdusername 
from table 
where tdtype = 'LOGOUT' 
group by tddate, tdtype, tdusername

Upvotes: 0

Brian
Brian

Reputation: 2778

You're going to need to either self-joins or unions as well as result partitioning to make this work:

   SELECT * FROM (
SELECT DateField
        , TimeField
        , ActionField
        , UserField
        , RealDate = Max(CAST(DateField + ' ' + TimeField AS DateTime))
        , RowNum = ROW_NUMBER() OVER (PARTITION BY UserField, ActionField ORDER BY Cast(DateField + ' ' + TimeField AS DateTime) DESC)
FROM   theTable
WHERE  ActionField='LOGOFF'
GROUP BY Datefield, TimeField, ActionField, UserField
UNION
SELECT DateField
        , TimeField
        , ActionField
        , UserField
        , RealDate = Max(Cast(DateField + ' ' + TimeField AS DateTime))
        , RowNum = ROW_NUMBER() OVER (PARTITION BY UserField, ActionField ORDER BY Cast(DateField + ' ' + TimeField AS DateTime) DESC)
FROM   theTable
WHERE  ActionField='LOGIN'
GROUP BY Datefield, TimeField, ActionField, UserField) AS Result
WHERE Result.RowNum = 1
ORDER BY UserField, RealDate

EDIT: Nevermind - I didn't realize this was SQLite. There's no row_number function there. You might need temp tables...

Upvotes: 0

Related Questions