Help Required - 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 Required

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-12-05 : 05:34:44

Hello All,
I have 2 tables Trans & Slmast
In Trans
Tran_type,Tran_amt,Slcode
----------------------------
VES,10000.00,AB01
DBN,20000.00,AB01
CRN,30000.00,AB01
BRT,40000.00,AB01
DBN,10000.00,AB02
CRN,50000.00,AB02
BRT,20000.00,AB03
CRN,30000.00,AB03

In Slmast
Slcode,Sldesc
--------------
AB01,ABC Ltd
AB02,CAB Ltd
AB03,BAC Ltd

All transaction related datas will be in Trans for which Slmast is the master.
I want to sum those records based on slcode,tran_type for slcode which does not have tran_type with 'VES'

Sample Output
Slcode,Sldesc,DBN,CRN,BRT
AB02,CAB Ltd,10000,50000,0
AB03,BAC Ltd,0,30000,20000

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 05:41:21

[code]Select sm.SlCode, sm.SlDesc,
Sum(Case when t.Tran_Type = 'DBN' then Tran_Amt else 0.00 end) as DBN,
...
From Trans t JOIN Slmast sm
on t.SlCode = sm.SlCode
group by sm.SlCode, sm.SlDesc[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-12-05 : 05:44:47

Hello Harsh,
I want to sum records which does not have Tran_type with VES

Nirene

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 05:48:55

So what?
Filter it by adding WHERE condition.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-12-05 : 05:52:17

Hello Harsh,
I want the result for only AB02 & AB03 not AB01 cos it has a record with Tran_type with VES.

Nirene

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 06:01:13

[code]Select sm.SlCode, sm.SlDesc,
Sum(Case when t.Tran_Type = 'DBN' then Tran_Amt else 0.00 end) as DBN,
...
From Trans t JOIN
(Select Distinct slm.SlCode, slm.SlDesc from Slmast slm JOIN Trans tr
On slm.SlCode = tr.SlCode
Where tr.Tran_Type <> 'VES') sm
on t.SlCode = sm.SlCode
group by sm.SlCode, sm.SlDesc[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 06:20:20

[code]-- Prepare sample data
DECLARE @Trans TABLE (TranType VARCHAR(3), TranAmt MONEY, SlCode VARCHAR(4))

INSERT @Trans
SELECT 'VES', 10000.00, 'AB01' UNION ALL
SELECT 'DBN', 20000.00, 'AB01' UNION ALL
SELECT 'CRN', 30000.00, 'AB01' UNION ALL
SELECT 'BRT', 40000.00, 'AB01' UNION ALL
SELECT 'DBN', 10000.00, 'AB02' UNION ALL
SELECT 'CRN', 50000.00, 'AB02' UNION ALL
SELECT 'BRT', 20000.00, 'AB03' UNION ALL
SELECT 'CRN', 30000.00, 'AB03'

DECLARE @Master TABLE (SlCode VARCHAR(4), SlDesc VARCHAR(19))

INSERT @Master
SELECT 'AB01', 'ABC Ltd' UNION ALL
SELECT 'AB02', 'CAB Ltd' UNION ALL
SELECT 'AB03', 'BAC Ltd'

-- Show the expected output
SELECT t.SlCode,
m.SlDesc,
SUM(CASE WHEN t.TranType = 'DBN' THEN TranAmt ELSE 0 END) AS DBN,
SUM(CASE WHEN t.TranType = 'CRN' THEN TranAmt ELSE 0 END) AS CRN,
SUM(CASE WHEN t.TranType = 'BRT' THEN TranAmt ELSE 0 END) AS BRT
FROM @Trans AS t
INNER JOIN @Master AS m ON m.SlCode = t.SlCode
WHERE NOT EXISTS (SELECT * FROM @Trans AS x WHERE x.TranType = 'VES' AND x.SlCode = t.SlCode)
GROUP BY t.SlCode,
m.SlDesc
ORDER BY t.SlCode[/code]

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-12-05 : 06:41:38

See, Peso understood right away.

"Give me the full answer, not a push in the right direction where i might actually learn something"

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources