write set based query /query/sub query instead of cursor

Question

1.00/5 (3 votes)

See more:

SQL

DECLARE @Sno INT
DECLARE @Id INT
DECLARE @RSUM INT
DECLARE @TotalMinutes int 
DECLARE @OverTimeMinutes int
DECLARE sumcal CURSOR GLOBAL SCROLL DYNAMIC OPTIMISTIC FOR SELECT EmpId,Sno FROM #tempTbl
OPEN sumcal
FETCH FIRST FROM sumcal INTO @Id,@Sno
WHILE ( @@FETCH_STATUS = 0 )
   BEGIN
      SELECT @RSUM = SUMinutes  FROM #tempTbl WHERE SNo=@Sno
      SELECT @TotalMinutes = SUM(SUMinutes) FROM #tempTbl WHERE Id=@Id and SNo<=@Sno
      IF(@TotalMinutes>2400)
         BEGIN
            IF(SELECT COUNT(*) FROM #tempTbl WHERE Id=@Id AND SNo<@Sno AND OvertimeMinutes>0)>0
               BEGIN
                  SELECT @OverTimeMinutes = SUMinutes FROM #tempTbl WHERE SNo=@Sno
                  SET @RSUM = 0
               END
            ELSE
               BEGIN
                  SET @OverTimeMinutes = (@TotalMinutes-2400)
                  SET @RSUM = (1000 - @OverTimeMinutes)
               END
         END
      ELSE
         BEGIN
            SET @OverTimeMinutes = 0
         END
								   
      --Calculate Employee Overtime Hours - End
      UPDATE #tempTbl SET OvertimeMinutes = @OverTimeMinutes, RSUM = @RSUM WHERE SNo=@Sno
      SET @OverTimeMinutes = 0
      SET @TotalMinutes = 0
      SET @RSUM = 0
      FETCH NEXT FROM sumcal INTO @Id,@Sno
   END
CLOSE sumcal
DEALLOCATE sumcal

Posted 10-Sep-13 7:18am

Member 10265077

Updated 10-Sep-13 8:28am

phil.o

v2

Add a Solution

Comments

RedDk 10-Sep-13 13:49pm

How about a temporary table?
CREATE TABLE #tempTbl( [EmpId][int], [Sno][int], [Id][int], [RSUM][int], [TotalMinutes][int], [OverTimeMinutes][int]) ... along these lines.

Raja Sekhar S 11-Sep-13 2:54am

What is ID and EmpId..? both are same..? in Cursor you are using EmpId, for calculating SumMinutes you are using Id. Provide some sample data...

Raja Sekhar S 11-Sep-13 3:13am

Provide some sample data of the table...

Raja Sekhar S 11-Sep-13 3:44am

what will be the output for this data...?

Raja Sekhar S 12-Sep-13 1:17am

as RedDk told.... the sample data which u gave and output from the procedure didn't add up... so clearly specify what u want... Give sample i/p and output..
so we can help...

RedDk 12-Sep-13 13:44pm

Ok AP,

Look at the "FROM" clause of the first INSERT into #tempTbl.
That clause is detailing where the target data that is to be put in the temporary table has it's origin. The table [dbo].[staff1]; short of knowing, now that the input/output shows a slight change @ [SNo] record 6, anything else about the content of that origin, there's little chance of commuting this issue to a non-issue. Perhaps you could render the contents of [dbo].[staff1] to another appendix here and post it via context menu "Script Table as"/"CREATE TO clipboard/SELECT To clipboard".

RedDk 13-Sep-13 13:46pm

inputdata

1 solution

Add a Solution

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

RedDk · Answer 1 · 2013-09-11T13:08:00

This is not the solution but a rehash of what is necessary to get output from what the edited OP code

CREATE TABLE #tempTbl( [Sno][int], [Id][int], [RSUM][int], [SUMinutes][int], [OverTimeMinutes][int])

And the table data "data_MZ_(td).txt" content

1	12001	0	430	500
2	12002	0	444	500
3	12003	10	200	30
4	12004	20	200	200
5	12005	0	400	200
6	12006	0	300	2000
7	12007	0	200	456
8	12008	0	200	34562
9	12009	0	200	34523
10	12010	0	200	563456

Input data

BULK INSERT #tempTbl
	FROM 'C:\Users\MZ\data_MZ_(td).txt'

Query on #tempTbl now shows in table

SELECT * FROM #tempTbl

As

Sno	Id	RSUM	SUMinutes	OverTimeMinutes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1	12001	0	430	500
2	12002	0	444	500
3	12003	10	200	30
4	12004	20	200	200
5	12005	0	400	200
6	12006	0	300	2000
7	12007	0	200	456
8	12008	0	200	34562
9	12009	0	200	34523
10	12010	0	200	563456

The SOOP

DECLARE @Sno INT
DECLARE @Id INT
DECLARE @RSUM INT
DECLARE @TotalMinutes int 
DECLARE @OverTimeMinutes int
--DECLARE sumcal CURSOR GLOBAL SCROLL DYNAMIC OPTIMISTIC FOR SELECT EmpId,Sno FROM #tempTbl
DECLARE sumcal CURSOR GLOBAL SCROLL DYNAMIC OPTIMISTIC FOR SELECT Id, Sno FROM #tempTbl
OPEN sumcal
FETCH FIRST FROM sumcal INTO @Id,@Sno
--FETCH FIRST FROM sumcal INTO @Sno

WHILE ( @@FETCH_STATUS = 0 )
   BEGIN
      SELECT @RSUM = SUMinutes  FROM #tempTbl WHERE SNo=@Sno
      SELECT @TotalMinutes = SUM(SUMinutes) FROM #tempTbl WHERE Id=@Id and SNo<=@Sno
      IF(@TotalMinutes>2400)
         BEGIN
            IF(SELECT COUNT(*) FROM #tempTbl WHERE Id=@Id AND SNo<@Sno AND OvertimeMinutes>0)>0
               BEGIN
                  SELECT @OverTimeMinutes = SUMinutes FROM #tempTbl WHERE SNo=@Sno
                  SET @RSUM = 0
               END
            ELSE
               BEGIN
                  SET @OverTimeMinutes = (@TotalMinutes-2400)
                  SET @RSUM = (1000 - @OverTimeMinutes)
               END
         END
      ELSE
         BEGIN
            SET @OverTimeMinutes = 0
         END
								   
--      Calculate Employee Overtime Hours - End
      UPDATE #tempTbl SET OvertimeMinutes = @OverTimeMinutes, RSUM = @RSUM WHERE SNo=@Sno
      SET @OverTimeMinutes = 0
      SET @TotalMinutes = 0
      SET @RSUM = 0
      FETCH NEXT FROM sumcal INTO @Id,@Sno
      --FETCH NEXT FROM sumcal INTO @Sno
   END
CLOSE sumcal
DEALLOCATE sumcal

Another query after the fetcher does its thing 'SELECT * FROM #tempTbl'

Sno	Id	RSUM	SUMinutes	OverTimeMinutes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1	12001	430	430	0
2	12002	444	444	0
3	12003	200	200	0
4	12004	200	200	0
5	12005	400	400	0
6	12006	300	300	0
7	12007	200	200	0
8	12008	200	200	0
9	12009	200	200	0
10	12010	200	200	0

This couldn't possibly be the output for which the OP is looking. Without any further information, exact errors encountered, some "good" output (expected), etc it's impossible to proceed on this matter.

write set based query /query/sub query instead of cursor

1 solution

Solution 1

Add your solution here

Preview 0

write set based query /query/sub query instead of cursor

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us