March 11, 2015 at 1:18 am
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
March 11, 2015 at 3:34 am
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/
March 11, 2015 at 4:01 am
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
March 11, 2015 at 5:25 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply