quote:

---

Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '.'.

---

declare @Table table(System_ID VarChar(50))
declare @delimiter nchar(1), @item_list varchar(5000), @item varchar(50), @delimiter_index int, @sysid_index int, @Date1 DateTime, @Date2 DateTime
set @delimiter=','
set @item_list='chamber1,water_09,'
set @Date1='07/19/2009'
set @Date2='08/19/2009'

-- Calculate end of first item in list:
set @delimiter_index = CharIndex(@delimiter, @item_list)

while (1 < @delimiter_index) begin -- when there are no items left in the list, delimiter index should be 0
  set @item = Lower(SubString(@item_list, 1, @delimiter_index - 1)) -- get the leftmost item
  set @item_list = LTrim(SubString(@item_list, @delimiter_index + Len(@delimiter), Len(@item_list)))
  set @delimiter_index = CharIndex(@delimiter, @item_list) -- Calcs end of leftmost item in shortened list for next pass
  insert into @Table (System_ID) Values (@item)
end

select system_id as 'System_ID', test_result as 'Test_Result'
from (
  select date_time, a.system_id, test_result
  from Test_Results a join @Table t on (a.system_id like t.system_id)
  where @Date1<Date_Time and Date_Time<@Date2
union
  select a.date_time, Cast('Packout_' + SubString(Cast(DatePart(Year, a.[Date_Time]) AS VarChar(4)), 3, 2) AS VarChar(50)) AS a.system_id,
  (case Len(c.[Supervisor]) when 0 then ('Packed in ' + a.[Box_Number]) else ('Packed in ' + a.[Box_Number] + ' Override by ' + c.[Supervisor]) end) AS 'Test_Result'
  from (Box_Data a INNER JOIN Box_Contents c ON a.[Box_Number]=c.[Box_Number]) join @Table t on (a.system_id like t.system_id)
  where @Date1<a.Date_Time and a.Date_Time<@Date2
) b order by date_time

  select date_time, Cast('Packout_' + SubString(Cast(DatePart(Year, date_time) AS VarChar(4)), 3, 2) AS VarChar(50)) AS system_id,
  (case Len(Supervisor) when 0 then ('Packed in ' + a.[Box_Number]) else ('Packed in ' + a.[Box_Number] + ' Override by ' + Supervisor) end) AS 'Test_Result'
  from (Box_Data as a INNER JOIN Box_Contents as c ON a.[Box_Number]=c.[Box_Number]) join @Table as t on (a.system_id = t.system_id)
  where @Date1<date_time and date_time<@Date2