need help on SQL - 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 2008 Forums

Transact-SQL (2008)

need help on SQL

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-18 : 04:05:25

I've table and data as following,


declare @xmlData xml
set @xmlData='<data>
<ag><icNo>60816105987</icNo><icNoOld> </icNoOld><payerNme>Samat Bin Sani</payerNme><amt>400.00</amt>
<subCd>5500</subCd></ag><ag><icNo>51130055335</icNo><icNoOld> </icNoOld><payerNme>Jalaludin Bin Jalil                

</payerNme><amt>250.00</amt><subCd>5700</subCd></ag><ag><icNo>90826055028</icNo><icNoOld> </icNoOld><payerNme>Mukarramah Bt 

Ibrahim</payerNme><amt>560.50</amt><subCd>1000</subCd></ag><ag><icNo>50317055242</icNo> <icNoOld>            

</icNoOld><payerNme>Fatimah Bt Ismail</payerNme><amt>510.40</amt><subCd>0290</subCd></ag><ag><icNo>40301045163G</icNo>
<icNoOld>0130</icNoOld><payerNme>Abu Mansor Bin Hj Mohd Nor</payerNme><amt>1000.00</amt><subCd>0390</subCd></ag>
</data>'
/*icNo is unique*/

declare @tTrnxPayer table
(idx int identity, payer varchar(30), amt decimal(10,2));
/*payer is unique*/

insert into @tTrnxPayer(payer, amt) values('60816105987',290.50);
insert into @tTrnxPayer(payer, amt) values('90826055028',200.50);
insert into @tTrnxPayer(payer, amt) values('50317055242',1190.50);
insert into @tTrnxPayer(payer, amt) values('40301045163G',1000.00);

1. Please focus on xmlData(icNo) that only exist in @tTrnxPayer(payer)
2. If xmlData(icNo, amt) > @tTrnxPayer(payer,amt) then 'tm'
3. If xmlData(icNo, amt) < @tTrnxPayer(payer,amt) then 'kr'
4. If xmlData(icNo, amt) = @tTrnxPayer(payer,amt) then 'eq'

My expected result

payer              | amt     | reference
------------------------------------------
60816105987			 400.00    tm
90826055028          560.50    tm
50317055242			 510.40    kr
40301045163G         1000.00   eq

Hopefully, somebody can help to show the SQL statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 04:21:29

you need spoonfed answers for everything?

Its very much similar to your earlier question which i provided solution for
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166821

Try this yourself first based on other solution and then we will help if you face any issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-18 : 04:44:23

I listen to you sir

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-18 : 04:56:12

I've as following,

select t.u.value('icNo[1]','varchar(20)') as icNo,
t.u.value('payerNme[1]','varchar(200)') as payerNme,
t.u.value('subCd[1]','varchar(10)') as subCd, 
t.u.value('amt[1]','decimal(10,2)') as amt
into #temp
from @xmlData.nodes('/data/ag') t(u)

select t.icNo as icNo, t.payerNme, t.amt as amtXML, e.amt as amtT
from #temp t
inner join @tTrnxPayer e
on e.payer=t.icNo


drop table #temp

But I dont know how to generate
2. If xmlData(icNo, amt) > @tTrnxPayer(payer,amt) then 'tm'
3. If xmlData(icNo, amt) < @tTrnxPayer(payer,amt) then 'kr'
4. If xmlData(icNo, amt) = @tTrnxPayer(payer,amt) then 'eq'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 05:09:34

ok see below


declare @xmlData xml
set @xmlData='<data>
<ag><icNo>60816105987</icNo><icNoOld> </icNoOld><payerNme>Samat Bin Sani</payerNme><amt>400.00</amt>
<subCd>5500</subCd></ag><ag><icNo>51130055335</icNo><icNoOld> </icNoOld><payerNme>Jalaludin Bin Jalil                

</payerNme><amt>250.00</amt><subCd>5700</subCd></ag><ag><icNo>90826055028</icNo><icNoOld> </icNoOld><payerNme>Mukarramah Bt 

Ibrahim</payerNme><amt>560.50</amt><subCd>1000</subCd></ag><ag><icNo>50317055242</icNo> <icNoOld>            

</icNoOld><payerNme>Fatimah Bt Ismail</payerNme><amt>510.40</amt><subCd>0290</subCd></ag><ag><icNo>40301045163G</icNo>
<icNoOld>0130</icNoOld><payerNme>Abu Mansor Bin Hj Mohd Nor</payerNme><amt>1000.00</amt><subCd>0390</subCd></ag>
</data>'
/*icNo is unique*/

declare @tTrnxPayer table
(idx int identity, payer varchar(30), amt decimal(10,2));
/*payer is unique*/

insert into @tTrnxPayer(payer, amt) values('60816105987',290.50);
insert into @tTrnxPayer(payer, amt) values('90826055028',200.50);
insert into @tTrnxPayer(payer, amt) values('50317055242',1190.50);
insert into @tTrnxPayer(payer, amt) values('40301045163G',1000.00);

select	m.icNo,
		m.amt,
		case when m.amt > p.amt then 'tm'
			 when m.amt < p.amt then 'kr'
			 when m.amt=p.amt then 'eq'
				end as reference
from(
		select	t.u.value('icNo[1]','varchar(30)') as icNo,
				t.u.value('amt[1]','decimal(10,2)') as amt
		from @xmldata.nodes('/data/ag')t(u)
	)m
inner join @tTrnxPayer p
on p.payer = m.icNo


output
-------------------------------------------------
icNo	        amt	reference
60816105987	400.00	tm
90826055028	560.50	tm
50317055242	510.40	kr
40301045163G	1000.00	eq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-18 : 05:16:48

tq sir. you're very strict. but gave me a big lesson :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 05:29:31

quote:
Originally posted by Idyana

tq sir. you're very strict. but gave me a big lesson :)

unless I'm like that people wont apply or learn anything by themselves

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources