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 different months and different years.
Here is the sample data...
I have to calculate the columns coloured in blue..
Please help guys!
Thanks
Mita
I don't fully understand the purpose of the year and month columns. Should they relate to the Service Start or Service End or are they a count of the years and months over which the volume is spread?
Presumably you then want to spread the volume over however many months. Should these volumes remain an integer value?
Months and years are not the best units of measure for the reasons you state in your post. I believe weeks is a much better measure (although we all know our managers know better ;) ). Obviously a Quantity spread over three months from Jan to Mar is not the same as the same quantity spread over say Oct to Dec. I'm sure you know this, and a query will not be impossible, but there is going to be either
a) an error in the results or
b) an inconsistency in results over different portions of the year.
Which would you prefer?
|||
Hi Thanks for replying
Well I have been doing this query in access from quiete a long time and am doing it for each individual month.
Therefore I need to worko out a way to make this less complicated. The reason why I included year and month was because when the month and year in start date and end date are not same,then we have to distribute the number of units in such a way for ex if start date is 20th oct and end date is 10th nov then we have to calculate the result by finding out volumes for 20 days in october and (october month) and 10 days for november(november month).
I have pasted the exact coding which I do in Access query for individual month(its a bit scary!)
This one is for Year 2004
Jan 2004
IIf([Service End]<#1/01/2004#,0,IIf([Service Start]>#31/12/2003# And [Service end]<#1/02/2004#,[MyTable]![NoOfUnits],IIf([Service Start]<#1/01/2004# And [Service end]<#1/02/2004#,([Service End]-#31/12/2003#)/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits],IIf([Service Start]<#1/01/2004# And [Service End]>#31/01/2004#,31/(1+[Service End]-[Service Start])*[MyTable]![NoofUnits,IIf([Service Start]>#31/01/2004#,0,(#1/02/2004#-[Service Start])/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits])))))
Feb 2004
IIf([Service End]<#1/02/2004#,0,IIf([Service Start]>#31/01/2004# And [Service end]<#1/03/2004#,[MyTable]![NoofUnits],IIf([Service Start]<#1/02/2004# And [Service end]<#1/03/2004#,([Service End]-#31/01/2004#)/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits],IIf([Service Start]<#1/02/2004# And [Service End]>#28/02/2004#,29/(1+[Service End]-[Service Start])*[MyTable]![NoofUnits],IIf([Service Start]>#28/02/2004#,0,(#1/03/2004#-[Service Start])/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits])))))
and so on for the rest of the months.....
No comments:
Post a Comment