help on SQL query - 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

General SQL Server Forums

New to SQL Server Programming

help on SQL query

Author

Topic

catwalk
Starting Member

1 Post

Posted - 2013-08-21 : 07:51:38

HI,

can some one help me on how to do the net value for the transaction which has positive and negative for eg I need the net sum for transaction +456 or -456
for eg if +456 has value 5 and the -456 has value -3 and the net details should be shoun as 2

below is the SQL
Select m1.PA as PA ,
cast(sum(m1.ASIS9) as bigint(2)) as ASIS9 ,
cast(sum(m1.ASIS88) as bigint(2)) as ASIS88 ,
cast(sum(m1.TT450) as bigint(2)) as TT450 ,

cast(sum(m1.TT456) as bigint(2)) as TT456

From
((Select aai.pa_no as PA,
case when ana.bargain = '1' and ana.art_no not Like '%0000088' then ana.amount_net else 0 end as ASIS9,
case when ana.art_no Like '%0000088' then ana.amount_net else 0 end as ASIS88,

0 as TT456
From
ana_art_info aai, ana_sales ana
Where ana.art_no = aai.art_no
And cast(ana.sale_date as date ansi) >= cast('PARAM_1' as date ansi)
And cast(ana.sale_date as date ansi) <= cast('PARAM_2' as date ansi))
UNION ALL
(Select aai.pa_no as PA,
0 as ASIS9,
0 as ASIS88,

case when ait.tran_no = 456 then ait.amount_calc else 0 end as TT456
From ana_art_info aai, ana_int_trans ait
Where ait.art_no = aai.art_no(+)
And cast(ait.tran_date as date ansi) >= cast('PARAM_1' as date ansi)
And cast(ait.tran_date as date ansi) <= cast('PARAM_2' as date ansi))
UNION ALL
(Select aai.pa_no as PA,
0 as ASIS9,
0 as ASIS88,
0 as TT450,

0 as TT456

From ana_art_info aai, ana_int_trans_reason aitr
Where aitr.art_no = aai.art_no(+)
And cast(aitr.tran_date as date ansi) >= cast('PARAM_1' as date ansi)
And cast(aitr.tran_date as date ansi) <= cast('PARAM_2' as date ansi))) m1
Group by m1.PA

union all
Select 'Total' as PA ,
sum(t1.ASIS9) as ASIS9 ,
sum(t1.ASIS88) as ASIS88 ,
sum(t1.TT450) as TT450 ,

sum(t1.TT456) as TT456

From
((Select aai.pa_no as PA,
case when ana.bargain = '1' and ana.art_no not Like '%0000088' then ana.amount_net else 0 end as ASIS9,
case when ana.art_no Like '%0000088' then ana.amount_net else 0 end as ASIS88,
0 as TT450,

0 as TT456

From
ana_art_info aai, ana_sales ana
Where ana.art_no = aai.art_no
And cast(ana.sale_date as date ansi) >= cast('PARAM_1' as date ansi)
And cast(ana.sale_date as date ansi) <= cast('PARAM_2' as date ansi))
UNION ALL
(Select aai.pa_no as PA,
0 as ASIS9,
0 as ASIS88,
case when ait.tran_no = 450 then ait.amount_calc else 0 end as TT450,

case when ait.tran_no = 456 then ait.amount_calc else 0 end as TT456

From ana_art_info aai, ana_int_trans ait
Where ait.art_no = aai.art_no(+)
And cast(ait.tran_date as date ansi) >= cast('PARAM_1' as date ansi)
And cast(ait.tran_date as date ansi) <= cast('PARAM_2' as date ansi))
UNION ALL
(Select aai.pa_no as PA,
0 as ASIS9,
0 as ASIS88,
0 as TT450,

0 as TT456
From ana_art_info aai, ana_int_trans_reason aitr
Where aitr.art_no = aai.art_no(+)
And cast(aitr.tran_date as date ansi) >= cast('PARAM_1' as date ansi)
And cast(aitr.tran_date as date ansi) <= cast('PARAM_2' as date ansi))) t1

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 07:53:41

SELECT SUM(value) Total, ABS(tran_Num)
FROM TableName
GROUP BY ABS(tran_Num)

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources