I want to display using a Bar Chart, only the equipment that has experienced a 'not ready for startup' condition on both shifts. I track this in a SQL Server table and then look for equipment startup issues common to both shifts.
When I run the code on SSMS I get the desired output, but pasting the exact same code into an Ignition report fails to do the same. The Bar Chart shows the correct count of 3 Qtr Glass incidents but also adds in two other equipment incidents that are not common to both shifts.
I have them both looking 30 days back from now (and in truth I only have a couple weeks of data since I only just started this).
I 'think' the key issue is a lack of some sort of SQL Pivot. The SSMS code produces a table and I'm doing the math in my head (performing a pivot function of sorts) when I look at it to get a count of 3 Qtr Glass incidents whereas the Ignition Report is attempting to do the math for me/pivot (which is perfect) but then adds in a couple other items I don't want.
I know enough SQL to not even be dangerous, otherwise I'd have figured out how to handle my duplicate column name [PrestartMaintTeam], create the sum of common [Equipment] incidents and hand it over to the report. However, I was hoping the report might do it for me.
If this is purely a SQL problem, please let me know and I will delete my question as I don't wish to take up anyone's time with non-Ignition issues.
I know I should be using v8.x but that decision is beyond my control. Any suggestions would be greatly appreciated.
Thank you.
This is the SQL Server code and it outputs exactly what I want.
declare @start datetime = DATEADD(day, -30, getutcdate())
declare @end datetime = getutcdate()
--declare @SheduledShift1 VARCHAR(1)= 'D' -- days
--declare @SheduledShift2 VARCHAR(1)= 'A' -- afternoons
;
--Count Unique Equipment Events by Team
SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
FROM tbl_PrestartRdyScoreboard
WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam <> 'U' and Equipment <> ''
GROUP BY PrestartMaintTeam, Equipment
order by PrestartMaintTeam, Equipment
;
--Count Unique Equipment Events by Team, only listing the ones that appear on both shifts
With
t1 AS
(
SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
FROM tbl_PrestartRdyScoreboard
WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'A' and Equipment <> ''
GROUP BY PrestartMaintTeam, Equipment
),
t2 AS
(
SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
FROM tbl_PrestartRdyScoreboard
WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'B' and Equipment <> ''
GROUP BY PrestartMaintTeam, Equipment
)
SELECT * FROM t1 INNER JOIN t2
ON t1.Equipment = t2.Equipment
ORDER BY t1.Equipment
;This is the Report code that is simply the above SQL Server code pasted into it, and it fails to display what I want on the Bar Chart.
DECLARE @start datetime =?
DECLARE @end datetime = ?
--Count Unique Equipment Events by Team, only listing the ones that appear on both shifts
With
t1 AS
(
SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
FROM tbl_PrestartRdyScoreboard
WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'A' and Equipment <> ''
GROUP BY PrestartMaintTeam, Equipment
),
t2 AS
(
SELECT DISTINCT PrestartMaintTeam, Equipment, count(Equipment) as NumIncidents
FROM tbl_PrestartRdyScoreboard
WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'B' and Equipment <> ''
GROUP BY PrestartMaintTeam, Equipment
)
SELECT * FROM t1 INNER JOIN t2
ON t1.Equipment = t2.Equipment
ORDER BY t1.Equipment
;