CODEWITHSUNDEEP
sqlsql-servert-sql
Muhammad
Muhammad

Reputation: 93

SQL Query to find effectiveto date

I have this data set:

enter image description here

and I would like to get this result: enter image description here

select c.DHUnitID as ID, UNIT_STAT_CD, DHUnitStatusTypeID, CONVERT(varchar(10), CAST(UNIT_STAT_DT as date), 101) as EffectiveFrom,
CONVERT(varchar(10), CAST(UNIT_STAT_DT as date), 101) as EffectiveTo
from [Q00HMS_DB001].[PMFUNITS].[PMFUNITS_STATUS_MV] a inner join Q00EDH_DB001.EDH.UnitStatusType b
on a.unit_stat_cd=b.UnitStatusTypeCode inner join Q00EDH_DB001.EDH.Unit c
on a.ID=c.UnitID
where c.DHUnitID='1223'
order by uniT_stat_dt desc

I tried in few ways but the EffectiveTo column is not getting the desired results. Any hint in changing the query to get the required result set is appreciated.

Thanks

Upvotes: 0

Views: 102

Answers (1)

Guillaume Cretot-Richert
Guillaume Cretot-Richert

Reputation: 213

You will want to use the LAG function. This allows you to retrieve the value of a previous row in an ordered partition (the OVER keyword).

I recreated your dataset in with a simple table with only the values you provided, and used the following query:

select *,
  LAG(EffectiveFrom) over
    (partition by ID order by EFfectiveFrom desc)
  as EffectiveTo
from dbo.PartitionOverDates
order by EffectiveFrom desc

Upvotes: 3

Related Questions