CASE and MATH - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CASE and MATH

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-08-21 : 15:10:06

I have the following:

SELECT SETTDT, 
       total = CASE SECTYPE 
                 WHEN 'E' THEN units * [PRICE] 
                 WHEN 'S' THEN units * [PRICE] 
                 WHEN 'B' THEN units * PRICE / 100 
                 WHEN 'F' THEN units * PRICE / 100 
              END, SECTYPE, SIDE
FROM   [sj]

Here is a sample of the output:

08/06/07	2315.25000000000	E	P
08/06/07	1559.25000000000	E	P
08/06/07	2214.45000000000	E	S
08/09/07	250000.00000000000	B	P
08/09/07	250000.00000000000	B	P
08/09/07	500000.00000000000	B	P
08/09/07	100000.00000000000	B	P
08/09/07	350000.00000000000	B	P
08/09/07	103.50000000000	        E       P
08/09/07	230.00000000000	        E       P
08/09/07	1104.00000000000	E	P
08/09/07	92.00000000000	        E	P

What I would like to do is add some logic now that says when its a P for side than multiply the amount * -1 and if its a sale leave as is.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 15:13:42

[code]SELECT SETTDT,
total = CASE SECTYPE
WHEN 'E' THEN units * [PRICE]
WHEN 'S' THEN units * [PRICE]
WHEN 'B' THEN units * [PRICE] / 100
WHEN 'F' THEN units * [PRICE] / 100
END *
CASE SIDE
WHEN 'P' THEN -1.0
ELSE 1.0
END
SECTYPE,
SIDE
FROM [sj][/code]

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-08-21 : 15:40:18

WOW, I didnt realize that was possible, thanks again

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-08-21 : 15:45:27

quote:
Originally posted by duhaas

WOW, I didnt realize that was possible, thanks again

Now what happens if I want to sum up the total side and group by SETTDT, not sure I have right syntax:

SELECT SETTDT, 
      SUM( total = CASE SECTYPE 
                 WHEN 'E' THEN units * [PRICE] 
                 WHEN 'S' THEN units * [PRICE] 
                 WHEN 'B' THEN units * [PRICE] / 100 
                 WHEN 'F' THEN units * [PRICE] / 100 
              END *
              CASE SIDE 
                 WHEN 'P' THEN -1.0 
                 ELSE 1.0 
              END)
FROM   [sj]
GROUP BY SETTDT

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-21 : 15:48:44

Just remove the total column alias:

SELECT SETTDT,
SUM(CASE SECTYPE
WHEN 'E' THEN units * [PRICE]
WHEN 'S' THEN units * [PRICE]
WHEN 'B' THEN units * [PRICE] / 100
WHEN 'F' THEN units * [PRICE] / 100
END *
CASE SIDE
WHEN 'P' THEN -1.0
ELSE 1.0
END) as Total
FROM [sj]
GROUP BY SETTDT

SQL Server Helper
http://www.sql-server-helper.com

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-08-21 : 15:51:21

quote:
Originally posted by sshelper

Just remove the total column alias:

SELECT SETTDT,
SUM(CASE SECTYPE
WHEN 'E' THEN units * [PRICE]
WHEN 'S' THEN units * [PRICE]
WHEN 'B' THEN units * [PRICE] / 100
WHEN 'F' THEN units * [PRICE] / 100
END *
CASE SIDE
WHEN 'P' THEN -1.0
ELSE 1.0
END) as Total
FROM [sj]
GROUP BY SETTDT

SQL Server Helper
http://www.sql-server-helper.com

PERFECT, THANKS AGAIN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 16:12:05

You can shorten it down to this

SELECT    SETTDT, 
          SUM(units * [PRICE] / CASE WHEN SECTYPE IN ('B', 'F') THEN 100.0 ELSE 1.0 END * CASE SIDE WHEN 'P' THEN -1.0 ELSE 1.0 END)
FROM      [sj]
GROUP BY  SETTDT

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources