Click here to Skip to main content
15,886,873 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 28-Mar-23 2:50am
Comments
OriginalGriff 27-Mar-23 1:48am    
"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So show us the SP code you tried, tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages, and where they happen.

Use the "Improve question" widget to edit your question and provide better information.
Swaroop Patil 2023 27-Mar-23 2:04am    
I'm using stored procedure for optimize the records
OriginalGriff 27-Mar-23 2:31am    
Oh, for ...
So what?

That doesn't help us to help you: show us what the SP you tried was and tell us what errors you got!
Swaroop Patil 2023 27-Mar-23 2:36am    
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)
)
Dave Kreskowiak 27-Mar-23 8:09am    
Do you think it might be a good idea to tell everyone trying to help you what the error message or problem is? "It doesn't work" does not explain the problem.

1 solution

If (as it was for me) your error is
Quote:
Argument data type datetime is invalid for argument 2 of dateadd function.
then you need to look carefully at the three lines
SQL
OR C.inserttimestamp = DATEADD(HOUR,6,DATEADD(MONTH,DATEDIFF(MONTH,-1,DATEADD(MONTH,@month-1,DATEADD(YEAR,@year-1900,0)))-1,-1))
OR, more likely, change the variable declarations
SQL
DECLARE @month INT --DATETIME
DECLARE @year INT --DATETIME
The much better solution to your problem is to go back to your original intent and rewrite the query - you do not need to use a cursor, remember SQL is set-based. I'd offer to help but I don't know your table structure, I don't have any sample data and I don't know what you are expecting to be returned. Hint: All of this information can be added to your question with the "Improve Question" link.

If you want to understand more about why you shouldn't be using a cursor then have a look at my article Processing Loops in SQL Server[^]

Finally, if you insist on using cursors then remember to deallocate them when you're finished using them
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900