Solved

Need a T-SQL String

Posted on 2003-11-29
5
895 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:chngtg
  • 2
  • 2
5 Comments
 
LVL 19

Assisted Solution

by:Dexstar
Dexstar earned 200 total points
ID: 9842939
@chngtg:

> 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*
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9843043
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
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9843142
@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*
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 300 total points
ID: 9854745
@Dex, Using Sign function will run faster than doing something like,
 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.
0
 

Author Comment

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

tg
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
set default date format in mssql to mm/dd/yyyy 22 66
Permissions on Database 11 36
ASP.NET 5 Templates 2 65
How to use left join to take all data from master table? 11 41
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now