Comments by Swaroop Patil 2023 (Top 2 by date)

USE [SALES];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- SQLINES LICENSE FOR EVALUATION USE ONLY
ALTER PROCEDURE Records
AS
BEGIN
DECLARE @locs nvarchar(max)
DECLARE @CGSName nvarchar(max)
DECLARE @month DATETIME
DECLARE @year DATETIME

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 [dbo].[Archive-HourlyFirstValues]
where CGS = @CGSName
OPEN VarC
FETCH FROM VarC into @locs
WHILE(@@FETCH_STATUS=0)
BEGIN
-- SQLINES LICENSE FOR EVALUATION USE ONLY
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 [dbo].[Archive-HourlyFirstValues] A
JOIN [dbo].[Archive-HourlyFirstValues] B ON A.inserttimestamp = B.inserttimestamp AND A.locs = B.locs
JOIN [dbo].[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
-- SQLINES LICENSE FOR EVALUATION USE ONLY
Select * from @RT
where ((MONTH(convert(date,inserttimestamp,103)) = @Month
AND DATENAME(YEAR,convert(date,inserttimestamp,103)) = @Year)
)