Insert missing rows

• David Nusovich

  Valued Member

  Points: 70

  More actions

  June 19, 2006 at 8:56 am

  #113541

  Help needed for the following issue.

  I need to insert the missing rows based on the the previous edit dates just read. I need to create a calendar to go back to 2003-10-01.

  Data Samples:

  code edit_date type

  --- ---------- ----

  358 2005-04-27 00:00:00.000 W

  5948 2004-03-30 00:00:00.000 LC

  5948 2006-02-25 00:00:00.000 S

  ------------------------------------------

  Final output should look like this:

  358 2003-10-01 00:00:00.000 W (inserted row)

  358 2003-10-02 00:00:00.000 W (inserted row)

  and so on

  358 2005-04-26 00:00:00.000 W (last inserted row)

  358 2005-04-27 00:00:00.000 W (original row)

  Second scenario:

  NOTE:

  There is same code 5948:

  for the first row same logic applies as in the previous scenario.

  5948 2003-10-01 00:00:00.000 LC (first inserted row)

  5948 2003-10-02 00:00:00.000 LC ( second inserted row)

  and so on ...

  ............

  .............

  5948 2004-03-29 00:00:00.000 LC (last inserted row)

  5948 2004-03-30 00:00:00.000 LC (first original row with same code)

  ------------------------------------------

  5948 2004-03-31 00:00:00.000 S (first inserted row for the second original row

  5948 2004-04-01 00:00:00.000 S (second inserted row for the second original row

  and so on .....

  .......

  ........

  5948 2005-02-24 00:00:00.000 S (last inserted row for the second original row with same code)

  5948 2006-02-25 00:00:00.000 S (second original row with same code 5948)

  Thanks in advance,

  David

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  June 19, 2006 at 10:55 am

  #644652

  Here is a start:

  --Build a table of numbers, you may want to make it a real table because it is a good tool to use

  select top 8001 Identity(int, 0,1) As ID

  into #Number

  from master.dbo.syscomments a

  cross join master.dbo.syscomments b

  go

  declare @sample table (code int, edit_date datetime, type char(2))

  insert @sample values(358, '2005-04-27 00:00:00.000', 'W')

  insert @sample values(5948, '2004-03-30 00:00:00.000', 'LC')

  insert @sample values(5948, '2006-02-25 00:00:00.000', 'S')

  declare @StartDate datetime

  declare @EndDate datetime

  select @StartDate = '01 Oct 2003',

         @EndDate = max(edit_date)

  from @sample

  insert @sample(code, edit_date, type)

  select s.code, dateadd(dd, ID, @StartDate), type

  from @sample s

  cross join #Number

  left outer join (select s.code, s.edit_date, max(pe.edit_date) prev_edit_date

             from @sample s

             inner join @sample pe

             on pe.edit_date < s.edit_date and pe.code = s.code

             group by s.code, s.edit_date) ns

  on  ns.edit_date = s.edit_date and ns.code = s.code

  where dateadd(dd, ID, @StartDate) < @EndDate

  and ((dateadd(dd, ID, @StartDate) > ns.prev_edit_date) or (ns.prev_edit_date is null and dateadd(dd, ID, @StartDate) < s.edit_date ))

  order by 1, 2

  select * from @sample

  order by 1, 2

  go

  drop table #number

  go

• stax68

  SSChampion

  Points: 11711

  More actions

  June 19, 2006 at 10:59 am

  #644653

  declare

  @calendar table(date as cast(i as smalldatetime), i int identity(37000,1), nul int null)

  insert

  @calendar(nul)

  select

  top 2000 null from syscolumns c, syscolumns c2

   

  select

  t1.code, c.date, t1.type

  from @calendar c

  join

  tbl t1 on t1.edit_date > c.date

  left

  join tbl t2

  on

  t2.edit_date >= c.date

  and

  t2.edit_date < t1.edit_date

  where

  t2.edit_date is null

  and c.date >= '2001-06-21'

  Do you realy need all these uninformative records though?

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant