Compare and select 2 diff files with same columns

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

Compare and select 2 diff files with same columns

Author

Topic

Rajansql
Starting Member

4 Posts

Posted - 2010-09-01 : 11:39:48

Respectful SQL Gurus,

I need to compare 2 different files which have same columns.
FILE 1 : policy, Amt1, Amt2, Amt3, Amt4, and Amt5... Amt16
FILE 2 : policy, Amt1, Amt2, Amt3, Amt4, and Amt5....Amt16

If any amount field in FILE 1 has ZERO, but has a value in the same amount field in FILE 2, then, I need to select that record.

It will be great, if i can show both values adjucent to each other.
Example : Policy#, 0.00/500.85

Any clue will be greatly appreciated. Oh, by the way, i use DB2. but, if you can give a hint in SQL SERVER, I can try change that in DB2.
Thank you so much
Raj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 12:25:25

do u mean you want to show only those amounts which has 0 value in file1?

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

Rajansql
Starting Member

4 Posts

Posted - 2010-09-01 : 12:43:07

Thank You for looking at my question.

Sorry if I have confused.

file 1 : Quarter 1
File 2 : Quarter 2
I wanted to tell the business that ( on any of these 16 different amount columns ) , " Look, Quarter 1 amount has 0 value, but quarter 2 amount has some dollar value.

Busienss does not care if quarter 1 ( File 1) amount field is greater than 0, but they care if quarter 1 has zero value, but quarter 2 has some dollars value.

If any amount field in FILE 1 has ZERO, but has a value in the same amount field in FILE 2, then, I need to select that record.
Hope, this time i try to be clear :)
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 12:51:19

[code]select
from file1 f1
JOIN file2 f2
ON f2.policy = f1.policy
AND ((f1.Amt1 = 0 AND f2.Amt1 >0)
OR (f1.Amt2 = 0 AND f2.Amt2 >0)
OR (f1.Amt3 = 0 AND f2.Amt3 >0)
OR (f1.Amt4 = 0 AND f2.Amt4 >0)
...
OR (f1.Amt16 = 0 AND f2.Amt16 >0)
)
[/code]

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

Rajansql
Starting Member

4 Posts

Posted - 2010-09-01 : 13:44:27

Oh..Thank You so much Visakh.. I really appreciate that. Your solution worked perfect.

It will be great, if i can show both values adjucent to each other.. ( only if there is a difference )

Output : POLICY#, AMT FIELD, QTR 1 AMT, QTR 2 AMT
11570 AMT FLD5 0.00 500.65

I dont know, if thats too much to do in SQL

Thank You again Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 13:49:17

quote:
Originally posted by Rajansql

Oh..Thank You so much Visakh.. I really appreciate that. Your solution worked perfect.

It will be great, if i can show both values adjucent to each other.. ( only if there is a difference )

Output : POLICY#, AMT FIELD, QTR 1 AMT, QTR 2 AMT
11570 AMT FLD5 0.00 500.65

I dont know, if thats too much to do in SQL

Thank You again Visakh


select f1.policy,f1.Amt1,f2.Amt1,f1.Amt2,f2.Amt2,....,f1.Amt16,f2.Amt16
from file1 f1
JOIN file2 f2
ON f2.policy = f1.policy
AND ((f1.Amt1 = 0 AND f2.Amt1 >0)
OR (f1.Amt2 = 0 AND f2.Amt2 >0)
OR (f1.Amt3 = 0 AND f2.Amt3 >0)
OR (f1.Amt4 = 0 AND f2.Amt4 >0)
...
OR (f1.Amt16 = 0 AND f2.Amt16 >0)
)

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

Rajansql
Starting Member

4 Posts

Posted - 2010-09-01 : 14:29:44

I am not sure, if this is possible.. ( with no dots in between )

Output :

POLICY#........Which AMT FIELD.......QTR 1 AMT..........QTR 2 AMT.......... heading

11570.............AMT FLD5............. 0.00 ........... 500.65............ value
12584 AMT FLD 14 0.00 800.45 VALUES

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources