MaxDate multiple columns - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

MaxDate multiple columns

Author

Topic

ALSZ37
Starting Member

25 Posts

Posted - 2015-01-06 : 14:09:23

Hi, I am trying to figure out how to pull records with the most recent status for a person in the program they are associated in. The person can be associated to many programs and I am looking to pull the most recent status for all the programs they are in. I know how to pull the max date for a single personID, but if they are in more than one program it will be the most recent status for a program.

Here is my query:

select
p.personid, p.program, p.status, b.[status date]
from (select a.personid, max(a.statusdate) as 'Status Date' from patient a group by a.personid) b join
patient p on p.personid = b.personid and p.statusdate = b.[status date]

Here is a example of data and my query in SQL Fiddle [url]http://sqlfiddle.com/#!3/a4f4a1/10/0[/url]

Personid 7 only displays program 9 because it has a more current date, but I want it to pull program 7 also. Any help or guidance would be greatly appreciated.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-06 : 14:30:20

Something like this?


select p.personID, p.Program, max(p.status) status, max(p.StatusDate)
from patient p
join (
	select personid, program, max(statusdate) statusdate
	from patient 
	group by personid, program
	) c 
  on p.PersonID = c.PersonID and p.Program = c.Program and p.statusdate = c.StatusDate
group by p.PersonID, p.Program
order by p.PersonID, p.Program

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-06 : 14:47:15

[code]
SELECT p.personid, p.program, p.status, p.[status date]
FROM (
SELECT personid, program, status, statusdate as 'Status Date',
ROW_NUMBER() OVER(PARTITION BY personid, program ORDER BY statusdate DESC) AS row_num
FROM patient
) AS p
WHERE
p.row_num = 1
--ORDER BY personid, program

[/code]

ALSZ37
Starting Member

25 Posts

Posted - 2015-01-06 : 15:15:10

quote:

Originally posted by gbritton

Something like this?


select p.personID, p.Program, max(p.status) status, max(p.StatusDate)
from patient p
join (
	select personid, program, max(statusdate) statusdate
	from patient 
	group by personid, program
	) c 
  on p.PersonID = c.PersonID and p.Program = c.Program and p.statusdate = c.StatusDate
gselect p.personID, p.Program, max(p.status) status, max(p.StatusDate)
from patient p
join (
	select personid, program, max(statusdate) statusdate
	from patient 
	group by personid, program
	) c 
  on p.PersonID = c.PersonID and p.Program = c.Program and p.statusdate = c.StatusDate
group by p.PersonID, p.Program
order by p.PersonID, p.Program   
order by p.PersonID, p.Program

GBritton, Thank you for the quick reply! I ended up figuring it out and adding the program_id in the sub select. Was just about to reply I figured it out and saw responses already. This query works as well, but why is the group bygroup by p.PersonID, p.Program required outside the sub select?

ALSZ37
Starting Member

25 Posts

Posted - 2015-01-06 : 15:16:51

quote:

Originally posted by ScottPletcher


SELECT p.personid, p.program, p.status, p.[status date]
FROM (
    SELECT personid, program, status, statusdate as 'Status Date',
        ROW_NUMBER() OVER(PARTITION BY personid, program ORDER BY statusdate DESC) AS row_num
    FROM patient
) AS p
WHERE
    p.row_num = 1
--ORDER BY personid, program

Scott, Thank you for the response! This query works as well, but i'm unfamiliar with ROW_NUMBER() OVER(PARTITION BY command. What does this do!?!??!?!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-06 : 15:25:49

quote:
Originally posted by ALSZ37

quote:
Originally posted by ScottPletcher

SELECT p.personid, p.program, p.status, p.[status date]
FROM (
SELECT personid, program, status, statusdate as 'Status Date',
ROW_NUMBER() OVER(PARTITION BY personid, program ORDER BY statusdate DESC) AS row_num
FROM patient
) AS p
WHERE
p.row_num = 1
--ORDER BY personid, program

