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

Transact-SQL (2000)

Difficult one

Author

Topic

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-09-19 : 18:44:52

Hi Guys

I Have not been able to solve this problem from quiete a while now.

I am using sql server 2005.

I have got a table which contains these columns - start date, end date and volumes
if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month

I have to perform a query on this table so that I can group the volumes for different months and different years.

Here is the sample data...

Service Start Service End FMIS Code volumes
01-Oct-00 15-Aug-01 6440 32
01-Oct-00 30-Sep-02 6441 40
01-Oct-02 22-May-03 6440 78
01-Oct-02 23-May-03 6990 87
06-Mar-03 31-Jul-03 6997 102
07-Mar-03 31-Jul-03 6744 3
01-May-03 31-May-03 6440 789
23-Jun-03 31-Aug-03 6447 1000
29-Jun-03 30-Jun-03 6440 981

I have to calculate the result in the following format
FMIS CodeYear Month FinalVolumes
6447 2000 11 ?
6447 2000 10 ?
and so on

I have to calculate the result by distributing the volumes in such a way that if the months of start and end dates are same, the the result (volumes) will have the value of no of units. But if the months and years are different in start and end dates for example if the start date is 1st jul and end date is 23rd aug, then I have to distribute the no of units in such a way that it gets prorated for 30 days in july and the rest 23 days in Aug.

Hope this helps

Please help guys!

Thanks

Mita

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-19 : 20:37:03

Mita,
Will your dates always be from consecutive months? If so it will make things easier...

Cheers,

Tim

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-09-19 : 21:37:39

Hi Tim
Unfortunately not . These could span upto different years as well!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-19 : 21:46:43

Divide the Volume by the number of days, and then sum up by year and month after joining to a date table.


select 
	a.FMIS,
	b.YEAR,
	b.MONTH,
	VOL_DISTRIBUTION =
	convert(decimal(10,2),
	round(sum(a.VOL/(datediff(dd,a.D1,a.D2)+1. )),2))
FROM
	(
	select
		d1=convert(datetime,'01-Oct-00'),
		d2=convert(datetime,'15-Aug-01'), 
		FMIS=6440,
		VOL=32
	union all
	select
		d1=convert(datetime,'01-Oct-00'),
		d2=convert(datetime,'30-Sep-02'), 
		FMIS=6441,
		VOL=40
	) a
	join
	-- Date table function available in Script Library Forum
	dbo.F_TABLE_DATE('20001001','20020930') b
	on b.DATE between a.D1 AND a.D2
group by
	a.FMIS,
	b.YEAR,
	b.MONTH
order by
	a.FMIS,
	b.YEAR,
	b.MONTH

Results:


FMIS        YEAR   MONTH VOL_DISTRIBUTION 
----------- ------ ----- ---------------- 
6440        2000   10    3.11
6440        2000   11    3.01
6440        2000   12    3.11
6440        2001   1     3.11
6440        2001   2     2.81
6440        2001   3     3.11
6440        2001   4     3.01
6440        2001   5     3.11
6440        2001   6     3.01
6440        2001   7     3.11
6440        2001   8     1.50
6441        2000   10    1.70
6441        2000   11    1.64
6441        2000   12    1.70
6441        2001   1     1.70
6441        2001   2     1.53
6441        2001   3     1.70
6441        2001   4     1.64
6441        2001   5     1.70
6441        2001   6     1.64
6441        2001   7     1.70
6441        2001   8     1.70
6441        2001   9     1.64
6441        2001   10    1.70
6441        2001   11    1.64
6441        2001   12    1.70
6441        2002   1     1.70
6441        2002   2     1.53
6441        2002   3     1.70
6441        2002   4     1.64
6441        2002   5     1.70
6441        2002   6     1.64
6441        2002   7     1.70
6441        2002   8     1.70
6441        2002   9     1.64

(35 row(s) affected)

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-19 : 22:21:25

Borrowing the codes from MVJ.

Note uses DATE >= Service_Start and and DATE < Service_End rather than between as between will double count the Volume for 6440 for June 2003


declare @table table
(
	Service_Start 	datetime,
	Service_End 	datetime,
	FMIS_Code 	int,
	Volumes		int
)
insert into @table
select	'01-Oct-00', '15-Aug-01', 6440, 32	union all
select	'01-Oct-00', '30-Sep-02', 6441, 40	union all
select	'01-Oct-02', '22-May-03', 6440, 78	union all
select	'01-Oct-02', '23-May-03', 6990, 87	union all
select	'06-Mar-03', '31-Jul-03', 6997, 102	union all
select	'07-Mar-03', '31-Jul-03', 6744, 3	union all
select	'01-May-03', '31-May-03', 6440, 789	union all
select	'23-Jun-03', '31-Aug-03', 6447, 1000	union all
select	'29-Jun-03', '30-Jun-03', 6440, 981 

declare	@min_date	datetime,
	@max_date	datetime

select	@min_date = min(Service_Start), @max_date = max(Service_End)
	from	@table

select	FMIS_Code, YEAR, MONTH, sum(convert(decimal(10,2), Volumes * 1.0 / datediff(day, Service_Start, Service_End))) as FinalVolumes
from	F_TABLE_DATE(@min_date, @max_date) d 
	inner join @table t
	on	d.DATE	>=	t.Service_Start
	and	d.DATE	<	t.Service_End
group by FMIS_Code, YEAR, MONTH
order by FMIS_Code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources