Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    19

    Cool Unanswered: Hi All, In need of help T-SQL dates filling in the gaps

    I have a currency table and I want to create a view that lists for every date a currency rate.

    for example in the table is:

    CurrCode,CurrDate,CurrRate
    EUR 22-Jan-11 1.20
    EUR 19-Feb-11 1.17
    EUR 19-Mar-11 1.15
    EUR 16-Apr-11 1.16
    EUR 14-May-11 1.10
    EUR 11-Jun-11 1.12
    HKD 22-Jan-11 12.55
    HKD 19-Feb-11 13.00
    HKD 19-Mar-11 12.57
    HKD 16-Apr-11 12.53
    HKD 14-May-11 12.43
    HKD 11-Jun-11 12.54

    I want it to become:

    EUR 22-Jan-11 1.20
    EUR 23-Jan-11 1.20
    EUR 24-Jan-11 1.20
    EUR 25-Jan-11 1.20
    EUR 26-Jan-11 1.20
    EUR 27-Jan-11 1.20
    EUR 28-Jan-11 1.20
    EUR 29-Jan-11 1.20
    EUR 30-Jan-11 1.20
    EUR 31-Jan-11 1.20
    until next rate EUR 19-Feb-11 1.17 an so on...

    Any ideas???

    Thanks.

    Script to create table:

    CREATE TABLE [CurrencyExchangeRates](
    [CurrCode] [varchar](10) NOT NULL,
    [CurDate] [datetime] NOT NULL,
    [CurrRate] [decimal](38, 20) NOT NULL
    )
    ;

    INSERT INTO CurrencyExchangeRates VALUES ('EUR','40565','1.2')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','40593','1.17')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','40621','1.15')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','40649','1.16')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','40677','1.1')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','40705','1.1182')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','40565','12.55')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','40593','13')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','40621','12.57')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','40649','12.53')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','40677','12.43')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','40705','12.54')
    ;

  2. #2
    Join Date
    Jun 2011
    Posts
    19
    Sorry, friggin dates vanished and were replaced with numbers before, here's correct version!

    INSERT INTO CurrencyExchangeRates VALUES ('EUR','22-Jan-11','1.2')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','19-Feb-11','1.17')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','19-Mar-11','1.15')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','16-Apr-11','1.16')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','14-May-11','1.1')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('EUR','11-Jun-11','1.1182')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','22-Jan-11','12.55')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','19-Feb-11','13')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','19-Mar-11','12.57')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','16-Apr-11','12.53')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','14-May-11','12.43')
    ;
    INSERT INTO CurrencyExchangeRates VALUES ('HKD','11-Jun-11','12.54')
    ;

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is hideous, but without more time to think about an elegant solution I'll go with what's easy to write that works.
    Code:
    CREATE TABLE [#CurrencyExchangeRates]
    (  [CurrCode] [varchar](10) NOT NULL
    ,  [CurDate] [datetime] NOT NULL
    ,  [CurrRate] [decimal](38, 20) NOT NULL
    );
    
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','22-Jan-11','1.2')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','19-Feb-11','1.17')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','19-Mar-11','1.15')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','16-Apr-11','1.16')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','14-May-11','1.1')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','11-Jun-11','1.1182')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','22-Jan-11','12.55')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','19-Feb-11','13')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','19-Mar-11','12.57')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','16-Apr-11','12.53')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','14-May-11','12.43')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','11-Jun-11','12.54')
    
    SELECT DISTINCT CurrCode
    ,  DATEADD(day, v.number, a.CurDate) AS CurDate
    ,  (SELECT z1.CurrRate
          FROM [#CurrencyExchangeRates] AS z1
          WHERE  z1.CurrCode = a.CurrCode
             AND z1.CurDate = (SELECT MAX(z2.CurDate)
                FROM [#CurrencyExchangeRates] AS z2
                WHERE  z2.CurrCode = z1.CurrCode
                   AND z2.CurDate <= a.CurDate))
       FROM [#CurrencyExchangeRates] AS a
       CROSS JOIN (SELECT number
          FROM master.dbo.spt_values
          WHERE  'P' = type) AS v
       ORDER BY 1, 2
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2011
    Posts
    19
    Thanks PatP Will give the code a go.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Pat,

    At first, I couldn't get my head around this problem. I ran your code to understand the logic behind it. In the result set, I noticed that the old rates kept popping up next to the newer ones.

    I wrote a script, based heavily on your code, that - at first glance - deals with those ghost rates.

    It determines the periods during which a certain Rate for a certain Code is valid. It arbitrary assigns the current date as the end for the last Code/Rate period.
    Then it SELECTS only those generated dates, that fall between the period start and period end.
    Code:
    SELECT T.CurrCode,
    	DATEADD(day, v.number, T.StartPeriod) AS CurDate,
    	T.CurrRate
    FROM (SELECT a.CurrCode,
    		a.CurDate as StartPeriod,
    		COALESCE((SELECT DATEADD(day, -1, MIN(z1.CurDate)) 
    			FROM [#CurrencyExchangeRates] AS z1
    			WHERE a.CurrCode = z1.CurrCode AND
    				a.CurDate < z1.CurDate
    			), CAST(GetDAte() as DATE)) as EndPeriod,
    		a.CurrRate
    	FROM [#CurrencyExchangeRates] AS a
    	) AS T
    	CROSS JOIN (SELECT number
    			FROM master.dbo.spt_values
    			WHERE type = 'P') AS v
    WHERE DATEADD(day, v.number, T.StartPeriod) <= T.EndPeriod
    ORDER BY 1, 2
    Last edited by Wim; 06-27-11 at 09:03.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uff-da, my bad! This is what I should have posted:
    Code:
    DROP TABLE [#CurrencyExchangeRates]
    GO
    
    CREATE TABLE [#CurrencyExchangeRates]
    (  [CurrCode]	[varchar](10)		NOT NULL
    ,  [CurDate]	[datetime]			NOT NULL
    ,  [CurrRate]	[decimal](38, 20)	NOT NULL
    );
    
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','22-Jan-11','1.2')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','19-Feb-11','1.17')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','19-Mar-11','1.15')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','16-Apr-11','1.16')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','14-May-11','1.1')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('EUR','11-Jun-11','1.1182')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','22-Jan-11','12.55')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','19-Feb-11','13')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','19-Mar-11','12.57')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','16-Apr-11','12.53')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','14-May-11','12.43')
    INSERT INTO [#CurrencyExchangeRates] VALUES ('HKD','11-Jun-11','12.54')
    
    SELECT DISTINCT a.CurrCode
    ,  DateAdd(day, v.number, a.CurDate) AS CurDate
    ,  (SELECT z1.CurrRate
          FROM [#CurrencyExchangeRates] AS z1
          WHERE  z1.CurrCode = a.CurrCode
             AND z1.CurDate = (SELECT Max(z2.CurDate)
                FROM [#CurrencyExchangeRates] AS z2
                WHERE  z2.CurrCode = a.CurrCode
                   AND z2.CurDate <= DateAdd(day, v.number, a.CurDate)))
       FROM [#CurrencyExchangeRates] AS a
       CROSS JOIN (SELECT number
          FROM master.dbo.spt_values
          WHERE  'P' = type) AS v
       ORDER BY 1, 2
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2011
    Posts
    19

    Smile

    Great many thanks!

Posting Permissions

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