Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: Create 'Dummy' rows

    Guys - scenario/DDL/DML below

    Create table #Periods (Period INT , Frequency INT , startdate datetime NULL , enddate datetime NULL )
    insert #Periods (Period , Frequency , startdate , enddate)
    select 0 , 3 , '01-Nov-2004' , '30-Nov-2004'
    union all
    select 1 , 3 , '01-Dec-2004' , '31-Dec-2004'
    union all
    select 2 , 3 , '01-Jan-2005' , '31-Jan-2005'
    union all
    select 2 , 6 , '01-Nov-2004' , '30-Nov-2004'
    union all
    select 3 , 6 , '01-Dec-2004' , '31-Dec-2004'
    union all
    select 4 , 6 , '01-Jan-2005' , '31-Jan-2005'

    select * from #periods
    Period Frequency startdate enddate
    0 3 2004-11-01 00:00:00.000 2004-11-30 00:00:00.000
    1 3 2004-12-01 00:00:00.000 2004-12-31 00:00:00.000
    2 3 2005-01-01 00:00:00.000 2005-01-31 00:00:00.000
    2 6 2004-11-01 00:00:00.000 2004-11-30 00:00:00.000
    3 6 2004-12-01 00:00:00.000 2004-12-31 00:00:00.000
    4 6 2005-01-01 00:00:00.000 2005-01-31 00:00:00.000


    For any frequency (in this simple example 6) where the 'lowest' period is not 0, I need to create dummy rows
    so here I need to create the following
    Period Frequency startdate enddate
    0 6 NULL NULL
    1 6 NULL NULL

    I've built a temp table to identify the min period for each Frequency but am not sure where to go from here using this to do the inserts ?
    select
    min(Period) as MinPeriod ,
    ResetFrequency
    into #Periods2
    from #Periods
    group by ResetFrequency

    select * from #CashFlow2
    MinPeriod ResetFrequency
    0 3
    1 6

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    I hope this is what u need
    Code:
    -------sample data---------
    Create table #Periods (Period INT , Frequency INT , startdate datetime NULL , enddate datetime NULL )
    go
    insert #Periods (Period , Frequency , startdate , enddate)
    select 0 , 3, '01-Nov-2004' , '30-Nov-2004'
    union all
    select 1 , 3 , '01-Dec-2004' , '31-Dec-2004'
    union all
    select 2 , 3 , '01-Jan-2005' , '31-Jan-2005'
    union all
    select 2 , 6 , '01-Nov-2004' , '30-Nov-2004'
    union all
    select 3 , 6 , '01-Dec-2004' , '31-Dec-2004'
    union all
    select 4 , 6 , '01-Jan-2005' , '31-Jan-2005'
    union all
    select 4 , 7, '01-Jan-2005' , '31-Jan-2005'
    go
     
    ------create sequence number in temp table #t--------
    declare @maxperoid int
    declare @id int
    set @id=-1
    select @maxperoid=max(Period) from #Periods
    create table #t (id int)
    while(0=0)
    begin
    set @id=@id+1
    insert into #t(id) values(@id)
    if @id=@maxperoid break
    end
    --------insert query------------------------
    insert into #Periods
    select id,Frequency,null,null from #t,(select 
    min(Period) as MinPeriod , 
    Frequency 
    from #Periods
    group by Frequency) as tm
    where id<MinPeriod
    Last edited by mallier; 12-06-05 at 10:01.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    37
    Spot on Mallier
    Thx large !!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mallier
    Code:
    ------create sequence number in temp table #t--------
    declare @maxperoid int
    declare @id int
    set @id=-1
    select @maxperoid=max(Period) from #Periods
    create table #t (id int)
    while(0=0)
    begin
    set @id=@id+1
    insert into #t(id) values(@id)
    if @id=@maxperoid break
    end
    ????WTF????

    K.I.S.S. please...
    Code:
    declare	@maxperiod int
    declare	@id int
    set	@id=0
    select	@maxperiod=max(Period) from #Periods
    
    create	table #t (id int)
    while	@id <= @maxperiod
    	begin
    		insert into #t(id) values(@id)
    		set @id=@id+1
    	end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •