June 22, 2006 at 12:36 pm
Hi all,
I am new and trying to grasp programming a little more than just database management. I have a new billing project that I'm working on to where I want to sum the total transactions from a print table and bill on a monthly basis. I have the dates delcared and set up, but I am not sure how to start a cursor so that I can bill individuals based on the date stamp. In my transaction a persons id is written to that table each time they print along with an amount. What I want is student1 has 11 transactions between a certain date, student 2 has 50, etc. How would I begin a cursor to fetch each student and the total amount charged during a specific time. I will show you the date range......
DECLARE @dt DATETIME
DECLARE @dt2 VARCHAR(10)
declare @dt3 datetime
declare @dt4 datetime
set @dt=(select CAST(LEFT(GETDATE()-DAY(GETDATE())+1,11) AS datetime))
set @dt2=(select REPLACE(CONVERT(VARCHAR(10), @dt, 101), '/', '') AS datetime)
set @dt3 = dateadd(mm, -1, @dt)
set @dt4 = dateadd(dd, -1, @dt3)
select
card_id,
'CPCG', 'UNI_FEED', (technology_purse + tuition_purse + user_purse)*-1, @dt2, 'UNI00065'
from users
where
(technology_purse + tuition_purse + user_purse) < -0.10
and type = 'user'
and group_id = '35192'
GO
The "select" stamt is ran against a table where I will have to join b/c I bill to where the (technology_purse + tuition_purse + user_purse) < -0.10. I uses to bill only once a semester and the select stmt worked fine. However, the "users" table datetime is in hex so I can't use that table.
Any help in getting this started would be greatly appreciated.
June 22, 2006 at 1:01 pm
This is something what I was trying to do. Again, the sum(transactions) is where I'm confused. Am I on the right track?
DECLARE @dt DATETIME
DECLARE @dt2 VARCHAR(10)
declare @dt3 datetime
declare @dt4 datetime
declare @student_id int,
declare @student_bal money,
declare @facstaff_id int,
declare @facstaff_bal money
set @dt=(select CAST(LEFT(GETDATE()-DAY(GETDATE())+1,11) AS datetime))
set @dt2=(select REPLACE(CONVERT(VARCHAR(10), @dt, 101), '/', '') AS datetime)
set @dt3 = dateadd(mm, -1, @dt)
set @dt4 = dateadd(dd, -1, @dt3)
declare billing_cursor for
select p.card_id, sum(t.transactions)
from people p, transactions t
where t.id = p.id
and (p.technology_purse +p. tuition_purse + p.user_purse) < -0.10
and p.type = 'user'
and p.group_id = '35192'
for read only
****How would I sum(transaction)
open billing_cursor
fetch NEXT FROM billing_cursor
while @@fetch_status = 0
Begin
Fetch next from billing_cursor
end
close billing_cursor
deallocate billing_cursor
** do I need a temp table to update?
**Now if @dt IN (1, 6, 8)
select @student_bal = balance from acu_start_balance where acu_group = 'student'
select @facstaff_bal = balance from acu_start_balance where acu_group = 'facstaff'
select @student_id = user_id from people where id = 'student' and type = 'Group'
update people_balances set people_balances.user_purse = @student_bal from people_balances, people p where people_balances.user_id = p.user_id and p.group_id=@student_id and p.type='User'
update people_balances set people_balances.tuition_purse = 0 from people_balances, people p where people_balances.user_id = p.user_id and p.group_id=@student_id and p.type='User'
update people_balances set people_balances.technology_purse = 0 from people_balances, people p where people_balances.user_id = p.user_id and p.group_id=@student_id and p.type='User'
select @facstaff_id = user_id from people where id = 'faculty_staff' and type = 'Group'
update people_balances set people_balances.user_purse = @facstaff_bal from people_balances, people p where people_balances.user_id = p.user_id and p.group_id=@facstaff_id and p.type='User'
update people_balances set people_balances.tuition_purse = 0 from people_balances, people p where people_balances.user_id = p.user_id and p.group_id=@facstaff_id and p.type='User'
update people_balances set people_balances.technology_purse = 0 from people_balances, people p where people_balances.user_id = p.user_id and p.group_id=@facstaff_id and p.type='User'
Else end
June 22, 2006 at 1:07 pm
If you are new to SQL, and find yourself wanting to write a cursor to sum things, its time to take a step back from the SQL code editor and go do some reading.
If you continue with a cursor solution, you will be completely missing some of the fundamental aspects of a set-based language like SQL. And you will create a difficult to maintain code-monster that perfroms several orders of magnitude worse than the correct set-based solution.
If you're trying to sum() something BY a certain column or columns, you use SQL aggregates and add a GROUP BY to the query.
Simple example:
Select StudentID, Sum(Transactions)
From SomeTable
Group By StudentID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply