SQL Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query Help

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2010-09-10 : 02:34:59

sql 2000 Question

i have following ddl and sample data

CREATE TABLE [Model_data] (
[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[model_1_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_2_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_3_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_4_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_1_Value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_2_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_3_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_4_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [tbl_status] (
[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [data_table] (
[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[model_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_data_table] PRIMARY KEY CLUSTERED
(
[main_id],
[group_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [work_table] (
[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[data_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[model_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert into data_table values('A001','1','AB900')
insert into data_table values('A001','2','AB100')
insert into data_table values('A001','3','AB2000')
insert into data_table values('A001','4','AB700')

insert into data_table values('A002','1','CF1000')
insert into data_table values('A002','3','CF4000')
insert into data_table values('A002','4','CF9000')

insert into data_table values('A003','3','DE4000')
insert into data_table values('A003','4','DE9000')

insert into data_table values('A004','1','GT4000')
insert into data_table values('A004','4','GT9000')

insert into Model_data values('A001','Y','Y','N','Y','ZZ3000','ZZ100',null,'ZZ700')
insert into Model_data values('A002','Y','Y','Y','Y','QT5000','QT4000','QT3000','QT9000')
insert into Model_data values('A003','N','N','Y','Y',null,null,'MM4000','MM9000')

insert into tbl_status values ('A001','T')
insert into tbl_status values ('A002','T')
insert into tbl_status values ('A003','T')

group_id 1,2,3,4 against any main_id in data_table forms complete set of data
i want to insert only those values in work_table by joining tbl_status , data_table and model_data where
status is 'T' and in data_table group_id = 1 and in model_data value of model_1_Group is 'Y' then data from data_table and model_data sholud be
inserted in work_table like following

main_id Group_id data_Value Model_Value
A001 1 AB900 ZZ3000
A002 1 CF1000 QT5000

if group_id 1 does not exist then group_id 2 should be checked in data_table with model_2_Group = 'Y' and data should be inserted in work_table
if group_id 2 does not exist then group_id 3 should be checked in data_table with model_3_Group = 'Y' and data should be inserted in work_table
if group_id 4 does not exist then group_id 4 should be checked in data_table with model_4_Group = 'Y' and data should be inserted in work_table

for example complete data data inserted in work table should be like according to above sample data

main_id Group_id data_Value Model_Value
A001 1 AB900 ZZ3000
A002 1 CF1000 QT5000
A003 3 DE4000 MM4000

Regards

Farid

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-10 : 10:02:18

Hi Farid,

-- Create this table to store distinct main_id's from table Model_data
CREATE TABLE #temp_distinct_main_id
(
id int identity(1,1),
main_id VARCHAR(50)
)

INSERT INTO #temp_distinct_main_id
SELECT DISTINCT(main_id) FROM Model_data -- Fill the distinct main_id into temp table

DECLARE @row_no INT
DECLARE @max_row_cnt INT

SET @row_no =1
SET @max_row_cnt =(SELECT COUNT(*) FROM #temp_distinct_main_id) -- get the total no of rows in temporary table

WHILE (@row_no <= @max_row_cnt) -- loop thgrough the distinct main_id's
BEGIN
DECLARE @main_id VARCHAR(50)
DECLARE @grp_id INT
DECLARE @data_value VARCHAR(200)
DECLARE @model_value VARCHAR(200)
DECLARE @status VARCHAR(10)
DECLARE @sql VARCHAR(1000)

SELECT @main_id = main_id FROM #temp_distinct_main_id WHERE id= @row_no -- get the main id
SELECT @status =status from tbl_status WHERE main_id = @main_id -- get the corresponding status from tbl_status

IF(@status='T') -- check for the status
BEGIN

SELECT @grp_id=MIN(group_id) FROM data_table WHERE main_id =@main_id -- get the minimum id, will sataisfy the condn you mentioned

SELECT @data_value=model_value FROM data_table WHERE main_id =@main_id AND group_id =@grp_id -- get the data value from data_table

SET @sql = 'INSERT INTO work_table ' -- insert into the table
SET @sql = @sql + ' SELECT ' + '''' + @main_id + '''' + ' as main_id,' + '''' + CAST(@grp_id as varchar(10)) + ''''
SET @sql = @sql + ' as group_id,' + '''' + @data_value + '''' + ' as data_value, model_' + CAST(@grp_id as varchar(10))
SET @sql = @sql + '_value as model_value FROM Model_data WHERE main_id = ' + '''' + @main_id + ''''
SET @sql = @sql + ' AND model_' + CAST(@grp_id as varchar(10)) + '_Group = ' + '''Y'''

EXEC(@sql)

END

SET @row_no=@row_no+1
SET @sql =''
END -- end of while loop

I hope this helps you. feel free to revert back in case of any difficulty.

Thanks
Rohit Vishwakarma

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources