Insert query to split data by day from week?
-
I have a database called budgets with records that are at week number level. I need to split these out by desp_day so that each week ends up with 7 (day) records and the bud_vol and bud_trn value fields are split down proportionally accross the days. desp_day is currently NULL for all these records.
If I perform an insert for week 1 using the current data and change the NULL to the first desp_day for that week then I will have 1 of the records I need created. I would then need a way of looping round and incrementing the desp_day by 1 until all 7 records have been created for the week. Then I would need to loop this whole process round again but increment the week by 1 until all 52 weeks are done - i.e. create 7 days worth of records for each week.
Then I could delete the NULL records and perform the proportional calculation by day on the values.
Can this be done? Am I along the right lines?
my SQL is very limited and I have started to build an insert query below. Any help would be greatly appreciated...
insert into budgets select
product,
/*if week = 1 then '2005-09-04 00:00:00' as desp_day,
if week = 2 then '2005-09-11 00:00:00' as desp_day,*/
week,
fyear,
bud_vol,
bud_trn,
from budgets where desp_day is NULL
-
I think your design is wrong. You'd be better off having a budget table with all the revenues/expanses. Use the date of the "transaction" as the clustered index, maybe an identity(1,1) primary key would be in order as the real primary key may contain a lot of columns?!?!
Then just select the data in the date range required and have the application resplit it by week/day.
P.S. There's more than 52 weeks in a year
. -
I think the design of the database is fine, as there are records with desp_day in there, it's just these records have NULL as the desp_day because they were imported by week and the day was not there.
what do you mean by 'have the application resplit it by week/day'? This is the bit I need help coding! How?
There are 53 weeks in our financial year this year, but next year, which is the year the budget data is based on, there are 52.
-
Ok I misunderstood the problem.
Can you post the ddl and sample data (DML) with the expected results.
-
Do some of your records have desp_day? In other words, will you only be updating those records which are NULL, or the entire table? And will this be part of the insert when you get data from an outside source, or something scheduled to run on a regular basis.
Plus, as rgR'us (?) requested, we need the ddl and sample data...
I wasn't born stupid - I had to study.
-
If you must know Farrell
-
gotta love that sushila!!
I wasn't born stupid - I had to study.
-
You mean WaitingInLineForLeftoverScraps???
-
Most of my records have desp_day. Yes I will only be updating the records that are NULL. This will be run once only. The source of the data are the NULL records in the current database.
Forgive my ignorance - what is the dll and how do I get it?
Here is some sample data, some with desp_day, some without:
product desp_day week fyear bud_vol bud_trn
--------------------------------------------------------------------------
avo NULL 44 2006 13.500000000000001 134.85270000000003
kiw NULL 45 2006 5.380000000000001 78.4470000000003
app NULL 46 2006 3.290000000000001 58.485270000000003
avo NULL 47 2006 85.30000000000001 696.32570000000003
kiw NULL 48 2006 4.290000000000001 34.6870000000003
app 2005-08-06 00:00:00 49 2006 15.9399999999999999 158.183
avo 2005-08-13 00:00:00 50 2006 -1.9399999999999999 -8.4830000000000005
ora 2005-08-20 00:00:00 51 2006 10.8799999999999999 243.8530000000000005
avo NULL 52 2006 9.9399999999999999 201.6530000000000005
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply