help converting a query to stored procedure

[patrichek]

Hi guys,
many thanks to all who've helped me so far.
I recently upsized my mdb to adp and need help converting this query to a stored procedure:

SELECT F.Date, F.Status, F.Notes, C.FirstName, C.LastName, C.HomePhone
FROM (Contacts AS C INNER JOIN tblFilmStatus AS F ON C.ContactID = F.ContactID) INNER JOIN [SELECT ContactID, Max([Date]) As LastDate FROM tblFilmStatus
WHERE [Date]<Date()-8 AND Status<>'Received' GROUP BY ContactID
]. AS L ON (F.Date = L.LastDate) AND (F.ContactID = L.ContactID)
WHERE F.Status<>'Received';

thanks!

 

[cmmrfrds]

Here is a link to The sql server help documentation (a download). You should be able to find the syntax and examples.

http://www.microsoft.com/downloads/...B1-A420-445F-8A4B-BD77A7DA194B&displaylang=en

 

[ESquared]

Convert your single quotes to double quotes.
Change Date() to
DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
Change square brackets around SELECT subquery to parentheses and remove trailing period.

tack on

CREATE PROCEDURE Whatever
AS

to the beginning and you should have a really good start.

 

[ESquared]

er, sorry, got the first one backward, single quotes is correct (blush).

 

[patrichek]

HI,
How would I make this statement only give the most recent date. I have older records as well as new for the same person and I want just the latest.
here's my statement so far but i can't figure out the criteria for only the newest records:

ALTER PROCEDURE dbo.filmstatusquery
AS SELECT dbo.tblFilmStatus.Date, dbo.tblFilmStatus.Type, dbo.tblFilmStatus.Status, dbo.tblFilmStatus.Notes, dbo.Contacts.FirstName, dbo.Contacts.LastName,
dbo.Contacts.EmailName, dbo.Contacts.HomePhone
FROM dbo.tblFilmStatus INNER JOIN
dbo.Contacts ON dbo.tblFilmStatus.ContactID = dbo.Contacts.ContactID
WHERE (dbo.tblFilmStatus.Status <> N'Received')

thanks!

 

[cmmrfrds]

I believe the default for date arthimetic is days so you should just be able to do the same thing.

WHERE (dbo.tblFilmStatus.Status <> N'Received'
and [Date] < getdate() -8)

 

[patrichek]

hmm...its still pulling all the records even the old ones!, any other ideas?

 

[cmmrfrds]

Do you only want the last 8 days? Then it needs to be >

[Date] > getdate() -8

 

[ESquared]

getdate() is like Now()... there's no equivalent of the Date function. So be careful how exactly you are doing it.

Please explain your data more. What is are the key values of the tables you're working with? How do you relate records in the same table about the same film that are on different dates? And is there a uniquely identifying single column for each record?

 

[patrichek]

thank you! its working