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 |
|
sharonmtowler
Starting Member
12 Posts |
 Posted - 2010-04-30 : 07:56:37
|
| i have a stored procedure that i would like to create months in from a date, even if the date doesnt exist in the table.i have cases where the book date ranges from 5/1/2008-current date, there are months where no cases are created. i need to be able to create a dummy record even if the date doesnt exist. if month(date)<>=january then 'january' and year <>'2008' then 2008how can i write soemthing of that nature. sorry if it is confusing, im a bit confused myself. |
|
|
sharonmtowler
Starting Member
12 Posts |
Posted - 2010-04-30 : 12:06:08
|
| my solution was to build a temporary table with the followingcreate TABLE #Temp_mnthyr (Date DateTime, MonthNum INT, MonthName VarChar(20), YearNum INT)INSERT INTO #Temp_mnthyr (Date) Values ('1/1/2006')WHILE (SELECT MAX(Date) FROM #Temp_mnthyr) < DateAdd(yy,1,GetDate())BEGININSERT INTO #Temp_mnthyr (Date) SELECT DateAdd(mm,1,Max(Date)) FROM #Temp_mnthyrENDUPDATE #Temp_mnthyrSET MonthNum = DatePart(mm,Date), MonthName = DateName(mm,Date), YearNum = DatePart(yy,Date)SELECT * FROM #Temp_mnthyr |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
months