I need to build a table that I'll use for a table based transformation.. Fort instance based on todays date I would need to have all of the dates returned from the first of the year up until today...
So the table data would look something like this..
FiscalMonth FiscalMonthYTD FiscalMonthLYYTD
201304 201301 201201
201304 201302 201202
201304 201303 201203
201304 201304 201204
201305 201301 201201
201305 201302 201202
201305 201303 201203
201305 201304 201204
201305 201305 201205
So here's how I'll be using this data, I'll link to the FiscalMonth column to my transaction date and from the FiscalMonthYTD I'll get the YTD months.. So my problem is I need a query to populate the transformation date... I've done this before but the way I did it is escaping me..
Check the following blog for generating date sequences:
Look at the following example: SQL date sequence - SQL Server date range generator - datetime sequence
Kalman Toth Database & OLAP Architect Free T-SQL Scripts
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012