August 7, 2009 at 12:35 pm
I am trying to pull the latest "status_date" and the corresponding "status" in the Prot2 table on an ongoing basis (by running the query in a job). In the Status table, there could also be multiple status entries for the same status_date, though with different time_stamps.
A example would be:
pnumber Status Status_Date time_stamp
E1234567 Active 2009-07-11 2009-07-11 11:00:03
E1234567 Pending 2009-07-18 2009-07-18 10:00:00
E1234567 Closed 2009-07-18 2009-07-18 10:34:55
This is what the Prot2 entry for this pnumber should look like if the queries work perfectly.
Prot2
pnumber latest_status max_status_date
E1234567 Closed 2009-07-18
Prot2 table is a master table with pnumber as the primary key. Every pnumber can have multiple entries in Status table, with different statuses and status_dates. I got the query to update the latest status_date but unable to get the corresponding status.
Here is the structure of the 2 tables, followed by the query that I wrote for updating the status_date
Prot2 table
[pnumber] [varchar] (10), [study_title] [varchar] (500),
[time_stamp] [datetime] NOT NULL CONSTRAINT DEFAULT (getdate()),
[latest_status] [varchar](40) NULL, [max_status_date] [datetime] NULL
Status
[pnumber] [varchar] (10), [status] [varchar] (40),
[status_date] [datetime] NULL,
[time_stamp] [datetime] NOT NULL DEFAULT (getdate()),
[status_id] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
--update prot2 max_status_date
update prot2
set max_status_date = (select max(status_date) from status where status.[pnumber] = prot2.[pnumber] group by pnumber)
Thank you for reading, and I really appreciate your time and help.
Regards..
August 7, 2009 at 12:39 pm
Please don't double-post in multiple forums on the same site.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply