Cursor
-
Hello all!
I'm totally dumped with current tasks, could you help me with this using Cursor?
Given:
SELECT TOP 1000 [id]
,[cardid]
,[date]
,[time]
,[amount]
FROM [test].[dbo].[cards]id - int
cardid - varchar
date - int
Time - int
amount - floatId isn't
We have got different types of cardid (for example 6)
Different date and time.
Field amount looks like:
1000
999
888
677
2300To do:
We need to select/print: sorted by cardid/date/time when amount become bigger then beforeEx: cardid1/data1/time1/110.1
cardid1/data2/time2/2390.1
cardid2/data5/time6/8761.5
...Thanks in advice!
-
Can we get some DDL and sample data to work with?
The way that that is now, I don't understand what the data looks like nor why you'd need a cursor.The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - Akiota - Wednesday, August 2, 2017 2:55 PM
First, as mentioned above, we need more information in order to help.
Second, if you have too much on your plate to complete the tasks assigned, you should probably talk to your supervisor rather than turn to the internet for free consulting work. I have no problem helping you solve a problem but don't expect me to simply do the work for you. Pretty sure there are others here that feel the same way.
-
How much are you offering per hour to help you with your overload of work?
_______________________________________________________________
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/ - bmg002 - Wednesday, August 2, 2017 2:58 PM
Well, I've already done it without cursor..
But I'm trying to do it with it. (like homework).
And I don't understand how to 🙁
I'v got some ideas but they don't work..@to Sean Lange I don't recieve any money, so that's not about money..
Table looks like this:
ID is not unique.
- Akiota - Wednesday, August 2, 2017 4:43 PMbmg002 - Wednesday, August 2, 2017 2:58 PM
Well, I've already done it without cursor..
But I'm trying to do it with it. (like homework).
And I don't understand how to 🙁
I'v got some ideas but they don't work..@to Sean Lange I don't recieve any money, so that's not about money..
Table looks like this:
ID is not unique.
Show us how you solved the problem.
If you solved it without a cursor, no need to try and figure out how to use one for this problem.
- Lynn Pettis - Wednesday, August 2, 2017 5:11 PMAkiota - Wednesday, August 2, 2017 4:43 PMbmg002 - Wednesday, August 2, 2017 2:58 PM
Well, I've already done it without cursor..
But I'm trying to do it with it. (like homework).
And I don't understand how to 🙁
I'v got some ideas but they don't work..@to Sean Lange I don't recieve any money, so that's not about money..
Table looks like this:
ID is not unique.
Show us how you solved the problem.
If you solved it without a cursor, no need to try and figure out how to use one for this problem.
Using
with t as( select id, cardid, [date], [time], amount, lag(amount) over (partition by cardid order by [date], [time]) as amount_prev from table)select id, cardid, [date], [time], amountfrom twhere amount > amount_prev;;
But I need a solution usimg a cursor..
DECLARE @id int
DECLARE @cardid nvarchar(12)
DECLARE @date int
DECLARE @time int
DECLARE @amount floatDECLARE @refill float
DECLARE @brefill floatDECLARE @refill_cur as CURSOR
set @refill_cur = cursor for
select id, cardid, date, time, amount
from mytable
order by id, cardid, date, time
open @refill_cur
FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @amount;
set @brefill = @amount;--FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @brefill;
--if @refill < 0WHILE @@FETCH_STATUS = 0
BEGINset @refill = @brefill - @amount;
FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @brefill;
begin
--if @refill < 0
PRINT 'CardID '+@CardID+'|Date '+ cast(@hour as char(10))+'|Time '+cast(@quater as char(10))+'|'+ cast(@brefill as char(10))+'|'+cast(@refill as char(10));
end--if @refill < 0
--set @brefill = @amount;
--FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @brefill;
--WHILE @@FETCH_STATUS = 0
IF @@FETCH_STATUS <> 0
PRINT ' <<End>>'END
I don't really understand what i should fix here.
If you can, help me please. - Akiota - Thursday, August 3, 2017 12:07 AM
This is the part I don't understand. Why do you *need* a cursor? Cursors are dangerous things and should only be used when there is no other, better solution. They go through items one at a time (like counting jelly beans out of one bag and into another) instead of all at once (like handing the bag of jelly beans to the person who wants them).
What could possibly require you to use a cursor if you have other solutions that do the same thing?
- Brandie Tarvin - Thursday, August 3, 2017 5:14 AM
It's like a challenge.
And I'm trying to understand how to it last 3 days. I'm totally dumped with it.
I'm asking for some help with this. I really want to understand how it works and where I've done a mistake. - Akiota - Thursday, August 3, 2017 5:38 AM
I don't understand what this means. Sounds messy,
Regarding the challenge bit, I can reword it for you:
"You have a solution that works, but you are required to design another solution which is much slower, requires more code, is inelegant and will be a puzzle to any future developers"
I'd tell them where to stick that challenge. -
Do you know how a WHILE loop works?
- Phil Parkin - Thursday, August 3, 2017 5:44 AM
I'm pretty sure that he means he's totally stumped with it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - Brandie Tarvin - Thursday, August 3, 2017 6:12 AM
Akiota, this question is directed to you. I'd like to clear up your confusion here, but how I do so depends on how you answer this question.
- Akiota - Thursday, August 3, 2017 5:38 AM
If you're really just trying to understand how to do this in a procedural manner don't start in SQL, try something straight forward like powershell or javascript and just feed it a sorted data set.
- Akiota - Thursday, August 3, 2017 12:07 AMLynn Pettis - Wednesday, August 2, 2017 5:11 PMAkiota - Wednesday, August 2, 2017 4:43 PMbmg002 - Wednesday, August 2, 2017 2:58 PM
Well, I've already done it without cursor..
But I'm trying to do it with it. (like homework).
And I don't understand how to 🙁
I'v got some ideas but they don't work..@to Sean Lange I don't recieve any money, so that's not about money..
Table looks like this:
ID is not unique.
Show us how you solved the problem.
If you solved it without a cursor, no need to try and figure out how to use one for this problem.
Using
with t as( select id, cardid, [date], [time], amount, lag(amount) over (partition by cardid order by [date], [time]) as amount_prev from table)select id, cardid, [date], [time], amountfrom twhere amount > amount_prev;;
But I need a solution usimg a cursor..
DECLARE @id int
DECLARE @cardid nvarchar(12)
DECLARE @date int
DECLARE @time int
DECLARE @amount floatDECLARE @refill float
DECLARE @brefill floatDECLARE @refill_cur as CURSOR
set @refill_cur = cursor for
select id, cardid, date, time, amount
from mytable
order by id, cardid, date, time
open @refill_cur
FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @amount;
set @brefill = @amount;--FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @brefill;
--if @refill < 0WHILE @@FETCH_STATUS = 0
BEGINset @refill = @brefill - @amount;
FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @brefill;
begin
--if @refill < 0
PRINT 'CardID '+@CardID+'|Date '+ cast(@hour as char(10))+'|Time '+cast(@quater as char(10))+'|'+ cast(@brefill as char(10))+'|'+cast(@refill as char(10));
end--if @refill < 0
--set @brefill = @amount;
--FETCH NEXT FROM @refill_cur INTO @id, @cardid, @date, @time, @brefill;
--WHILE @@FETCH_STATUS = 0
IF @@FETCH_STATUS <> 0
PRINT ' <<End>>'END
I don't really understand what i should fix here.
If you can, help me please.First, you keep saying you "need" a cursor solution. You have said it is out of curiosity, to learn how to write one. My problem, I can't see the need. You have a set based solution that should scale well as data volume increases. A cursor solution is RBAR (a Modenism for Row By Agonizing Row) which will not scale well.
I find myself writing many cursor based routines. Luckily each iteration through the cursor is running set based code. It is the nature of the system I help support that requires using cursors to accomplish some of the reporting and maintenance routines I have written. Cursors are a tool and have a place in which to use them. This isn't one of them.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply