Welcome :Guest

Congratulations!!!

Top 5 Contributors of the Month
Shivakumaran
Pankaj Mishra
alex morco
Imran Ghani
Meenakshi

 Home >> Articles >> DataBase >> Post New Resource

# How to duplicate records in sql according to a specific a criteria ?

Posted By:Kemal AL GAZZAH       Posted Date: June 23, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com

This article shows how to duplicate data n times, n depends on specific creteria for each record

How to duplicate records in sql according to a specific a criteria ?

A colleague to me asked me to help in creating a sql script which displays
duplicated records as per a specific criteria

Type1  has a periodocity of "monthly" and should displayed 12 times
Type 2 has a periodicity of  "each 3 months" and should be displayed 4 times
Type 3 has a periodicity of "each 6 months" and should be displayed 2 times

we have a Table called Tab1 having records with data of type among these 3 ones

The solution I gave him is the following:

1) Create a table with numbers varying from 1 to 12 called tab_frequencies
create table tab_frequency( freq as in)
insert into tab_frequency(freq) values (1)
insert into tab_frequency(freq) values (2)
.
.
.
insert into tab_frequency(freq) values (12)

2) Create a table linking the types with frequencies called tab_freq_types
create table tab_freq_types (type nvarchar(10),freq int)

insert into tab_freq_types (type,freq) values('Type 1','12')
insert into tab_freq_types (type,freq) values('Type 2','4')
insert into tab_freq_types (type,freq) values('Type 3','2')

Then to dispaly the data in Tab1 duplicated as per the corresponding frequencies, I  mad the following script

select t.*,tf.freq from tab1 t
inner join tab_freq_types tft on t.type=tft.type
inner join tab_frequency tf on tft.freq <= tf.freq

The important point here was to make a jointure based on INEGUALITY and not to an eguality (which most commonly used)

Responses

No response found. Be the first to respond this post

Post Comment