June 15, 2005 at 12:34 pm
I wanted to take the speed of a machine (parts per hour) and divide this by how many parts remain to get a time in hh:mm.
How can this be done in SQL? Is there a convert statement to do this?
Regards
Bob
June 15, 2005 at 1:04 pm
GETDATE() and DATEDIFF should help you do your job
Vasc
June 15, 2005 at 1:10 pm
Thanks Vasc...
The problem is I do not have a date, just a current speed and a remaining parts. Maybe I am doing a terrible job at explaining the situation. Let me try this:
I have a column 'speed' and a column 'partsRemaining'. If I do the following query:
SELECT partsRemaining / speed
FROM LiveProduction
and speed is 50000 (in parts per hour)
and partsRemaining is 100000 (parts to be produced)
I get the value of 2. I want to get the format 02:00 (for 02 hours and 00 minutes).
Does this clarify what I mean?
Regards
Bob
June 15, 2005 at 1:25 pm
This will give you the HOURS and minutes
Declare @PartsRemaining float
DECLARE @Speed float
SELECT @PartsRemaining=100000,@Speed=50000
SELECT LTRIM(STR(CAST(@partsRemaining / @Speed as INT)))+':'
+REPLICATE('0',2-LEN(LTRIM(STR(CAST((CAST(@partsRemaining / @Speed * 100 as INT)%100)*60 / 100 as int)))))+
LTRIM(STR(CAST((CAST(@partsRemaining / @Speed * 100 as INT)%100)*60 / 100 as int)))
Vasc
June 15, 2005 at 2:05 pm
excellent!!!! thank you soooo much Vasc....much appreciated!!!!
one last thing....sorry to be a pain in the neck...is there a way to format the hours so it shows in hh so 5 hours shows 05?
Thanks a million!!
Regards
Bob
June 15, 2005 at 2:09 pm
SELECT
CASE WHEN LEN(LTRIM(STR(CAST(@partsRemaining / @Speed as INT))))<2 THEN '0' ELSE '' END +
LTRIM(STR(CAST(@partsRemaining / @Speed as INT)))+':'
+REPLICATE('0',2-LEN(LTRIM(STR(CAST((CAST(@partsRemaining / @Speed * 100 as INT)%100)*60 / 100 as int)))))+
LTRIM(STR(CAST((CAST(@partsRemaining / @Speed * 100 as INT)%100)*60 / 100 as int)))
Did it with case because you might have more than 99 hours
Vasc
June 15, 2005 at 2:14 pm
true....thanks.
have a great day!
Regards
Bob
June 15, 2005 at 2:23 pm
Srry wrong calculations before....
this is the right solution
Declare @PartsRemaining float
DECLARE @Speed float
SELECT @PartsRemaining=300,@Speed=50
SELECT
CASE WHEN LEN(LTRIM(STR(CAST(@partsRemaining / @Speed as INT))))<2 THEN '0' ELSE '' END +
LTRIM(STR(CAST(@partsRemaining / @Speed as INT)))+':'
+REPLICATE('0',2-LEN(LTRIM(STR(CAST(@partsRemaining * 60 / @Speed AS INT)%60))))+
LTRIM(STR(CAST(@partsRemaining * 60 / @Speed AS INT)%60))
try to give pair values for which you can calculate the time
for example 1 part speed 60 should give 1 min...
30 parts speed 120 should give 15 min
Vasc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply