TableA = contains all inventory data
TableB = contains four fields: ID, source, date_from, date_to
This is where multiple range of dates are populated.
Sample 1:
1,'A','9/1/2004','9/30/2004'
Sample 2:
2,'A','1/1/2003','3/31/2003'
3,'A','10/1/2004','10/31/2004'
Data populated on TableB varies.
Sample SQL for Sample 1:
SELECT *
FROM TableA
WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
DATE_TO from TableB)
Problem: How to approach sql statement based on Sample 2 above?B (no_spam@.no_spam.com) writes:
> Using SQLServer ver 7.0, two tables:
> TableA = contains all inventory data
> TableB = contains four fields: ID, source, date_from, date_to
> This is where multiple range of dates are populated.
> Sample 1:
> 1,'A','9/1/2004','9/30/2004'
> Sample 2:
> 2,'A','1/1/2003','3/31/2003'
> 3,'A','10/1/2004','10/31/2004'
> Data populated on TableB varies.
>
> Sample SQL for Sample 1:
> SELECT *
> FROM TableA
> WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
> DATE_TO from TableB)
SELECT *
FROM TableA A
JOIN TableB B ON B.ID = A.ID
WHERE A.inventory_date BETWEEN B.date_from ABD B.date_to
But this is really a guess. If this does not answer your question, please
post:
o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The expected result given the sample data.
That makes it possible to post a tested solution.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 8 Nov 2004 22:40:44 -0500, B wrote:
>Using SQLServer ver 7.0, two tables:
>TableA = contains all inventory data
>TableB = contains four fields: ID, source, date_from, date_to
>This is where multiple range of dates are populated.
>Sample 1:
>1,'A','9/1/2004','9/30/2004'
>Sample 2:
>2,'A','1/1/2003','3/31/2003'
>3,'A','10/1/2004','10/31/2004'
>Data populated on TableB varies.
>
>Sample SQL for Sample 1:
>SELECT *
>FROM TableA
>WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
>DATE_TO from TableB)
>Problem: How to approach sql statement based on Sample 2 above?
Hi B,
If you want it to return all inventory details with an inventory_date
between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
and 10/31/2004, try this query:
SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM TableA AS A
INNER JOIN TableB AS B
ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This is exactly solution I needed.
Many thanks for your time!
Bob
> If you want it to return all inventory details with an inventory_date
> between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
> and 10/31/2004, try this query:
> SELECT A.Column1, A.Column2, ..., A.ColumnN
> FROM TableA AS A
> INNER JOIN TableB AS B
> ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
