Click here to Skip to main content
15,889,116 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
SQL
-- i hava one temp table (#tempTbl)
DECLARE @Sno INT
DECLARE @Id INT
DECLARE @TotalMinutes AS INTEGER=0
DECLARE @OTimeMinutes AS INTEGER=0
DECLARE @RUMinutes INTEGER=0

DECLARE sumcal CURSOR GLOBAL SCROLL STATIC OPTIMISTIC FOR SELECT Id,Sno FROM #tempTbl
OPEN sumcal
FETCH FIRST FROM sumcal INTO @Id,@Sno
WHILE ( @@FETCH_STATUS = 0 )
BEGIN 
SELECT @RUMinutes = SUAM FROM #tempTbl WHERE SNo=@Sno
SELECT @TotalMinutes = SUM(SUAM) FROM #tempTbl WHERE Id=@Id and SNo<=@Sno
IF(@TotalMinutes>2400)
BEGIN
if (SELECT COUNT(*) FROM #tempTbl WHERE Id=@Id AND SNo<@Sno AND OTM>0)>0
BEGIN
SELECT @OTimeMinutes = SUAM FROM #tempTbl WHERE SNo=@Sno SET @RUMinutes = 0
END
ELSE
BEGIN
SET @OTimeMinutes = (@TotalMinutes-2400)
SET @RUMinutes = (@RUMinutes - @OTimeMinutes)
END
END
ELSE
BEGIN
SET @OTimeMinutes = 0
END
UPDATE #tempTbl SET OTM = @OTimeMinutes, RSUM = @RUMinutes WHERE SNo=@Sno
SET @OTimeMinutes = 0
SET @TotalMinutes = 0
SET @RUMinutes = 0
FETCH NEXT FROM sumcal INTO @Id,@Sno
END
CLOSE sumcal
DEALLOCATE sumcal


HTML
#tempTbl table :
SNo	Id	SUAM	OTM	RSUM
1	254	480	0	0
2	254	480	0	0
3	254	480	0	0
4	254	480	0	0
5	254	480	0	0
6	254	480	0	0
7	277	480	0	0
8	277	480	0	0
9	277	480	0	0
10	277	480	0	0


HTML
output

SNo	Id	SUAM	OTM	RSUM
1	254	480	0	480
2	254	480	0	480
3	254	480	0	480
4	254	480	0	480
5	254	480	0	480
6	254	480	480	0
7	277	480	0	480
8	277	480	0	480
9	277	480	0	480
10	277	480	0	480
Posted
Updated 17-Sep-13 2:37am
v2
Comments
Maciej Los 17-Sep-13 8:36am    
I feel like i have seen it before... Matrix?
a.pkumar 17-Sep-13 8:42am    
yes
Maciej Los 17-Sep-13 8:44am    
Could you be more specific and provide more details about your needs?
a.pkumar 17-Sep-13 8:48am    
i have one temporary table(#tempTbl) in that table i want to update otm and rsum column values based on some coditions

for that i used cursor it is working fine but it takes more time when table has huge data
[no name] 17-Sep-13 8:46am    
Repost of a repost of a repost, ad nauseum. When are you actually going to try this yourself or ask a question or hire someone to do it for you? Just dumping your code here, over and over, hoping someone will do it for is not going to win you any friends.

1 solution

 
Share this answer
 
v2
Comments
SoMad 18-Sep-13 3:19am    
If they do not restore the deleted question, this link will take you to your answer: http://www.codeproject.com/script/Answers/EntryVersion.aspx?aid=652804&av=955676[^]

Soren Madsen
Maciej Los 18-Sep-13 14:57pm    
Thank you ;)

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