Query help - selecting date 'range'

wbodger · Jan 16, 2013

I have data like this

Code:

STUDY_PARTICIPANT_ID	PROC_LABS_PSA_DATE	PROC_LABS_PSA_VALUE	Phase
08800021	2008-07-02 00:00:00.000	1.44	BLN
08800021	2008-10-13 00:00:00.000	1.30	BLN
08800021	2009-02-09 00:00:00.000	1.20	FU
08800021	2009-04-01 00:00:00.000	1.40	FU
08800021	2009-07-01 00:00:00.000	1.43	FU
08800021	2009-10-14 00:00:00.000	1.33	FU
08800021	2009-12-15 00:00:00.000	2.01	FU
08800021	2010-04-05 00:00:00.000	5.30	FU
08800021	2010-07-05 00:00:00.000	1.60	FU
08800021	2010-09-24 00:00:00.000	1.50	FU
08800021	2010-12-29 00:00:00.000	1.40	FU
08800021	2011-04-14 00:00:00.000	1.80	FU
08800021	2011-06-24 00:00:00.000	1.60	FU
08800021	2011-09-28 00:00:00.000	1.30	FU
08800021	2011-12-16 00:00:00.000	1.90	FU
08800021	2012-04-13 00:00:00.000	1.90	FU
08800021	2012-08-08 00:00:00.000	1.90	FU
08800518	2005-02-17 00:00:00.000	19.50	BLN
08800518	2007-06-18 00:00:00.000	1.60	BLN
08800518	2008-06-25 00:00:00.000	1.40	BLN
08800518	2009-01-15 00:00:00.000	2.30	BLN
08800518	2009-08-10 00:00:00.000	2.06	BLN
08800518	2009-11-04 00:00:00.000	1.50	BLN
08800518	2010-05-05 00:00:00.000	3.60	FU
08800518	2010-12-03 00:00:00.000	3.30	FU
08800518	2011-03-02 00:00:00.000	2.80	FU
08800518	2011-05-05 00:00:00.000	3.80	FU
08800534	2009-01-27 00:00:00.000	4.30	BLN
08800534	2009-02-09 00:00:00.000	3.30	BLN
08800534	2009-03-16 00:00:00.000	4.40	BLN
08800534	2009-09-09 00:00:00.000	4.62	BLN
08800534	2010-07-19 00:00:00.000	4.60	FU
08800534	2010-10-29 00:00:00.000	5.10	FU
08800534	2011-05-13 00:00:00.000	5.30	FU
08800534	2012-01-23 00:00:00.000	5.90	FU
08800534	2012-08-07 00:00:00.000	6.30	FU

For each participant I need 5 PSAs for each PSA date that are: before that PSA Date and the span for those 5 values has to be at least 12 months and no more than 24 months. So, for each participant the first four values will not have a grouping of their own, but if there is a fifth value then I can check to see if there are four lower (datewise) values that fall within the required window. Then if there is a sixth I can see if there are four lower (datewise) values that fall within the required window. and so on and so on for each PSA record for each participant. Each record can be in up to 5 different groups. Any suggestions?

wb

simian336 · Jan 16, 2013

Not sure I follow your rules.

Simi

wbodger · Jan 16, 2013

I am calculating PSA Doubling Time. For that I have to have 5 PSA values within a 12-24 month window. So, what I want to do is grab each date and see if there are 4 dates below it that fall within that window (making a total of 5). And I want to do that for each date. What I want to return is the date and associated value so it might look something like

pptid dates values
08800021 2008-07-02,2008-10-13,2009-02-09,2009-04-01,2009-07-01 1.44,1.30,1.20,1.40,1.43
08800021 2008-10-13,2009-02-09,2009-04-01,2009-07-01,2009-10-14 1.30,1.20,1.40,1.43,1.33

gmmastros · Jan 16, 2013

I'm not sure I follow your rules either, but here is a query that you can use to help get you started.

Code:

Declare @Temp Table(STUDY_PARTICIPANT_ID VarChar(20),
    PROC_LABS_PSA_DATE DateTime,
	PROC_LABS_PSA_VALUE	Decimal(10,2),
	Phase VarChar(20))

Insert Into @Temp Values('08800021','2008-07-02 00:00:00.000',	1.44,	'BLN')
Insert Into @Temp Values('08800021','2008-10-13 00:00:00.000',	1.30,	'BLN')
Insert Into @Temp Values('08800021','2009-02-09 00:00:00.000',	1.20,	'FU')
Insert Into @Temp Values('08800021','2009-04-01 00:00:00.000',	1.40,	'FU')
Insert Into @Temp Values('08800021','2009-07-01 00:00:00.000',	1.43,	'FU')
Insert Into @Temp Values('08800021','2009-10-14 00:00:00.000',	1.33,	'FU')
Insert Into @Temp Values('08800021','2009-12-15 00:00:00.000',	2.01,	'FU')
Insert Into @Temp Values('08800021','2010-04-05 00:00:00.000',	5.30,	'FU')
Insert Into @Temp Values('08800021','2010-07-05 00:00:00.000',	1.60,	'FU')
Insert Into @Temp Values('08800021','2010-09-24 00:00:00.000',	1.50,	'FU')
Insert Into @Temp Values('08800021','2010-12-29 00:00:00.000',	1.40,	'FU')
Insert Into @Temp Values('08800021','2011-04-14 00:00:00.000',	1.80,	'FU')
Insert Into @Temp Values('08800021','2011-06-24 00:00:00.000',	1.60,	'FU')
Insert Into @Temp Values('08800021','2011-09-28 00:00:00.000',	1.30,	'FU')
Insert Into @Temp Values('08800021','2011-12-16 00:00:00.000',	1.90,	'FU')
Insert Into @Temp Values('08800021','2012-04-13 00:00:00.000',	1.90,	'FU')
Insert Into @Temp Values('08800021','2012-08-08 00:00:00.000',	1.90,	'FU')
Insert Into @Temp Values('08800518','2005-02-17 00:00:00.000',	19.50,	'BLN')
Insert Into @Temp Values('08800518','2007-06-18 00:00:00.000',	1.60,	'BLN')
Insert Into @Temp Values('08800518','2008-06-25 00:00:00.000',	1.40,	'BLN')
Insert Into @Temp Values('08800518','2009-01-15 00:00:00.000',	2.30,	'BLN')
Insert Into @Temp Values('08800518','2009-08-10 00:00:00.000',	2.06,	'BLN')
Insert Into @Temp Values('08800518','2009-11-04 00:00:00.000',	1.50,	'BLN')
Insert Into @Temp Values('08800518','2010-05-05 00:00:00.000',	3.60,	'FU')
Insert Into @Temp Values('08800518','2010-12-03 00:00:00.000',	3.30,	'FU')
Insert Into @Temp Values('08800518','2011-03-02 00:00:00.000',	2.80,	'FU')
Insert Into @Temp Values('08800518','2011-05-05 00:00:00.000',	3.80,	'FU')
Insert Into @Temp Values('08800534','2009-01-27 00:00:00.000',	4.30,	'BLN')
Insert Into @Temp Values('08800534','2009-02-09 00:00:00.000',	3.30,	'BLN')
Insert Into @Temp Values('08800534','2009-03-16 00:00:00.000',	4.40,	'BLN')
Insert Into @Temp Values('08800534','2009-09-09 00:00:00.000',	4.62,	'BLN')
Insert Into @Temp Values('08800534','2010-07-19 00:00:00.000',	4.60,	'FU')
Insert Into @Temp Values('08800534','2010-10-29 00:00:00.000',	5.10,	'FU')
Insert Into @Temp Values('08800534','2011-05-13 00:00:00.000',	5.30,	'FU')
Insert Into @Temp Values('08800534','2012-01-23 00:00:00.000',	5.90,	'FU')
Insert Into @Temp Values('08800534','2012-08-07 00:00:00.000',	6.30,	'FU')

; With Data As
(
Select	Row_Number() Over (Partition By STUDY_PARTICIPANT_ID Order By PROC_LABS_PSA_DATE) As RowID,
		*
From    @Temp
)
Select	STUDY_PARTICIPANT_ID,
		Min(Case When (RowId-1) % 5 = 0 Then PROC_LABS_PSA_DATE End) As D1,
		Min(Case When (RowId-1) % 5 = 1 Then PROC_LABS_PSA_DATE End) As D2,
		Min(Case When (RowId-1) % 5 = 2 Then PROC_LABS_PSA_DATE End) As D3,
		Min(Case When (RowId-1) % 5 = 3 Then PROC_LABS_PSA_DATE End) As D4,
		Min(Case When (RowId-1) % 5 = 4 Then PROC_LABS_PSA_DATE End) As D5,
		Min(Case When (RowId-1) % 5 = 0 Then PROC_LABS_PSA_VALUE End) As V1,
		Min(Case When (RowId-1) % 5 = 1 Then PROC_LABS_PSA_VALUE End) As V2,
		Min(Case When (RowId-1) % 5 = 2 Then PROC_LABS_PSA_VALUE End) As V3,
		Min(Case When (RowId-1) % 5 = 3 Then PROC_LABS_PSA_VALUE End) As V4,
		Min(Case When (RowId-1) % 5 = 4 Then PROC_LABS_PSA_VALUE End) As V5
From	Data
Group By STUDY_PARTICIPANT_ID, (RowId-1) / 5
Order By STUDY_PARTICIPANT_ID, (RowId-1) / 5

This query will pivot your data in groups of 5, nothing more, nothing less. It doesn't take in to account your date range 12 month/24 month rule.

-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 · Jan 16, 2013

The only thing I could think of was to grab each date and do a date diff to back 24 months. I would then use that as the lower date and the selected date as the upper date. Now I select from the data table by participant id between the two tables. If count=5 then a I can calculate doubling time. If it is less than five then discard, move to the next date and start again. If it is >5 then I would have to somehow start over again, so perhaps this is not a reasonable method.

Another approach I had thought of was to start at the first record for each participant and count down four places in the recordset (to give me five records). If there are not five records, then move to the next record. If there are five, then calculate the datediff between the first and 'last' record in that set. The difference here to what you said, George, is that a value can (and most likely will) be re-used, just at a different point in the recordset. So, doing this all in SQL, how would I run a query, then step backwards the the recordset for each participant a certain number of times (4) so that I could check the datediff between my starting point and ending point?

wbodger · Jan 17, 2013

Let's try this. I have this data

Code:

study_participant_id	proc_labs_psa_date	proc_labs_psa_value
8800021	7/2/2008	1.44
8800021	10/13/2008	1.3
8800021	2/9/2009	1.2
8800021	4/1/2009	1.4
8800021	7/1/2009	1.43
8800021	10/14/2009	1.33
8800021	12/15/2009	2.01
8800021	4/5/2010	5.3
8800021	7/5/2010	1.6
8800021	9/24/2010	1.5
8800021	12/29/2010	1.4
8800021	4/14/2011	1.8
8800021	6/24/2011	1.6
8800021	9/28/2011	1.3
8800021	12/16/2011	1.9
8800021	4/13/2012	1.9
8800021	8/8/2012	1.9

then this code

Code:

; With Data As
(
Select	Row_Number() Over (Partition By STUDY_PARTICIPANT_ID Order By PROC_LABS_PSA_DATE) As RowID,
		 study_participant_id, proc_labs_psa_date, proc_labs_psa_value, GETDATE() as inidate
From    [dbEDRN195].[dbo].[PSADates_Values]
)
  
  
select top 100 percent rowid, study_participant_id, proc_labs_psa_date, substring ( stuff(
  (
    select ' , ' + cast(proc_labs_psa_date as varchar(max)) 
    from Data 
    for xml path ('') ),
    1 ,
    2 ,
    '') 
    , 2 , 1000 )
from Data
order by RowID desc

which gets me close, but rather than

Code:

