Help with Query\Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Query\Join

Author

Topic

dt293
Starting Member

8 Posts

Posted - 2007-06-22 : 10:05:13

Hello all,

I am trying to write a stored procedure that will return data based on the creation date. Basically the first table (Register) contains two fields called From Entry No and To Entry No. Eg
Creation Date
2007-06-22
From Entry No
655530
To Entry No
655537

The second table (Entry) has each individual line entry for these Entry No's. Eg
Entry No
655530
655531
655532
655533
655534
655535
655536
655537

How would I join these to tables to return each line from the Entry table based on a date?

Any help would be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 10:07:07

creation date is in which table ?

KH
[spoiler]Time is always against us[/spoiler]

dt293
Starting Member

8 Posts

Posted - 2007-06-22 : 10:18:48

Register table contains;
Creation Date
From Entry No
To Entry No

Entry table contains;
Entry No (Which is each individual line item)

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 10:24:41

[code]DECLARE @Register TABLE
(
Creation_Date datetime,
From_Entry_No int,
To_Entry_No int
)
INSERT INTO @Register
SELECT '2007-06-22', 655530, 655537

DECLARE @Entry TABLE
(
Entry_No int
)

INSERT INTO @Entry
SELECT 655530 UNION ALL
SELECT 655531 UNION ALL
SELECT 655532 UNION ALL
SELECT 655533 UNION ALL
SELECT 655534 UNION ALL
SELECT 655535 UNION ALL
SELECT 655536 UNION ALL
SELECT 655537

SELECT *
FROM @Register r INNER JOIN @Entry e
ON r.From_Entry_No <= e.Entry_No
AND r.To_Entry_No >= e.Entry_No
WHERE r.Creation_Date = '2007-06-22'[/code]

KH
[spoiler]Time is always against us[/spoiler]

dt293
Starting Member

8 Posts

Posted - 2007-06-22 : 10:40:18

OK, but I need to get back all entries from a particular day rather than specifying the entry no's.

Here is some data

Register table
From Entry No To Entry No Creation Date Column1 Column2
3466557 3466558 2007-06-22 00:00:00.000 PURCHASES ITAHIR

Entry table
Entry No Column1
3466557 ******
3466558

Hope this make a bit more sense.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 10:43:00

quote:
Originally posted by dt293

OK, but I need to get back all entries from a particular day rather than specifying the entry no's.

Here is some data

Register table
From Entry No To Entry No Creation Date Column1 Column2
3466557 3466558 2007-06-22 00:00:00.000 PURCHASES ITAHIR

Entry table
Entry No Column1
3466557 ******
3466558

Hope this make a bit more sense.

The query did just what you wanted by specifying the date in red

SELECT	*
FROM	@Register r INNER JOIN @Entry e
	ON	r.From_Entry_No	<= e.Entry_No
	AND	r.To_Entry_No	>= e.Entry_No
WHERE	r.Creation_Date		= '2007-06-22'

KH
[spoiler]Time is always against us[/spoiler]

dt293
Starting Member

8 Posts

Posted - 2007-06-22 : 10:53:55

Maybe I just haven't explain my issue very clearly...

I need to return all entry no's for a particular day from the entry table, there may be 200 entries each day with varying no's. The query above specifies a set range from 655530 to 655537 which returns that range perfectly but what about the other 100 or so entry no's?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 11:00:21

can you post the more sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

dt293
Starting Member

8 Posts

Posted - 2007-06-22 : 11:17:11

OK here is the top 10 from the Register table...

No_ From Entry No_ To Entry No_ Creation Date
----------- -------------- ------------ -----------------------
537318 3466557 3466558 2007-06-22 00:00:00.000
537319 3466559 3466560 2007-06-22 00:00:00.000
537320 3466561 3466562 2007-06-22 00:00:00.000
537321 3466563 3466577 2007-06-22 00:00:00.000
537322 3466578 3466579 2007-06-22 00:00:00.000
537323 3466580 3466592 2007-06-22 00:00:00.000
537324 3466593 3466612 2007-06-22 00:00:00.000
537325 3466613 3466613 2007-06-22 00:00:00.000
537326 3466614 3466616 2007-06-22 00:00:00.000
537327 3466617 3466619 2007-06-22 00:00:00.000

I need to return each corresponding line from the entry table for every range on a particular day... for this example lets use the last range 3466617 to 3466619. This is what appears on the entry table for that range...

Entry table
Entry No_ G_L Account No_ Document Type Document No_
----------- -------------------- ------------- --------------------
3466617 73320 2 PINV07_009749
3466618 21422 2 PINV07_009749
3466619 20010 2 PINV07_009749

I would like to see every entry on a particluar day....
Entry No_ G_L Account No_ Document Type Document No_ Creation Date
----------- -------------------- ------------- ------------------------------------
3466617 73320 2 PINV07_009749 2007-06-22
3466618 21422 2 PINV07_009749 2007-06-22
3466619 20010 2 PINV07_009749 2007-06-22

Thanks again for you patience!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 11:28:29

Is it what i posted earlier ?

DECLARE @Register TABLE
(
	[No]		int,
	From_Entry_No	int,
	To_Entry_No	int,
	Creation_Date 	datetime
)
INSERT INTO @Register
SELECT	537327, 3466617, 3466619, '2007-06-22'

DECLARE @Entry TABLE
(
	Entry_No	int
)

INSERT INTO @Entry
SELECT	3466616	UNION ALL
SELECT	3466617	UNION ALL
SELECT	3466618	UNION ALL
SELECT	3466619	UNION ALL
SELECT	3466620	

SELECT	e.Entry_No, r.Creation_Date
FROM	@Register r INNER JOIN @Entry e
	ON	r.From_Entry_No	<= e.Entry_No
	AND	r.To_Entry_No	>= e.Entry_No
/*
Entry_No    Creation_Date                                          
----------- -------------
    3466617 2007-06-22 
    3466618 2007-06-22 
    3466619 2007-06-22 
*/

KH
[spoiler]Time is always against us[/spoiler]

dt293
Starting Member

8 Posts

Posted - 2007-06-22 : 11:59:31

I dont think so because the Entry No's are basically unknown, the code above assumes that you know what entry no's you need. I just want everything for the day....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources