DECLARE @locs nvarchar(max)
DECLARE @RT TABLE(make nvarchar(max),
CGS NVARCHAR(MAX),
inserttimestamp DATETIME,
NAME NVARCHAR(MAX),
CGSName nvarchar(max),
locs nvarchar(max),
Running_Hour DECIMAL(18,2),
IC_consumption_Kg Decimal(18,2),
Running_Min DECIMAL(18,2)
)
DECLARE VarC cursor
FOR SELECT Distinct locs from [Archive-HourlyFirstValues]
where CGS = @CGSName
OPEN VarC
FETCH FROM VarC into @locs
WHILE(@@FETCH_STATUS=0)
BEGIN
insert into @RT
SELECT
A.make
,A.CGS
,A.inserttimestamp
,A.name
,A.CGS AS CGSName
,A.locs
,(A.value - COALESCE(LAG(A.VALUE) OVER(ORDER BY A.INSERTTIMESTAMP),0)) "Running_Hour"
,(B.value - COALESCE(LAG(B.VALUE) OVER(ORDER BY B.INSERTTIMESTAMP),0)) "IC_consumption_Kg"
,(C.value - COALESCE(LAG(C.VALUE) OVER(ORDER BY C.INSERTTIMESTAMP),0)) "Running_Min"
FROM [Archive-HourlyFirstValues] A
JOIN [Archive-HourlyFirstValues] B ON A.inserttimestamp = B.inserttimestamp AND A.locs = B.locs
JOIN [Archive-HourlyFirstValues] C ON C.inserttimestamp = B.inserttimestamp AND C.locs = B.locs
WHERE A.parameter = 'RUN-HR'
AND ((MONTH(convert(date,A.inserttimestamp,103)) = @Month
AND DATENAME(YEAR,convert(date,A.inserttimestamp,103)) = @Year)
OR A.inserttimestamp = DATEADD(HOUR,6,DATEADD(MONTH,DATEDIFF(MONTH,-1,DATEADD(MONTH,@Month-1,DATEADD(YEAR,@Year-1900,0)))-1,-1)))
AND a.locs = @locs
AND CAST(A.inserttimestamp AS TIME) = '06:00:00'
AND B.parameter = 'ENG-MASSTOT'
AND ((MONTH(convert(date,B.inserttimestamp,103)) = @Month
AND DATENAME(YEAR,convert(date,B.inserttimestamp,103)) = @Year)
OR B.inserttimestamp = DATEADD(HOUR,6,DATEADD(MONTH,DATEDIFF(MONTH,-1,DATEADD(MONTH,@Month-1,DATEADD(YEAR,@Year-1900,0)))-1,-1)))
AND B.CGS = @CGSName
AND CAST(B.inserttimestamp AS TIME) = '06:00:00'
AND C.parameter = 'RUN-Min'
AND ((MONTH(convert(date,C.inserttimestamp,103)) = @Month
AND DATENAME(YEAR,convert(date,C.inserttimestamp,103)) = @Year)
OR C.inserttimestamp = DATEADD(HOUR,6,DATEADD(MONTH,DATEDIFF(MONTH,-1,DATEADD(MONTH,@Month-1,DATEADD(YEAR,@Year-1900,0)))-1,-1)))
AND C.locs = @locs
AND CAST(C.inserttimestamp AS TIME) = '06:00:00'
order by a.inserttimestamp,b.inserttimestamp,C.inserttimestamp,a.locs,b.locs,C.locs
FETCH NEXT FROM VarC into @locs
END
CLOSE VarC
DEALLOCATE VarC
Select * from @RT
where ((MONTH(convert(date,inserttimestamp,103)) = @Month
AND DATENAME(YEAR,convert(date,inserttimestamp,103)) = @Year)
)
What I have tried:
How to convert the below Query into Stored Procedure(MS Sql Server)?
I have tried to write a query in stored procedure but some error is given.
Please give me solution