SELECT DATA (PROCESS LOGIC) - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT DATA (PROCESS LOGIC)

Author

Topic

ashwini.anand
Starting Member

5 Posts

Posted - 2012-02-15 : 02:36:48

[code]
id v_id type p_id day
1322 234 woutmin 1 3
1322 235 woutmax 1 7
1322 234 woutmin 2 4
1322 235 woutmax 2 5
[/code]

To get the above data, I used SELECT statement from a table.
Now what I want to achieve is for p_id=(1,2,3...),
see if [type] is woutmin or max and similarly pick their day and insert into another table as shown below.

[code]
id p_id woutmin woutmax
1322 1 3 7
1322 2 4 5
[/code]

How can I do this? Do I have to first insert the records into another temp table while I get using SELECT statement.
And then loop through the temp table and insert into another table ?

Thank You guys

Ashwini Anand

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-15 : 03:12:06

[code]
select id, p_id, min(day), max(day)
from yourtable
group by id, p_id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ashwini.anand
Starting Member

5 Posts

Posted - 2012-02-15 : 03:20:36

DECLARE @WashOutMinMax TABLE (
[PK] INT IDENTITY(1,1),
[study_cohort_map_id] INT,
[visit_type_id] SMALLINT,
[visit_type] VARCHAR(50),
[period_id] SMALLINT,
[study_day] SMALLINT
)
INSERT INTO @WashOutMinMax (study_cohort_map_id,visit_type_id,visit_type,period_id,study_day)
select study_cohort_map_id,COHORT_INTERVAL_DTLS.visit_type_id,VISIT_TYPE_MST.visit_type,period_id,study_day from COHORT_INTERVAL_DTLS
inner join VISIT_TYPE_MST ON VISIT_TYPE_MST.visit_type_id=COHORT_INTERVAL_DTLS.visit_type_id
where study_cohort_map_id=1322 and VISIT_TYPE_MST.visit_type IN ('Wash Out Min','Wash Out Max')
order by period_id,VISIT_TYPE_MST.visit_type DESC

select * from @WashOutMinMax

Declare @maxPK int;
Select @maxPK = MAX(PK) From @WashOutMinMax
Declare @pk int;Set @pk = 1
Declare @map_id int;
Declare @period int;
Declare @wmin_day int;
Declare @wmax_day int;

While @pk <= @maxPK
Begin
if (@pk % 2 = 0)
BEGIN
Select @wmax_day=[study_day]
From @WashOutMinMax
Where PK = @pk;

insert into COHORT_MAP_WMINWOUT_DTLS VALUES (@map_id,@period,@wmin_day,@wmax_day);
END
ELSE
BEGIN
Select @map_id=[study_cohort_map_id],@period=[period_id],@wmin_day=[study_day]
From @WashOutMinMax
Where PK = @pk;
END
Select @pk = @pk + 1
End

select * from COHORT_MAP_WMINWOUT_DTLS

Thanks Guys, for help. I found the solution. The above procedure will complete my task which I just wrote.


PK	s_id	v_id	visit_type	pv_id	day
1	1322	234	Wash Out Min	1	3
2	1322	235	Wash Out Max	1	7
3	1322	234	Wash Out Min	2	4
4	1322	235	Wash Out Max	2	5

s_id	p_id	woutmin	woutmax
1322	1	3	7
1322	2	4	5

Ashwini Anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 09:41:22

sorry didnt understand why you need loop for this
whats the problem with this?


select id, p_id, min(case when type='woutmin' then day end) as woutmin, 
min(case when type='woutmax' then day end) as woutmax
from   yourtable
group by id, p_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources