title: sql server insert missing data
body: <p><strong>Table A</strong></p>
  
  <pre><code>empid   tdate     transcode
  1       2006-01-1     HI
  1       2008-01-1     PR
  1       2008-11-30    TE
  1       2009-01-02    RH
  2       2007-01-1     HI
  2       2009-01-1     PR
  2       2011-11-30    TE
  </code></pre>
  
  <p>I am trying to do this in SQL Server 2008, basically I want to fill the missing <code>tdate</code> as show below, however if RH tdate is greater than TE tdate than include missing tdate upto current year as in 2013 for each employee else stop at TE tdate. Thank for the help </p>
  
  <p>Final Results</p>
  
  <p><strong>Table A</strong></p>
  
  <pre><code>empid   tdate     transcode
  1       2006-01-1     HI
  1       2007-01-1     HI
  1       2008-01-1     PR
  1       2008-11-30    TE
  1       2009-01-02    RH
  1       2010-01-02    RH
  1       2011-01-02    RH
  1       2012-01-02    RH
  1       2013-01-02    RH
  2       2007-01-1     HI
  2       2008-01-1     HI
  2       2009-01-1     PR
  2       2010-01-1     PR
  2       2011-11-30    TE
  </code></pre>
  
  <p>I started but I'm stuck, not sure if I am on the right path.</p>
  
  <pre><code>select t.empid, t.tdate,t.transcode
  from table1 t 
  inner join table2 t2  on t.empid = t2.empid and t.tdate &lt; t2.tdate 
  order by t.transcode desc
  </code></pre>
  
  <p>Here are the queries one can use to make my table quickly and run queries on it -</p>
  
  <pre><code>CREATE TABLE [dbo].[TableDates](
      [empid] [int] NULL,
      [tdate] [datetime] NULL,
      [transcode] [varchar](2) NULL
  ) ON [PRIMARY]
  GO
  
  INSERT [dbo].[TableDates] ([empid], [tdate], [transcode]) VALUES (1, CAST(0x0000973C00000000 AS DateTime), N'HI')
  INSERT [dbo].[TableDates] ([empid], [tdate], [transcode]) VALUES (1, CAST(0x00009A1600000000 AS DateTime), N'PR')
  INSERT [dbo].[TableDates] ([empid], [tdate], [transcode]) VALUES (1, CAST(0x00009B6400000000 AS DateTime), N'TE')
  INSERT [dbo].[TableDates] ([empid], [tdate], [transcode]) VALUES (1, CAST(0x00009B8500000000 AS DateTime), N'RH')
  INSERT [dbo].[TableDates] ([empid], [tdate], [transcode]) VALUES (2, CAST(0x000098A900000000 AS DateTime), N'HI')
  INSERT [dbo].[TableDates] ([empid], [tdate], [transcode]) VALUES (2, CAST(0x00009B8400000000 AS DateTime), N'PR')
  INSERT [dbo].[TableDates] ([empid], [tdate], [transcode]) VALUES (2, CAST(0x00009FAB00000000 AS DateTime), N'TE')
  </code></pre>