August 7, 2009 at 12:34 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
Does this get you what you need?
;
WITH Statuses
AS (SELECT
pnumber,
Status,
time_stamp,
row_number() OVER (PARTITION BY pnumber ORDER BY time_stamp DESC) AS ReverseSequence
FROM
Prot2)
SELECT
*
FROM
Statuses
WHERE
ReverseSequence = 1 ;
- 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
August 7, 2009 at 12:46 pm
How's this? Gets the data based on the Max(Status_Date) then gets the underlying status(text) from the table.
It is untested, since I cannot test it.
--update prot2 max_status_date
UPDATE prot2
SET max_status_date = GroupedStatus.[Status_Date],
[Status] = GroupedStatus.[Status] --(select max(status_date) from status where status.[pnumber] = prot2.[pnumber] group by pnumber)
FROM Prot2 -- Joining the found Max Status will help us get both fields, based on only 1 max value max(Status_Date)
INNER JOIN (SELECT Status_Date,
[Status],
[pnumber]
FROM Status s -- Joining the Max Status_Date + Pnumber gets us the Status (text)
INNER JOIN (SELECT max(status_date) AS Status_Date, -- Get the latest status_Date from the Status, by PNumber
[pnumber]
FROM Status
GROUP BY [pNumber]) AS MaxStatus
ON s.[pnumber] = MaxStatus.[pnumber] -- Join on the Pnumber + Max(status_Date) as the key to reference
AND s.[Status_Date] = MaxStatus.[Status_Date]) AS GroupedStatus
ON Prot2.[pNumber] = GroupedStatus.[pNumber]
Cheers,
J-F
August 7, 2009 at 12:48 pm
GSquared (8/7/2009)
Does this get you what you need?
;WITH Statuses
AS (SELECT
pnumber,
Status,
time_stamp,
row_number() OVER (PARTITION BY pnumber ORDER BY time_stamp DESC) AS ReverseSequence
FROM
Prot2)
SELECT
*
FROM
Statuses
WHERE
ReverseSequence = 1 ;
Gus, I have to say this is brilliant.. I never thought of using ROW_Number to get the max date + other fields... Good job on this one, this beats my query by far...
Cheers,
J-F
August 7, 2009 at 12:51 pm
It's a single-pass version of that problem, and in tests I've done, it's MUCH faster.
That and deduping are the two best uses of Row_Number that I've found. I've used it for other things, but those two are the best.
- 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
August 7, 2009 at 12:53 pm
Yes, I really love the solution, I'll keep a small script for that, it'll be useful!
Thanks again
Cheers,
J-F
August 7, 2009 at 1:21 pm
You're welcome.
- 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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply