asked on

Need a T-SQL String

Hi all,

I am new to T-SQL. Been a programmer for VB-Access only. Got an urgent assignment on SQL-2000 for stored procedure but not familiar with T-SQL. Need to have the complete -TSQL string to put into the stored procedure

I need a summary from a table but an analysis of certains fields are required.

Transactions Table
------------------
Type Amount FactorA FactorB Status
A 1200 -0.98 0.70 1
A 1400 0.80 0.78 1
A 1100 -0.98 -0.78 -1
B 1400 0.92 -0.78 1
B 1350 -0.89 -0.66 -0.5

From the table above,Type will only be A or B. FactorA, FactorB and Status can be either positive or negative.

If Type is A then TOTAL = Amount * FactorA * Status
If Type is B then TOTAL = Amount * FactorB * Status

The summary that I need is very simple. It is an eight-line summary of TOTAL

Type A, Positive Status, Positive factor
Type A, Positive Status, Negative factor
Type A, Negative Status, Positive factor
Type A, Negative Status, Negative factor

Type B, Positive Status, Positive factor
Type B, Positive Status, Negative factor
Type B, Negative Status, Positive factor
Type B, Negative Status, Negative factor

The table can have thousands of rows. The calculations/query has to be efficient and fast as an auto-calculation is required every 30seconds. I need to use the results and put into recordset for further analysis.

thanks
TG

SOLUTION

Dexstar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

namasi_navaretnam

This solution is uses SIGN function.

Create Table FactorTest
(
Type char(1),
Amt numeric(18,0),
FactorA numeric(25,5),
FactorB numeric(25,5),
Status numeric(25,5)
)

go
Insert into FactorTest Values(
'A', 1200, -0.98, 0.70, 1)

Insert into FactorTest Values(
'A', 1400, 0.80, 0.78, 1)
Insert into FactorTest Values(
'A', 1100, -0.98, -0.78, -1)
Insert into FactorTest Values(
'B', 1400 , 0.92, -0.78, 1)
Insert into FactorTest Values(
'B', 1350, -0.89, -0.66, -0.5)
go

select Type,
Sign(Status),
Sign(CASE Type When 'A' Then FactorA When 'B' Then FactorB Else 0 End),
SUM(CASE Type When 'A' Then Amt * FactorA * Status When 'B' Then Amt * FactorA * Status Else 0 End)
from FactorTest
group by Type,
Sign(Status),
Sign(CASE Type When 'A' Then FactorA When 'B' Then FactorB Else 0 End)

go

-- Or

select Type,
Case Sign(Status) When -1 Then 'Negative' Else 'Positive' End as PosSign,
Case Sign(CASE Type When 'A' Then FactorA When 'B' Then FactorB Else 0 End) When -1 Then 'Negative' Else 'Positive' End As FactorSign,
SUM(CASE Type When 'A' Then Amt * FactorA * Status When 'B' Then Amt * FactorA * Status Else 0 End)
from FactorTest
group by Type,
Sign(Status),
Sign(CASE Type When 'A' Then FactorA When 'B' Then FactorB Else 0 End)

go

HTH

Namasi Navaretnam

Dexstar

@Namasi: I also thought about returning fields with values "Positive Status" "Positive Factor", etc, but I thought I was being too literal to what the Asker wanted. What do you think?

Dex*

ASKER CERTIFIED SOLUTION

namasi_navaretnam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chngtg

ASKER

Thanks folks. I need some time to digest all. I was given a partner who is more familiar. Thanks again for the help.

tg