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

[Ramesh_Aravabh1]

Angela check if you have it as hsi.itemdata and not his.itemdata

[Angela_Crowley]

Thanks Ramish, it is right in my query. Spellcheck changed it in my post.

[Linda_Hankemeie]

Here is what I used for something just like you are asking.

In this item:  where (i.itemtypenum = '####')  the #### should be your Document Type Number

Some of the Keyvaluechar might be different just based on how your KW was configured.  

HOpe this helps. 

Linda

---------------------------

SELECT

i.itemnum AS 'Doc Handle',

i.itemname AS ' Document Name',

K1.keyvaluechar AS 'Student ID',

K2.keyvaluechar AS 'First Name',

K3.keyvaluechar AS 'Last Name',

K4.keyvaluechar AS 'Address',

K5.keyvaluechar AS 'City',

K6.keyvaluechar AS 'ZIp Code',

K7.keyvaluechar AS 'County -MN',

K8.keyvaluechar AS 'MN Leg District'

FROM hsi.itemdata i

LEFT OUTER JOIN hsi. keyitem742 K1 ON i.itemnum = K1.itemnum

LEFT OUTER JOIN hsi. keyitem175 K2 ON i.itemnum = K2.itemnum

LEFT OUTER JOIN hsi. keyitem176 K3 ON i.itemnum = K3.itemnum

LEFT OUTER JOIN hsi. keyitem137 K4 ON i.itemnum = K4.itemnum

LEFT OUTER JOIN hsi. keyitem624 K5 ON i.itemnum = K5.itemnum

LEFT OUTER JOIN hsi. Keyitem231 K6 ON i.itemnum = K6.itemnum

LEFT OUTER JOIN hsi. keyitem168 K7 ON i.itemnum = K7.itemnum

LEFT OUTER JOIN hsi. keyitem736 K8 ON i.itemnum = K8.itemnu

where (i.itemtypenum = '####')

and (i.status <> '16')

ORDER BY i.itemnum

[Angela_Crowley]

Thanks @Linda. I will take a look. I was trying to use part of this but my Microsoft SQL Server doesn't like the his.itemdata.  I also tried to run part of Tom's below and his mentions his.itemdata but MSSMS says invalid object name.

[Angela_Crowley]

meant hsi.itemdata (darn spellcheck in Communities changed it in my post. LOL