Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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 8:18am
Edited 10-Sep-13 9:28am
phil.o41.9K
v2
Comments
RedDk at 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 at 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 at 11-Sep-13 3:13am
   
Provide some sample data of the table...
Raja Sekhar S at 11-Sep-13 3:44am
   
what will be the output for this data...?
Raja Sekhar S at 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 at 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 at 13-Sep-13 13:46pm
   
inputdata

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  

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



Advertise | Privacy | Mobile
Web04 | 2.8.150327.1 | Last Updated 11 Sep 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100