Update query nightmare!

• christopher.hawkes

  SSC Veteran

  Points: 276

  More actions

  January 17, 2011 at 6:35 am

  #1274218

  Ok.....

  I've edited the test data to include another transaction for the same 'Facility_id' (In the live data there would be 100's of Facility_ids with multiple transactions)

  The test data include the first example with a shorter end date adn another set of dates for the same 'Facility_ID'

  In my cursor i can sort the test_sect data by facility_id and startdate so i know the last update is the correct one, albeit i'm updating the same row twice.

  The data below should return numbers in all but 1 of the 'test days' table....

  -- this table is the one that needs updating.....

  CREATE TABLE [dbo].[test_days](

  [facility_id] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

  [in_date] [datetime] NULL,

  [mhsection] [varchar](50) COLLATE Latin1_General_CI_AS NULL

  ) ON [PRIMARY]

  INSERT INTO [iPM_Info_Reporting].[dbo].[test_days]

  ([facility_id]

  ,[in_date]

  ,[mhsection])

  SELECT '1052876','Sep 27 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Sep 28 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Sep 29 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Sep 30 2009 12:00AM','0' union all

  SELECT '1052876','Dec 1 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 2 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 3 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 4 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 5 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 6 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 7 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 8 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 9 2009 12:00AM','0' UNION ALL

  SELECT '1052876','Dec 10 2009 12:00AM','0'

  -- this table and data hold the info that should update the above table

  CREATE TABLE [dbo].[test_Sect](

  [facility_id] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

  [Startdate] [datetime] NULL,

  [Enddate] [datetime] NULL,

  [Sectioncode] [varchar](20) COLLATE Latin1_General_CI_AS NULL

  ) ON [PRIMARY]

  INSERT INTO [iPM_Info_Reporting].[dbo].[test_Sect]

  ([facility_id]

  ,[Startdate]

  ,[Enddate]

  ,[Sectioncode])

  SELECT '1052876','Sep 2 2009 12:00AM','Sep 28 2009 12:00AM','2' UNION ALL

  SELECT '1052876','Sep 28 2009 12:00AM','30/09/2009 00:00','3' union all

  SELECT '1052876','01/12/2009 08:00','05/12/2009 00:00','2' union all

  Select '1052876','05/12/2009 00:00','10/12/2009 00:00','5'