Generate records based on field value in DB table's records

Question

Post reply

Generate records based on field value in DB table's records

Adelphi

SSC Journeyman

Points: 91
More actions
April 7, 2011 at 12:02 pm

#238159

Hi everyone,
I have the following issue:
[Customer Name][Financial Year][No of Years][Total Value]
Customer A2010/11 3 30,000
Customer B2011/12 2 50,000
I would like the following:
Create records and increase financial years based on [No of Years] plus divide (Total Value) by (No of Years) to produce in above example 5 records in the following format:
[Customer Name][Financial Year][Total Value]
Customer A2011/1210,000
Customer A2012/1310,000
Customer A2013/1410,000
Customer B2012/1325,0000
Customer B2013/1425,0000
Any help will be highly appreciated
Many thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Hi,

There might be other easy solutions also.

/*Your data in to this table*/

declare @Temp table ([Customer_Name] varchar(50),[Financial_Year] varchar(25), [No_of_Years] int,[Total_Value] int)

insert @Temp values ('Customer A', '2010/11' ,3, 30000)

insert @Temp values ('Customer B', '2011/12', 2, 50000)

/*Your data with identity to this new table*/

declare @NewTemp table (Rid int identity,[Customer_Name] varchar(50),[Financial_Year] varchar(25), [No_of_Years] int,[Total_Value] int)

insert into @NewTemp

Select * from @Temp

--Select * from @NewTemp

declare @I int

declare @j-2 int

set @j-2=1

while @j-2<=(Select COUNT(Rid) from @NewTemp)

Begin

set @I=1

while @I<=(Select No_of_Years from @NewTemp where Rid=@j)

begin

select [Customer_Name],Convert(Varchar,Left(Financial_Year,4)+@i)

+'/'+ Convert(Varchar,Right(Financial_Year,2)+@i)[Financial_Year],

Total_Value/(Select No_of_Years from @NewTemp where Rid=@j) [Total_Value]

from @NewTemp where Rid=@j

set @I=@I+1

end

set @j-2=@J+1

end

Thanks
Parthi

Lowell SSC Guru Points: 323518 More actions · Answer 2

i forced your data to be integers so i could perform math on them, like adding a year; then used a Tally table to generate the results:

With MyData ([Customer Name],[Financial Year],[No of Years],[Total Value])

As

(

SELECT 'Customer A',2010,3,30000.00 UNION ALL

SELECT 'Customer B',2011,2,50000.00

), miniTally As

(

SELECT top 50 row_number() over (ORDER BY name) As N from sys.columns

)

select

MyData.[Customer Name],

(miniTally.N + MyData.[Financial Year]),

[Total Value] / [No of Years]

FROM MyData

INNER JOIN miniTally on (MyData.[No of Years] ) > = miniTally.N

ORDER BY

MyData.[Customer Name]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adelphi SSC Journeyman Points: 91 More actions · Answer 3

Many thanks for your swift responses guys

I will have a go at both solutions and will let you know progress ind due course.

Hopefully it will be fine.

A

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

adel.mutlak1 (4/8/2011)
Many thanks for your swift responses guys
I will have a go at both solutions and will let you know progress ind due course.
Hopefully it will be fine.
A

It's been more than a week... how'd it all turn out?

--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)