October 11, 2006 at 10:42 pm
My Status: SQL newb (so please go easy on me)
I'm reading article after article about cursors and how some people despise them, others say don't use them if you don't have to and yet others say they can be good if you use them in the right situation.
I'm searching but can't find a good cursors 101 tutorial. I've looked at Books Online but that gives me usage and I need a tutorial-by-example document to read over. I've discovered Books Online doesn't do a very good job in the "example" area...for me anyway. Does anyone have any tutorial/howto links they can share?
What I'm trying to do, in case anyone can make any suggestions in this forum...
I have a report that lists all of our charge codes at the hospital I work at. I want to get a count of how many times each charge code was used and put that count on the row of the charge code. What I'm getting from my code...
('Count' = count(Isnull([table_name].[field],0))
...is something like this...
Chg_code Chg_amount Count
0060N 179.72 253
0061N 269.58 253
0062N 270.51 253
It's counting how many times all the charge codes were used and putting that number on every record. What I want is something like this...
Chg_code Chg_amount Count
0060N 179.72 16
0061N 269.58 5
0062N 270.51 22
Someone recommended in an irc channel that I take a look at cursors so that I can do line by line processing. They didn't give me a good source to read up on. We have just over 6300+ charges and they aren't added all that often. I'd run this SQL script once a month and time really isn't an issue just as long as my script doesn't bog down our server for more than a couple of minutes. We're a small hospital so I can bog it down from time to time if I really need to...at night. I do realize that best practices would be not to bog it down at all no matter how small we are.
What would be the simplest way for me to attack this problem? I'm willing to do a bunch of reading but I'd prefer that any links given have good examples.
Thanks in advance.
John
October 11, 2006 at 10:48 pm
SELECT C.Chg_code , SUM(R.Amount) as Chg_Amount, COUNT(R.Amount) AS Chg_Count
FROM dbo.ChargeCodesTable C
INNER JOIN dbo.ChargeRecords R ON C.Chg_code = R.Chg_code
GROUP BY C.Chg_code
_____________
Code for TallyGenerator
October 11, 2006 at 10:48 pm
No need for line by line processing - it is slow and you have alternatives.
Seeing as you haven't given the whole query, I am surmising that there is something not right with your GROUP BY clause.
If you can supply the whole query, I am sure that you will get the help you need to resolve the problem.
October 11, 2006 at 11:28 pm
Everytime I include the a group by on that column ('Count'), every charge code is listed exactly 45 times and the count is different on every line and I have no idea where those numbers are coming from. As of right now, without the group by on the 'Count' line, every charge code is listed once.
I'll post more questions later, if I figure out why my script is acting this way.
Thanks,
John
October 12, 2006 at 10:33 am
I think you need a sum instead of a count on the count field. If there are 45 rows in the table your always going to return 45 rows for the count.
Tom
October 13, 2006 at 8:51 am
POST the actual query and your responses will exponentially increase in value!
October 13, 2006 at 8:57 am
Ray is correct, we need more information. However, I quickly threw this together:
CREATE TABLE #Charges
(
id int IDENTITY(1,1) PRIMARY KEY
, Chg_code varchar(6)
, Chg_amount decimal(8,2)
)
INSERT #Charges (Chg_code, Chg_amount)
SELECT '060N', 179.72
UNION ALL
SELECT '0061N', 269.58
UNION ALL
SELECT '0062N', 270.51
UNION ALL
SELECT '060N', 179.72
UNION ALL
SELECT '0061N', 269.58
UNION ALL
SELECT '0061N', 269.58
UNION ALL
SELECT '0062N', 270.51
UNION ALL
SELECT '0062N', 270.51
UNION ALL
SELECT '0062N', 270.51
UNION ALL
SELECT '0062N', 270.51
UNION ALL
SELECT '0062N', 270.51
-- SELECT Chg_code, Chg_amount FROM #Charges
-- Method 1: Chg_Amount is the same for each distinct charge code, just want to include it in the output
SELECT DISTINCT
n.Chg_code
, c.Chg_amount
, n.NumberOfCharges
FROM #Charges c
JOIN (SELECT Chg_Code, Count(*) As NumberOfCharges
FROM #Charges
GROUP BY Chg_Code
) n
ON c.Chg_Code = n.Chg_Code
ORDER BY n.Chg_Code
-- Method 2: Chg_Amount needs to be summed, and all rows counted for each code
SELECT Chg_code
, Sum(Chg_amount) As TotalCharges
, Count(*) As NumberOfCharges
FROM #Charges
GROUP BY Chg_Code
-- Cleanup
DROP TABLE #Charges
October 13, 2006 at 9:22 am
Since I had no original query to modify, it's hard for me to quess what exactly needed to be changed to do this. As stated earlier in the thread, if the actual query were supplied we could work with it. I would still be quessing to answer the question which is why I explained the idea of using a sum instead of a count to get the actual number of rows that meet the criteria, instead of a count which will always return them all.
Here's a crude example.
use northwind
go
select sum(cust) as victe_count,count(*) total_count from (
select case when customerid = 'victe' then 1 else 0 end cust, * from orders)a
victe_count total_count
----------- -----------
10 827
(1 row(s) affected)
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply