October 25, 2010 at 12:23 pm
I have a speed setting for each machine. I only want the last 5 records. I want to see if the total count is 5 where the actuall speed is less than 500 of the set speed.
Example
Machine, Set_speed, Actual_speed
-------------------------------
Loft 1000 450
Loft 1000 335
Loft 1000 120
Loft 1000 140
Loft 1000 750
My count should be 4
SELECT TOP 5 machine, machinedatetime,
Count(actual_speed) < set_speed - 500
FROM table
October 25, 2010 at 12:58 pm
DECLARE @Table Table(
Machine varchar(10)
,Set_speed int
,Actual_speed int
)
INSERT @Table VALUES
('Loft', 1000, 450),
('Loft', 1000, 335),
('Loft', 1000, 120),
('Loft', 1000, 140),
('Loft', 1000, 750)
GO
SELECT TOP 5 Machine,
Count(actual_speed) MachineCount
FROM @Table
WHERE Actual_speed < set_speed - 500
GROUP By Machine
October 25, 2010 at 1:20 pm
Thanks A Lot
October 27, 2010 at 4:30 pm
That query will only include rows where the condition is true, so assuming 5 such rows exist, I think it will always return 5.
I assume by "last 5" you mean chronologically. If so, you will need an ORDER BY somewhere in the query to do what you need to do.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply