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.
Author |
Topic |
singularity
Posting Yak Master
153 Posts |
Posted - 2013-01-24 : 11:26:35
|
I have data as follows:assessment_date2/29/20129/11/201210/17/2012I need to create a row for every month based on the latest assessment_date as of that month:assessment_date month2/29/2012 2/29/20122/29/2012 3/31/20122/29/2012 4/30/20122/29/2012 5/31/20122/29/2012 6/30/20122/29/2012 7/31/20122/29/2012 8/31/20129/11/2012 9/30/201210/17/2012 10/31/201210/17/2012 11/30/201210/17/2012 12/31/2012I 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_monthfrom 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] |
|
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 01:56:41
|
[code]select *from CalendarTable fINNER JOIN assessment_table tON t.assessment_date = (SELECT TOP 1 assessment_date FROM assessment_table WHERE assessment_date<=f.[Date] ORDER BY assessment_date DESC)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
singularity
Posting Yak Master
153 Posts |
Posted - 2013-01-25 : 21:38:21
|
Thank you, James and Visakh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-26 : 00:37:24
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|