Looping thru a recordset in T-SQL

[wbodger]

OK, I started this problem last week going down a different road. Now I have two views, one with all of the data that I need including a rowID

RowID	study_participant_id	proc_labs_psa_date	proc_labs_psa_value	Phase	inidate
1	08800021	2008-07-02 00:00:00.000	1.44	BLN	2013-01-22 09:24:02.183
2	08800021	2008-10-13 00:00:00.000	1.30	BLN	2013-01-22 09:24:02.183
3	08800021	2009-02-09 00:00:00.000	1.20	FU	2013-01-22 09:24:02.183
4	08800021	2009-04-01 00:00:00.000	1.40	FU	2013-01-22 09:24:02.183
5	08800021	2009-07-01 00:00:00.000	1.43	FU	2013-01-22 09:24:02.183
6	08800021	2009-10-14 00:00:00.000	1.33	FU	2013-01-22 09:24:02.183
7	08800021	2009-12-15 00:00:00.000	2.01	FU	2013-01-22 09:24:02.183
8	08800021	2010-04-05 00:00:00.000	5.30	FU	2013-01-22 09:24:02.183
9	08800021	2010-07-05 00:00:00.000	1.60	FU	2013-01-22 09:24:02.183
10	08800021	2010-09-24 00:00:00.000	1.50	FU	2013-01-22 09:24:02.183
11	08800021	2010-12-29 00:00:00.000	1.40	FU	2013-01-22 09:24:02.183
12	08800021	2011-04-14 00:00:00.000	1.80	FU	2013-01-22 09:24:02.183
13	08800021	2011-06-24 00:00:00.000	1.60	FU	2013-01-22 09:24:02.183
14	08800021	2011-09-28 00:00:00.000	1.30	FU	2013-01-22 09:24:02.183
15	08800021	2011-12-16 00:00:00.000	1.90	FU	2013-01-22 09:24:02.183
16	08800021	2012-04-13 00:00:00.000	1.90	FU	2013-01-22 09:24:02.183
17	08800021	2012-08-08 00:00:00.000	1.90	FU	2013-01-22 09:24:02.183
[/code

and another that whittles this down to give me that ranges that I need

[code]
newrowid	Study_Participant_ID	highrow	lowrow
1	08800021	6	1
2	08800021	7	1
3	08800021	8	1
4	08800021	9	2
5	08800021	10	2
6	08800021	11	3
7	08800021	12	5
8	08800021	13	5
9	08800021	14	6
10	08800021	15	8
11	08800021	16	9
12	08800021	17	10

(Both of these views have many more rows, I have just shown the data for the first study_participant_id.)

Now I want to use the range and study_participant_id from the second view to get the PSA_Values from the first view. I
have tried variations on

DECLARE @newid int, @intRowCount int, @spid int, @hr int, @lr int
SELECT @newid = -1,  -- Smaller value than any real data
       @intRowCount = 1 -- Force first iteration
WHILE @intRowCount >= 1
BEGIN
    SELECT @newid = MIN(newrowid)
			,@spid=[Study_Participant_ID]
			,@hr=[highrow]
			,@lr=[lowrow]
	FROM   [dbEDRN195].[dbo].[PSALimits]
    WHERE  newrowid > @newid
	group by Study_Participant_ID, highrow, lowrow
    SELECT @intRowCount = @@ROWCOUNT

    IF @intRowCount >= 1
    BEGIN
	select * from dbEDRN195.dbo.PSADates_Values
	where study_participant_id=@spid
	and RowID >= @lr and RowID<=@hr
    END
END

and

declare @newid int
declare @spid int
declare @hr int
declare @lr int

SELECT @newid=MIN(newrowid)
	   ,@spid=[Study_Participant_ID]
       ,@hr=[highrow]
       ,@lr=[lowrow]
  FROM [dbEDRN195].[dbo].[PSALimits]
  group by Study_Participant_ID, highrow, lowrow
  
print @newid
print @hr
print @lr
  
WHILE @newid is not null
begin
	select * from dbEDRN195.dbo.PSADates_Values
	where study_participant_id=@spid
	and RowID >= @lr and RowID<=@hr
	select @newid=MIN(newrowid) from dbEDRN195.dbo.psalimits where newrowid>@newid
end

but these both give me only the last record in the view. It is correct, but it is the last record and I want to get all of the sets. Below is teh output for both of these last two queries

RowID	study_participant_id	proc_labs_psa_date	proc_labs_psa_value	Phase	inidate
10	54500442	2010-10-11 00:00:00.000	7.70	BLN	2013-01-22 09:31:02.833
11	54500442	2011-05-16 00:00:00.000	8.90	BLN	2013-01-22 09:31:02.833
12	54500442	2011-08-22 00:00:00.000	8.00	BLN	2013-01-22 09:31:02.833
13	54500442	2012-02-10 00:00:00.000	8.05	BLN	2013-01-22 09:31:02.833
14	54500442	2012-08-16 00:00:00.000	9.90	BLN	2013-01-22 09:31:02.833

 

[gmmastros]

I apologize, but I still don't "get it".

Regardless, I have a query that I would like you to try. I think it might return the data you are looking for.

Select *
From   ViewNumber1
       Inner Join ViewNumber2
         On ViewNumber1.study_participant_id = ViewNumber2.study_participant_id
         And ViewNumber1.RowId Between ViewNumber2.LowRow And ViewNumber2.HighRow

In the code I show above, ViewNumber2 is the view that contains the HighRow and LowRow data. ViewNumber1 is the other view. You'll need to change them both to the actual view names.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

George, that was the first thing that I tried (sorry, I should have said that), but it does not give me the data that I need. To (hopefully) explain more fully a participant has a visit every (roughly) three months. At that visit their PSA value is recorded along with the date. Now, what I need to do is grab each date, go back 24 months and make sure there are at least 5 PSA values in that time frame (this is what view number 2 does). So, view number 1 has all of the data and view number 2 tells me what the breakdown for each set of data should be. So, this is where I stand right now.

Now, I have to use those upper and lower values (created rowids) to grab the actual PSA values for each range for each participant. I then need to do some calculations with those PSA values. I could do this in classic asp using ADO recordsets, but the way it is going to be used, creating a final view would be the most helpful route. So, what I want to get when I loop through each row in view number 2 (pulling data from view number 1) is

RowID	study_participant_id	proc_labs_psa_date	proc_labs_psa_value	Phase	inidate
10	08800021	2010-09-24 00:00:00.000	1.50	FU	2013-01-22 10:04:51.117
11	08800021	2010-12-29 00:00:00.000	1.40	FU	2013-01-22 10:04:51.117
12	08800021	2011-04-14 00:00:00.000	1.80	FU	2013-01-22 10:04:51.117
13	08800021	2011-06-24 00:00:00.000	1.60	FU	2013-01-22 10:04:51.117
14	08800021	2011-09-28 00:00:00.000	1.30	FU	2013-01-22 10:04:51.117
15	08800021	2011-12-16 00:00:00.000	1.90	FU	2013-01-22 10:04:51.117
16	08800021	2012-04-13 00:00:00.000	1.90	FU	2013-01-22 10:04:51.117
17	08800021	2012-08-08 00:00:00.000	1.90	FU	2013-01-22 10:04:51.117

I need a discreet set for 10-17 AND 9-16 AND 8-15 etc. Hey, do you think I could accomplish this by using a cte and partitioning the data by the rowid in view number 2?

 

[wbodger]

This

declare @mrow int
declare @newid int
declare @spid int
declare @hr int
declare @lr int
declare @counter int
set @counter=1

SELECT @mrow=MAX(newrowid)
  FROM [dbEDRN195].[dbo].[PSALimits]
  
  
WHILE @counter<@mrow
begin
SELECT @newid=MIN(newrowid)
	   ,@spid=[Study_Participant_ID]
       ,@hr=[highrow]
       ,@lr=[lowrow]
  FROM [dbEDRN195].[dbo].[PSALimits]
  where newrowid=@counter
  group by Study_Participant_ID, highrow, lowrow


	select * from dbEDRN195.dbo.PSADates_Values
	where study_participant_id=@spid
	and RowID between @lr and @hr
	--select @newid=MIN(newrowid) from dbEDRN195.dbo.psalimits where newrowid>@newid
	
	set @counter = @counter+1
end

gives me the groupings that I am looking for, but it seems to be very slow.

 

[fredericofonseca]

unless I'm mistaken the sql you posted translates to the following

select * 
from dbEDRN195.dbo.PSADates_Values
inner join
(
select newrowid
      ,Study_Participant_ID as spid
      ,highrow as hr
      ,lowrow as lr
  FROM dbEDRN195.dbo.PSALimits
  where newrowid <= (select MAX(newrowid)
                       FROM dbEDRN195.dbo.PSALimits
                    )
  group by newrowid
          ,Study_Participant_ID
          ,highrow
          ,lowrow
) newid
on study_participant_id = spid
and RowId between lr and hr

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[wbodger]

Yes, that does give me the same results and much faster. Now, how would I step thru those as in an array without using a cursor inside T-SQL? This gives me (with some slight modifications) data in groupings (by participant id, and a ranked rowid). I then need to loop thru data from those groupings (psa date and value). I have done it in classic asp, but really would like to accomplish the task in a view for ease of use.

 

[fredericofonseca]

what you mean loop through.. you have the data, return it to your app, and the app does what it wishes with it.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[wbodger]

That was the issue, how to step thru a specified subset of data within T-SQL. In classic asp I took two comma delimited fields of values and placed them into arrays and the I stepped thru those arrays for my calculations. I could not see how to do this in T-SQL without using a loop and my data is ~5k rows with two fields that have 5-8 comma separated values that I placed into arrays and then loop thru, so I did not want to use cursors. IUn my web app I jsut did this

Dim strSQL, rsSQL, strSQL2, rsSQL2, pid, lr, hr, phase, psadate, psavalue, numvals, arrpsavals, arrpsadates
	dim tempCnt, fixedStartDate, psaLogValue, diffDate, slope, vNumerator, vDenominator, psaDoubleTime

	fixedStartDate = CDate("January 1, 1960")

	dim sumXY, sumX, sumY, sumXSq
	sumXY = 0
	sumX= 0
	sumY = 0
	sumXSq = 0

  strsql = "SELECT study_participant_id, lowrow, highrow FROM db.dbo.PSADTLimits where study_participant_id=08800021 order by study_participant_id, newrowid"
  Set rsSQL = RunWithRS(strSQL)

  if not rsSQL.BOF or not rsSQL.EOF then 
   while not rsSQL.EOF
   pid=rsSQL("study_participant_id")
   lr=rsSQL("lowrow")
   hr=rsSQL("highrow")
   numvals=(cint(hr)-cint(lr))+1
    strSQL2 = "SELECT distinct pdv.study_participant_id, phase, cast(proc_labs_psa_date as DATE) as psadate, proc_labs_psa_value , pl.valueset, pd.dateset " & _
                "FROM db.dbo.PSADates_Values as pdv join (select distinct study_participant_id, substring ( stuff( " & _
                              "( " & _
                                "select ' , ' + cast(proc_labs_psa_value as varchar(max)) " & _
                                "from db.dbo.PSADates_Values " & _
                                "where study_participant_id = "&pid&" and RowID between "&lr&" and "&hr&" " & _
                                "order by proc_labs_psa_date " & _
                                "for xml path ('') ), " & _
                                "1 , " & _
                                "2 , " & _
                                "'') " & _
                                ", 2 , 1000 ) as valueset " & _
         "from db.dbo.PSADates_Values as results) as pl on pdv.study_participant_id=pl.study_participant_id " & _
        "join (select distinct study_participant_id, substring ( stuff( " & _
                                      "( " & _
                                        "select ' , ' + cast(cast(proc_labs_psa_date as date) as varchar(max)) " & _
                                        "from db.dbo.PSADates_Values " & _
                                "where study_participant_id = "&pid&" and RowID between "&lr&" and "&hr&" " & _
                                "order by proc_labs_psa_date " & _
                                "for xml path ('') ), " & _
                                "1 , " & _
                                "2 , " & _
                                "'') " & _
                                ", 2 , 1000 ) as dateset " & _
                 "from db.dbo.PSADates_Values as results) as pd on pdv.study_participant_id=pd.study_participant_id " & _
         "WHERE pdv.study_participant_id = " &pid& "and rowid= "&hr
    Set rsSQL2 = RunWithRS(strSQL2)
   phase=rsSQL2("phase")
   psadate=rsSQL2("psadate")
   psavalue=rsSQL2("proc_labs_psa_value")
   arrpsavals=Split(rsSQL2("valueset"),",")
   arrpsadates=Split(rsSQL2("dateset"),",")

      if not rsSQL2.BOF or not rsSQL2.EOF then 
       while not rsSQL2.EOF


		For tempCnt = 0 To UBound(arrpsavals)

		  diffDate = DateDiff("d", fixedStartDate, arrpsadates(tempCnt))   '                          : X
		  psaLogValue = Log(arrpsavals(tempCnt))                         'natural log of PSA value  : Y

		  sumXY = sumXY + (diffDate * psaLogValue)
		  sumX = sumX + diffDate
		  sumY = sumY + psaLogValue
		  sumXSq = sumXSq + (diffDate * diffDate)

		Next

		vNumerator = (sumXY) - ((sumX * sumY) / numvals)
		vDenominator = (sumXSq) - ((sumX * sumX) / numvals)

		slope = vNumerator / vDenominator

        psaDoubleTime = (Log(2) / slope)/365.25

But that looping and the array was what I could not see clear how to do in T-SQL.