SQL Query Question

Kevin_Goodwin

Hi All,

I have a script that Justin in support helped me with. It is great at showing me the data I need to see, but I am wondering if someone could help me tweak it a bit so that I can have a color differentiation between the CR's and the RR's. Right now the only use I have for the color coordination is the "status" but that isn't very useful for most of the audience that is using this. Any help on this would be much appreciated!

SELECT 

WorkItemId,

Title,

ScheduledStartDate,

ScheduledEndDate,

sds.DisplayString as 'Status',

ids.DisplayString as 'Impact'

FROM 

WorkItem wi

JOIN DisplayString ids

ON ids.ElementID = wi.Impact

and ids.LocaleID = 'enu'

JOIN DisplayString sds

ON sds.ElementID = wi.StatusId

and sds.LocaleID = 'enu'

where 

sds.DisplayString IN ('Closed','In Progress', 'Submitted')

AND (

classId = 'E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C' --change request

or classId = 'D02DC3B6-D709-46F8-CB72-452FA5E082B8' --release record

)

Geoff_Ross

Hi @Kevin_Goodwin

Added a CASE in to bring the Ticket Type back with the data. then you can flip the Event Color Field to Type and choose your colours for each. I hope you choose blue for CR and purple for RR!

SELECT 
	WorkItemId,
	Title,
	ScheduledStartDate,
	ScheduledEndDate,
	sds.DisplayString as 'Status',
	ids.DisplayString as 'Impact',
	CASE
		WHEN WI.ClassId = 'E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C' THEN 'Change Request'
		WHEN WI.ClassId = 'D02DC3B6-D709-46F8-CB72-452FA5E082B8' THEN 'Release Record'
	END AS [Type]
FROM 
WorkItem WI
JOIN DisplayString ids
ON ids.ElementID = WI.Impact
and ids.LocaleID = 'enu'
JOIN DisplayString sds
ON sds.ElementID = WI.StatusId
and sds.LocaleID = 'enu'
where 
sds.DisplayString IN ('Closed','In Progress', 'Submitted')
AND (
	WI.ClassId = 'E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C' --change request
	or WI.ClassId = 'D02DC3B6-D709-46F8-CB72-452FA5E082B8' --release record
)

Brett_Moffett

Hi @Kevin_Goodwin

I think it is all going to come down to how you want to display the information.

What sort of graph are you using to show the data?

If you are wanting to show the difference in Closed and In Progress items then what you have is perfect.

If you are wanting to show the count of CRs verses RRs then we could change the SQL to return just the CRs and RRs that meet the criteria and you can split them in to their type.

If you want a combination of CRs that are Closed or In Progress and RRs that are Closed and In Progress, that gets a little tricky as there are two data sets in the one graph. We are working on a graph widget that will show two data sources, but it is not ready for release just yet.

Describe what you want to analyse (Rather than the specific data) and I'll see if I can assist you in getting the result you are after.

Kevin_Goodwin

Hi @Brett,

Sorry I left out some details on that one. I am currently using this query to populate a SQL Calendar. I do want to have both CR's and RR's on the same calendar, and this query does that very nicely. The only thing I want to adjust is the ability to have the colors differentiate the work item type instead of the status. Does that help?

Geoff_Ross

Hi @Kevin_Goodwin

Added a CASE in to bring the Ticket Type back with the data. then you can flip the Event Color Field to Type and choose your colours for each. I hope you choose blue for CR and purple for RR!

SELECT 
	WorkItemId,
	Title,
	ScheduledStartDate,
	ScheduledEndDate,
	sds.DisplayString as 'Status',
	ids.DisplayString as 'Impact',
	CASE
		WHEN WI.ClassId = 'E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C' THEN 'Change Request'
		WHEN WI.ClassId = 'D02DC3B6-D709-46F8-CB72-452FA5E082B8' THEN 'Release Record'
	END AS [Type]
FROM 
WorkItem WI
JOIN DisplayString ids
ON ids.ElementID = WI.Impact
and ids.LocaleID = 'enu'
JOIN DisplayString sds
ON sds.ElementID = WI.StatusId
and sds.LocaleID = 'enu'
where 
sds.DisplayString IN ('Closed','In Progress', 'Submitted')
AND (
	WI.ClassId = 'E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C' --change request
	or WI.ClassId = 'D02DC3B6-D709-46F8-CB72-452FA5E082B8' --release record
)

Kevin_Goodwin

That worked great @Geoff_Ross, thank you!