Scott, Thank you for the response! This query works as well, but i'm unfamiliar with ROW_NUMBER() OVER(PARTITION BY command. What does this do!?!??!?!

ROW_NUMBER() assigns a row number starting at 1 for each new PARTITION break across all the rows in the result set. For example, suppose you had a list of all states and selected cities within those states.

ALSZ37
Starting Member

25 Posts

Posted - 2015-01-06 : 15:39:34

quote:
Originally posted by ScottPletcher

quote:
Originally posted by ALSZ37

quote:
Originally posted by ScottPletcher

SELECT p.personid, p.program, p.status, p.[status date]
FROM (
SELECT personid, program, status, statusdate as 'Status Date',
ROW_NUMBER() OVER(PARTITION BY personid, program ORDER BY statusdate DESC) AS row_num
FROM patient
) AS p
WHERE
p.row_num = 1
--ORDER BY personid, program

Scott, Thank you for the response! This query works as well, but i'm unfamiliar with ROW_NUMBER() OVER(PARTITION BY command. What does this do!?!??!?!

ROW_NUMBER() assigns a row number starting at 1 for each new PARTITION break across all the rows in the result set. For example, suppose you had a list of all states and selected cities within those states.

LOL! Google is my best friend as well!

I just noticed that there is no max statement so how does the subselect determine which had the latest date? This may be over my head so if it's too hard to explain no worries. I'll play with it some more. Thanks again!

ALSZ37
Starting Member

25 Posts

Posted - 2015-01-06 : 15:48:14

quote:
Originally posted by ALSZ37

quote:
Originally posted by ScottPletcher

quote:
Originally posted by ALSZ37

quote:
Originally posted by ScottPletcher

SELECT p.personid, p.program, p.status, p.[status date]
FROM (
SELECT personid, program, status, statusdate as 'Status Date',
ROW_NUMBER() OVER(PARTITION BY personid, program ORDER BY statusdate DESC) AS row_num
FROM patient
) AS p
WHERE
p.row_num = 1
--ORDER BY personid, program

Scott, Thank you for the response! This query works as well, but i'm unfamiliar with ROW_NUMBER() OVER(PARTITION BY command. What does this do!?!??!?!

ROW_NUMBER() assigns a row number starting at 1 for each new PARTITION break across all the rows in the result set. For example, suppose you had a list of all states and selected cities within those states.

LOL! Google is my best friend as well!

I just noticed that there is no max statement so how does the subselect determine which had the latest date? This may be over my head so if it's too hard to explain no worries. I'll play with it some more. Thanks again!

Scott, I put this logic in my query and see how it works. Appreciate it sir!!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-06 : 16:19:42

quote:
Originally posted by ALSZ37

quote:
Originally posted by gbritton

Something like this?

select p.personID, p.Program, max(p.status) status, max(p.StatusDate)
from patient p
join (
select personid, program, max(statusdate) statusdate
from patient
group by personid, program
) c
on p.PersonID = c.PersonID and p.Program = c.Program and p.statusdate = c.StatusDate
gselect p.personID, p.Program, max(p.status) status, max(p.StatusDate)
from patient p
join (
select personid, program, max(statusdate) statusdate
from patient
group by personid, program
) c
on p.PersonID = c.PersonID and p.Program = c.Program and p.statusdate = c.StatusDate
group by p.PersonID, p.Program
order by p.PersonID, p.Program
order by p.PersonID, p.Program

GBritton, Thank you for the quick reply! I ended up figuring it out and adding the program_id in the sub select. Was just about to reply I figured it out and saw responses already. This query works as well, but why is the group bygroup by p.PersonID, p.Program required outside the sub select?

No, in fact this should do it:

[code]
select p.personID, p.Program, p.status, p.StatusDate
from patient p
join (
select personid, program, max(statusdate) statusdate
from patient
group by personid, program
) c
on p.PersonID = c.PersonID and p.Program = c.Program and p.statusdate = c.StatusDate
order by p.PersonID, p.Program

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources