Diff - 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 2005 Forums

Transact-SQL (2005)

Diff

Author

Topic

merriebeth
Starting Member

2 Posts

Posted - 2009-03-30 : 14:35:54

Wow, what a great forum! New here but thought I would give it a try.

Here is the layout of the table:

GiftID Date Amount DonorID EffortID
1 26-03-2009 25 1 09DAB
2 25-03-2009 30 2 09DAB
3 15-01-2009 20 2 09DAA
4 13-01-2009 50 1 09DAA
5 26-12-2008 15 2 08DAH
6 24-12-2008 25 1 08DAH
7 16-11-2008 15 1 08DAG

I am trying to get the difference in amounts and credit effort ID so results would look like this, where the diff in amounts is based on date order by donorid and effort id is the more recent by date of the two gifts.

Difference EffortID DonorID
10 08DAH 1
25 09DAA 1
-25 09DAB 1
5 09DAA 2
10 09DAB 2

Thanks for your help!!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-30 : 15:11:00

couldn't follow the output. Can you explain ?

merriebeth
Starting Member

2 Posts

Posted - 2009-03-30 : 16:03:42

quote:
Originally posted by sakets_2000

couldn't follow the output. Can you explain ?

Sure, thanks for asking. I'll use only those for Donor ID 1.

Difference EffortID DonorID
10 08DAH 1
25 09DAA 1
-25 09DAB 1

The results are the difference between gift records in descending order by donor id. First one is the difference between gift id 7 and gift id 6 as they are the first two gifts by donor 1 in date order. I should have numbered gift id's in opposite order, would have made better sense. The effortid indicated is the most recent of the two gift records. Second difference listed is difference between giftid 6 and 4, with effortid being 4, the more recent of the two. Third difference is teh diff between giftid 4 and 1, with effortid being 1, the most recent of the two.

Thanks again for your help, this one really has me...

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-30 : 17:17:01

[code]

Declare @MYTable table (ROWID int Primary Key Clustered, GiftID int ,date datetime,Amount money,DonorID int,EffortID varchar(6),Differance money)
insert into @MyTable(RowID,GiftID,Date,Amount,DonorID,EffortID)
Select Row_Number() over (order by donorID,Date), GiftID,Date,Amount,DonorID,EffortID
From
(
select 1 as GiftID, convert(datetime,'26-03-2009',105)as date, 25 as Amount, 1 as DonorID, '09DAB' as EffortID Union All
select 2, convert(datetime,'25-03-2009',105), 30, 2, '09DAB' Union All
select 3, convert(datetime,'15-01-2009',105), 20, 2, '09DAA' Union All
select 4, convert(datetime, '13-01-2009',105), 50, 1, '09DAA' Union All
select 5, convert(datetime,'26-12-2008',105), 15, 2, '08DAH' Union All
select 6, convert(datetime,'24-12-2008',105), 25, 1, '08DAH' Union All
select 7, convert(datetime,'16-11-2008',105), 15, 1, '08DAG'
) a
order by DonorID,Date

Declare @DonorID int,@Amount money ,@Anchor int, @PriorAmount money

Update s
Set
@Anchor = s.RowID
,@Amount = Differance =case when s.DonorID = @DonorID then s.Amount - @PriorAmount else null end
,@PriorAmount = s.Amount
,@DonorID = s.DonorID
from
@MyTable s

select Differance,EffortID,DonorID
From @MyTable
where not Differance is null
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources