Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Creating a row for each month

Author  Topic 

singularity
Posting Yak Master

153 Posts

Posted - 2013-01-24 : 11:26:35
I have data as follows:

assessment_date
2/29/2012
9/11/2012
10/17/2012

I need to create a row for every month based on the latest assessment_date as of that month:

assessment_date month
2/29/2012 2/29/2012
2/29/2012 3/31/2012
2/29/2012 4/30/2012
2/29/2012 5/31/2012
2/29/2012 6/30/2012
2/29/2012 7/31/2012
2/29/2012 8/31/2012
9/11/2012 9/30/2012
10/17/2012 10/31/2012
10/17/2012 11/30/2012
10/17/2012 12/31/2012

I have a calendar table but can't figure out how to achieve this result.

Thanks in advance.












James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-24 : 12:39:02
[code]select
b.assessment_date,
a.date_month
from
calendar a
outer apply
(
select top (1) b.assessment_date
from assessment_table b
where b.assessment_date <= a.date_month
order by b.assessment_date desc
)b[/code]
Go to Top of Page

singularity
Posting Yak Master

153 Posts

Posted - 2013-01-24 : 18:16:52
Thank you, that worked great. Just out of curiosity, is there any way to achieve the same result without using APPLY?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 01:56:41
[code]
select *
from CalendarTable f
INNER JOIN assessment_table t
ON t.assessment_date = (SELECT TOP 1 assessment_date
FROM assessment_table
WHERE assessment_date<=f.[Date]
ORDER BY assessment_date DESC)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

singularity
Posting Yak Master

153 Posts

Posted - 2013-01-25 : 21:38:21
Thank you, James and Visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 00:37:24
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -