asked on

SQL - Selecting max date before specified date

Hi SQL Experts,

Below shows a sample data set that shows rent change history throughout the years. Every time there's a change in rent for the unit, the date of the change and the new rent is recorded. Now I want to know what the rent is for all or selected units at say.. 2012-12-01. For hUnit of "24", the row that I need is of course 2010-01-08 since that was the last rent change before 2012-12-01.

I do not know a good way to get rid of the other irrelevant rows.

***************************************************
select uh.hUnit, MAX(uh.dtDate), uh.cRent from unit_history uh
where uh.sType = 'Rent Change'
AND uh.dtDate <= '2012-12-01'
group by uh.hUnit, uh.dtDate, uh.cRent
order by uh.hUnit
***************************************************

hUnit      dtDate        cRent
24      2007-02-21 14:24:04.310      671.00
24      2010-01-08 00:00:00.000      650.00
24      2009-01-19 00:00:00.000      635.00
24      2008-01-18 12:24:35.043      625.00
24      2009-04-08 00:00:00.000      640.00
24      2009-06-08 00:00:00.000      645.00
24      2005-10-17 14:26:07.750      625.00
25      2009-06-08 00:00:00.000      595.00
25      2009-04-08 00:00:00.000      590.00
25      2008-01-18 12:24:46.920      575.00
25      2009-01-19 00:00:00.000      585.00
25      2010-01-08 00:00:00.000      600.00
25      2007-02-21 14:24:11.217      591.00
26      2007-02-21 14:24:19.433      591.00
26      2010-01-08 00:00:00.000      600.00
26      2009-01-19 00:00:00.000      585.00
26      2005-10-24 15:52:58.670      500.00
26      2006-01-31 08:58:10.103      575.00
26      2008-01-18 12:24:59.857      575.00
26      2009-04-08 00:00:00.000      590.00
26      2009-06-08 00:00:00.000      595.00
26      2005-10-17 14:29:24.937      510.00
27      2009-06-08 00:00:00.000      545.00

Surendra Nath

the below code might help you out

;with CTE AS
(
select uh.hUnit, MAX(uh.dtDate) dtDate from unit_history uh
where uh.sType = 'Rent Change'
AND uh.dtDate <= '2012-12-01'
group by uh.hUnit, uh.dtDate
order by uh.hUnit
)
select C.hUnit,c.dtDate,uh.cRent 
FROM unit_history UH
JOIN CTE C 
ON UH.hUnit = C.hUnit
and UH.dtDate = C.dtDate

Open in new window

timberadmin

ASKER

In addition to this, I'm hoping to select additional columns in the query to capture the december 2010 and december 2011 rent.

mbizup

Try this:

select q.hUnit, q.LastChange, t.cRent 
FROM unit_history t 
INNER JOIN
(select uh.hUnit, MAX(uh.dtDate) as LastChange
from unit_history uh
where uh.sType = 'Rent Change'
AND uh.dtDate <= '2012-12-01'
group by uh.hUnit) q
ON t.hUnit = q.hUnit AND t.dtDate = q.LastChange
order by uh.hUnit

Open in new window

Surendra Nath

what are the additional columns, you can add all of the to the query I posted above prefixed with uh.

timberadmin

ASKER

Desired columns here:

hUnit | Dec2010rent | Dec2011rent | Dec2012rent

Thanks!

Surendra Nath

the below query might help you

;with CTE AS
(
select uh.hUnit, MAX(uh.dtDate) dtDate from unit_history uh
where uh.sType = 'Rent Change'
AND uh.dtDate <= '2012-12-01'
group by uh.hUnit, uh.dtDate
order by uh.hUnit
)
select C.hUnit,c.dtDate 
, CASE when YEAR(c.DtDate) = '2010' THEN uh.cRent ELSE '' END AS Dec2010rent 
, CASE when YEAR(c.DtDate) = '2011' THEN uh.cRent ELSE '' END AS Dec2011rent 
, CASE when YEAR(c.DtDate) = '2012' THEN uh.cRent ELSE '' END AS Dec2012rent 
FROM unit_history UH
JOIN CTE C 
ON UH.hUnit = C.hUnit
and UH.dtDate = C.dtDate
and MONTH(c.dtDate) = 12

Open in new window

David Todd

Hi,

CTE's are a SQL 2005+ technique. For all we know since this is posted in SQL asker could be still running SQL 2000.

Maybe this will help.

select *
from dbo.unit_history h
inner join (
    select 
        ui.hUnit
        , max( ui.dtDate ) as maxdtDate
    from dbo.unit_history ui
    where
        ui.dtDate < '2012-12-01'
    group by
        ui.hUnit
    ) hh
    on hh.hUnit = h.hUnit
    and hh.maxdtDate = h.dtDate
;

Open in new window

Otherwise suggest that you want a pivot table.

Regards
David

timberadmin

ASKER

Neo that last one was pretty close but the rent change doesn't always happen in month 12 of a specific year. It could've changed in July and as long as there are no other changes after that, the December rent will be the same.

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

HUNIT CURRENTRENT  LASTREVIEW      PREVIOUSRENT PREVOUSREVIEW   RENTBEFORE   REVIEWBEFORE
24      650      January, 08 2010      645      June, 08 2009      640      April, 08 2009
25      600      January, 08 2010      595      June, 08 2009      590      April, 08 2009
26      600      January, 08 2010      595      June, 08 2009      590      April, 08 2009
27      545      June, 08 2009        (null)       (null)         (null)      (null)

Open in new window

This isn't exactly what the desired columns are, but may assist:

select 
  hunit
, max(case when rentChange = 1 then cRent  else null end) as CurrentRent
, max(case when rentChange = 1 then dtDate else null end) as LastReview
, max(case when rentChange = 2 then cRent  else null end) as PreviousRent
, max(case when rentChange = 2 then dtDate else null end) as PrevousReview
, max(case when rentChange = 3 then cRent  else null end) as RentBefore
, max(case when rentChange = 3 then dtDate else null end) as ReviewBefore
from (
      select hUnit, dtDate, cRent
      , row_number() over (partition by hUnit order by dtDate desc) as rentChange
      from unit_history
     ) as rents
where rentChange < 4
group by 
  hunit

Open in new window

awking00

;with CTE as
(select uh.hUnit, uh.dtDate, uh.cRent, <other columns>
row_number() over (partition by uh.hUnit order by uh.dtDate desc) rn
from unit_history uh
where uh.sType = 'Rent Change'
and uh.dtDate <='2012-12-01')
select hUnit, dtDate, cRent, <other columns>
from CTE
where CTE.rn = 1;

timberadmin

ASKER

wow i'm grateful for all this help. Let me go through this today and let you guys know! Thanks again!

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial