SQl Query to Calculate No of Days from LastDate and PreviousDate

  • Hi All,

    Below is a table which holds the rent updates done for properties at any point of time.

    Like to show the out as

    Prop_Code, PreviousRent, PreviousRentUpdateDate, LastRent, LastRentUpdateDate DayDifference

    1008 206.38 2014-06-16 209.04 2014-12-22 189

    DECLARE @table TABLE

    ( Prop_Code INT

    ,Current_Rent INT

    ,Revised_Rent INT

    ,Rent_Review_Date varchar(10)

    ,Rent_Review_Time DATEtime)

    INSERT INTO @table (PROP_CODE,Current_Rent,Revised_Rent,Rent_Review_Date,Rent_Review_Time) VALUES

    (2977,372,339.15,'2013-07-08','7:44')

    ,(2977,372,339.15,'2013-07-03','11:01')

    ,(2977,372,372,'2014-06-30','9:07')

    ,(2977,372,372,'2014-07-07','11:06')

    ,(2981,372,372,'2014-07-07','11:06')

    ,(2981,372,340.15,'2013-07-08','7:23')

    ,(2981,372,314.15,'2013-07-08','7:44')

    ,(2981,372,340.15,'2013-07-29','7:16')

    ,(3089,205.63,400,'2014-10-27','8:38')

    ,(3089,205.63,205.63,'2014-02-03','8:29')

    ,(3089,205.63,127.64,'2014-01-20','0:52')

    ,(3089,205.63,123.02,'2013-08-12','8:28')

    ,(3089,205.63,205.63,'2014-12-15','8:46')

    ,(3109,252.62,198,'2014-01-20','0:52')

    ,(3109,252.62,252.62,'2014-04-07','8:30')

    ,(3109,252.62,198,'2013-08-12','8:28')

    ,(3117,284.96,336,'2014-04-21','1:03')

    ,(3125,267.53,267.53,'2014-02-03','8:29')

    ,1008, 181.32, '2013-03-19, '04:41')

    ,(1008 , 186.15, '2013-03-19, '04:41')

    ,(1008 , 187.62, '2013-03-19, '04:41')

    ,(1008, 191.07, '2013-03-19, '04:41')

    ,(1008, 202.33, '2013-08-12', '08:28')

    ,(1008, 202.53, '2013-11-25', '08:33')

    ,(1008, 206.38, '2014-06-16', '09:38')

    ,(1008, 209.04, '2014-12-22', '07:55')

    Select * from @table

  • Hi,

    Welcome to SSC!!

    I think the table structure that you have provided and output you desired is lacking somewhere due to missing information..

    As in table structure there is only one date column while in output there are two date columns from which day_difference were calculated...

    So can you please provide some more information on this...

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hi Kapil,

    This is why I came to the forum for support. Below is the solution from another forum.

    DECLARE @table TABLE

    ( Prop_Code INT

    ,Current_Rent Money

    ,Revised_Rent Money

    ,Rent_Review_Date varchar(10)

    ,Rent_Review_Time DATEtime)

    INSERT INTO @table (PROP_CODE,Current_Rent,Revised_Rent,Rent_Review_Date,Rent_Review_Time) VALUES

    (2977,372,339.15,'2013-07-08','7:44')

    ,(2977,372,339.15,'2013-07-03','11:01')

    ,(2977,372,372,'2014-06-30','9:07')

    ,(2977,372,372,'2014-07-07','11:06')

    ,(2981,372,372,'2014-07-07','11:06')

    ,(2981,372,340.15,'2013-07-08','7:23')

    ,(2981,372,314.15,'2013-07-08','7:44')

    ,(2981,372,340.15,'2013-07-29','7:16')

    ,(3089,205.63,400,'2014-10-27','8:38')

    ,(3089,205.63,205.63,'2014-02-03','8:29')

    ,(3089,205.63,127.64,'2014-01-20','0:52')

    ,(3089,205.63,123.02,'2013-08-12','8:28')

    ,(3089,205.63,205.63,'2014-12-15','8:46')

    ,(3109,252.62,198,'2014-01-20','0:52')

    ,(3109,252.62,252.62,'2014-04-07','8:30')

    ,(3109,252.62,198,'2013-08-12','8:28')

    ,(3117,284.96,336,'2014-04-21','1:03')

    ,(3125,267.53,267.53,'2014-02-03','8:29')

    ,(1008, NULL ,181.32, '2013-03-19', '04:41')

    ,(1008 , NULL , 186.15, '2013-03-19', '04:41')

    ,(1008 ,NULL , 187.62, '2013-03-19', '04:41')

    ,(1008, NULL , 191.07, '2013-03-19', '04:41')

    ,(1008, NULL , 202.33, '2013-08-12', '08:28')

    ,(1008, NULL , 202.53, '2013-11-25', '08:33')

    ,(1008, NULL , 206.38, '2014-06-16', '09:38')

    ,(1008, NULL , 209.04, '2014-12-22', '07:55')

    ;

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

    WITH TempCTE

    AS (

    SELECT *

    ,ROW_NUMBER() OVER (

    PARTITION BY Prop_Code ORDER BY Rent_Review_Date DESC

    ) RowNum

    FROM @table

    )

    SELECT a.Prop_Code

    ,a.Revised_Rent

    ,a.Rent_Review_Date

    ,b.Revised_Rent

    ,b.Rent_Review_Date

    ,DATEDIFF(Day, a.Rent_Review_Date, b.Rent_Review_Date)

    FROM TempCTE a

    INNER JOIN TempCTE b ON a.Prop_Code = b.Prop_Code

    AND a.RowNum = b.RowNum + 1

    WHERE b.RowNum = 1

    Cheers

  • Perhaps a better soultion would be to use the LEAD and LAG windowing functions to avoid the self join and thus do it in a single query without using a cte.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply