# Miscellaneous > SQL Scripts >  SQL Script with either CASE/While/or Loop

## jjohnsen

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?

----------


## jjohnsen

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.

----------


## mikr0s

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--

----------

