March 10, 2015 at 4:41 am
Hi All,
I have the table below and want to show the prop_code if the rent_review_date count is less than 1 in 12 months. This means to show only propcode if there has not been any rent update since the first rent_review_date
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')
Select * from @table
Thanks
Jag
March 10, 2015 at 5:06 am
Hi,
First I would suggest you to use appropriate data types for attributes. If you use DATETIME datatype for the attribut Rent_Review_Date you don't need anymore two attributes for review_date which simplifies your data model. Storing a date value as a string introduces different problems related to data integrity, performance and misleading interpretations. So, you should change your sample table definition to:
DECLARE @table TABLE
( Prop_Code INT
,Current_Rent INT
,Revised_Rent INT
,Rent_Review_Date DATETIME)
Then, the following query will return Prop_Code and Rent_Review_Date if there is not Rent_Review_Date in the last 12 months, but there is at least one review for the Prop_Code:
;WITH cte AS
(
SELECT PROP_CODE,Rent_Review_Date,
ROW_NUMBER() OVER(PARTITION BY PROP_CODE ORDER BY Rent_Review_Date DESC) rn
FROM @table
)
SELECT PROP_CODE, Rent_Review_Date
FROM cte
WHERE rn = 1 AND Rent_Review_Date < DATEADD(month,-12,GETDATE())
___________________________
Do Not Optimize for Exceptions!
March 10, 2015 at 6:19 am
Hi,
I have updated the table to reflect to prop_code with no rent updates.
Jag
March 10, 2015 at 6:26 am
Hi Milos,
We will need time as well as for few prop_code the rent was updated twice on the same day and we need to take the max for that date using time. Please feel free to update the data_type as I shared the columns matching my table in the database.
Thanks
jag
March 10, 2015 at 6:43 am
If you define the column with the DATETIME data type you save (as the name says) date and time component of a date together so you can have any number of rows for prop_code for the same day and you can still implement your requirement.
Anyway, if you really want to store them separately then change the data type for your inital column Rent_Review_Date from varchar(10) to DATE. Using varchar(10) as data type would allow you to store '2015-45-78' as date value and I guess that you would not be happy to see such value as a date. Similar for the column Rent_Review_Time you have to use the data type TIME instead of DATETIME. Here is the modified code:
DECLARE @table TABLE
( Prop_Code INT
,Current_Rent INT
,Revised_Rent INT
,Rent_Review_Date DATE
,Rent_Review_Time TIME)
;WITH cte AS
(
SELECT PROP_CODE,Rent_Review_Date,Rent_Review_Time,
ROW_NUMBER() OVER(PARTITION BY PROP_CODE ORDER BY Rent_Review_Date DESC, Rent_Review_Time DESC) rn
FROM @table
)
SELECT PROP_CODE, Rent_Review_Date, Rent_Review_Time
FROM cte
WHERE rn = 1 AND Rent_Review_Date < DATEADD(month,-12,GETDATE())
___________________________
Do Not Optimize for Exceptions!
March 10, 2015 at 7:01 am
Thanks Milos that really helped. If you do mind could you explain how the partition query works please for my understanding.
Thanks
Jag
March 10, 2015 at 7:18 am
The best resource for window function is the Itzik's book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
Here is a great article series (3 articles) written by Fabiano Amorim explaining windowing functions:
Window Functions in SQL Server [/url]
Window Functions in SQL Server: Part 2-The Frame[/url]
Window Functions in SQL Server: Part 3: Questions of Performance[/url]
Another article that introduces window functions: Working with Window Functions in SQL Server [/url]
___________________________
Do Not Optimize for Exceptions!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply