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 for a VIEW

Author  Topic 

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-04-22 : 09:25:18
Hello All,
My sample data looks like this in SQL Server 2000

SubGr,Trno,Glcode,Amount,DrCr
----------------------------
Bank,1000,100001,1000.00,D
Other,1000,100012,1000.00,C

Other,1001,100010,1500.00,D
Other,1001,100010,1500.00,D
Bank,1001,100002,3000.00,C

Bank,1002,100001,2000.00,D
Bank,1002,100003,2000.00,C

Other,1003,100051,5000.00,D
Other,1003,100051,5000.00,C

I want a view which gives a output like this for those SubGr with 'Bank'.

Output

SubGr,Trno,Glcode,Amount,DrCr,Bank
----------------------------------
Other,1000,100012,1000.00,C,100001
Other,1001,100010,1500.00,D,100002
Other,1001,100010,1500.00,D,100002
Bank,1002,100001,2000.00,D,100003
Bank,1002,100003,2000.00,C,100001

Thanks in anticipation

Nirene

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-22 : 09:29:12
i don't understand your logic. can you explain more?

Em
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 09:59:53
[code]-- Prepare sample data
DECLARE @Sample TABLE (SubGr VARCHAR(5), TrNo INT, GlCode INT, Amount MONEY, DrCr CHAR(1))

INSERT @Sample
SELECT 'Bank', 1000, 100001, 1000.00, 'D' UNION ALL
SELECT 'Other', 1000, 100012, 1000.00, 'C' UNION ALL
SELECT 'Other', 1001, 100010, 1500.00, 'D' UNION ALL
SELECT 'Other', 1001, 100010, 1500.00, 'D' UNION ALL
SELECT 'Bank', 1001, 100002, 3000.00, 'C' UNION ALL
SELECT 'Bank', 1002, 100001, 2000.00, 'D' UNION ALL
SELECT 'Bank', 1002, 100003, 2000.00, 'C' UNION ALL
SELECT 'Other', 1003, 100051, 5000.00, 'D' UNION ALL
SELECT 'Other', 1003, 100051, 5000.00, 'C'

-- Show the expected output
SELECT o.SubGr,
o.TrNo,
o.GlCode,
o.Amount,
o.DrCr,
b.GlCode AS Bank
FROM @Sample AS o
INNER JOIN @Sample AS b ON b.TrNo = o.TrNo
AND b.SubGr = 'Bank'
WHERE o.SubGr = 'Bank'
AND o.GlCode <> b.GlCode
OR o.SubGr = 'Other'[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-04-23 : 02:43:42
Hello Peso,
Thanks for your reply.In the same scenario if SubGr is in a separate master as GLMAST with fields SubGr VarChar(6),Glcode Char(10),Gldesc Varchar(5).How do I go about?.I tried but I got messed up.

Awaiting your reply.

Nirene
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 02:58:31
Please read and understand this blog post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Follow the steps (similar to what I posted) and you will get an answer.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -