![]() ![]() RETURN CAST(ROUND(DATEDIFF(SECOND, / 60.0, 0) AS INT) WHEN LEFT(CONVERT(VARCHAR, StartDate, 120), 10) = LEFT(CONVERT(VARCHAR, EndDate, 120), 10) = dbo.CalculateReportDuration(StartDate, EndDate)ĬONVERT(Date, StartDate) = CONVERT(Date, EndDate) = ALLĭbo.CalculateReportDuration( LEFT(CONVERT(VARCHAR, EndDate, 120), 10) + ' 00:00:00.0000000 +00:00', EndDate) as DurationĬONVERT(Date, EndDate) = CONVERT(Date, StartDate) CONVERT(Date, StartDate) = I want to know is does anyone have any better ideas? Ideally I’d like to create view but currently I'm relying on an SQL variable to hold a date value that I was going to increment all dates in the table using a while loop.Ĭode for CalculateReportDuration function USE ![]() I have come up with a fairly messy solution, I was going to use this as a basis to create a scheduled stored procedure that outputs the processed data to a separate table. Here's a link to an Excel file that contains sample data The problem is a state can start late one day and end in the early hours of the next which making it difficult to calculate state duration per day. I want to total up the duration of these states for each machine per day to determine their efficiency.Įach state is given a start and end time I initially thought it would be a simple case of calculating the difference between the two dates but its not that simple. The monitoring software records the duration the machines are in different states like machining, stopped, out of work setup etc. I'm having some difficulty with an SQL query for reporting machine efficiency from a CNC monitoring system. ![]()
0 Comments
Leave a Reply. |