3 Replies - 1032 Views - Last Post: 21 May 2008 - 06:54 PM Rate Topic: -----

#1 mxwebb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-May 08

Need help wit SQL Assignment

Posted 11 May 2008 - 09:25 AM

Just started SQL Class and I am struggling with an assignment.
Would someone be kind to give me some help please?

your task is to make a stored procedure that will only billcustomers making monthly payments higher than $500.A Customer can have multiple Plans (services for which they pay amonthly fee).

You have to execute a billing procedure for each single active planunder an eligible customer account. Eligible customer account is anaccount that makes estimated monthly payment higher than $500.

You are given 2 tables, "Customers" and "Plans". Table "Customers" has columns: CustomerID, EmailAddressTable "Plans" has columns: PlanID, CustomerID, MonthlyPrice, isClosed,NextBillDate

Also, you are given two stored procedures named "Bill_Plan" and"Send_Email". All stored procedures returns 0 upon success and negativenumber upon failure.The "Bill_Plan" actually bills a customer for a plan. The storedprocedure takes one input parameter and have one output parameter.

Bill_Plan
@nPlanID = @PlanID,
@dtNewBillDate = @NextBillDate output

You have to use the output parameter @NextBillDate to updatePlans.NextBillDate with its new value.

Upon successful billing you have to send en email to the customer'semail address. The "Send_Email" stored procedure takes two inputparameters.
Send_Email
@sEmailAddress = @EmailAddress,
@sSubject = 'Your Invoice is Ready'
You have to rollback any single billing transaction upon any kind offailure and continue billing the remaining plans. (Important!!!)


OK HERE IS WHAT I HAVE AND I AM STUCK!!
/**********************************************************************
* SP Name: sp_Bill_Cust_monthly_over_500
* Author: Michael Webb
* Date: 05/08/2008
* 
*
* Purpose:
* stored procedure that will only billcustomers making monthly payments higher than $500.
* A Customer can have multiple Plans (services for which they pay amonthly fee).
* You have to execute a billing procedure for each single active planunder an eligible customer account.
* Eligible customer account is anaccount that makes estimated monthly payment higher than $500.
* You are given 2 tables, "Customers" and "Plans". Table "Customers" has columns: CustomerID, 
* EmailAddressTable "Plans" has columns: PlanID, CustomerID, MonthlyPrice, isClosed,NextBillDate Also,
* you are given two stored procedures named "Bill_Plan" and"Send_Email". 
* All stored procedures returns 0 upon success and negativenumber upon failure.
* The "Bill_Plan" actually bills a customer for a plan. The storedprocedure takes one input parameter 
* and have one output parameter. Bill_Plan@nPlanID = @PlanID,@dtNewBillDate = @NextBillDate 
* output You have to use the output parameter @NextBillDate to updatePlans.NextBillDate with its
* new value. Upon successful billing you have to send en email to the customer'semail address.
* The "Send_Email" stored procedure takes two inputparameters. Send_Email@sEmailAddress = @EmailAddress,
* @sSubject = 'Your Invoice is Ready' You have to rollback any single billing transaction upon any 
* kind offailure and continue billing the remaining plans. (Important!!!)
*
**********************************************************************

*
*/

if exists( select * from sysobjects where name='sp_Bill_Cust_monthly_over_500' and type='P' )
drop proc sp_Bill_Cust_monthly_over_500
go

CREATE PROCEDURE sp_Bill_Cust_monthly_over_500
AS



/***************************
** Variable declarations **
***************************
*/

declare @tc int /* tran count */
declare @nPlanID int
declare @dtNewBillDate
declare @sEmailAddress varchar(100)
declare @sSubject varchar (100)
Set @sSubject = 'Your Invoice is Ready'

/*******************************************************************
** Check whether to start a new transaction, or save the old one
*******************************************************************
*/

select @tc = @@trancount
if ( @tc > 0 )
save tran my_tran
else
begin tran my_tran


/********************************************************************
** PLACE YOUR PROCEDURE CODE HERE **
********************************************************************
*/
Declare BillCur Cursor

For

Select P.PlanID, C.EmailAddress
From customer C
INNER JOIN plans P on c.customerID = p.customerID
Where P.MonthlyPrice > 500
and P.isclose = 0


Open BillCur
Fetch Next From Cursor Into @nPlanID, @sEmailAddress 
Bill_Plan( @nPlanID)


End

/********************************************************************
** Transaction was successful
** - Commit if this procedure issued a begin tran
********************************************************************
*/

if ( @tc = 0 )
commit tran my_tran

return 0

go

GRANT EXECUTE ON sp_Bill_Cust_monthly_over_500


This post has been edited by PsychoCoder: 21 May 2008 - 06:49 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Need help wit SQL Assignment

#2 jjsaw5  Icon User is offline

  • I must break you
  • member icon

Reputation: 90
  • View blog
  • Posts: 3,060
  • Joined: 04-January 08

Re: Need help wit SQL Assignment

Posted 12 May 2008 - 05:09 AM

Just for future reference, please you [code] tags.

Thanks and Good Luck!
Was This Post Helpful? 0
  • +
  • -

#3 mikeblas  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 44
  • View blog
  • Posts: 390
  • Joined: 08-February 08

Re: Need help wit SQL Assignment

Posted 21 May 2008 - 05:55 PM

View Postmxwebb, on 11 May, 2008 - 09:25 AM, said:

OK HERE IS WHAT I HAVE AND I AM STUCK!!
What's your specific question?
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Need help wit SQL Assignment

Posted 21 May 2008 - 06:54 PM

I'm with Mike, you say you're stuck but give no explanation. Are you receiving any errors, if so what are they? What is this code doing versus what you want/need it to do? These are vital pieces of information needed to help us help you. You cannot just post code and expect us to figure out what you want or what's going wrong :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1