January 20, 2006 at 4:54 pm
hello, i have an easy query for one of your braniacs.
id number name Date
2537 DAN1 Dan's Tutoring 2005-07-01
7135 DAN1 Dan's Tutoring 2006-07-01
...
i need a query that returns all the columns for all the records that are joined on the number, as it is unique per date. The above results has 2 records. i want to not show either record if the number is same and the date is 1 year greater.
This is my failed attempt to not show the 2537 record since the 7135 record has the same number and is 1 year older.
SELECT DISTINCT
a.ID,
a.NUMber,
a.NAME,
a.DaTe
FROM
CLASS a
LEFT OUTER JOIN
CLASS b ON a.NUMber = b.NUMBER
AND a.DATE != DATEADD(YEAR, -1, b.DATE)
i do need the query to also not show the 7135 either as i only want the results to contain the records that don't have a match for the year + 1
thank you so much to anyone who helps.
January 20, 2006 at 5:58 pm
Thomas,
This isn't the most efficient, but it works...
----------------------------
SELECT
ID,
Number,
Name,
Date
FROM
CLASS
WHERE
Number NOT IN
(SELECT
B.Number
FROM
CLASS A LEFT OUTER JOIN
CLASS B ON
A.Number = B.Number AND
A.Date = DATEADD(year, 1, B.Date)
WHERE B.ID Is NOT NULL)
--------------------------
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 20, 2006 at 6:02 pm
Sorry about the spacing! Extra spaces got removed from my post ?????
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 20, 2006 at 10:08 pm
No this is how html gets presented.
January 23, 2006 at 7:39 am
thanks for the response, but it doesn't seem be be working correctly, it is not returning any records.
I need a list of records that don't have a matching record exactly 1 year later.
i am also filtering on displaying only the records in the following date range: 7/1/2005 - 6/30/2006 who don't have a matching record 1 year later, so the 7135 record would not show up, even though it doesn't have a matching record a year later (2007-07-01).
So to add some more records see:
id number name Date
2537 DAN1 Dan's Tutoring 2005-07-01
7135 DAN1 Dan's Tutoring 2006-07-01
2112 SS2211 SuperSaver 2005-09-01
3311 Upper3 Upper III 2005-09-07
6336 Lowest LowestClass 2006-08-01
So what should be returned is records 2112 & 3311
Again, i am in great need of this and much thanks to any contributors
January 23, 2006 at 10:05 am
--using the dateformat yyyy-mm-dd
select * from class cc
where cc.number in
(
select aa.number from
class aa
full outer join
class bb on aa.number = bb.number and ABS(datediff(dd,aa.date, bb.date))< 365
where bb.date between '2005-7-1' and '2006-6-30' and aa.date between '2005-7-1' and '2006-6-30'
)
and cc.date between '2005-7-1' and '2006-6-30'
i tried it on my machine i don't know if it will produce what you are really looking for
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply