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 : 01:30:56

My table as following,


declare @tPayer table
(idx int identity, icNo varchar(20), payerNme varchar(200));
/*icNo is a unique*/

insert into @tPayer(icNo,payerNme) values('60816105987','Samat Bin Sani');
insert into @tPayer(icNo,payerNme) values('90826055028','Mukarramah Bt Ibrahim');


declare @tEmployer table
(idx int identity, subCd varchar(10));
/*subCd is a unique*/

insert into @tEmployer(subCd) values('5500');
insert into @tEmployer(subCd) values('0390');

My XML as following,


<data><ag><icNo>60816105987 </icNo><icNoOld>            </icNoOld><payerNme>Samat Bin Sani</payerNme><amt>15004</amt><subCd>5500</subCd></ag><ag><icNo>51130055335 </icNo><icNoOld>            </icNoOld><payerNme>Jalaludin Bin Jalil                     </payerNme><amt>25004</amt><subCd>5700</subCd></ag><ag><icNo>90826055028 </icNo><icNoOld>            </icNoOld><payerNme>Mukarramah Bt Ibrahim                   </payerNme><amt>56074</amt><subCd>1000</subCd></ag><ag><icNo>50317055242 </icNo><icNoOld>            </icNoOld><payerNme>Fatimah Bt Ismail                       </payerNme><amt>51004</amt><subCd>0290</subCd></ag><ag><icNo>40301045163G</icNo><icNoOld>0130        </icNoOld><payerNme>Abu Mansor Bin Hj Mohd Nor              0</payerNme><amt>100004</amt><subCd>0390</subCd></ag>
</data>

I need to listing the New Payer and New Employer

My expected result as following,


/*New Payer*/
icNo                 | payerNme
----------------------------------------
51130055335            Jalaludin Bin Jalil 
50317055242            Fatimah Bt Ismail  
40301045163G           Abu Mansor Bin Hj Mohd Nor 0 

/*New Employer*/
subCd
-----------------------------------------
5700
1000
0290

Hopefully, someone can help me to write the SQL Statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 01:55:24

[code]
declare @tPayer table
(idx int identity, icNo varchar(20), payerNme varchar(200));
/*icNo is a unique*/

insert into @tPayer(icNo,payerNme) values('60816105987','Samat Bin Sani');
insert into @tPayer(icNo,payerNme) values('90826055028','Mukarramah Bt Ibrahim');

declare @tEmployer table
(idx int identity, subCd varchar(10));
/*subCd is a unique*/

insert into @tEmployer(subCd) values('5500');
insert into @tEmployer(subCd) values('0390');

--My XML as following,

declare @x xml
set @x='<data><ag><icNo>60816105987</icNo><icNoOld></icNoOld><payerNme>Samat Bin Sani</payerNme><amt>15004</amt><subCd>5500</subCd></ag><ag><icNo>51130055335</icNo><icNoOld> </icNoOld><payerNme>Jalaludin Bin Jalil</payerNme><amt>25004</amt><subCd>5700</subCd></ag><ag><icNo>90826055028 </icNo><icNoOld> </icNoOld><payerNme>Mukarramah Bt Ibrahim</payerNme><amt>56074</amt><subCd>1000</subCd></ag><ag><icNo>50317055242</icNo><icNoOld></icNoOld><payerNme>Fatimah Bt Ismail</payerNme><amt>51004</amt><subCd>0290</subCd></ag><ag><icNo>40301045163G</icNo><icNoOld>0130</icNoOld><payerNme>Abu Mansor Bin Hj Mohd Nor</payerNme><amt>100004</amt><subCd>0390</subCd></ag>
</data>'

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
into #temp
from @x.nodes('/data/ag') t(u)

select t.subCd as newemployer
from #temp t
left join @tEmployer e
on e.subCd=t.subCd
WHERE e.subCd IS NULL

select t.icNo as newpayerno,t.payerNme as newpayername
from #temp t
left join @tPayer p
on p.icNo = t.icNo
and p.payerNme=t.payerNme
WHERE p.payerNme IS NULL

drop table #temp

[/code]

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

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-18 : 02:09:00

tq sir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 02:25:13

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources