Select the last updated date item in a table

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

Select the last updated date item in a table

Author

Topic

elsteshepardo
Starting Member

4 Posts

Posted - 2011-11-23 : 08:19:35

Hi,

I am very new to SQL, I have mostly been copying queries directly from the system (I can trace actions in the GUI)

This is the problem;

I have two tables - one which stores the most recently entered value.
Table is Schedules (value is RTTST_REFNO)
I have another table which stores the cumulative entries for each entered value
Table is RTT

I want to be create a validation report which shows me what the most recently updated RTTST_REFNO from the RTT table is compared to the entry in the Schedules table.

I have worked out the joins etc. but the query i have brings back each entry in the RTT table - there can be many entries in this table.

I need to see only the most recent - is there a way?

Thanks

Steven

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-23 : 08:40:13

ORDER BY can help.
Perhaps MAX also.
Totally depends on your table structures that we can't see.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

elsteshepardo
Starting Member

4 Posts

Posted - 2011-11-23 : 08:46:45

Sorry, I know it's not much to go on.
What do you need to know?
The table RTT is a history table, used to store all updates to RTTST_REFNO throughout the system. The table also contains;
create_dttm - could that be used, i.e. select the latest updated row only?

Steven

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-23 : 09:41:02

follow the HOW TO ASK link in my sig. It will give you instructions on how to supply all the data the good folks here need to help you.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 02:07:22

Post the query you have used. It can be altered to get the output you want

Madhivanan

Failing to plan is Planning to fail

elsteshepardo
Starting Member

4 Posts

Posted - 2011-11-24 : 06:40:40

Hi,
this is the query - apologies if this doesn't look correct after pasting it;

SELECT PATIENTS.PASID, refrl.PATNT_PATHWAY_ID, REFERENCE_VALUES_1.DESCRIPTION AS previous_rtt, SCHEDULES.START_DTTM as appt_date, REFERENCE_VALUES.DESCRIPTION as appt_rtt,
REFERRAL_TO_TREATMENTS.RTTST_DATE
,REFERRAL_TO_TREATMENTS.CREATE_DTTM

FROM

REFERRAL_TO_TREATMENTS,
referrals as refrl,
SCHEDULES,
PATIENTS,
REFERENCE_VALUES,
REFERENCE_VALUES AS REFERENCE_VALUES_1

where
REFERRAL_TO_TREATMENTS.REFRL_REFNO = refrl.REFRL_REFNO
and SCHEDULES.PATNT_REFNO = PATIENTS.PATNT_REFNO
and refrl.REFRL_REFNO = SCHEDULES.REFRL_REFNO
and SCHEDULES.RTTST_REFNO = REFERENCE_VALUES.RFVAL_REFNO
and REFERRAL_TO_TREATMENTS.RTTST_REFNO = REFERENCE_VALUES_1.RFVAL_REFNO

and schedules.start_dttm >= '20111113'
and schedules.start_dttm < '20111117'
and schedules.rttst_refno in (2004320,2004321)
and REFERRAL_TO_TREATMENTS.ARCHV_FLAG = 'n'
and schedules.attnd_refno <> 2000725
and refval.description <> 'cancelled'
order by pasid,SCHEDULES.START_DTTM,REFERRAL_TO_TREATMENTS.CREATE_DTTM

The results gets me something like this;
PASID Pathway ID previous rtt Apptdate apprtt rttstdate create d
22418 123546 90 16/11/2011 90 16/11/2011 16/11 8.24
22418 123456 20 16/11/2011 90 16/11/2011 16/11 8.24
22418 123456 20 16/11/2011 90 16/11/2011 16/11 9.13

I need to know the latest previous rtt priort to the addition of the app rtt code

Thanks

Steven

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-25 : 05:44:28

Post the expected result. It is not clear from what you have said

Madhivanan

Failing to plan is Planning to fail

elsteshepardo
Starting Member

4 Posts

Posted - 2011-11-25 : 06:11:10

Hi,
What i want is 1 row, per pathway_id which shows the RTTST_REFNO from the schedules table and the last updated RTTST_REFNO from the Referral_to_treatments table, so it would look like this;

PASID Pathway_ID RTTST (from referral_to_treatments) RTTST (from schedules table)
12345 000123131313 20 30
54321 000132979416 30 90
etc.

Thanks

Steven

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources