Proportion of min record in sum

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)

Proportion of min record in sum

Author

Topic

odieb
Starting Member

2 Posts

Posted - 2004-06-24 : 04:14:15

Hi,
I am struggling with the following query. I need to simplify it, but can't get my head around it -

declare @End as datetime, @Start as datetime, @Over as int, @Minus as float, @Sum as float

select @End = max(enddate) from testeps where enddate <= '19990331'

-- to check what is actually used
select * from testeps
where enddate <= @End
and enddate > dateadd("yy", -1, @End)

--!!
select @Over = (sum(nomonths) - 12), @Start = min(startdate), @Sum = sum(earning)
from testeps
where enddate <= @End
and enddate > dateadd("yy", -1, @End)

if @Over > 0
select @Minus = (earning * @Over / NoMonths) from testeps where Startdate = @Start
else
set @Minus = 0

select sum(earning) - @Minus '12month', @Sum 'sum'
from testeps
where enddate <= @End
and enddate > dateadd("yy", -1, @End)

Details: I have records with a startdate, enddate and earnings. I need to get a sum of the earnings over a 12 month period, working back from the last end date found, closest to the date the user specified. Here is a sample of the data:

startdate,enddate,nomonths,earning
01 Jul 2003,31 Dec 2003,6,401.19999999999902
01 Jan 2003,30 Jun 2003,6,481.80000000000001
01 Jul 2002,31 Dec 2002,6,629.45000000000005
01 Jan 2002,30 Jun 2002,6,659.54999999999905
01 Jul 2001,31 Dec 2001,6,565.84000000000003
01 Jan 2001,30 Jun 2001,6,459.94
01 Jul 2000,31 Dec 2000,6,437.81999999999903
01 Jan 2000,30 Jun 2000,6,432.82999999999902
01 Jul 1999,31 Dec 1999,6,311.94999999999902
01 Jan 1999,30 Jun 1999,6,203.5
01 Oct 1998,31 Dec 1998,3,150.5
01 Apr 1998,30 Sep 1998,6,247.0
01 Oct 1997,31 Mar 1998,6,268.52999999999901
01 Apr 1997,30 Sep 1997,6,262.0
01 Oct 1996,31 Mar 1997,6,274.57999999999902
01 Apr 1996,30 Sep 1996,6,261.25
01 Oct 1995,31 Mar 1996,6,220.68000000000001
01 Apr 1995,30 Sep 1995,6,215.0
01 Oct 1994,31 Mar 1995,6,193.849999999999
01 Apr 1994,30 Sep 1994,6,167.5
01 Oct 1993,31 Mar 1994,6,183.25
01 Apr 1993,30 Sep 1993,6,137.25
01 Oct 1992,31 Mar 1993,6,146.08000000000001
01 Apr 1992,30 Sep 1992,6,119.0
01 Oct 1991,31 Mar 1992,6,144.5
01 Apr 1991,30 Sep 1991,6,136.5
01 Oct 1990,31 Mar 1991,6,155.5
01 Apr 1990,30 Sep 1990,6,124.0
01 Oct 1989,31 Mar 1990,6,195.5
01 Apr 1989,30 Sep 1989,6,142.5
01 Oct 1988,31 Mar 1989,6,176.25
01 Apr 1988,30 Sep 1988,6,110.75
01 Oct 1987,31 Mar 1988,6,127.75
01 Apr 1987,30 Sep 1987,6,69.75
01 Oct 1986,31 Mar 1987,6,100.75
01 Apr 1986,30 Sep 1986,6,63.5
01 Oct 1985,31 Mar 1986,6,81.0
01 Apr 1985,30 Sep 1985,6,49.75
01 Oct 1984,31 Mar 1985,6,58.329999999999899
01 Apr 1984,30 Sep 1984,6,38.18
01 Oct 1983,31 Mar 1984,6,51.630000000000003
01 Apr 1983,30 Sep 1983,6,36.380000000000003
01 Oct 1982,31 Mar 1983,6,36.899999999999899
01 Apr 1982,30 Sep 1982,6,34.079999999999899
01 Oct 1981,31 Mar 1982,6,42.229999999999897
01 Jan 1900,30 Sep 1981,6,42.75

odieb
Starting Member

2 Posts

Posted - 2004-06-24 : 05:21:57

figured it out ... here it is, if anybody's interested.

declare @End as datetime

select @End = max(enddate) from testeps where enddate <= '20000831'--'19990331' --'19980630'--

select sum(earning) 'sum', sum(case when startdate < dateadd("yy", -1, @End) then (earning * datediff("m", dateadd("d",-1,startdate), dateadd("yy", -1, @End))/nomonths) else earning end) '12m'
from testeps
where enddate <= @End
and enddate > dateadd("yy", -1, @End)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-24 : 07:38:42

amazing! sometimes....just asking others to help inspires the author!
another sqlteam.com success.......for no effort

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources