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...
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...