CODEWITHSUNDEEP
sql-servert-sql
Rafael
Rafael

Reputation: 3111

How to select a specific set of rows and then select next row relevant to the original row

I don't know if what i'm looking for it's possible with my current dataset, or if what i'm expecting it's possible at all.

what i am trying to accomplish is to get all rows with status = 2 or 7 get the date and then get the next row with different status to obtain the dateinterval and get the nuber of days that the status had.

DataSet

id_compromiso|fecha      |id_actividad|status
-------------+-----------+------------+----------
32            2013-12-10  359          2
32            2013-12-16  380          5
32            2013-12-18  401          7
32            2013-12-24  485          8
58            2013-12-02  248          2
58            2013-12-03  254          2
58            2013-12-10  360          2
58            2013-12-10  378          5
58            2013-12-12  395          2

what have i tried:

SQL query:

WITH pausa AS (
        SELECT tmp.id_compromiso, tmp.fecha, MIN(tact.id_actividad) as id_actividad
        FROM Actividades as tact 
        INNER JOIN (
            SELECT act.id_compromiso, CAST(act.fecha as date) as fecha 
            FROM actividades as act 
            WHERE act.[status]=7
        ) as tmp 
        ON(tmp.id_compromiso = tact.id_compromiso AND tmp.fecha = CAST(tact.fecha as date))
        WHERE tact.[status]=7
        GROUP BY tmp.id_compromiso, tmp.fecha
    ),
    revision AS (
        SELECT tmp.id_compromiso, tmp.fecha, MIN(tact.id_actividad) as id_actividad
        FROM Actividades as tact 
        INNER JOIN (
            SELECT act.id_compromiso, CAST(act.fecha as date) as fecha 
            FROM actividades as act 
            WHERE act.[status]=2
        ) as tmp 
        ON(tmp.id_compromiso = tact.id_compromiso AND tmp.fecha = CAST(tact.fecha as date))
        WHERE tact.[status]=2
        GROUP BY tmp.id_compromiso, tmp.fecha
    )
SELECT * FROM revision ORDER BY id_compromiso;

but really running i'm out of ideas on how to get the next item with different status from the table ...

Upvotes: 0

Views: 61

Answers (2)

Jayvee
Jayvee

Reputation: 10875

-- First, it extends actividades to include the minimum fecha for the status
-- on the compromiso; this is min(fecha) in the partition by compromiso/status

WITH status_start AS(
SELECT *, MIN(fecha) OVER (PARTITION BY id_compromiso, status) sStart
FROM actividades 
),

-- Then, join the extended actividades table with itself (aliased a and b) by compromiso but status 2,7 with status not 2,7 
-- (this is the AND a.STATUS IN (2,7)  AND b.STATUS NOT IN(2,7) in the join clause)
-- and making sure it's a later status (the a.sStart <b.sStart bit) 
-- at this point also calculates the date difference in days

status_start_end AS(
SELECT a.*,b.sStart sEnd, DATEDIFF(d, a.sStart, b.sStart) AS sDiff FROM status_start a
JOIN status_start b ON (a.id_compromiso =b.id_compromiso AND a.STATUS IN (2,7)  AND b.STATUS NOT IN(2,7) AND a.sStart <b.sStart))

-- Finaly as the previous query would have day difference in relation to ALL later status, we need to select only the minimum difference
-- as this is when the status actually change. We also need to eliminate duplicates using 'distinct; 
-- as it could be many entries for the same status and
-- also many later status.


SELECT DISTINCT id_compromiso, status ,
MIN(sDiff)  OVER (PARTITION BY id_compromiso) "Nr. of days in status" 
FROM status_start_end

Upvotes: 2

Mack
Mack

Reputation: 2552

Without knowing more about the context in question it's difficult to provide a fitting answer, but something like this may help:

SELECT TOP 1 id_compromiso, fecha, id_actividad, status
FROM Actividades
WHERE CAST(fecha AS DATE)>( SELECT  MAX(CAST(fecha AS DATE))
                            FROM Actividades
                                WHERE status IN (2,7))
AND status NOT IN (2,7)
ORDER BY CAST(fecha AS DATE) DESC

I have set up a SQL Fiddle here.

Upvotes: 0

Related Questions