CODEWITHSUNDEEP
sqlsql-serversql-server-2008
PMC
PMC

Reputation: 4756

SQL - find total records that occur before another

I have the following schema and want to find the number of clients that have a typeId of 10 appearing first. The answer with the following data would be 2 (client id 1000 and 1003 have a 10 on the 1st January and an 11 on 2nd January.

CREATE TABLE Event (ClientId int, TypeId int, Date smalldatetime)
INSERT Event (ClientId , TypeId, Date) VALUES (1000, 10, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1000, 11, '2 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1001, 11, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1001, 10, '2 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1002, 11, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1003, 10, '1 JAN 12')
INSERT Event (ClientId , TypeId, Date) VALUES (1003, 11, '2 JAN 12')

I have tried a few queries with ROW_NUMBER() but have got a bit lost.

Upvotes: 2

Views: 172

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this with two row_number()s:

select count(*)
from (select e.*,
             row_number() over (partition by clientId order by date) as seqnum,
             row_number() over (partition by clientId, typeid order by date) as ct_seqnum
      from event e
     ) e
where seqnum = 1 and ct_seqnum = 1 and typeid = 10

This is doing an overall ordering and an ordering by type. Your condition is equivalent to saying that both these are 1 on a row where the typeid is 10.

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

SELECT a.* FROM 
    (SELECT CLIENTID,TYPEID,MIN(DATE) AS DATE
     FROM EVENT
     GROUP BY CLIENTID, TYPEID)A
JOIN
    (SELECT CLIENTID,MIN(DATE) AS DATE
     FROM EVENT
     GROUP BY CLIENTID)B
 ON   B.CLIENTID=A.CLIENTID
 AND  B.DATE=A.DATE
 WHERE TYPEID=10


SQL Fiddle Demo

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You could try the following.

  • self join the table on ClientID
  • only when the typeID of the self joined table is larger
  • and the Date of the self joined table is larger

SQL Statement 

SELECT e1.*
FROM   Event e1
       INNER JOIN Event e2 ON e2.ClientID = e1.ClientID
                              AND e2.TypeID > e1.TypeID
                              AND e2.Date > e1.Date
WHERE  e1.TypeID = 10

Example Fiddle

Upvotes: 1

Related Questions