Thursday, 8 August 2013

JIRA db query data convert error

JIRA db query data convert error

Please help me with this query against a jira db, I keep getting the error
'Error converting data type nvarchar to numeric.'. I have looked around
and seen other instances of this against a jira db, but the examples and
help posted there did not help me resolve this problem.
DECLARE @startdate datetime = '2013-07-01'
DECLARE @enddate datetime = '2013-08-01'
SELECT * FROM
(
SELECT
pkey
, SUMMARY
, CASE WHEN cfv.customfield = 14800 THEN cfv.customvalue ELSE '' END
AS [Department]
, first_name +' '+ last_name AS [EMPLOYEE]
, timeworked/3600 as timeworked
FROM [JiraDB].[jiraschema].[worklog] wl
JOIN [JiraDB].[jiraschema].[jiraissue] ji on wl.issueid = ji.ID
JOIN [JiraDB].[jiraschema].[cwd_user] u on wl.Author = u.[user_name]
--JOIN [JiraDB].[jiraschema].[customfieldvalue] cfv on cfv.issue = wl.ID
--JOIN [JiraDB].[jiraschema].[customfieldoption] cfo on
cfv.stringvalue = cfo.id
where startdate >= @startdate
and startdate < @enddate
) AS logs
PIVOT
(
SUM(timeworked)
FOR [EMPLOYEE] IN (
--list all current users
)
) AS piv
ORDER BY pkey

No comments:

Post a Comment