Revisions to Find "n" consecutive free numbers from table

fix postgres syntax

Source Link

edited Mar 18, 2013 at 19:03

182.2k
28
457
620

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

DECLARESELECT @Consec 
 int = 3 number
FROM
SELECT 
    mytable numberm
FROMCROSS JOIN
   (SELECT MyTable3 MAS consec) x
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTablemytable
        WHERE number = Mm.number -@consec+1 x.consec + 1
        AND Statusstatus = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTablemytable
        WHERE number BETWEEN Mm.number -@consec+1 x.consec + 1 AND Mm.Numbernumber
        AND Statusstatus = 'ASSIGNED')

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

DECLARE @Consec int = 3

SELECT 
     number
FROM
    MyTable M
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number = M.number-@consec+1
        AND Status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number BETWEEN M.number-@consec+1 AND M.Number
        AND Status = 'ASSIGNED')

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

SELECT  
    number
FROM
    mytable m
CROSS JOIN
   (SELECT 3 AS consec) x
WHERE 
    EXISTS
       (SELECT 1 
        FROM mytable
        WHERE number = m.number - x.consec + 1
        AND status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM mytable
        WHERE number BETWEEN m.number - x.consec + 1 AND m.number
        AND status = 'ASSIGNED')

added 4 characters in body

Source Link

edited Mar 18, 2013 at 18:05

JNK

18k
5
62
98

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

DECLARE @Consec int = 3

SELECT 
    number
FROM
    MyTable M
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number = M.number-@consec@consec+1
        AND Status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number BETWEEN M.number-@consec@consec+1 AND M.Number
        AND Status = 'ASSIGNED')

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

DECLARE @Consec int = 3

SELECT 
    number
FROM
    MyTable M
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number = M.number-@consec
        AND Status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number BETWEEN M.number-@consec AND M.Number
        AND Status = 'ASSIGNED')

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

DECLARE @Consec int = 3

SELECT 
    number
FROM
    MyTable M
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number = M.number-@consec+1
        AND Status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number BETWEEN M.number-@consec+1 AND M.Number
        AND Status = 'ASSIGNED')

Rollback to Revision 1

Source Link

edited Mar 18, 2013 at 17:56

swasheck

10.7k
5
48
89

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

consecDECLARE integer@Consec :int = 3

SELECT 
    number
FROM
    MyTable M
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number = (M.number-consec)@consec
        AND Status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number BETWEEN (M.number-consec)@consec AND M.Number
        AND Status = 'ASSIGNED');

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

consec integer := 3

SELECT 
    number
FROM
    MyTable M
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number = (M.number-consec)
        AND Status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number BETWEEN (M.number-consec) AND M.Number
        AND Status = 'ASSIGNED');

This is a fairly generic way to do this.

Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed:

DECLARE @Consec int = 3

SELECT 
    number
FROM
    MyTable M
WHERE 
    EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number = M.number-@consec
        AND Status = 'FREE')
    AND NOT EXISTS
       (SELECT 1 
        FROM MyTable
        WHERE number BETWEEN M.number-@consec AND M.Number
        AND Status = 'ASSIGNED')

edited body

Source Link

edited Mar 18, 2013 at 17:37

swasheck

10.7k
5
48
89

Loading

Source Link

created Mar 18, 2013 at 17:16

JNK

18k
5
62
98

Loading

Stack Exchange Network

Return to Answer