Professional Web Applications Themes

SELECTing sequence of data - Microsoft SQL / MS SQL Server

Are you looking for something like this? SELECT * FROM TemporyTable t1 WHERE (OperatorSideEdge = 0.0) AND (OperatorSideQuarter = 0.0) AND (Center = 0.0) AND (DriveSideQuarter = 0.0) AND (DriveSideEdge = 0.0) AND EXISTS (SELECT * FROM TemporyTable t2 WHERE (t2.OperatorSideEdge = 0.0) AND (t2.OperatorSideQuarter = 0.0) AND (t2.Center = 0.0) AND (t2.DriveSideQuarter = 0.0) AND (t2.DriveSideEdge = 0.0) AND t2.TCS_ViolationDisplayIndex = t1.TCS_ViolationDisplayIndex +1) -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "Don Ronemus" <net> wrote in message news:0cfd01c35055$9dc40f60$gbl... ...

  1. #1

    Default Re: SELECTing sequence of data

    Are you looking for something like this?

    SELECT *
    FROM
    TemporyTable t1
    WHERE
    (OperatorSideEdge = 0.0) AND
    (OperatorSideQuarter = 0.0) AND
    (Center = 0.0) AND
    (DriveSideQuarter = 0.0) AND
    (DriveSideEdge = 0.0)
    AND EXISTS (SELECT * FROM TemporyTable t2
    WHERE (t2.OperatorSideEdge = 0.0) AND (t2.OperatorSideQuarter = 0.0)
    AND
    (t2.Center = 0.0) AND (t2.DriveSideQuarter = 0.0) AND
    (t2.DriveSideEdge = 0.0)
    AND t2.TCS_ViolationDisplayIndex = t1.TCS_ViolationDisplayIndex +1)
    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Don Ronemus" <net> wrote in message
    news:0cfd01c35055$9dc40f60$gbl... 


    Jacco Guest

  2. #2

    Default Re: SELECTing sequence of data

    Assuming that TCS_ViolationDisplayIndex is the primary key and that there
    are no gaps in the sequence:

    SELECT *
    FROM TemporaryTable AS T1
    JOIN
    (SELECT A.TCS_ViolationDisplayIndex AS ilow,
    MIN(B.TCS_ViolationDisplayIndex) ihigh
    FROM TemporaryTable AS A
    JOIN TemporaryTable AS B
    ON
    A.OperatorSideEdge = 0.0 AND
    A.OperatorSideQuarter = 0.0 AND
    A.Center = 0.0 AND
    A.DriveSideQuarter = 0.0 AND
    A.DriveSideEdge = 0.0 AND
    B.OperatorSideEdge <> 0.0 AND
    B.OperatorSideQuarter <> 0.0 AND
    B.Center <> 0.0 AND
    B.DriveSideQuarter <> 0.0 AND
    B.DriveSideEdge <> 0.0 AND
    B.TCS_ViolationDisplayIndex > A.TCS_ViolationDisplayIndex
    GROUP BY A.TCS_ViolationDisplayIndex
    HAVING MIN(B.TCS_ViolationDisplayIndex) - A.TCS_ViolationDisplayIndex >=6
    ) AS T2
    ON T1.TCS_ViolationDisplayIndex >= ilow
    AND T1.TCS_ViolationDisplayIndex < ihigh


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Don Ronemus" <net> wrote in message
    news:0cfd01c35055$9dc40f60$gbl... 


    David Guest

  3. #3

    Default Re: SELECTing sequence of data

    Don,

    Is this what you want? This will give a range of more than 6
    just once, without showing all the 6-length subranges.
    select SequenceStart, max(SequenceFinish) as SequenceFinish from (
    select min(SequenceStart) as SequenceStart, SequenceFinish from (
    SELECT
    TCS_ViolationDisplayIndex as SequenceStart,
    HigherIndex as SequenceFinish, zEROSBETWEEN
    FROM (
    SELECT
    T1.TCS_ViolationDisplayIndex,
    T2.TCS_ViolationDisplayIndex AS HigherIndex,
    COUNT(T3.TCS_ViolationDisplayIndex) AS ZerosBetween
    FROM TemporyTable T1, TemporyTable T2, TemporyTable T3
    WHERE T1.TCS_ViolationDisplayIndex <= T3.TCS_ViolationDisplayIndex
    AND T3.TCS_ViolationDisplayIndex <= T2.TCS_ViolationDisplayIndex
    AND (T1.OperatorSideEdge = 0.0) AND
    (T1.OperatorSideQuarter = 0.0) AND
    (T1.Center = 0.0) AND
    (T1.DriveSideQuarter = 0.0) AND
    (T1.DriveSideEdge = 0.0)
    AND (T2.OperatorSideEdge = 0.0) AND
    (T2.OperatorSideQuarter = 0.0) AND
    (T2.Center = 0.0) AND
    (T2.DriveSideQuarter = 0.0) AND
    (T2.DriveSideEdge = 0.0)
    AND (T3.OperatorSideEdge = 0.0) AND
    (T3.OperatorSideQuarter = 0.0) AND
    (T3.Center = 0.0) AND
    (T3.DriveSideQuarter = 0.0) AND
    (T3.DriveSideEdge = 0.0)
    GROUP BY T1.TCS_ViolationDisplayIndex, T2.TCS_ViolationDisplayIndex
    ) T
    WHERE ZerosBetween >= 6
    AND TCS_ViolationDisplayIndex + ZerosBetween - 1 = HigherIndex
    ) U1
    group by SequenceFinish
    ) U2
    group by SequenceStart


    -- Steve Kass
    -- Drew University
    -- Ref: CF9D92CF-4A83-4BD5-82B9-794EE3328DF2

    Don Ronemus wrote:
     

    Steve Guest

  4. #4

    Default Re: SELECTing sequence of data

    Why are all the columns NULL-able? This means you can never have a key.
    And in a production system, you would not use a proprietary temp table
    syntax which should be either a base table or a VIEW. Can I asume that
    you meant to say:

    CREATE TABLE Foobar
    (tcs_violation_display_index INTEGER NOT NULL PRIMARY KEY,
    drive_side_edge FLOAT NOT NULL,
    drive_side_quarter FLOAT NOT NULL,
    center FLOAT NOT NULL,
    operator_side_quarter FLOAT NOT NULL,
    operator_side_edge FLOAT NOT NULL);
     [/ref]
    index only increments by one. <<

    Let's genrealize it to any length_of_run:

    SELECT F1.tcs_violation_display_index AS start,
    MIN(F2.tcs_violation_display_index) AS finish
    FROM foobar AS F1, foobar AS F2
    WHERE F2.tcs_violation_display_index - F1.tcs_violation_display_index
    = length_of_run
    GROUP BY F1.tcs_violation_display_index
    HAVING SUM(F2.operator_side_quarter) = 0.0
    AND SUM(F2.center) = 0.0
    AND SUM(F2.drive_side_quarter) = 0.0
    AND SUM(F2.drive_side_edge) = 0.0

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  5. #5

    Default Re: SELECTing sequence of data

    CORRECTION:

    SELECT DISTINCT T1.*
    FROM TemporaryTable AS T1
    JOIN...

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  6. #6

    Default Re: SELECTing sequence of data

    Hi CELKO,
    I will try your SQL code when I get back to work. Thanks
    for the effort in solving my problem not the un-asked for
    condescending attitude! This is only a TEST! NOT a
    production SQL. You seem to be the only one here that has
    this stance.
     
    never have a key. 
    proprietary temp table 
    Can I asume that 
    KEY, [/ref][/ref]
    time when the 
    F1.tcs_violation_display_index 
    what the keys, 
    datatypes, etc. in your 
    *** 
    Don Guest

  7. #7

    Default Re: SELECTing sequence of data

    Hi Jacco,
    Thanks for your effort. I will try this ASAP. I hope I
    can be as helpfull some time in the future.
    Don 
    (t2.OperatorSideQuarter = 0.0) 
    AND 
    t1.TCS_ViolationDisplayIndex +1) [/ref]
    one. [/ref]
    I'm [/ref]
    I'm [/ref]
    of 
    >
    >
    >.
    >[/ref]
    Don Guest

  8. #8

    Default Re: SELECTing sequence of data

    Hi David,
    Thanks! I will try this out when I get to work. I would
    have NEVER got this solution without the newgroup's help.
     
    key and that there 
    A.TCS_ViolationDisplayIndex 
    A.TCS_ViolationDisplayIndex >=6 [/ref]
    one. [/ref]
    I'm [/ref]
    I'm [/ref]
    of 
    >
    >
    >.
    >[/ref]
    Don Guest

  9. #9

    Default Re: SELECTing sequence of data

    >> This is only a TEST! NOT a production SQL. You seem to be the only
    one here that has this stance. <<

    1) In a lot of shops, this IS production code. Really.

    2) You really need to watch the DDL. Keys, constraints, etc. are VITAL
    to a correct answer. When you have hung around the newsgroup for
    awhile, you will see how much people assume is obvious that just ain't.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Selecting certain data from Array
    By eric.mccalla in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: April 22nd, 04:44 AM
  2. Selecting Listbox item by data
    By FlashAsh99 in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 8th, 02:27 AM
  3. inserting and selecting BLOB data in db2
    By Poul Møller Hansen in forum PHP Development
    Replies: 0
    Last Post: November 3rd, 09:41 PM
  4. SQL SELECT, selecting data within a cell
    By Mark in forum PHP Development
    Replies: 1
    Last Post: August 15th, 11:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139