|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-15 : 08:41:40
|
| Tony writes "I am trying to calculate some money fields in the table in the SQL database. My aim is to calculat about five money fields, create a total for them and a sub total for all the fields. The senarion is this, I have a SSN with dependant SSN's and the income of all the dependant SSN's would need to be subtracted from the main SSN. And then all would need to be total and subtotaled. I am looking at a Horizontal calculation with a sum total and a vertical calculation with subtotal.ex: 2-1+3+4 = ? 4-1+1+1 = ? ___________ Subtotal" |
|
|
yoursfriend
Starting Member
5 Posts |
Posted - 2002-04-16 : 03:32:57
|
| Hi,Try to like this...Create Table Test (Grp_Name VarChar(10), EmpNo Int, Fir_Fld Money, Sec_Fld Money, Thrd_Fld Money)Insert Into Test Values('Salary', 1001, 1000.00,120.00,300.00)Insert Into Test Values('Salary', 1002, 3000.00,320.00,900.00)Insert Into Test Values('Expences', 1001, 0.00,20.00,100.00)Insert Into Test Values('Expences', 1002, 100.00,0.00,0.00)Select * From TestCreate Procedure Test_OPAsBegin Declare @Grp_Name Varchar(10), @Rep_Ord TinyInt Set @Rep_Ord = 1 Create Table #Temp (Grp_Name VarChar(10), EmpNo Int, Fir_Fld Money, Sec_Fld Money, Thrd_Fld Money ,Horiz_Total Money, Rep_Ord TinyInt) Declare TmpCur Cursor For Select Distinct Grp_Name From Test OPEN TmpCur Fetch Next From TmpCur Into @Grp_Name While @@Fetch_Status = 0 Begin Insert Into #Temp Select Grp_Name, EmpNo, Fir_Fld, Sec_Fld, Thrd_Fld, (Fir_Fld + Sec_Fld + Thrd_Fld), @Rep_Ord From Test Where Grp_Name = @Grp_Name Insert Into #Temp Select 'Sub Total:', Null, NUll, Null, Null, Sum((Fir_Fld + Sec_Fld + Thrd_Fld)), @Rep_Ord From Test Where Grp_Name = @Grp_Name Fetch Next From TmpCur Into @Grp_Name Set @Rep_Ord = @Rep_Ord + 1 End Close TmpCur Deallocate TmpCur SELECT * FROM #Temp ORDER BY REP_ORDEndEXEC Test_OP |
 |
|
Stored procdure