SELECT
CAST((SUM(CASE WHEN Status = 1 THEN DATEDIFF(SECOND, t_stamp, ISNULL(Next_t_stamp, SYSDATETIME())) ELSE 0 END) * 100.0) /
SUM(DATEDIFF(SECOND, t_stamp, ISNULL(Next_t_stamp, SYSDATETIME()))) AS DECIMAL(5,2)) AS PercentageTimeInStatus1
FROM
(SELECT
Machine,
Status,
t_stamp,
LEAD(t_stamp) OVER (PARTITION BY Machine ORDER BY t_stamp) AS Next_t_stamp
FROM
[Ignitioncw].[dbo].[Machine_Statuses]
WHERE
Machine = 'Palletizer 1'
AND t_stamp >= DATEADD(MINUTE, 330, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME()))) -- Today 5:30 AM
AND t_stamp < DATEADD(MINUTE, 330, DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME())))) -- Tomorrow 5:30 AM
) AS StatusDurations
The indented code may make it easier to see what's going on.
Indented query for legibility
SELECT
CAST(
(SUM(
CASE
WHEN Status = 1
THEN
DATEDIFF(
SECOND,
t_stamp,
ISNULL(Next_t_stamp, SYSDATETIME())
)
ELSE 0
END
) * 100.0)
/
SUM(
DATEDIFF(
SECOND,
t_stamp,
ISNULL(Next_t_stamp, SYSDATETIME())
)
) AS DECIMAL(5,2)
) AS PercentageTimeInStatus1
FROM
(SELECT
Machine,
Status,
t_stamp,
LEAD(t_stamp) OVER (PARTITION BY Machine ORDER BY t_stamp) AS Next_t_stamp
FROM
[Ignitioncw].[dbo].[Machine_Statuses]
WHERE
Machine = 'Palletizer 1'
AND t_stamp >= DATEADD(MINUTE, 330, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME()))) -- Today 5:30 AM
AND t_stamp < DATEADD(MINUTE, 330, DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME())))) -- Tomorrow 5:30 AM
) AS StatusDurations
I'm not clear what this query is doing. You're only returning one column, PercentageTimeInStatus1. Where are you using the result?
I am running a query against the PLC capture all of the statuses for the palletizer. So it looks at all of them and use total time in status 1 divided by the total to get a percentage uptime for that piece of equipment.
It starts at 5:30 so the operators can watch the uptime through out the day.
If i just run the data it shows all times for the 24 hour period but when I use it in the chart is where it does the gapping.