-
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.
-
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.
-
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
-
Forum Rules
|
|