Populate Id's Based on another table

user_7740387 0
Viewed 36

I have the following Table:

Sample Data 1

The sample data for above is as following:

declare @years table (idYear int primary key identity, cYearDescription varchar(20), dYearStartDate datetime)

insert into @years (cYearDescription,dYearStartDate) values
    ('Year 1','2014-08-31')
,   ('Year 2','2015-07-01')
,   ('Year 3','2016-07-01')
,   ('Year 4','2017-07-01')
,   ('Year 5','2018-07-01')
,   ('Year 6','2019-07-01')

select
*
from    @years

Notice the Start Date for where idYear = 1 is the end of that month. I've provided the sample data this way, as it's exactly how it is in my actual table...

I then generate the End of each month for all the periods in between these months with this script:

;with cte as
(
select  dateadd(day, nbr - 1, (select min(dYearStartDate) from @years)) CalendarDate
from    ( select    row_number() over ( order by c.object_id ) as nbr
          from      sys.columns c
        ) nbrs
where   nbr - 1 <= datediff(day, (select min(dYearStartDate) from @years), (select max(dYearStartDate) from @years))
)
,   months as (
select
eomonth(CalendarDate)   EndOfMonth
from    cte
group by eomonth(CalendarDate)
)

select
*
from    months

I then join the cte with my virtual table like following:

declare @years table (idYear int primary key identity, cYearDescription varchar(20), dYearStartDate datetime)

insert into @years (cYearDescription,dYearStartDate) values
    ('Year 1','2014-08-31')
,   ('Year 2','2015-07-01')
,   ('Year 3','2016-07-01')
,   ('Year 4','2017-07-01')
,   ('Year 5','2018-07-01')
,   ('Year 6','2019-07-01')


;with cte as
(
select  dateadd(day, nbr - 1, (select min(dYearStartDate) from @years)) CalendarDate
from    ( select    row_number() over ( order by c.object_id ) as nbr
          from      sys.columns c
        ) nbrs
where   nbr - 1 <= datediff(day, (select min(dYearStartDate) from @years), (select max(dYearStartDate) from @years))
)
,   months as (
select
eomonth(CalendarDate)   EndOfMonth
from    cte
group by eomonth(CalendarDate)
)

select
*
from    months m
left join @years y  on  eomonth(dYearStartDate) = EndOfMonth
order by EndOfMonth

The above gives me the following results:

Results

How do I manipulate this to look as follows:

Expected Results

I've tried rank, ntile and row_number, but none give me the results I require...

Your assistance is much appreciated!

EDIT

I've altered the select above to the following:

select
*
,   isnull(idYear,lag(idYear) over (order by EndOfMonth))   idYear
,   (select idYear from @years where year(dYearStartDate) = year(EndOfMonth))   idYear
from    months m
left join @years y  on  eomonth(dYearStartDate) = EndOfMonth
order by EndOfMonth

But as can be seen, it's not looking like my expected results:

No Luck

sql-server-2017 tsql
2 Answers

You could maybe try getting the information for each year from the @years table by joining YEAR(@years.dYearStartDate) and YEAR(month.EndOfMonth), like so:

select a.EndOfMonth, b.idYear, b.cYearDescription, b.dYearStartDate
 from (
    select year(m.EndOfMonth) curYear, *
       from months m
     left join @years y  
       on  eomonth(dYearStartDate) = EndOfMonth) a
   inner join (select year(dYearStartDate) curYear, * from @years) b
     on a.curYear = b.curYear
   order by EndOfMonth
user_84825880

The only way I can work around this, was to add an End Date to my table using LEAD, which worked.

With that stated, I am able to query the idYear between these dates as following:

select
*
,       iif(idYear is null,(select idYear from LeadDateAdded where EndOfMonth between dYearStartDate and dYearEndDate),idYear)  idYear
from    months m
left join LeadDateAdded y   on  eomonth(dYearStartDate) = EndOfMonth
order by EndOfMonth

The full and final code looks like this:

declare @years table (idYear int primary key identity, cYearDescription varchar(20), dYearStartDate datetime)

insert into @years (cYearDescription,dYearStartDate) values
    ('Year 1','2014-08-31')
,   ('Year 2','2015-07-01')
,   ('Year 3','2016-07-01')
,   ('Year 4','2017-07-01')
,   ('Year 5','2018-07-01')
,   ('Year 6','2019-07-01')
,   ('Year 7','2020-07-01')


;with cte as
(
select  dateadd(day, nbr - 1, (select min(dYearStartDate) from @years)) CalendarDate
from    ( select    row_number() over ( order by c.object_id ) as nbr
          from      sys.columns c
        ) nbrs
where   nbr - 1 <= datediff(day, (select min(dYearStartDate) from @years), (select max(dYearStartDate) from @years))
)
,   months as (
select
eomonth(CalendarDate)   EndOfMonth
from    cte
group by eomonth(CalendarDate)
)
,   Years as (
select
    idYear
,   cYearDescription
,   dYearStartDate
,   cast(lead(eomonth(dYearStartDate)) over (order by idYear) as datetime)  dYearEndDate
from    @years
)

select
*
,       iif(idYear is null,(select idYear from Years where EndOfMonth between dYearStartDate and dYearEndDate),idYear)  idYear
from    months m
left join Years y   on  eomonth(dYearStartDate) = EndOfMonth
order by EndOfMonth

The results are as following (Last Column is the result I needed):

Results

user_77403870
Related