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

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

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

When you say you want a "summary", what do you mean? Do you want the SUM of all of the TOTALs for each of those 8 lines?

If so, try this statement:

SELECT Type, PosStatus, PosFactor, SUM(TOTAL) as TotalTotal FROM

(SELECT TYPE, Amount*Status*CASE TYPE WHEN 'A' THEN FactorA ELSE FactorB END as TOTAL,

CONVERT( BIT, CASE WHEN Status > 0 THEN 1 ELSE 0 END ) AS PosStatus,

CONVERT( BIT, CASE WHEN (CASE TYPE WHEN 'A' THEN FactorA ELSE FactorB END) > 0 THEN 1 ELSE 0 END ) As PosFactor

FROM Transactions) As TransData

GROUP BY TYPE, PosStatus, PosFactor

That should compute the "TOTAL" for each row, break it up by whether Factor is positive or negative, and Status is Positive or Negative, and then give you a SUM of TOTAL by Type, PosFactor and PosStatus.

Let me know if you have any issues.

Hope That Helps,

Dex*

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

Dex*

tg

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

CONVERT( BIT, CASE WHEN Status > 0 THEN 1 ELSE 0 END )

If you run the queries below you will find out.

create Table MyTable

(

Status int

)

go

declare @i int

select @i = 1

While @i <= 1000

begin

insert MyTable Values (@i )

select @i = @i + 1

end

While @i <= 1000

begin

insert MyTable Values (@i * -1)

select @i = @i + 1

end

select getdate()

select CONVERT( BIT, CASE WHEN Status > 0 THEN 1 ELSE 0 END )

from MyTable

group by CONVERT( BIT, CASE WHEN Status > 0 THEN 1 ELSE 0 END )

select getdate()

select sign(Status)

from MyTable

group by sign(Status)

select getdate()

Also see below.

If you run this query you will get an error message saying 'Invalid Column Name PosStatus'

select CONVERT( BIT, CASE WHEN Status > 0 THEN 1 ELSE 0 END ) as PosStatus

from MyTable

group by PosStatus

go

If you run this query it will work,

select CONVERT( BIT, CASE WHEN Status > 0 THEN 1 ELSE 0 END ) as PosStatus

from MyTable

group by CONVERT( BIT, CASE WHEN Status > 0 THEN 1 ELSE 0 END )

go

HTH

Namasi Navaretnam.