May 3, 2012 at 5:28 am
I am trying to set up a table that will enable me to run queries to update a number of fields but am struggling with my start point. I need to populate a table with several blank fields but with a line each for combination of portfolio name and report month. The starting report month will be the acquisition date and I need an individual line for each report month up to today's date.
e.g.
Portolio 1 has an acquisition date of 1st Jan 2011. Therefore there should be a line for each of the months from 1st Jan 2011 until 1st May 2012.
Porfolio 2 has an acquisition date of 1st May 2011. Therefore there should be a line for each of the months from 1st May 2011 until 1st May 2012.
Anyone know a straightforward way to do this?
Thanks
Matt
May 3, 2012 at 5:56 am
Yes, you can:
declare @Portolio table (Id int, AcquisitionDate date)
insert @Portolio select 1, '1 Jan 2011'
insert @Portolio select 2, '1 May 2011'
insert @Portolio select 3, '15 Apr 2011'
select p.Id, p.AcquisitionDate, MM.mon
from @Portolio p
join (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'20090101') mon
FROM sys.columns) MM
on mm.mon >= p.AcquisitionDate
where mm.mon <= GETDATE()
order by 1,2,3
Do you want to see the record for the month if the Acquisition date is in the middle of this month as per portfolio with id 3?
If so, you will need to calculate beginning of the month for Acquisition date and join on it:
declare @Portolio table (Id int, AcquisitionDate date)
insert @Portolio select 1, '1 Jan 2011'
insert @Portolio select 2, '1 May 2011'
insert @Portolio select 3, '15 Apr 2011'
select p.Id, p.AcquisitionDate, MM.mon
from @Portolio p
join (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'20090101') mon
FROM sys.columns) MM
on mm.mon >= DATEADD(dd,-(DAY(AcquisitionDate)-1),AcquisitionDate)
where mm.mon <= GETDATE()
order by 1,2,3
May 3, 2012 at 6:04 am
Try:
with CTE as
(
select PortfolioId, AcquisitionDate from MyTable
union all
select PortfolioId, DATEADD(month, 1, AcquisitionDate) as AcquisitionDate
from CTE
where DATEADD(month, 1, AcquisitionDate) <= GETDATE()
)
Select * from CTE
Hope this helps.
May 3, 2012 at 6:42 am
imex (5/3/2012)
Try:
with CTE as(
select PortfolioId, AcquisitionDate from MyTable
union all
select PortfolioId, DATEADD(month, 1, AcquisitionDate) as AcquisitionDate
from CTE
where DATEADD(month, 1, AcquisitionDate) <= GETDATE()
)
Select * from CTE
Hope this helps.
What about if Acquisition did Happen 1 Dec 2003?
Also, if the dataset is large, the performance of it will not be as good as for few rows...
May 3, 2012 at 7:29 am
Perfect Eugene! Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply