SQL Server Performance Forum – Threads Archive

equal distribution of dates

i have a table in which i have three columns- start dates,end dates and
volume..the table has got about
90 000 rows …
i want to distribute the volume between two dates such as the volume gets
distributed according to the no of days in that month. lets say the start date is 22/05/2000 and end date is 20/06/2000 and the
volume is 121 , i need to distribute 121 in such a way that we find out the
volume for 22 days in may and then for 20 days in june…
i also want to create an additional column called month which should have
the month names and in the next column it should have the volumes??
can you please guide me??

Can you please elaborate a bit more? Usually the informations requested here<a target="_blank" href=http://www.aspfaq.com/etiquette.asp?id=5006>http://www.aspfaq.com/etiquette.asp?id=5006</a> greatly helps understanding ones problem. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

my table structure is like this start date end date volume month finalVolume
12/10/2000 22/10/2000 4000 oct 4000
03/09/2001 23/10/2001 200 sep ?
oct ? i need to distribute the volume(200) in such a way that i calculate volume for the no of days in sep and the rest in oct….. how shall i do that
i ve got a million rows like that
i mean if the month of start date and end date is same,volume remains the same……

Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail

quote:lets say the start date is 22/05/2000 and end date is 20/06/2000 and the
volume is 121 , i need to distribute 121 in such a way that we find out the
volume for 22 days in may and then for 20 days in june…

Instead of 22 days for May, I suppose you mean the number of days in May until June 1st, right? You will need to use a derived table that gives you the numbers relating to the dates, and then you can do the distribution calculations more easily. One issue that you haven’t mentioned is what entries that span 3 months or more – you’ll need additional processing for that. Assuming that StartDate and EndDate are never more than 1 month apart, you can use this – please add an identity column so you have an easy handle on each row: SELECT
X.RestOfStart * (A.FinalVolume / (X.RestOfStart + X.StartOfEnd)),
X.StartOfEnd * (A.FinalVolume / (X.RestOfStart + X.StartOfEnd)),
DATENAME(mm, A.startDate), A.FinalVolume
FROM
MyTable A
INNER JOIN
(SELECT T.IdentityColumn,
DATEDIFF(DAY, T.StartDate, DATEADD(MONTH, 1, CAST(CAST(YEAR(T.StartDate) AS VARCHAR(4)) + ‘-‘ + CAST(MONTH(T.StartDate) AS VARCHAR(2)) + ‘-1’ AS DATETIME))) RestOfStart,
DAY(t.EndDate) StartOfEnd
FROM MyTable T) AS X
ON A.IdentityColumn = X.IdentityColumn

Hm – that works only where StartDate and EndDate really are in different months. You’ll need to add WHERE MONTH(StartDate) <> MONTH(EndDate) to this query, and add a UNION to cover periods that are within a single month.

Hi guys Here is my sample data here is the result i want
Start_date End_dateVolume | vol_1 vol_2Nov
29-Oct 7-Nov 100 | 30(oct) 70(nov)
12-Jun 1-Jul 450 | 427.5(jun) 22.5(july)
30-Jan 12-Feb 1000 | 142.8571429(jan)857.1428571(feb)
the start dates and end date could be more than a month apart… the kind of final structure i want is something like this month Final_vol
1 12
2 34

]]>