1S1TzBQ4

1ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
2     @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
3    ,@rangeStart as DateTime = '1970-01-01'
4    ,@rangeEnd as DateTime = '2099-12-31'
5) AS BEGIN
6SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
7DECLARE @loclist as TABLE (
8    Location nvarchar(50) PRIMARY KEY
9)
10IF @Locations is NULL
11    INSERT INTO @loclist(Location)
12        SELECT Location from Monitor_Locations order by Location
13ELSE --irrelevant for this question
14    INSERT INTO @loclist(Location)
15        SELECT
16            ML.Location
17        FROM Monitor_Locations as ML join
18            tvf_splitstring(@Locations) as ss ON 
19                ML.Location=ss.Item OR 
20                ML.Location like ss.Item+'[_]%'
21        ORDER BY ML.Location;
22With Deploys as (
23    SELECT
24        D.Location,
25        MIN(D.Start) as Start,
26        MAX(D.[Stop]) as Stop
27    FROM
28        Deployments as D 
29    WHERE 
30        D.Stop is not NULL
31)
32	
33WHERE 
34    CASE
35        WHEN D.Stop IS NULL THEN 0
36        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
37        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
38    END=1