help with stored procedure

[patrichek]

HI,
How would I make this statement only pull the most recent record by date. I have older records as well as new for the same person and I want just the most recent record in the query along with the other criteria status "Received".
here's my statement so far but i can't figure out the criteria for only the most recent record:

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!

 

[SQLDenis]

most recent record by what date Film or Contact?
Do you have a date field in both table
order by date desc if you need to

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[patrichek]

sry, I'm a newbie who recently upsized my access db and only 1 query didn't convert. I don't yet understand stored procedures so please bear with me!

Yes i have a date in both tables but i'm concerned with the date in the tblfilmstatus as there are multiple dates for each record. There is only 1 date in the contacts table.

thank you!

 

[SQLDenis]

If you are returning records for 1 movie only use this

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

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[patrichek]

Hi SQLDenis,

the films aren't movies, they're xrays and MRIs
this statement gives me what i want almost. I need to narrow it down to pull records on the most current date.

i'll try to explain; I have a tab on a form called Patient status, in this form i have a subform named film status. Users enter the status of patients xrays and mris. The status is a combo box with mailed, received and sent back as selections. So one record (patient) can have multiple statuses and dates. What i need is to pull all the records where the date is 8 days past today and the status is not received. we don't need multiple records though, only the most current.

I need some kind of rule that only allows the most current status of a record. like if i put in 2 "sent" for the same patient, I want only the most current "sent".
sorry for the long post!

here's my statement:

ALTER PROCEDURE dbo.FilmStatusQuery
AS SELECT t.Date, t.Type, t.Status, t.Notes, c.FirstName, c.LastName, c.EmailName, c.HomePhone
FROM dbo.tblFilmStatus t INNER JOIN
dbo.Contacts c ON t.ContactID = c.ContactID
WHERE (t.Status <> N'Received') AND (t.Date < GETDATE() - 8)

 

[jiqjaq]

patricheck: You could add another JOIN using a "derived" table, but since you're new to this, it's easier to change your WHERE and use a subquery. It's easier to see what's happening.

ALTER PROCEDURE dbo.FilmStatusQuery
AS SELECT t.Date, t.Type, t.Status, t.Notes, c.FirstName, c.LastName, c.EmailName, c.HomePhone
FROM dbo.tblFilmStatus t INNER JOIN
dbo.Contacts c ON t.ContactID = c.ContactID
WHERE t.Date =
(
SELECT MAX(Date) MaxDate
FROM dbo.tblFilmStatus
WHERE (Status <> N'Received') AND (t.Date < GETDATE() - 8) AND ContactID = t.ContactID
)

Now you're checking the date to use the most recent date for that customer, that also meets your other criteria.