Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --table must have at least 10000 rows, so that it doesn't get lost in NULLs. I think.
- DECLARE @table_name sysname, @date_column sysname, @sql NVARCHAR(4000), @lowest_date VARCHAR(20)
- SET @table_name = 'mytablename'
- SET @date_column = 'mydatefield'
- SET @lowest_date = '20150415'
- SELECT @sql = '
- DECLARE @tempdate DATETIME, @target_id bigint, @max_id bigint
- SELECT @target_id = MAX(id) FROM ' + @table_name + ' with (NOLOCK)
- set @max_id = @target_id
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 10000000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SELECT @target_id = @target_id + 10000000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 1000000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SELECT @target_id = @target_id + 1000000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 100000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SELECT @target_id = @target_id + 100000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 10000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SELECT @target_id = @target_id + 10000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 1000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SELECT @target_id = @target_id + 1000
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 100
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SELECT @target_id = @target_id + 100
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 10
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SELECT @target_id = @target_id + 10
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- WHILE @tempdate >''' + @lowest_date + '''
- BEGIN
- SELECT @target_id = @target_id - 1
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- IF @@ROWCOUNT = 0 SET @tempdate = ''2000/01/01''
- END
- SET @target_id = @target_id + 1
- SELECT @tempdate = ' + @date_column + ' FROM ' + @table_name + ' with (NOLOCK) WHERE id = @target_id
- SELECT @max_id as max_id, @target_id as lowest_id_for_date, @tempdate as value_for_LowID'
- EXEC dbo.sp_executesql @sql
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement