date distribution

Hi
I have a table with 3 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

for ex
start date end date volume month finalVolume
1/1/2000 12/1/2000 100 jan 100
12/10/2000 30/11/2000 120 oct ?
nov ?

Please help me

Are you trying to do this with a query or using a VS language such as C#?

Regards,

Mike Wachal
SQL Express team

|||

Hi Mike

i am trying to do this with a query..but dont really know how to achieve this...can u help

|||

To do this in a query you'll either need a stored procedure or a function. I'm sending this one over to the T-SQL forum where the gurus hang out.

Mike

|||

Stored Procedures? C#? bit of over kill :)

1 query :

using a table VOLUMES with the columns

startdate datetime

enddate datetime

volumes float

i ran the following query, it gets the data you want and you can expand it to cover more that 2 months, each month getting its own column.

I hope it helps, it is just a starter though.

select
startdate, enddate, month1,
case
when month2 is null then month1
when month2 is not null then
total * (month1/(month1+month2))
end,
month2,
case
when month2 is null then month1
when month2 is not null then
total * (month2 /(month1+month2))
end,
total
from (
select
startdate,
enddate,
cast(datediff(day, startdate,dateadd(day, -1, cast(year(startdate)as varchar)+right('0'+cast(month(startdate)+1 as
varchar),2)+'01'))as float) as month1,
case
when month(startdate) = month(enddate) then null
when month(startdate) <> month(enddate) then
datediff(day, cast(year(enddate)as varchar)+right('0'+cast(month(enddate) as varchar),2)+'01', enddate)+1
end as month2,
volumes as total
from volumes) as tab1

|||

Hi
Sam

Thanks for your help

i just tried out the query u sent me ..but i am getting an error as

"the conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

i have got the same data type as u had in the volume table

|||

Hi Sam

i just tried the query which u sent me .. iam getting the following error..

i have the same data types as u do in ur volumes table..

This is the error i am getting....

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

|||

Easily fixed :)

I only tested for jan - may... when the month is december the part that figures out the last day of that month is trying to get to the 13th month

the two lines that contain:

cast(year(startdate)as varchar)+right('0'+cast(month(startdate)+1 as
varchar)

need that replacing with something like:

cast(year(startdate)as varchar)+right('0'+cast(case when month(startdate)+1 = 13 then 1 else month(startdate)+1 end as varchar)

|||

OK - now with comments and proper formatting. I should point out that this will ONLY work if the difference is always between 2 contiguous months. If there are 3 or 4 or 5 months between the start and end dates then this won't work. However this method can be built on. Although going past 4 months you might want to use a different method.

I think to get any number of months you might need to join on to a second "dates" table.. but let me know how you get on with this one :)

One last point... SQL Server 2000 (which i use) is very poor for built in date functions, especially if you're from an Oracle back ground. If you use date calculations a lot then its worth while investing in a day to write some user defined functions to do the calculations.

select
/* this section selects from our derived table -
use a derived table to work out how many days
are in each of our 2 months
*/
startdate,
enddate,
month1 as [month1 days],
/* calculate the portion for month 1 using: volume *(month1 days/total days) */
/* however is month2 is null then everything is in month1 */
case
when month2 is null then
month1
when month2 is not null then
total * (month1/(month1+month2))
end as [month1 pro rata volume],
/* number of days in month2 */
month2 as [month2 days],
/* month 2 is calculated a similar way : volume *(month2 days/total days) */
case
when month2 is null then
month1
when month2 is not null then
total * (month2 /(month1+month2))
end as [month2 pro rata volume],
/* now show the original total volumne to check the figures against */
total

from
/*
this is the main body of the select, our derived table. It could be done without the
derived table and might be quicker but it nicely seperates the 2 different calculations
that the query needs to do. The derived table works out how many days are in each of our 2 months
*/
(
select
startdate,
enddate,
/* need to figure out the number of days in month1
take the year, month+1 and the first day of the month as strings to create a new date string
which is the first day of the next month. Subtract 1 day from this date to get the last day
of the month we're working with.
the number of days left in the month is this difference (datediff) between the derived month end date
and the original column value
*/
cast(
datediff(day, startdate,
dateadd(day, -1,
case /* accomodate for end of year */
when right('0'+cast(month(startdate)+1 as varchar),2) = '13' then
cast(year(startdate)+1 as varchar)
else
cast(year(startdate) as varchar)
end
+
case /* accomodate for end of year */
when right('0'+cast(month(startdate)+1 as varchar),2) = '13' then
'01'
else
right('0'+cast(month(startdate)+1 as varchar),2)
end
+'01'))as float) as month1,
/* Month2 is similar to month 1 but going the other way round - ie we need to
get the first day of this month month, then work out the difference between the original
date and the derived first day of month. The calculation is a bit easier on the eye :) */
case
when month(startdate) = month(enddate) then
null
when month(startdate) <> month(enddate) then
cast(datediff(day,
cast(year(enddate) as varchar)
+ right('0'+cast(month(enddate)as varchar),2)
+'01',
enddate)
as float) + 1
end as month2,
/* and get the volume */
volumes as total
from volumes) as tab1

|||

Cheers Sam!!!

This works well

so now will this query work even if the difference in the no of months are more than one?

i would really like to have that query sam..

and one more thing ..

i want to perform the grouping on my table probably on months and sum of the volumes...

how shall i do that? because the whole purpose of generating this query is to make reports and send it across to our clients....

pls help

many thanks

Mita

|||I've got the code for the full version, i'll post it up when i get home from work.|||

Back from work now :)

ok - here's how to do the full pro rata in a select statement.

First we need to do some prep

1. Create a dates table, this just holds a very long list of dates. Its actually a very handy table to have on your database if you do this sort of work frequently.

create table static_tab(
cnt int,
dates datetime)
GO

2. Populate the table. I've picked a start date of 1st Jan 1990, you can pick any date you like. Set the while condition for the number of days, or you could easily set an end date.

declare @.cnt int
set @.cnt = 1

declare @.date datetime
set @.date = '19900101'

while @.cnt < 10000
begin
insert into static_tab values (@.cnt, @.date)
set @.date = dateadd(day, 1, @.date)
set @.cnt = @.cnt+1
end
GO

3. Date functions. I'll get your library of functions started off with these three.

3a. First day - this returns the first day of the month for a given date

create function firstday (@.ldate as datetime) returns datetime
as
begin
return
cast(
cast(
year(@.ldate) as varchar) +
right('0'+cast(month(@.ldate)as varchar),2) +
'01'
as datetime)

end

GO

3b. Last day - returns the last day of the month

create function lastday(@.ldate as datetime) returns datetime
as
begin
return
cast(
dateadd(day, -1,
case /* accomodate for end of year */
when right('0'+cast(month(@.ldate)+1 as varchar),2) = '13' then
cast(year(@.ldate)+1 as varchar)
else
cast(year(@.ldate) as varchar)
end
+
case /* accomodate for end of year */
when right('0'+cast(month(@.ldate)+1 as varchar),2) = '13' then
'01'
else
right('0'+cast(month(@.ldate)+1 as varchar),2)
end
+'01')
as datetime)
end

GO

3c. days left - returns the number of days remainnig in a month for any given date

create function daysleft(@.ldate as datetime) returns int
as
begin
return

cast(
datediff(day, @.ldate,
dateadd(day, -1,
case /* accomodate for end of year */
when right('0'+cast(month(@.ldate)+1 as varchar),2) = '13' then
cast(year(@.ldate)+1 as varchar)
else
cast(year(@.ldate) as varchar)
end
+
case /* accomodate for end of year */
when right('0'+cast(month(@.ldate)+1 as varchar),2) = '13' then
'01'
else
right('0'+cast(month(@.ldate)+1 as varchar),2)
end
+'01'))as int)

end
GO

OK - so now we have a table and 3 functions which are added to your database and which will provide some good use in future.

The select statement for the full query is as follows, you will notice that with the functions we've created that the select statement is a lot neater:

select
[month],per.dates,
/* now work out the volume for this month
there are 4 different scenarios
1. start and end dates are both in a single month
2. the end of a first month
3. the start of an end term month
4. a complete mid term month
We can use a case statement to capture each one then calculate accordingly
*/
sum(
cast(
case
-- in each of these cases we want to check the month and the year
when month(vol.startdate) = month(vol.enddate)
and year(vol.startdate) = year(vol.enddate)then
-- this is case 1
-- use vol
vol.volumes
when month(per.dates) = month(vol.startdate)
and year(per.dates) = year(vol.startdate)
and month(vol.startdate) <> month(vol.enddate) then
-- this is case 2, end of the first month
-- use vol * (days left in month / total days)
vol.volumes * (dbo.daysleft(vol.startdate)/cast(datediff(day, vol.startdate, vol.enddate)as float))
when month(per.dates) = month(vol.enddate)
and year(per.dates) = year(vol.enddate)
and month(vol.startdate) <> month(vol.enddate) then
-- this is case 3, the start of an end term month
vol.volumes * (datepart(day, vol.enddate)/cast(datediff(day, vol.startdate, vol.enddate)as float))
else
-- this is case 4, a month in between the start and end months
vol.volumes * (dbo.daysleft(per.dates)/cast(datediff(day, vol.startdate, vol.enddate)as float))
end
as decimal(38,2))
) as [pro rata volume]
from
( -- derived table, just for readability
select
cast(year(dates)as varchar) +'-'+ cast(datename(month, dates)as varchar) as [month],
dates
from static_tab
where datepart(day, dates) = 1) as [per]
inner join volumes as vol on
(
-- use the two functions we created to help the join
per.dates >= dbo.firstday(vol.startdate) and per.dates <= dbo.lastday(vol.enddate)
)
-- the group by
group by per.[month], per.dates
order by per.dates