SQL Query that gives me all keywords, including datestored for 1 particular doc type

[Angela_Crowley]

I am looking for some help with a SQL query that would give me all keywords, including datestored (since it has time) for 1 particular doc type. Currently my user is using Generate CSV file which has worked great but it doesn't give the time that it came in. She needs a query that gives all of her keywords, date and time. Ideally date and time would be two separate columns but I can handle them in one to at least get the data out. Once I have that she also needs to know when she is using the ad hoc tasks (to approve, deny or delete which is basically changing to status keyword #741) in the workflow for this particular doc type. This doc type is a Unity form and is the only doc type in the doc type group (#122). If anyone can help, that would be great. I have this so far which does give me my datestored info but it doesn't give me any keywords (and there are 25 of them). I am thinking that maybe I need to join each keyword?

Here is what I have so far:

select * from hsi.itemdata

where itemtypegroupnum = 122

[Angela_Crowley]

I got it to work, thank you so much Linda for your help getting started!

[Linda_Hankemeie]

Sorry I missed the time part.  You can add

i.datestored AS 'Date Stored',

[Chris_Breisch]

I know that I'm a bit late to the party, but this query will generate SQL statements for all doctypes in your system. I include a where clause at the end "where itemnum = 0" just so you don't accidentally run a query that will take years to complete. 🙂 You'll want to change that to the itemnums that you're interested in, or you can eliminate the where clause if you're not worried about retrieving too many items.

set nocount onif object_id('tempdb..#doctypes') is not null drop table #doctypescreate table #doctypes(	itemtypenum bigint not null,--	itemtypename varchar(66) not null,	keytypenum bigint not null)-- get a list of document types and their associated keyword typesinsert into #doctypes(itemtypenum,/* itemtypename,*/ keytypenum)	select		dt.itemtypenum,		--rtrim(dt.itemtypename),		itk.keytypenum		from			hsi.doctype dt			inner join hsi.itemtypexkeyword itk on dt.itemtypenum = itk.itemtypenumif object_id('tempdb..#keywords') is not null drop table #keywordscreate table #keywords(	keytypenum bigint not null primary key,	keyword varchar(51) not null,	--datatype bigint not null,	columnExpression nvarchar(500) not null,	columnAlias nvarchar(100) not null,	joinStatement nvarchar(500) not null,	cteExpression nvarchar(1000) null)-- get the column expressions and join statements necessary for each keyword type;with KeywordTable(keytypenum, tableName, tableAlias, keyword, datatype)as(	select		ktt.keytypenum,		'hsi.keyitem' + cast(ktt.keytypenum as varchar(3)) as tableName,		'ki' + + cast(ktt.keytypenum as varchar(3)) as tableAlias,		ktt.keytype as keyword,		ktt.datatype		from			hsi.keytypetable ktt)insert into #keywords(keytypenum, keyword,/* datatype,*/ columnExpression, columnAlias, joinStatement, cteExpression)	select		kt.keytypenum,		kt.keyword,		case 			when kt.datatype = 1 then kt.tableAlias + '.keyvaluebig'			when kt.datatype = 2 then 'RTRIM(cte' + cast(kt.keytypenum as varchar(3)) + '.keyvaluechar)' 			when kt.datatype = 3 then kt.tableAlias + '.keyvaluecurr' 			when kt.datatype = 4 then kt.tableAlias + '.keyvaluedate' 			when kt.datatype = 5 then kt.tableAlias + '.keyvaluefloat' 			when kt.datatype = 6 then kt.tableAlias + '.keyvaluesmall'			when kt.datatype = 9 then kt.tableAlias + '.keyvaluetod'			when kt.datatype = 10 then 'RTRIM(' + kt.tableAlias + '.keyvaluechar)' 			when kt.datatype = 11 then kt.tableAlias + '.keyvaluecurr'			when kt.datatype = 12 then 'RTRIM(cte' + cast(kt.keytypenum as varchar(3)) + '.keyvaluecharcs)' 			when kt.datatype = 13 then 'RTRIM(' + kt.tableAlias + '.keyvaluecharcs)'			else cast (kt.datatype as varchar(20))		end,		'['+ RTRIM(kt.keyword) + ']',		case 			when kt.datatype in (2, 12) then ' left join cte' + cast(kt.keytypenum as varchar(3)) + ' on id.itemnum = cte' + cast(kt.keytypenum as varchar(3)) + '.itemnum'			else ' left join '+ kt.tableName + ' as ' + kt.tableAlias + ' on id.itemnum = ' + kt.tableAlias + '.itemnum'		end,		case 			when kt.datatype in (2, 12) then 			'cte' + cast(kt.keytypenum as varchar(3)) + 			' as (select kxi' + cast(kt.keytypenum as varchar(3)) + '.itemnum, stuff((select '', '' + rtrim(kt' + cast(kt.keytypenum as varchar(3)) +'.keyvaluechar) [text()] ' +			' from hsi.keyxitem' + cast(kt.keytypenum as varchar(3)) + ' ikxi' + cast(kt.keytypenum as varchar(3)) +			' inner join hsi.keytable' + cast(kt.keytypenum as varchar(3)) + ' kt' + cast(kt.keytypenum as varchar(3)) + ' on ikxi' + cast(kt.keytypenum as varchar(3)) + '.keywordnum = kt' + cast(kt.keytypenum as varchar(3)) + '.keywordnum' +			' where ikxi' + cast(kt.keytypenum as varchar(3)) + '.itemnum = kxi' + cast(kt.keytypenum as varchar(3)) + '.itemnum' +			' for xml path (''''), type) ' +			'.value(''.'',''nvarchar(max)''),1,2,'' '') keyvaluechar' + 			' from hsi.keyxitem' + cast(kt.keytypenum as varchar(3)) + ' kxi' + cast(kt.keytypenum as varchar(3)) +			' group by itemnum)'			else null		end		from			KeywordTable kt--select--	*--	from--		#doctypes dt--		inner join #keywords kw--			on dt.keytypenum = kw.keytypenum--	order by--		dt.itemtypenum,--		kw.keyworddeclare @itemnum bigintdeclare @itemtypenum bigintif object_id('tempdb..#sqlByDocType') is not null drop table #sqlByDocTypecreate table #sqlByDocType(	itemtypenum bigint not null,	sqlStatement nvarchar(max))-- loop through all the document types that have this keyword and keyword valuedeclare docTypeList cursor for	select		itemtypenum		from			#doctypes dt		group by			dt.itemtypenumopen docTypeListfetch next	from docTypeList	into @itemtypenumwhile @@FETCH_STATUS = 0begin	declare @ctes nvarchar(max) = ''	declare @selectStatement nvarchar(max) = 'select id.itemnum, rtrim(dt.itemtypename) as [Document Type], lc.lifecyclename as [Life Cycle], lcs.statename as [Queue]'	declare @fromClause nvarchar(max) = ' from hsi.itemdata id inner join hsi.doctype dt on id.itemtypenum = dt.itemtypenum left join hsi.workitemlc wilc on id.itemnum = wilc.contentnum left join hsi.lcstate lcs on wilc.statenum = lcs.statenum left join hsi.lifecycle lc on wilc.lcnum = lc.lcnum '	declare @joinStatement nvarchar(500)	declare @columnExpression nvarchar(500)	declare @cteExpression nvarchar(1000)	-- loop through the keywords and get the necessary column expressions and join statements	declare sqlBuilder cursor for		select			',' + kw.columnExpression + ' ' + kw.columnAlias,			kw.joinStatement,			kw.cteExpression			from				#doctypes dt				inner join #keywords kw					on dt.keytypenum = kw.keytypenum			where				dt.itemtypenum = @itemtypenum			group by				kw.columnExpression, 				kw.columnAlias,				kw.joinStatement,				kw.cteExpression	open sqlBuilder	fetch next		from sqlBuilder		into @columnExpression, @joinStatement, @cteExpression	declare @haveCtes bit = 0	while @@FETCH_STATUS = 0	begin		if @cteExpression is not null		begin			if @haveCtes = 0				set @ctes = '; with '			else				set @ctes = @ctes + ', '			set @ctes = @ctes + @cteExpression			set @haveCtes = 1		end		-- add each column to the select statement		set @selectStatement = @selectStatement + @columnExpression		-- add each join to the from clause		set @fromClause = @fromClause + @joinStatement		fetch next			from sqlBuilder			into @columnExpression, @joinStatement, @cteExpression	end	close sqlBuilder	deallocate sqlBuilder	-- set up the final sql statement to execute with a dummy where clause	insert into #sqlByDocType(itemtypenum, sqlStatement)		select @itemtypenum, @ctes + @selectStatement + @fromClause + ' where id.itemnum = 0'	fetch next		from docTypeList		into @itemtypenumendclose docTypeListdeallocate docTypeListselect * from #sqlByDocType order by 1

[Damian_Grabowsk]

That's a solid piece of work! Good job!