Find oldest warranty ExpDate(s) for each customer

[Ken]

I have a tblWarranties containing multiple entries for each customer. I
would like to return a record set of only the oldest ExpDate(s) for each
customer. It is possible that a customer would have multiple warranties with
the same ExpDate, so I would want all.

So it would need to ignore the customers warranty records with ExpDates
older then the most current ExpDate. For instance, in the example below only
the last two entries would be part of the record set.

WarrantyType
Term
BeginDate
ExpDate

Original Warranty
2
09-01-01
09-30-03

Parts Only
1
11-28-05
11-30-06

Phone Support
1
11-28-05
11-30-06

Parts Only
1
01-07-07
01-31-08

Phone Support
1
01-07-07
01-31-08



Once I get this intial record set then I know how to filter it further by
quering for records with a ExpDate within a user selectable date range.

Thanks,

Ken

 

[John Spencer]

I am confused as to what you want. In paragraph one you say you want the
OLDEST ExpDate per customer. Then in paragraph two you want to ignore
records with expiration dates that are older than the most current ExpDate.
That seems contradictory to me.

The easiest way to do this is to use two queries. The first query get the
customer and the Minimum Expiration date for each customer. The second
query uses the first query joined to the table to return the details you
want.

More complex is one query that uses a subquery in the FROM clause

SELECT W.CustomerId, W.WarrantyType
, W.BeginDate, W.ExpDate
FROM Warranties as W INNER JOIN
(SELECT CustomerID, Min(ExpDate) as EarliestExp
FROM Warranties
GROUP BY CustomerID) as wOld
ON W.CustomerID = wOld.CustomerID
AND W.ExpDate =wOld.EarliestExp

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

More complex is one query that uses a subquery in the FROM clause

SELECT W.CustomerId, W.WarrantyType
, W.BeginDate, W.ExpDate
FROM Warranties as W INNER JOIN
(SELECT CustomerID, Min(ExpDate) as EarliestExp
FROM Warranties
GROUP BY CustomerID) as wOld
ON W.CustomerID = wOld.CustomerID
AND W.ExpDate =wOld.EarliestExp

Same query but without the subquery:

SELECT W.CustomerId, W.WarrantyType
, W.BeginDate, W.ExpDate
FROM Warranties as W INNER JOIN Warranties as wOld
ON W.CustomerID = wOld.CustomerID
GROUP BY W.CustomerId, W.WarrantyType
, W.BeginDate, W.ExpDate
HAVING W.ExpDate = MIN(wOld.ExpDate)

Jamie.

--

 

[MrGQ via AccessMonster.com]

I posted a similar question about a week ago and am still trying to
understand. I got it to work but don't understand why.

I know this may be asking too much, but can you explain each step in the SQL
process.....not necessarily what the command SELECT or INNER JOIN means but
how and why you opted for what you did.

thanks in advance

More complex is one query that uses a subquery in the FROM clause

[quoted text clipped - 6 lines]

ON W.CustomerID = wOld.CustomerID
AND W.ExpDate =wOld.EarliestExp

Same query but without the subquery:

SELECT W.CustomerId, W.WarrantyType
, W.BeginDate, W.ExpDate
FROM Warranties as W INNER JOIN Warranties as wOld
ON W.CustomerID = wOld.CustomerID
GROUP BY W.CustomerId, W.WarrantyType
, W.BeginDate, W.ExpDate
HAVING W.ExpDate = MIN(wOld.ExpDate)

Jamie.

--

 

[Jamie Collins]

I posted a similar question about a week ago and am still trying to
understand. I got it to work but don't understand why.

I know this may be asking too much, but can you explain each step in the SQL
process.....not necessarily what the command SELECT or INNER JOIN means but
how and why you opted for what you did.

thanks in advance

Interesting phrasing, "each step in the SQL process." SQL is a
declarative language, hence a SQL query reads more like a spec than a
program. You question suggests a procedural mindset, in a way the
antithesis of declarative programming. I could say the same about John
Spencer's approach i.e. the subquery is the first step in a two step
process.

The truth is, John Spencer's approach is equivalent and valid, makes
more sense to him and others hence will be easier to maintain and I
also doubt that my approach would perform any better. Now I think
about it, I prefer John Spencer's approach myself! I merely posted
mine out of interest because it avoids the subquery.

If you like my approach then you may want to learn about how to 'think
in sets'. Joe Celko writes about this e.g.

It's the Code, Stupid!
http://www.dmreview.com/article_sub.cfm?articleID=1028737

Thinking in SQL
http://www.dbazine.com/ofinterest/oi-articles/celko5

and I understand he has a book out later this year on this very
subject.

I think SQL is hard to learn if you are used to procedural
programming. You probably just need lots of practice <g>.

Jamie.

--

 

[Jamie Collins]

I posted a similar question about a week ago and am still trying to
understand. I got it to work but don't understand why.

I know this may be asking too much, but can you explain each step in the SQL
process.....not necessarily what the command SELECT or INNER JOIN means but
how and why you opted for what you did.

thanks in advance

Interesting phrasing, "each step in the SQL process." SQL is a
declarative language, hence a SQL query reads more like a spec than a
program. You question suggests a procedural mindset, in a way the
antithesis of declarative programming. I could say the same about John
Spencer's approach i.e. the subquery is the first step in a two step
process.

The truth is, John Spencer's approach is equivalent and valid, makes
more sense to him and others hence will be easier to maintain and I
also doubt that my approach would perform any better. Now I think
about it, I prefer John Spencer's approach myself! I merely posted
mine out of interest because it avoids the subquery.

If you like my approach then you may want to learn about how to 'think
in sets'. Joe Celko writes about this e.g.

It's the Code, Stupid!
http://www.dmreview.com/article_sub.cfm?articleID=1028737

Thinking in SQL
http://www.dbazine.com/ofinterest/oi-articles/celko5

and I understand he has a book out later this year on this very
subject.

I think SQL is hard to learn if you are used to procedural
programming. You probably just need lots of practice <g>.

Jamie.

--

 

[MrGQ via AccessMonster.com]

WOW.....I didn't realize there was sooooooo much packed behind my question.
I simply was interested in understanding the design of the solution you
provided.

There is no way I can debate the philosophical aspects of structured query
language. I'm not a programmer and I create simple access databases to use
in a laboratory. I thought you may be able to provide a 50,000 ft view of
the statements and from that, I may be able to understand sufficiently to
apply to my situation since it is very similar to the originator's data table.


Frank

I posted a similar question about a week ago and am still trying to
understand. I got it to work but don't understand why.

[quoted text clipped - 4 lines]

thanks in advance

Interesting phrasing, "each step in the SQL process." SQL is a
declarative language, hence a SQL query reads more like a spec than a
program. You question suggests a procedural mindset, in a way the
antithesis of declarative programming. I could say the same about John
Spencer's approach i.e. the subquery is the first step in a two step
process.

The truth is, John Spencer's approach is equivalent and valid, makes
more sense to him and others hence will be easier to maintain and I
also doubt that my approach would perform any better. Now I think
about it, I prefer John Spencer's approach myself! I merely posted
mine out of interest because it avoids the subquery.

If you like my approach then you may want to learn about how to 'think
in sets'. Joe Celko writes about this e.g.

It's the Code, Stupid!
http://www.dmreview.com/article_sub.cfm?articleID=1028737

Thinking in SQL
http://www.dbazine.com/ofinterest/oi-articles/celko5

and I understand he has a book out later this year on this very
subject.

I think SQL is hard to learn if you are used to procedural
programming. You probably just need lots of practice <g>.

Jamie.

--

 

[Jamie Collins]

WOW.....I didn't realize there was sooooooo much packed behind my question.
I simply was interested in understanding the design of the solution you
provided.

There is no way I can debate the philosophical aspects of structured query
language. I'm not a programmer and I create simple access databases to use
in a laboratory. I thought you may be able to provide a 50,000 ft view of
the statements and from that, I may be able to understand sufficiently to
apply to my situation since it is very similar to the originator's data table.

See:

http://tinyurl.com/2xo7m3

"Here is how a SELECT works in SQL..."

I feel I covered the point in my original reply but my advice is
practice, practice, practice (warning: I was a business analyst
manager when I first started using SQL in anger -- interested in team
progress reports, etc -- and I'm now a software engineer). Learn how
to write a SELECT query with a WHERE clause, progress to simple JOINS
(hint: a RIGHT OUTER JOIN is the same as a LEFT OUTER JOIN with the
tables switched), look at GROUP BY then HAVING (e.g. how does a HAVING
differ from a WHERE clause? hint: you can't refer to a set function in
a WHERE clause) and finally learn that derived tables (a la John
Spencer) mean you probably don't ever need a HAVING clause.

There are some good, if basic, interactive online tutorials (e.g.
http://www.sqlcourse.com/ ). Newsgroups such as this one are great for
practice scenarios ;-)

I you do find any short cuts to a fuller understanding of how SQL
works, please post them here.

Jamie.

--

 

[Ken]

Thank you for your response, sorry it took so long to get back to you.

Thanks for catching my contradiction with the Oldest problem. I modified
your query to use the Max instead of Min. I'm experiencing a wierd
behavior. Some, not all, of the results are being duplicated. Seems to be
random.

SELECT W.CompName, W.SN, W.Model, W.ContactID, W.WarrantyType, W.BeginDate,
W.ExpDate
FROM Warranties AS W INNER JOIN [SELECT ContactID, Max(ExpDate) as LatestExp
FROM Warranties
GROUP BY ContactID]. AS wOld ON (W.ExpDate = wOld.LatestExp) AND
(W.ContactID = wOld.ContactID)
ORDER BY W.CompName;

Thanks,

Ken