Stacking 2 bar charts into 1 stacked chart

Grafana MSSQL
1

Hi There,

I have 2 bar charts in Grafana which show 2 sets of data from the same table but i want to stack them into one chart.

The code for each is follows:
Unbillable chart:
SELECT
u.First_Name,
SUM(t.Duration_Hours) AS Total_Duration_Hours
FROM
vw_Case_Timeline AS t
INNER JOIN
Internal_Users AS u ON t.Staff_ID = u.ID
WHERE
t.Start_Date BETWEEN $__timeFrom() AND $__timeTo() AND
(
Process_Type = ‘Unbillable’
OR Process_Type = ‘Training’
OR Process_Type = ‘Validation/Calibration’
OR Process_Type = ‘Other’
)
GROUP BY
u.First_Name
ORDER BY
Total_Duration_Hours DESC; – Optional, to order by duration

Billable:
SELECT
u.First_Name,
SUM(t.Duration_Hours) AS Total_Duration_Hours
FROM
vw_Case_Timeline AS t
INNER JOIN
Internal_Users AS u ON t.Staff_ID = u.ID
WHERE
t.Start_Date BETWEEN $__timeFrom() AND $__timeTo() AND
(
Process_Type <> ‘Unbillable’
OR Process_Type <> ‘Training’
OR Process_Type <> ‘Validation/Calibration’
OR Process_Type <> ‘Other’
)
GROUP BY
u.First_Name
ORDER BY
Total_Duration_Hours DESC; – Optional, to order by duration

Anyone have any idea?

Grefana Chart
Grefana Chart1907×304 33.9 KB

2

how do you feel about a union?

SELECT
u.First_Name,
SUM(t.Duration_Hours) AS Total_Billable_Hours
FROM
vw_Case_Timeline AS t
INNER JOIN
Internal_Users AS u ON t.Staff_ID = u.ID
WHERE
t.Start_Date BETWEEN $__timeFrom() AND $__timeTo() AND
(
Process_Type = ‘Unbillable’
OR Process_Type = ‘Training’
OR Process_Type = ‘Validation/Calibration’
OR Process_Type = ‘Other’
)
GROUP BY
u.First_Name
ORDER BY
Total_Duration_Hours DESC
union
SELECT
u.First_Name,
SUM(t.Duration_Hours) AS Total_Non_Billable_Hours
FROM
vw_Case_Timeline AS t
INNER JOIN
Internal_Users AS u ON t.Staff_ID = u.ID
WHERE
t.Start_Date BETWEEN $__timeFrom() AND $__timeTo() AND
(
Process_Type <> ‘Unbillable’
OR Process_Type <> ‘Training’
OR Process_Type <> ‘Validation/Calibration’
OR Process_Type <> ‘Other’
)
GROUP BY
u.First_Name
ORDER BY
Total_Duration_Hours DESC;

3

I did try a union and it kept hitting an error.

I have been able to get this work since, not sure how lol

SELECT
u.First_Name,
SUM(CASE WHEN Process_Type NOT IN (‘Unbillable’, ‘Training’, ‘Validation/Calibration’, ‘Other’) THEN t.Duration_Hours ELSE 0 END) AS Total_Billable_Duration_Hours,
SUM(CASE WHEN Process_Type IN (‘Unbillable’, ‘Training’, ‘Validation/Calibration’, ‘Other’) THEN t.Duration_Hours ELSE 0 END) AS Total_Non_Billable_Duration_Hours
FROM
vw_Case_Timeline AS t
INNER JOIN
Internal_Users AS u ON t.Staff_ID = u.ID
WHERE
t.Start_Date BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY
u.First_Name
ORDER BY
Total_Billable_Duration_Hours DESC, Total_Non_Billable_Duration_Hours DESC;

1 Like