Sign change? - 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 2005 Forums

Transact-SQL (2005)

Sign change?

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-06-08 : 15:12:46

[code]select TR.EMP_ID as EMP_ID,
E.LST_NM as LAST_NAME,
CASE TR.TRAN_CD
WHEN '97' THEN ISNULL(SUM(T.AMT)*-1,0)
WHEN '1' THEN ISNULL(SUM(T.AMT),0)
END AS SYSTEM_TOTAL,
T1.TND_DESCR as TENDER_TYPE
from TRANSACTION_TENDER T
join TENDER T1 on T.TND_CD = T1.TND_CD
join LN_DETAIL L on L.TRAN_ID = T.TRAN_ID
join TRANS_HEADER TR on TR.TRAN_ID=T.TRAN_ID
join EMPLOYEE E on TR.EMP_ID = E.EMP_ID
where TR.TRAN_CD in (1,2,3,97) and BUS_DT = '08 Jun 09 12:00:00 PM' Group By TR.EMP_ID,E.LST_NM,T1.TND_DESCR,TR.TRAN_CD[/code]
Hi, I want to calculate the SYSTEM_TOTAL by TRAN_CD, I guess something wrong but not sure for [code]SUM(T.AMT)*-1[/code] or [code]SUM(T.AMT)[/code].
Can you look at this?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-08 : 15:40:21

The only thing I see is that you aren't accounting for tran_cd (2,3) in your CASE statement. Why do you "guess something wrong"?

Be One with the Optimizer
TG

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-06-08 : 15:45:31

Ignore the cases of tran_cd (2,3), suppose TRAN_CD=1 or 97.
The question is how to count the summary of AMT?
I mean if TRAN_CD is only 1, sum(T.AMT) is okay. If TRAN_CD=97, SUM(T.AMT)*-1 is also fine.
But if there is a case both exist, how to count it?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-08 : 15:52:18

how about this:


select TR.EMP_ID       as EMP_ID,
      E.LST_NM         as LAST_NAME,

--CASE TR.TRAN_CD
--      WHEN '97' THEN ISNULL(SUM(T.AMT)*-1,0)
--	  WHEN '1'  THEN ISNULL(SUM(T.AMT),0)
--      END AS SYSTEM_TOTAL,

       sum(t.amt * case when tr.tran_cd = 97 then -1 else 1 end) as SYSTEM_TOTAL,

	  T1.TND_DESCR               as TENDER_TYPE 
from  TRANSACTION_TENDER T 
join TENDER T1 on T.TND_CD = T1.TND_CD
join LN_DETAIL L on L.TRAN_ID = T.TRAN_ID
join TRANS_HEADER TR on TR.TRAN_ID=T.TRAN_ID
join EMPLOYEE E on TR.EMP_ID = E.EMP_ID
where TR.TRAN_CD in (1,2,3,97)   and BUS_DT = '08 Jun 09 12:00:00 PM'  Group By TR.EMP_ID,E.LST_NM,T1.TND_DESCR,TR.TRAN_CD

Be One with the Optimizer
TG

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-06-08 : 15:57:48

Okay, if 1,2,3,97 all considered.

sum(t.amt * case when tr.tran_cd = 97 then -1
when tr.tran_cd = 1 then 1
when tr.tran_cd = 2 then 1
when tr.tran_cd = 3 then 1
end) as SYSTEM_TOTAL

Is it right?
Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-08 : 16:02:21

That looks fine. It is equivilent to what I proposed. Since your where clause limits to (1,2,3,97) then the ELSE would cover (1,2,3)

Be One with the Optimizer
TG

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-06-08 : 16:28:55

SUM(T.AMT * CASE WHEN TR.TRAN_CD = 97 THEN -1
CASE WHEN TR.TRAN_CD = 1 THEN 1
CASE WHEN TR.TRAN_CD = 2 THEN 1
CASE WHEN TR.TRAN_CD = 3 THEN 1 end) as SYSTEM_TOTAL

Incorrect syntax near the keyword 'CASE'. What's wrong?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-08 : 16:51:20

You only need the CASE keyword once.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources