Results 1 to 3 of 3

Thread: SQL Script with either CASE/While/or Loop

  1. #1
    Join Date
    Sep 2006
    Posts
    2

    SQL Script with either CASE/While/or Loop

    I have a script currently running that provides a count of pieces of equipment at a location every minute of a 12 hour shift.

    Min At Site
    1 0
    2 0
    3 1
    4 3
    5 1
    6 1
    7 0
    8 0
    9 0
    10 0
    11 1
    12 2
    13 1
    14 1
    15 1
    16 1
    17 3
    18 2
    19 3
    20 1
    21 1
    22 1
    23 0
    24 0
    25 0
    26 0
    27 0
    28 0
    29 0
    30 0
    31 1
    32 1
    33 1
    34 1
    35 1
    36 0
    37 1
    38 1
    39 2


    and so on all the way to Min = 720.
    Anyways, I am trying to come up with a way to show a adjusted section of the At Site column because there is a known 3 minute digest time. So if there is a minute that has at least 1 piece of equipment at site and it is followed by a minute in the shift with a zero at the site, it must be filled in with a 1 for a maximum of three minutes.

    I have been trying to get this to work with Case Statements because that is what I know best, but it doesn't seem possible.

    Is there a way to make this work?
    Last edited by jjohnsen; 09-01-2006 at 05:58 PM.

  2. #2
    Join Date
    Sep 2006
    Posts
    2
    That is a column of "Min" and a column of "At Site"...for some reason it doesn't what to seperate the two when I submit the Thread.

  3. #3
    Join Date
    Sep 2005
    Posts
    168
    SELECT a.[MIN], CASE a.at_site WHEN 0 THEN CASE WHEN ISNULL(b.at_site, 0) + ISNULL(c.at_site, 0)+ISNULL(d.at_site, 0) > 0 THEN 1 ELSE 0 END
    ELSE a.at_site END
    FROM table a
    LEFT JOIN table b ON b.[MIN] = a.[MIN] - 1
    LEFT JOIN table c ON c.[MIN] = a.[MIN] - 2
    LEFT JOIN table d ON d.[MIN] = a.[MIN] - 3

    --OR

    SELECT a.[MIN], CASE a.at_site WHEN 0 THEN CASE WHEN EXISTS(SELECT 1 FROM table b WHERE b.[MIN] BETWEEN a.[MIN]-3 AND a.[MIN] AND b.at_site > 0) THEN 1 ELSE 0 END
    ELSE a.at_site END
    FROM table a

    --HTH--

Posting Permissions

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