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 2000SubGr,Trno,Glcode,Amount,DrCr----------------------------Bank,1000,100001,1000.00,DOther,1000,100012,1000.00,COther,1001,100010,1500.00,DOther,1001,100010,1500.00,DBank,1001,100002,3000.00,CBank,1002,100001,2000.00,DBank,1002,100003,2000.00,COther,1003,100051,5000.00,DOther,1003,100051,5000.00,CI want a view which gives a output like this for those SubGr with 'Bank'.OutputSubGr,Trno,Glcode,Amount,DrCr,Bank----------------------------------Other,1000,100012,1000.00,C,100001Other,1001,100010,1500.00,D,100002Other,1001,100010,1500.00,D,100002Bank,1002,100001,2000.00,D,100003Bank,1002,100003,2000.00,C,100001Thanks in anticipationNirene |
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-22 : 09:59:53
|
[code]-- Prepare sample dataDECLARE @Sample TABLE (SubGr VARCHAR(5), TrNo INT, GlCode INT, Amount MONEY, DrCr CHAR(1))INSERT @SampleSELECT 'Bank', 1000, 100001, 1000.00, 'D' UNION ALLSELECT 'Other', 1000, 100012, 1000.00, 'C' UNION ALLSELECT 'Other', 1001, 100010, 1500.00, 'D' UNION ALLSELECT 'Other', 1001, 100010, 1500.00, 'D' UNION ALLSELECT 'Bank', 1001, 100002, 3000.00, 'C' UNION ALLSELECT 'Bank', 1002, 100001, 2000.00, 'D' UNION ALLSELECT 'Bank', 1002, 100003, 2000.00, 'C' UNION ALLSELECT 'Other', 1003, 100051, 5000.00, 'D' UNION ALL SELECT 'Other', 1003, 100051, 5000.00, 'C'-- Show the expected outputSELECT o.SubGr, o.TrNo, o.GlCode, o.Amount, o.DrCr, b.GlCode AS BankFROM @Sample AS oINNER 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" |
|
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|