SQL Server Performance Forum – Threads Archive
how to prorate?
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 ? ? ?
How about the expected result ?
KH
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
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 ?
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 ? ? ?
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
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
]]>