code instead of manual changes

[andycape]

I have the following piece of code that needs the last month added manually each month when it is run.
I'm sure there must be a way to do this automatically ?

Insert into Migration
Select h.PersonNo,
	   'May99' = '',
	   'Jun99' = '',
	   'Jul99' = '',
	   'Aug99' = '',
	   'Sep99' = '',
	   'Oct99' = '',
	   'Nov99' = '',
	   'Dec99' = '',
	   'Jan00' = '',
	   'Feb00' = '',
	   'Mar00' = '',
	   'Apr00' = '',
	   'May00' = '',
	   'Jun00' = '',
	   'Jul00' = '',
	   'Aug00' = '',
	   'Sep00' = '',
	   'Oct00' = '',
	   'Nov00' = '',
	   'Dec00' = '',
	   'Jan01' = '',
	   'Feb01' = '',
	   'Mar01' = '',
	   'Apr01' = '',
	   'May01' = '',
	   'Jun01' = '',
	   'Jul01' = '',
	   'Aug01' = '',
	   'Sep01' = '',
	   'Oct01' = '',
	   'Nov01' = '',
	   'Dec01' = '',
	   'Jan02' = '',
	   'Feb02' = '',
	   'Mar02' = '',
	   'Apr02' = '',
	   'May02' = '',
	   'Jun02' = '',
	   'Jul02' = '',
	   'Aug02' = '',
	   'Sep02' = '',
	   'Oct02' = '',
	   'Nov02' = '',
	   'Dec02' = '',
	   'Jan03' = '',
	   'Feb03' = '',
	   'Mar03' = '',
	   'Apr03' = '',
	   'May03' = '',
	   'Jun03' = '',
	   'Jul03' = '',
	   'Aug03' = '',
	   'Sep03' = '',
	   'Oct03' = '',
	   'Nov03' = '',
	   'Dec03' = '',  
	   'Jan04' = '',		
	   'Feb04' = '',
	   'Mar04' = '',
	   'Apr04' = h.Grp  --// Roll this down every month
from HC_Segment	h

The last month is always 'last month' (ie if its May now, then its April)

There must be a better way of writing the above, using the logic :
"Select all months + year from May99 to last month, make all = '' except last month =h.Grp" ??

Any ideas ?

 

[nigelrivett]

My first idea is to not do it like this.
It's dubious table design and will eventually not work due to the size of the row.
Better to normalise it.

Why not put a default on each of the columns of '' and just insert the PersonNo. Then it just becomes a matter of adding the column to the table rather than changing the insert command.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[NathanGriffiths]

As Nigel says this doesn't seem like a good way to do this. However, if you still need the code, this will dynamically generate the SELECT statement based on the current date;

SET DATEFORMAT ymd

DECLARE @Date datetime,
	@EndDate datetime,
	@sql varchar(4000)

SET @Date = Convert(DateTime, '1999-05-01')
SET @EndDate = GetDate()
SET @sql = 'SELECT h.PersonNo'

WHILE DatePart(year, @Date) <> DatePart(year, @EndDate) OR DatePart(month, @Date) <> DatePart(month, @EndDate)
	BEGIN
		SET @sql = @sql + ', ' + char(39) + DateName(month, @Date) + substring(DateName(year, @Date), 3, 2) + ''' = ' + char(39) + char(39)
		SET @Date = DateAdd(month, 1, @Date)
	END

SET @sql = left(@sql, len(@sql) - 3) + 'h.Grp FROM HC_Segment h'

PRINT @sql

Hope that helps,



Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------