study_participant_id	(No column name)
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM
08800021	Aug  8 2012 12:00AM , Apr 13 2012 12:00AM , Dec 16 2011 12:00AM , Sep 28 2011 12:00AM , Jun 24 2011 12:00AM , Apr 14 2011 12:00AM , Dec 29 2010 12:00AM , Sep 24 2010 12:00AM , Jul  5 2010 12:00AM , Apr  5 2010 12:00AM , Dec 15 2009 12:00AM , Oct 14 2009 12:00AM , Jul  1 2009 12:00AM , Apr  1 2009 12:00AM , Feb  9 2009 12:00AM , Oct 13 2008 12:00AM , Jul  2 2008 12:00AM

I need it to be

Code:

rowid	study_participant_id	proc_labs_psa_date	List
17	8800021	8/8/2012	Jun 24 2011 12:00AM , Sep 28 2011 12:00AM , Dec 16 2011 12:00AM , Apr 13 2012 12:00AM , Aug  8 2012 12:00AM
16	8800021	4/13/2012	Apr 14 2011 12:00AM , Jun 24 2011 12:00AM , Sep 28 2011 12:00AM , Dec 16 2011 12:00AM , Apr 13 2012 12:00AM
15	8800021	12/16/2011	Dec 29 2010 12:00AM , Apr 14 2011 12:00AM , Jun 24 2011 12:00AM , Sep 28 2011 12:00AM , Dec 16 2011 12:00AM
14	8800021	9/28/2011	Sep 24 2010 12:00AM , Dec 29 2010 12:00AM , Apr 14 2011 12:00AM , Jun 24 2011 12:00AM , Sep 28 2011 12:00AM
13	8800021	6/24/2011	Jul  5 2010 12:00AM , Sep 24 2010 12:00AM , Dec 29 2010 12:00AM , Apr 14 2011 12:00AM , Jun 24 2011 12:00AM
12	8800021	4/14/2011	Apr  5 2010 12:00AM , Jul  5 2010 12:00AM , Sep 24 2010 12:00AM , Dec 29 2010 12:00AM , Apr 14 2011 12:00AM
11	8800021	12/29/2010	Dec 15 2009 12:00AM , Apr  5 2010 12:00AM , Jul  5 2010 12:00AM , Sep 24 2010 12:00AM , Dec 29 2010 12:00AM
10	8800021	9/24/2010	Oct 14 2009 12:00AM , Dec 15 2009 12:00AM , Apr  5 2010 12:00AM , Jul  5 2010 12:00AM , Sep 24 2010 12:00AM
9	8800021	7/5/2010	Jul  1 2009 12:00AM , Oct 14 2009 12:00AM , Dec 15 2009 12:00AM , Apr  5 2010 12:00AM , Jul  5 2010 12:00AM
8	8800021	4/5/2010	Apr  1 2009 12:00AM , Jul  1 2009 12:00AM , Oct 14 2009 12:00AM , Dec 15 2009 12:00AM , Apr  5 2010 12:00AM
7	8800021	12/15/2009	Feb  9 2009 12:00AM , Apr  1 2009 12:00AM , Jul  1 2009 12:00AM , Oct 14 2009 12:00AM , Dec 15 2009 12:00AM
6	8800021	10/14/2009	Oct 13 2008 12:00AM , Feb  9 2009 12:00AM , Apr  1 2009 12:00AM , Jul  1 2009 12:00AM , Oct 14 2009 12:00AM
5	8800021	7/1/2009	Jul  2 2008 12:00AM , Oct 13 2008 12:00AM , Feb  9 2009 12:00AM , Apr  1 2009 12:00AM , Jul  1 2009 12:00AM
4	8800021	4/1/2009	
3	8800021	2/9/2009	
2	8800021	10/13/2008	
1	8800021	7/2/2008

This should show how I need to use the dates and values, how they can overlap (actually, should overlap). After this I will do some calculations, but this is the step I am stuck on right now, how to start at the most recent record (17 here), count back five and then stick those in a column, then go to the next most recent record (16 here) and stick those in a column and so on until there are no longer 5 records.

wb

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query help - selecting date 'range'

wbodger

Programmer

simian336

Programmer

wbodger

Programmer

gmmastros

Programmer

wbodger

Programmer

wbodger

Programmer

Similar threads

Part and Inventory Search

Sponsor