July 3, 2008 at 11:19 am
Hi Guys. I am not sure whether this question is ETL or StoreProcedure question... but since my boss wants me to write a query...
I am currently working on a old excel data import project. I was able to convert majority of data by using 'Case when' and simple data conversion. However, there is two fields I have to convert and I am not exactly sure how to do this on SQL.
In the imported table, we have 'Total Amount' and 'Number of Month' field.
This is sum of all amounts and month we have to apply to customer’s account.
However, problem I am having is that from imported table, I need to create new detail table, creating separate line for each month and monthly amount. For example, if customer’s total amount is 120 and number of month is 6 in top level, then I need to create detailed tables with 6 entries having amount value $20.
Is this something I can do in SQL query??? or stored procedure???
July 3, 2008 at 11:55 am
It seems that a table that has 1 row for each number_of_months like this should get you started:
declare @Months table ( month int, primary key ( month ) )
insert into @Months
(select top(1000) row_number() over (order by A.object_id)
from sys.objects A cross join sys.objects B) as M
insert into {your_detail_table}
select ...,
M.month_num, D.total_amount / D.number_of_months, ...
from
details as D
join @Months M
on M.month between 1 and D.number_of_months
July 3, 2008 at 1:56 pm
Thanks Top Hand! but I guess my example was bad... so this is what I have in my excel imported table:
[Contract Start Date] | [Contract End Date] | [Remaining Month] | [Monthly Fee]
6/1/2006 | 6/30/2007 | 6 | $30
based on above information, then I need to populate detail montly charge table which should looks like (we always charge our customer at first date of each month):
[Charged Date] | [Amount Charged]
1/1/2007 | $30
2/1/2007 | $30
3/1/2007 | $30
4/1/2007 | $30
5/1/2007 | $30
6/1/2007 | $30
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply