CODEWITHSUNDEEP
sql-servert-sql
ARidder101
ARidder101

Reputation: 315

Populating declared variable with table data in SQL

I am trying to get a value from a hour meter table in my database. I need the machine name, total hours, and the date recorded from 4 weeks before (I am creating without using datetime functions because i cannot guarantee the meter i am building will read on the right days) i have an aggregate function to show days since 1/1/1 0:00:00 (or something like that) so i am trying to use this:

Declare @Value as Integer

WITH Derp as (Select top 4 [HLRD], TotalHours, MachineName from dbo.HourMeterLog where MachineName = 'Chester' ORDER BY HLRD Desc)
 @Value = MIN(Derp.HLRD)

WITH Derp as (Select top 4 [HLRD], TotalHours, MachineName from dbo.HourMeterLog where MachineName = 'Chester' ORDER BY HLRD Desc)
Select * from Derp where HLRD = @Value

but i cannot seem to get @Value to accept the value i need. Anyone able to tell me what i am doing wrong or suggest a better path to my answer?

Upvotes: 0

Views: 43

Answers (1)

Lamak
Lamak

Reputation: 70638

This would be the right syntax for what you want:

DECLARE @Value INT;

WITH Derp AS 
(
    SELECT TOP 4 [HLRD], 
                 TotalHours, 
                 MachineName 
    FROM dbo.HourMeterLog 
    WHERE MachineName = 'Chester' 
    ORDER BY HLRD DESC
)
SELECT @Value = MIN(HLRD)
FROM Derp;

WITH Derp AS 
(
    SELECT TOP 4 [HLRD], 
                 TotalHours, 
                 MachineName 
    FROM dbo.HourMeterLog 
    WHERE MachineName = 'Chester' 
    ORDER BY HLRD DESC
)
SELECT * 
FROM Derp 
WHERE HLRD = @Value;

Of course, this could be accomplished much simpler doing:

WITH CTE AS
(
    SELECT  *,
            ROW_NUMBER() OVER(ORDER BY HLRD DESC) RN
    FROM dbo.HourMeterLog 
    WHERE MachineName = 'Chester' 
)
SELECT *
FROM CTE
WHERE RN = 4;

Upvotes: 2

Related Questions