October 30, 2015 at 11:40 am
Having a problem finding a solution for the following issue:
I have a table that includes the fields below:
ID - INT
Machine - TINYINT
StartTime - DATETIME
EndTime - DATETIME
What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:
ID - 1
Machine - 2
StartTime - 2015-09-01 22:00:00.000
EndTime - 2015-09-03 22:00:00.000
So what I am looking for is taking the above record and turning it into 3 records like below:
ID Machine StartTime EndTime
1 2 2015-09-01 22:00:00.000 2015-09-01 23:59:59.999
1 2 2015-09-02 00:00:00.000 2015-09-02 23:59:59.999
1 2 2015-09-03 00:00:00.000 2015-09-03 22:00:00.000
Does anyone have a way to do this?
Thank you in advance,
Bob
October 30, 2015 at 11:57 am
It's possible by using a calendar table, but it doesn't make sense to duplicate rows when you're not adding anything special. Why do you want to have 3 ranges instead of one? What will you do with those additional rows?
October 30, 2015 at 12:03 pm
Here is what I have so far:
SELECT
prod.ID,
prod.Machine,
prod.StartTime,
DATEADD(dd, DATEDIFF(dd, 0, prod.EndTime), 0)
FROM
prod
WHERE
DATEDIFF(dd, prod.StartTime, prod.EndTime) = 1
UNION ALL
SELECT
prod.ID,
CASE WHEN DATEDIFF(dd, prod.StartTime, prod.EndTime) = 1
THEN DATEADD(dd, DATEDIFF(dd, 0, prod.EndTime), 0)
ELSE prod.StartTime
END,
prod.EndTime
FROM
prod
ORDER BY
prod.ID
This works, unless the number of days between the range is greater than 1.
I need to do this in order to have a way to extract the production data from the database to give me the number of hours per day, per machine that were used in order to get an OEE report.
Thanks again,
Bob
October 30, 2015 at 12:04 pm
also, I will be adding columns to these rows after I get the rows to display.....they will be calculations based on values in other tables.
October 30, 2015 at 12:43 pm
It makes sense to generate reports that show data per date. It won't make sense to generate additional data that will be simply duplicating rows.
Here's an example I did playing with some sample data.
CREATE TABLE #Prod(
ID INT,
Machine TINYINT,
StartTime DATETIME,
EndTime DATETIME);
INSERT INTO #Prod
VALUES( 1, 2, '2015-09-01 22:00:00.000', '2015-09-03 12:00:00.000'),
( 2, 2, '2015-09-03 14:00:00.000', '2015-09-03 22:00:00.000'),
( 3, 3, '2015-09-01 22:00:00.000', '2015-09-03 22:00:00.000'),
( 4, 4, '2015-10-01 22:00:00.000', '2015-09-03 22:00:00.000'),
( 4, 4, '2015-10-01 22:00:00.000', '2015-10-02 22:00:00.000'),
( 5, 5, '2015-09-01 12:00:00.000', '2015-09-01 22:00:00.000');
DECLARE @StartDate date = '20150901',
@EndDate date = '20150902';
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E4
),
cteResult AS(
SELECT Machine,
DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0) AS CalDay,
SUM(CASE --When the range is in the same day
WHEN DATEADD( dd, DATEDIFF( dd, 0, p.StartTime), 0) = DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0)
THEN DATEDIFF( MI, p.StartTime, p.EndTime) / 60.
-- When first day of multiple days
WHEN DATEADD( dd, DATEDIFF( dd, 0, p.StartTime), 0) < DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) AND n = 0
THEN ((60*24) - (DATEPART(HH, p.StartTime) * 60) - DATEPART(MI, p.StartTime)) / 60.
-- When multiple days but not first nor last
WHEN DATEADD( dd, DATEDIFF( dd, 0, p.StartTime), 0) < DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)
AND DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) > DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)
THEN 24
-- When last day of multiple days
WHEN DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) = DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)
THEN ((DATEPART(HH, p.EndTime) * 60) + DATEPART(MI, p.EndTime)) / 60.
END) AS HoursPerDay
FROM #Prod p
JOIN cteTally t ON p.StartTime < DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), -t.n + 1)
--WHERE p.StartTime < DATEADD( dd, 1, @EndDate)
--AND p.EndTime > @StartDate
GROUP BY Machine,
DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)
)
SELECT *
FROM cteResult
--WHERE CalDay BETWEEN @StartDate AND @EndDate
ORDER BY Machine, CalDay
GO
DROP TABLE #Prod
Review the code and ask questions that you might have. Also, read the following article which explains the tally table that is used here: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
EDIT: Corrected the code after CKinley's remark.
October 30, 2015 at 1:27 pm
Thank you, Luis. I have had a chance to look it over. I am comfortable with most of it, but still looking over a few pieces of the code to make sure I understand it. I will comment back when I finish it this weekend.
Have a good weekend,
Bob
November 9, 2015 at 10:37 am
Luis, that seems like a great way to accomplish what nailers is trying to do. I would like to take some of your ideas for what I am trying to accomplish. Thank you for sharing.
I have two concerns, though.
1. I think the result row (3, 2, 2015-09-03 00:00:00.000, 8) should be (3, 2, 2015-09-03 00:00:00.000, 20) because on 2015-09-03, there were 20 hours of machine time. 12 hours from row 1 of #Prod and 8 hours from row 2 of #Prod. It looks like the algorithm didn't grab the 12 hours from row 1.
2. For row 4 of #Prod, you have 2015-10-01 22:00:00.000 as StartTime and 2015-09-03 22:00:00.000 as EndTime. This means you are going backward in time. I think either you meant a different StartTime, or you are demonstrating the algorithm's ability to go back in time. Not sure which one, though.
November 9, 2015 at 10:57 am
You're right, there's an error on the calculation that must be fixed for issue #1.
To fix it, the JOIN condition should look like this:
JOIN cteTally t ON p.StartTime <= DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), -t.n + 1)
The second issue that you mention is just an example on how this would ignore rows where the EndTime is older than the StartTime. I'm not sure if it would validate it correctly if the dates are the same. Of course, this data should be prevented with a CHECK constraint.
November 9, 2015 at 12:39 pm
Ok, thank you for the update, Luis.
November 9, 2015 at 12:45 pm
You're welcome.
Now that I think about it, this might work better as an inline table valued function. I don't have time to test this right now, but it could be worth trying.
November 9, 2015 at 5:03 pm
Just to share a warning... run the following and see what's wrong in the original query...
SELECT CAST('2015-09-01 23:59:59.999' AS DATETIME);
You shouldn't try to "hit the mark" on the end date. Always use the next date and use "<".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2015 at 12:40 pm
Jeff, thanks for that warning. I will look into that.
Luis, I noticed that the HoursPerDay is a little off for the case "When last day of multiple days". It's not off for the example data we have used thus far, but let me insert a new row to show what's going on:
INSERT INTO #Prod(ID, Machine, StartTime, EndTime)
VALUES(6, 6, '2015-11-06 00:00:00', '2015-11-07 06:17:58');
Here is the result set when I run the query now:
MachineCalDay HoursPerDay
22015-09-01 00:00:00.000 2.000000
22015-09-02 00:00:00.000 24.000000
22015-09-03 00:00:00.000 20.000000
32015-09-01 00:00:00.000 2.000000
32015-09-02 00:00:00.000 24.000000
32015-09-03 00:00:00.000 22.000000
42015-10-01 00:00:00.000 2.000000
42015-10-02 00:00:00.000 22.000000
52015-09-01 00:00:00.000 10.000000
62015-11-06 00:00:00.000 24.000000
62015-11-07 00:00:00.000 5.716666
62015-11-08 00:00:00.000 NULL
The HoursPerDay for 2015-11-06 is fine, but it is off for 2015-11-07 (should be 6.283) and 2015-11-08 comes out of nowhere and gives a NULL.
After testing it, I found that the minutes datepart of EndTime is being subtracted from the hours datepart of EndTime:
-- When last day of multiple days
WHEN DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), 0) = DATEADD( dd, DATEDIFF( dd, 0, p.StartTime) + n, 0)
THEN ((DATEPART(HH, p.EndTime) * 60) - DATEPART(MI, p.EndTime)) / 60.
Maybe I don't fully understand the algorithm, but I think that the minutes datepart of EndTime should be added to the hours datepart. When I adjust change the "-" to a "+", here is the result set:
MachineCalDay HoursPerDay
22015-09-01 00:00:00.000 2.000000
22015-09-02 00:00:00.000 24.000000
22015-09-03 00:00:00.000 20.000000
32015-09-01 00:00:00.000 2.000000
32015-09-02 00:00:00.000 24.000000
32015-09-03 00:00:00.000 22.000000
42015-10-01 00:00:00.000 2.000000
42015-10-02 00:00:00.000 22.000000
52015-09-01 00:00:00.000 10.000000
62015-11-06 00:00:00.000 24.000000
62015-11-07 00:00:00.000 6.283333
62015-11-08 00:00:00.000 NULL
So we would add the minutes to the hours, then divide by 60.
The problem with 2015-11-08 coming out of nowhere with a NULL is still a mystery to me, though.
November 12, 2015 at 12:56 pm
That's why it's important to test correctly.
You're right about adding the minutes instead of subtracting them. The NULL value might be there because you're still using the <= comparison.
I updated the code in my original post to correct this issues.
November 12, 2015 at 1:17 pm
You are right about the <= comparison. I was using <= for the join condition, and just changed it to <.
JOIN cteTally t ON p.StartTime < DATEADD( dd, DATEDIFF( dd, 0, p.EndTime), -t.n + 1)
The results look good now:
MachineCalDay HoursPerDay
22015-09-01 00:00:00.000 2.000000
22015-09-02 00:00:00.000 24.000000
22015-09-03 00:00:00.000 20.000000
32015-09-01 00:00:00.000 2.000000
32015-09-02 00:00:00.000 24.000000
32015-09-03 00:00:00.000 22.000000
42015-10-01 00:00:00.000 2.000000
42015-10-02 00:00:00.000 22.000000
52015-09-01 00:00:00.000 10.000000
62015-11-06 00:00:00.000 24.000000
62015-11-07 00:00:00.000 6.283333
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply