Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 27th, 2003, 02:26 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default Query Returning Date Ranges

I'm building a car parts web site where users will advertise and specify year ranges for their parts, like muffler for Honda's from 1996-2000. On the search page you can specify year range for your search, 1993-1997. My problem is that I'm not getting the list returned incorrectly.

There seems to be four different states in the search.
For instance, the database lists Honda mufflers 1996-2000.
1. Listed date range is totally within the query range- 1994-2003.
2. Listed date range falls within the upper limit of the search- 1998-2003.
3. Listed date range falls within the lower limit of the search- 1990-1996.
4. The query date range falls totally with the listed date range- 1997-1998.

In simpler terms, I need to find out if any part of the listed date range(by way of two columns in the database- FromYear and ToYear) is within the specified search criteria. And be able to pull those records.

I've been using different combinations of >=, <= and the "BETWEEN" function with varying results.

Any help will be greatly appreciated.

Thank you,
Richard Stelma

 
Old October 27th, 2003, 11:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I'm not sure I understand what difficulty you are having...

If I understand your problem correctly, I'll assume the following:

1. Your products table contains two columns I'll call 'PStart' and 'PEnd' which are integer (not datetime) columns containing a year value (e.g. 2003) and which represent the years of availability for the part. I'll further assume that a table constraint exists on these two columns such that PStart<=PEnd.

2. The query has two parameters '@QStart' and '@QEnd' which are the requested date ranges. These are also integers in the same format as the product years. The client application has insured that these two dates obey the constraint that @QStart<=@QEnd.

If you analyze how the query year values relate to the product year values, there are 6 possible combinations, of which 4 are valid (2 represent the query ranges totally before or totally after the product availability range, so these products should not be selected.

I think the query should be something like:
Code:
SELECT ...
FROM Products
WHERE @QStart BETWEEN PStart AND PEnd
   OR @QEnd BETWEEN PStart AND PEnd
   OR PStart BETWEEN @QStart AND @QEnd
   OR PEnd BETWEEN @QStart AND @QEnd
...
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 27th, 2003, 10:00 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi Jeff,

Thank you so much for your reply.

You did understand perfectly what I am trying to do, even adding the two cases where the search range is completely out of range of the target, being either totally above or totally below.

Your suggestion worked perfectly and I understand why you couldn't understand what I was struggling with because, yeah, it seems pretty simple now, to me too, just test for each one of the four conditions. If any of them are true, meeting the conditions of the search, pull the record.

Its good to see you're still with this group.

Thank you.

Cheers,
Richard



 
Old October 28th, 2003, 07:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by rstelma


Its good to see you're still with this group.
I didn't realize it looked like I had left ... :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 28th, 2003, 08:57 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Just cause the group was gone and wasn't sure if it would come back. Really have enjoyed this group and all the great info shared.

Anyway, fashioned the query and all is well.

Thanks,
Richard







Similar Threads
Thread Thread Starter Forum Replies Last Post
puzzle for date ranges navjot C# 1 October 16th, 2008 02:21 PM
Date Ranges nbuckwheat Access 1 December 12th, 2005 12:45 PM
date ranges yuqlin BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 December 23rd, 2004 05:23 PM
Returning the newest date in CR? johnyo101 Crystal Reports 0 November 18th, 2004 06:31 PM
Query regarding Date Ranges spraveens SQL Language 7 January 13th, 2004 08:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.