locked
Need query - Previous date and current date RRS feed

  • Question

  • create table FirstTable(id int, FirstTablenumber varchar(10))

    Create table t1(id int identity(1,1), FirstTableid int, accountid varchar(5), startdate datetime, enddate datetime)

    insert into FirstTable values(10,'P0000001')

    insert into FirstTable values(11,'P0000002')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(10,'A1234','2006-12-23','2007-12-24')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(10,'A1235','2007-12-25','2008-06-10')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(10,'A1236','2008-06-11','2008-12-24')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(10,'A1237','2008-12-25','')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(11,'B1234','2006-12-23','2007-12-24')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(11,'B1235','2007-12-25','2008-06-10')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(11,'B1236','2008-06-11','2008-12-24')

    Insert into t1(FirstTableid, accountid, startdate, enddate) values(11,'B1237','2008-12-25','')

    select * from FirstTable

    select * from t1 order by FirstTableid, startdate desc


    I need the output

    Ftableid Accountid currentstart_date      previous start_date       previous end_date
    10  A1237 2008-12-25 00:00:00.000  2008-06-11 00:00:00.000  2008-12-24 00:00:00.000

    11  B1237  2008-12-25 00:00:00.000  2008-06-11 00:00:00.000  2008-12-24 00:00:00.000

    I think we can use row_number concept but am bit confused to acheive this.

    Thanks and Regards,
    Venkatesan Prabu .J


    raj
    Tuesday, March 17, 2009 6:04 AM

Answers

  • Try this

     
    WITH CTE AS 
    (  
    select FirstTableId, accountId, startdate, enddate, ROW_NUMBER()OVER(PARTITION BY FirstTableId ORDER BY StartDate descas row  
    from t1  
    )  
    SELECT C.FirstTableId   
            ,C.accountId   
            ,c.startdate as CurrentDate  
            ,p.startdate PreviusStartDate   
            ,p.enddate as PreviousEndDate  
    FROM CTE C INNER JOIN CTE P  
    ON C.FirstTableId = P.FirstTableId  
    AND c.row = 1  
    AND P.row =2 

    Mangal Pardeshi
    SQL With Mangal
    Technical Skill is the mastery of complexity, while Creativity is the master of simplicity
    Tuesday, March 17, 2009 6:15 AM