Prorating no of units
-
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...
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 ? ? ? 30-Jun-03 31-Jul-03 6000 50 ? ? ? 01-Jul-03 08-Jul-03 6002 54 ? ? ? 01-Jul-03 13-Jul-03 6000 562 ? ? ? I have to calculate the volume by distributing the no of units in such a way that if the months of start and end dates are same, the the result (volumes) will have the value of no of units. But if the months and years are different in start and end dates for example if the start date is 1st jul and end date is 23rd aug, then I have to distribute the no of units in such a way that it gets prorated for 30 days in july and the rest 23 days in Aug.
Hope this helps
Please help guys!
Thanks
Mita
-
This should do it... if you don't already have a TALLY table, here's how to make one...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
...and now, to solve the problem... (sure wish folks would save us some time by posting data in the form of executable code like below...)...
--===== If the test table exists, drop it IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL DROP TABLE #yourtable--===== Create the test table CREATE TABLE #yourtable ( [Service Start] DATETIME, [Service End] DATETIME, [FMIS Code] INT, [No of Units] INT, [Year] INT, [Month] INT, Volumes DECIMAL(15,6) )
--===== Populate the test table INSERT INTO #yourtable ([Service Start],[Service End],[FMIS Code],[No of Units]) SELECT '01-Oct-00','15-Aug-01',6440,32 UNION ALL SELECT '01-Oct-00','30-Sep-02',6441,40 UNION ALL SELECT '01-Oct-02','22-May-03',6440,78 UNION ALL SELECT '01-Oct-02','23-May-03',6990,87 UNION ALL SELECT '06-Mar-03','31-Jul-03',6997,102 UNION ALL SELECT '07-Mar-03','31-Jul-03',6744,3 UNION ALL SELECT '01-May-03','31-May-03',6440,789 UNION ALL SELECT '23-Jun-03','31-Aug-03',6447,1000 UNION ALL SELECT '29-Jun-03','30-Jun-03',6440,981 UNION ALL SELECT '30-Jun-03','31-Jul-03',6000,50 UNION ALL SELECT '01-Jul-03','08-Jul-03',6002,54 UNION ALL SELECT '01-Jul-03','13-Jul-03',6000,562
--===== Solve the problem SELECT y.[Service Start], y.[Service End], y.[FMIS Code], y.[No of Units], [YEAR] = YEAR(y.[Service Start]+t.N-1), [MONTH] = MONTH(y.[Service Start]+t.N-1), Volumes = CAST(SUM(y.[No of Units]*1.0/DATEDIFF(dd,y.[Service Start],y.[Service End]+1)) AS DECIMAL(15,6)) FROM Tally t , #yourtable y WHERE y.[Service Start]+t.N-1 <= y.[Service End] GROUP BY y.[Service Start], y.[Service End], y.[FMIS Code], y.[No of Units], YEAR(y.[Service Start]+t.N-1), MONTH(y.[Service Start]+t.N-1) ORDER BY y.[FMIS Code], y.[Service Start], y.[Service End]
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff
I did try this query out.
The only issue is it does not work when the month and year in start and end dates are same because if the month and year in start and end dates are same, then there is no change in the no of units. It remains same.
-
Thanks Jeff
I did try this query out.
The only issue is it does not work when the month and year in start and end dates are same because if the month and year in start and end dates are same, then there is no change in the no of units. It remains same.
-
Mita,
If you take a look... it is working correctly... you have multiple entries for some of the FMIS codes... notice in the sample data that you have the following inputs (not right next to each other as done below)...
SELECT '01-Oct-02','22-May-03',6440,78 UNION ALL
SELECT '01-May-03','31-May-03',6440,789 UNION ALL
SELECT '29-Jun-03','30-Jun-03',6440,981 UNION ALL
Run the sample code I gave you again... notice that the highlighted entry above is for just a single month... if you look in the output for that FMIS (6440) and number of units (789), you'll find that the number of units has NOT been prorated and that the Volumes column has 789 for that entry... just as you asked. The other two entries are, in fact, prorated across the correct months.
If there's something else I'm missing, post back.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff
I had a go again with your solution and it works perfectly all right.
You have been a great help
This forun rocks!!!!
Keep up the good work guys..
Regards
Mita
-
Hi Guys
I just ran the above query on a table which has about 14 million rows.But its taking really long time to process.Its been two hours since I started the query and its still not finished.
Is this time normal or is there a work around for that?
Thanks
Mita
-
Good grief... I had no idea you had that many rows
... I should'a asked. 
The query generates 1 record for every day in every date range for every FMIS... it's making a huge number of records... dunno if you want to stop it but I don't see it finishing in the near future... We'll have to do it a different way...
My appologies...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff
One more thing.
My manager does not want the volumes for each day. We just want to sum it up for each month in each year.
So can you redesign your query in some way?
Cheers
Mita
-
The end result of the query I wrote doesn't give totals by day... it simply generates an internal record for each day so it can sum them by month (that's what was taking the time). Check the example I gave previously and tell me if that's the kind of output you want. If not, please describe the output completely. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
ya the format is exactly what I want.
Just concerned with the time. Because I had to stp the query yesterday because it was taking way too long.
Your help will be greatly appreciated.
Cheers
Mita
-
Mita,
Probably you don't need report from all 14 mil rows every day.
You must be interested in service records for some period of time.
If you have set up clustered index on [Service Start] and apply filter to get only records relevant to desired period of time then performance of the query must be really nice for you.
_____________
Code for TallyGenerator -
Hi Jeff
The problem is for this particular table, I cant filter on the dates. The result which I get out of your query, I can just group it by FMIS code and add up the volumes for each month and each year.
Can I do all this in 1 single query??
Sorry for my lack of knowledge
Thanks
-
I'm thinking about it by why just a single query? Hopefully, you're not going to create a view of this monster...
.I have an idea... dunno if I can make it work, but I'll give it a try... might require an INSERT/EXEC instead of just a single query but it should be a heck of a lot faster in the light of 14 million records... it'll be similar to the last query but by month segement instead of cross joining to break it up into days like I did last time (silly me...)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Looking forward to your solution
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply