Need Help with Returning Values based on a date range

[luvmy2Js]

I have a project where I need to pull back data based on 2 criterias:

1. Has more than one value in the table
2. At least one of those values fall within the range of
9/27/10-10/25/10.

I have it to where it pulls "1", but now I am struggling with getting it to only pull back "2". The issue is, I can get it to pull back "2" criteria, but I am not bringing back the history associated with it, just that one record. Example:

Should return:
id lastchange subtype Created Date
500383 20080623 1 10/28/2010
500383 20100818 1 10/28/2010

But it returns:

id lastchange subtype Created Date
500838 20101018 1 10/28/2010

Here is the code I have thus far:

CREATE TABLE addressinfo# (sapid int, number int)

EXECUTE adm_Supplimental

INSERT INTO addressinfo# (sapid, number)

(
SELECT sapid, count (sapid)
From ystdy_PA0006
Where CONVERT(nvarchar, DecryptByKey([Subtype])) = '1'
Group by sapid
Having count (sapid) > 1
)

SELECT
p6.sapid, CONVERT(nvarchar, DecryptByKey (p6.[Date of Last Change])) as lsatchange, CONVERT(nvarchar, DecryptByKey([Subtype])) as subtype,
'Created Date' = CONVERT(nvarchar, getdate(), 101)

FROM
addressinfo# e
INNER JOIN ystdy_PA0006 p6 on p6.sapid = e.sapid and CONVERT(nvarchar, DecryptByKey([Subtype])) = '1'
Where CONVERT(nvarchar, DecryptByKey (p6.[Date of Last Change])) between '20100927' and '20101025'

Order by 1

DROP TABLE addressinfo#

I know the issue is in the "where" statement at the end of the code, but how am I suppose to tell it to only pull the values in the date range needed?

Any help would be GREATLY appreciated!!!

Thanks a million...

 

[luvmy2Js]

Got it!!

 

[sparkbyte]

I thaught I would share the date range selection I use.

This one is for all todays records.

WHERE     (TrackingDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0) AND DateAdd(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1)))

And here it is used in a Stored Procedure

WHERE     (TrackingDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @DateParam1), 0) 
			AND DateAdd(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, @DateParam2), 1)))

Hope this helps.

Thanks

John Fuhrman