March 16, 2013 at 4:03 pm
Hi,
Hope you can help me.
Every week KPI's for our HR-dept. have to be generated.
The problem with this is that the KPI's from the previous week have to be overwritten by the KPI's from the current week. I'll save you the reason for this, but I tried to solve this using a cursor.
The cursor variables store the difference in days between today and 7 days back, 14 days back, 21 days back etc.etc. (and stores the corresponding day and year). So for the current week this day-difference is 0; for the previous week this is 7; 2 weeks ago this is 14, 3 weeks ago this is 21 etc.
when I run the query for the current week the current week and all the previous weeks have to be inserted in a table and all the existing weeks in the table have to be deleted. The delete-part of the query is not shown below; only the insert part.
when I run the following query I get the error:
"Msg 137, Level 15, State 2, Line 99
Must declare the scalar variable "@kpi_cursor"."
I come accross several topics when I google around, but no solution so far. Probably there is a simple solution to the problem.
Hope someone can help me with fixing the error.
The query is:
declare @Created varchar(50)
declare @ReferenceDay date
declare @Type varchar(10)
set @Created = getdate()
set @ReferenceDay = getdate()
set @Type = 'Prognosis'
-- Cursor variables
DECLARE @DaysJump int
DECLARE @Date varchar(10)
DECLARE @DateYear varchar(4)
-- End cursor variables
DECLARE @Days int
SET @Days = @Days + 7
DECLARE kpi_cursor CURSOR FOR
SELECT@Days as jump,
GETDATE() - @Days as jump_date,
YEAR(GETDATE() - @Days) as jump_year
WHEREYEAR(GETDATE() - @Days) >= 2013
OPEN kpi_cursor
FETCH NEXT FROM kpi_cursor INTO @DaysJump, @Date, @DateYear
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Q_KPIs_HRM_Test
(
KPI,
SubKPI,
Type,
Yr,
RefDate,
Week,
Weekday,
StartDate,
EndDate,
BV,
Value,
Syscreated,
Syscreator
)
/* 6. Employees out based on 711-workflow */
SELECT'Employees out' as KPI,
'' as SubKPI,
@Type as Type,
YEAR(a.EndDate) as Yr, /* Year of contract enddate */
CONVERT(VARCHAR(10), @ReferenceDay, 105) as RefDay,
(select dbo.udf_GetISOWeekNumberFromDate(a.EndDate)) as Week,
DATENAME(DW, @ReferenceDay) as Weekday,
CONVERT(varchar(50), (GETDATE() - 6 - @DaysJump), 105) as StartDate,
CONVERT(varchar(50), (GETDATE() - @DaysJump), 105) as EndDate,
h.costcenter as BV,
COUNT(*) as Value,
CONVERT(VARCHAR(10), @Created, 105) as Syscreated,
'4' as Syscreator
FROMAbsences a
LEFT OUTER JOIN humres h ON a.EmpID = h.res_id
WHEREISNULL(a.hid, 1) > 0
and isnull(h.res_id, 999999) > 5000
and a.Type = 711
and a.Status <> 2
and a.EndDate > GETDATE() - @DaysJump
GROUP BY h.costcenter, a.EndDate
UNION ALL
/* 7. Employees in based on 500-workflow */
SELECT'Employees in' as KPI,
'' as SubKPI,
@Type as Type,
YEAR(a.StartDate) as Yr, /* Year of contract startdate */
CONVERT(VARCHAR(10), @ReferenceDay, 105) as RefDay,
(select dbo.udf_GetISOWeekNumberFromDate(a.StartDate)) as Week,
DATENAME(DW, @ReferenceDay) as Weekday,
CONVERT(varchar(50), (GETDATE() - 6 - @DaysJump), 105) as StartDate,
CONVERT(varchar(50), (GETDATE() - @DaysJump), 105) as EndDate,
h.costcenter as BV,
COUNT(*) as Value,
CONVERT(VARCHAR(10), @Created, 105) as Syscreated,
'4' as Syscreator
FROMAbsences a
LEFT OUTER JOIN humres h ON a.EmpID = h.res_id
WHEREISNULL(a.hid, 1) > 0
and isnull(h.res_id, 999999) > 5000
and a.Type = 500
and a.Status <> 2
and a.StartDate > GETDATE() - @DaysJump
GROUP BY h.costcenter, a.StartDate
FETCH NEXT FROM @kpi_cursor INTO @DaysJump, @Date, @DateYear
END
CLOSE kpi_cursor
DEALLOCATE kpi_cursor
March 16, 2013 at 5:45 pm
It would help if you would provide the DDL (CREATE TABLE statement(s)) for the table(s) involved in the query, sample data (NOT real data, just sample data that mimics your problem domain) as a series of INSERT INTO statements for the table(s) involved, and expected resutls based on the sample data (best if provided as a table and the values as a series of INSERT INTO statements to load the table).
March 17, 2013 at 2:42 pm
Hi
You have
FETCH NEXT FROM @kpi_cursor INTO ...
inside your loop. you should have
FETCH NEXT FROM kpi_cursor INTO ...
the same as you have for your first fetch
March 18, 2013 at 3:13 pm
Thanks! That was the trick!
Answers are very simple, sometimes!
March 18, 2013 at 3:21 pm
michielbijnen (3/18/2013)
Thanks! That was the trick!Answers are very simple, sometimes!
Cursors are notoriously slow. There are times when they are the best choice but from what you have posted it does not seem like this is one of those rather rare scenarios. If you want some help to remove the cursor from this I will be happy to help you. However, you will have to post ddl, sample data and desired output as Lynn already suggested.
If however, you are content with the slow processing your cursor provides I am happy you found a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply