query in stored procedure - 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

General SQL Server Forums

New to SQL Server Programming

query in stored procedure

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-07-31 : 09:06:03

Hi All:

I have a situation where Im calling a stored procedure to insert some information.

When this information is inserted it is given a date/time stamp - say something in Aug.

There will be some corresponding records from the previous month already in the database that have some additional information that was manually added.

I need to query the corresponding records from the previous month and insert that info into the records that were just inserted.

the problem Im having is that i need to grab the most recent corresponding record. could be a day or a month prior to the one I just inserted. the also could be a record for months prior to that.

So how do i get the most recent corresponding record to my inserted record

Any suggestion on how to query this? and then pass the result to an udpate statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 09:21:53

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86769

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-07-31 : 09:30:02

im not sure of the difference though -- it can be one day, one hour, one month etc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 09:39:13

In the view, you will find information about how to get previous month's last day and other calculations.
Just replace CURRENT_TIMESTAMP with the date parameter instead.

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-07-31 : 09:42:47

it is not necessarily the previous months value -- its just the most recent record compared to the one im inserting

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-07-31 : 09:43:47

cant get to work but it gives the general idea

select *
from tblproject
where ProjectTimePeriod = 'select top 1 ProjectTimePeriod from tblproject
where ProjectName = @ProjectName and ProjectJobItem = @ProjectJobItem and ProjectJobSubItem = @ProjectJobSubItem and ProjectTimePeriod <= @ProjectTimePeriod'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 09:57:17

Replace single quotes with "(" and ")" without double quotes.

select *
from tblproject
where ProjectTimePeriod = (select top 1 ProjectTimePeriod from tblproject
where ProjectName = @ProjectName and ProjectJobItem = @ProjectJobItem and ProjectJobSubItem = @ProjectJobSubItem and ProjectTimePeriod <= @ProjectTimePeriod)

And you need an order by too in the subquery.

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-07-31 : 10:00:06

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 10:00:12

And you do not need a subquery at all.

SELECT TOP 1	ProjectTimePeriod
FROM		tblProject
WHERE		ProjectName = @ProjectName
		AND ProjectJobItem = @ProjectJobItem
		AND ProjectJobSubItem = @ProjectJobSubItem
		AND ProjectTimePeriod <= @ProjectTimePeriod
ORDER BY	ProjectTimePeriod DESC

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources