Comparing Rows...Important Pls

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 2000 Forums

Transact-SQL (2000)

Comparing Rows...Important Pls

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-06-23 : 09:26:55

Salute...
How can I compare two rows based on some field value??
I have an employees payroll table. I want to compare between the current month transaction and the previous month transaction. I want to fetch every field that differed!.
For example I have the following data :


EmployeeNo   BasicSalary   Loan   Treatment ...  TransMonth
----------   -----------   -----  ----------     -----------
1            100           0       0              4
2            200           0       0              4
3            300           0       0              4
1            150           10      20             5
2            250           0       0              5
3            300           0       0              5

I want the output to be as follows


EmployeeNo   CurrentMonth   PreviousMonth
----------   ------------   -------------
1            150            100
1            10             0
1            20             0
2            250            0

p.s. Please note that I dont want to fix the coulmns.. I want to detect ANY column that changed and fetch the change!
How can I do that??
Thanks In Advance.

Nazim
A custom title

1408 Posts

Posted - 2003-06-23 : 09:47:45

there might be a more efficent way.

select a.empno,b.basicsalary,a.basicsalary
from #emp a
inner join #emp b
on a.months=b.months-1
and a.empno=b.empno
and a.basicsalary<>b.basicsalary
union

select a.empno,b.loan,a.loan
from #emp a
inner join #emp b
on a.months=b.months-1
and a.empno=b.empno
and a.loan<>b.loan
union

select a.empno,b.treatment,a.treatment
from #emp a
inner join #emp b
on a.months=b.months-1
and a.empno=b.empno
and a.treatment<>b.treatment

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-23 : 09:48:09

You will have difficulty I think in returning randomly different columns per row. What could be done is that the entire row could be returned for any changed rows. That would be

SELECT * from yourtable
first
INNER JOIN yourtable second
ON
first.employeeno = second.employeeno and
first.transmonth = second.transmonth - 1 and
( first.basicsalary > second.basicsalary or
first.basicsalary < second.basicsalary
)

The more not equal conditions that you add, the poorer this query will become....

-------
Moo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-23 : 16:53:51

the most efficient way to compare rows in a table is the GROUP BY operator... joins don't work that well -- they don't handle nulls, and you need to do funky FULL OUTER stuff to get all possibilities.

Look at this:

select EmployeeNo, BasicSalary, Loan, Treatment, MIN(TransMonth) as Month
FROM
yourdata
WHERE TransMonth in (@CurrentMonth, @PreviousMonth)
GROUP BY EmployeeNo, BasicSalary, loan, treatment
HAVING COUNT(*) = 1

that returns all rows that not exactly the same between @currentMonth and @PreviousMonth.

If that format is not OK, let me know I can tell you how to get it into a cross tab-like format.

note that you are NOT grouping by transMonth, and the MIN() aggregate function is arbitrary since we are only returning groups in which the COUNT(*) is one.

- Jeff

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-24 : 04:19:21

quote:

select EmployeeNo, BasicSalary, Loan, Treatment, MIN(TransMonth) as Month
FROM
yourdata
WHERE TransMonth in (@CurrentMonth, @PreviousMonth)
GROUP BY EmployeeNo, BasicSalary, loan, treatment
HAVING COUNT(*) = 1

that returns all rows that not exactly the same between @currentMonth and @PreviousMonth.

Shouldn't it be returning the rows where the count is > 1?

-------
Moo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-24 : 07:46:10

if all columns in 2 rows are the same, the group by will summarize them together in 1 row and the count(*) will be 2.

if ANY columns are different, the group by will not be able to summarize them (since we are grouping on all columns we wish to compare) and the result will be two rows with no grouping being done -- each with a count(*) of 1.

that is why we DON'T group by the TransMonth -- if we did, the count(*) would always be 1. and since we are only returning rows in which no summarization was done, the Min(transmonth) just returns the transmonth of each row that could not be GROUP'ed -- and thus is a unique row in the database.

- Jeff

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-06-24 : 08:26:29

First Of All Thanks To All Of U!...
Nazim...
Your method was Exactly the way I did it!.Thanks.
jsmith8858...
I like your way... Thanks...but I would like to know how to do the cross-tab view?
Thanks Again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources