SQL Server Performance

how to prorate?

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

  1. mita New Member

    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 months and years.
    Here is the sample data...

    Service Start Service End FMIS Code No of Units Year Month 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 ? ? ?


  2. khtan New Member

    How about the expected result ?


    KH
  3. mita New Member

    Thats what I want
    If the month and year of service start equals that of service end, then no of units remain same . But if the month and year of service start and end are different, then I have to find out the no of days in the start date and distribute the no of units by multiplying no of days * no of units ) for that month and the remaining no of units go to the other month accordingly.
    Hope this helps
  4. khtan New Member

    quote:Here is the sample data...

    Service Start Service End FMIS Code No of Units Year Month 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 ? ? ?
    This is the sample data ?


    quote:"Thats what I want"
    Can you also kindly provide the value for the '?'


    quote: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
    Please also identify which is the original volume to be distributed.

    KH
  5. mita New Member

    I apologise for not being able to explain my problem to you properly.
    Volume is the one which is under the column no of units.
    I tried to cut and paste the table i have in this forum but it just shrinks .
    Anyways '?' means the value which i need to calculate. The column named volumes is the one under which the actual results go(when we distribute the no of units).

    Year and month are just the columns whose values we can find out just by simpe inbuilt functions.I should not have included those two. my apologies.

    Hope this helps

Share This Page