June 26, 2015 at 11:10 am
THis is my code.. how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
declare columncursor CURSOR FOR
SELECT ColumnName FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
open columncursor
FETCH NEXT FROM columncursor into @CNAME
WHILE @@FETCH_STATUS = 0
BEGIN
update #temptable
set earliestdate = (select top 1 @CNAME from eddsdbo.Document),
mostrecentdate = (select top 1 @CNAME from eddsdbo.Document)
where @CNAME IS NOT NULL and @cname = #temptable.columname
FETCH NEXT from columncursor INTO @CNAME
END
CLOSE columncursor
DEALLOCATE columncursor
select columname, columnheader, earliestdate, mostrecentdate from #temptable
drop table #temptable
June 26, 2015 at 1:06 pm
taseedorf (6/26/2015)
THis is my code.. how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
declare columncursor CURSOR FOR
SELECT ColumnName FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
open columncursor
FETCH NEXT FROM columncursor into @CNAME
WHILE @@FETCH_STATUS = 0
BEGIN
update #temptable
set earliestdate = (select top 1 @CNAME from eddsdbo.Document),
mostrecentdate = (select top 1 @CNAME from eddsdbo.Document)
where @CNAME IS NOT NULL and @cname = #temptable.columname
FETCH NEXT from columncursor INTO @CNAME
END
CLOSE columncursor
DEALLOCATE columncursor
select columname, columnheader, earliestdate, mostrecentdate from #temptable
drop table #temptable
That's going to depend on what date you want to pull, and from where. Also, while I haven't tried it, for some non-apparent reason, you have a column destined for the temp table that has no column name, and it's value is always just a comma and a space, and that lack of a column name will probably fail the query. If you could post a CREATE statement for the original source table and some sample data for it, in addition to your existing code, that would be helpful as well. The primary question that needs answering is why the cursor query isn't grabbing any of the date columns. Also wondering why the date columns are varchar(120), as that seems excessive for a field that will hold a date value, where all that's probably necessary is 10, maybe 21 if you have to include the day of the week spelled out, and 29 if you also need the month spelled out.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 1:12 pm
i think i see what you want, and you'll need to use dynamic SQL
build a complete update command, and execute it inside the cursor.
declare @isql varchar(max)
SELECT @isql ='update #temptable
set earliestdate = (select top 1 ' + @CNAME + ' from eddsdbo.Document),
mostrecentdate = (select top 1 ' + @CNAME + ' from eddsdbo.Document)
where ' + @CNAME + ' IS NOT NULL and ' + @CNAME + ' = #temptable.columname'
PRINT @isql
EXEC(@isql)
Lowell
June 26, 2015 at 4:02 pm
Thanks for the advice - this is what I ended up using that worked (in case it helped anyone else) I basically had to take a list of columns, and run a query for the max and min date in each column... if there is a better way please post or if this is helpful, this is what I did
create table #temptable (columnname varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
--insert into #temptable
--SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
DECLARE @sqlText nvarchar(1000)
declare @isql varchar(max)
declare columncursor CURSOR FOR
SELECT ColumnName FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
open columncursor
FETCH NEXT FROM columncursor into @CNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlText = '''' + @CNAME + ''''
SELECT @isql ='insert into #temptable select ' + @sqlText + ', min(' + @CNAME + '), max(' + @CNAME + ') from eddsdbo.Document where ' + @CNAME + ' IS NOT NULL'
--PRINT @sqltext
--PRINT @isql
EXEC(@isql)
FETCH NEXT from columncursor INTO @CNAME
END
CLOSE columncursor
DEALLOCATE columncursor
select columnname, earliestdate, mostrecentdate from #temptable
drop table #temptable
June 29, 2015 at 7:58 am
You can actually do this without CURSORs or temp tables.
DECLARE @sqlText nvarchar(1000)
SELECT @sqlText = 'SELECT c.ColumnName, MIN(dt) AS EarliestDate, MAX(dt) AS MostRecentDate FROM eddsdbo.Document CROSS APPLY ('
+ STUFF((
SELECT 'UNION SELECT ' + QUOTENAME(ColumnName, '''') + ', ' + QUOTENAME(ColumnName) + ' '
FROM eddsdbo.[ArtifactViewField]
WHERE ItemListType = 'DateTime'
AND ArtifactTypeID = 10
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(1000)')
, 1
, 6
, ''
)
+ ') AS c(columnname, dt) GROUP BY c.ColumnName'
EXEC(@sqlText)
You should be aware that this opens you up to SQL injection. I used QUOTENAME() to add quotes or square brackets around the column name, because it will automatically handle columns with quotes or brackets in the name. You may also want to join your source table to INFORMATION_SCHEMA.COLUMNS to ensure that the column does exist in that table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply