Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    1

    Unhappy Unanswered: Previous date and Current date issue.

    Hi all,
    I have a requirement and I am newbie to Sybase and TSQL.

    I have a table, which is populated daily, except for weekends.
    so, for august,I have a date entries like,

    trade_date
    __________

    08/02/2010
    08/03/2010
    08/04/2010
    08/05/2010
    08/06/2010
    08/09/2010
    08/10/2010
    08/11/2010
    08/12/2010
    08/13/2010
    08/16/2010
    08/17/2010

    such a way.. note that, there are no entries for 08/07/2010, 08/08/2010,08/14/2010, 08/15/2010 as they are weekends.

    Now I need a table derived from this table such that, when I Give a date to query, It should give me a table,
    with the dates starting from that month, till that date in one column and corresponding previous dates in next column.

    Suppose, if choose 08/20/2010,

    I need to write an TSQL statement such that, I need a a table this way:

    http://img413.imageshack.us/img413/2328/52458957.jpg

    This is very urgent. Can any one help me please?

    I could write a query to give me previous date for a given date in this way:
    Select trade_date from table1 where trade_date = (select max(trade_date) from table where trade_date < user_given_date)
    If we give a date, this will retrieve the previous date in the table for that date.

    But, I need the same to be replicated for the set of dates as stated above.
    Last edited by psiri3; 08-31-10 at 11:58.

  2. #2
    Join Date
    Aug 2010
    Posts
    19
    what you need is an analytical function, have a look at "lead" and "lag" of oracle, you can find a few entries how to solve this with pure ANSI SQL.

    However, if you don´t have too much data, you can use the following solution:

    create a table with a identity field and selfjoin that table via the identity column, please have a look at my solution:

    use tempdb


    --drop table #t1
    -- creating test data
    create table #t1 (fdate datetime)

    declare @fdate datetime
    declare @i int

    set @fdate = convert(char(8),getdate(),112)
    set @i = 0

    while @i < 20
    begin
    -- exclude weekends
    if datepart(cdw,dateadd(day,@i*-1,getdate())) < 6
    begin
    set @fdate = convert(char(8),dateadd(day,@i*-1,getdate()),112)

    insert into #t1
    values (@fdate)
    end
    set @i = @i +1
    end
    -- end of creating test data

    -- below is your solution

    create table #t2 (fid int identity ,fdate datetime)

    insert into #t2
    select * from #t1
    order by fdate

    select *
    from #t2 t1
    inner join #t2 t2 on t1.fid = t2.fid-1

Posting Permissions

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