SQL Server Performance

equal distribution of dates

Discussion in 'SQL Server 2005 General Developer Questions' started by mita, Jun 17, 2006.

  1. mita New Member

    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??
  2. FrankKalis Moderator

    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=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  3. mita New Member

    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......
  4. Madhivanan Moderator

    Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  5. Adriaan New Member

    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
  6. Adriaan New Member

    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.
  7. mita New Member

    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

Share This Page