July 27, 2016 at 7:11 am
Hello,
I need some assistance with a code. I am trying to return the APPL_CURRENT_STATUS where the APPL_CURRENT_STATUS_DATE is the max(APPL_CURRENT_STATUS_DATE)
SELECTAPPLICANTS_ID,
APPLICATIONS_OCC.APPL_CURRENT_STATUS,
APPL_CURRENT_STATUS_DATE
FROM APPLICANTS
LEFT OUTER JOIN APPLICATIONS AS APPL ON APPLICANTS.APPLICANTS_ID = APPL.APPL_APPLICANT
LEFT OUTER JOIN APPLICATIONS_OCC ON APPL.APPLICATIONS_ID = APPLICATIONS_OCC.APPLICATIONS_ID
WHERE APPL_ACAD_LEVEL = 'UG'
APPLICANTS_IDAPPL_CURRENT_STATUSAPPL_CURRENT_STATUS_DATE
1111111 RF 2014-07-10 00:00:00.000
1111111 RW 2012-06-01 00:00:00.000
1111111 AT 2015-05-06 00:00:00.000
Any help would be greatly appreciated.
July 27, 2016 at 7:17 am
ajspencer (7/27/2016)
Hello,I need some assistance with a code. I am trying to return the APPL_CURRENT_STATUS where the APPL_CURRENT_STATUS_DATE is the max(APPL_CURRENT_STATUS_DATE)
SELECTAPPLICANTS_ID,APPLICATIONS_OCC.APPL_CURRENT_STATUS,
APPL_CURRENT_STATUS_DATE
FROM APPLICANTS
LEFT OUTER JOIN APPLICATIONS AS APPL ON APPLICANTS.APPLICANTS_ID = APPL.APPL_APPLICANT
LEFT OUTER JOIN APPLICATIONS_OCC ON APPL.APPLICATIONS_ID = APPLICATIONS_OCC.APPLICATIONS_ID
WHERE APPL_ACAD_LEVEL = 'UG'
APPLICANTS_IDAPPL_CURRENT_STATUSAPPL_CURRENT_STATUS_DATE
1111111 RF 2014-07-10 00:00:00.000
1111111 RW 2012-06-01 00:00:00.000
1111111 AT 2015-05-06 00:00:00.000
Any help would be greatly appreciated.
Hi and welcome to the forums. I am not totally sure what you are trying to do here. Are you looking for only 1 row to be returned or do you want the most current status for a number of rows?
Assuming you only want 1 row you could use something like this.
select top 1 APPLICATIONS_OCC.APPL_CURRENT_STATUS
FROM APPLICANTS
LEFT OUTER JOIN APPLICATIONS AS APPL ON APPLICANTS.APPLICANTS_ID = APPL.APPL_APPLICANT
LEFT OUTER JOIN APPLICATIONS_OCC ON APPL.APPLICATIONS_ID = APPLICATIONS_OCC.APPLICATIONS_ID
WHERE APPL_ACAD_LEVEL = 'UG'
order by APPL_CURRENT_STATUS_DATE desc
If that isn't what you are looking for can you please try to explain this with a bit more detail? I am not sure if the data you posted is the expected output or the sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2016 at 7:35 am
This is one option:
WITH CTE AS(
SELECTAPPLICANTS_ID,
APPLICATIONS_OCC.APPL_CURRENT_STATUS,
APPL_CURRENT_STATUS_DATE
ROW_NUMBER() OVER( PARTITION BY APPLICANTS_ID ORDER BY APPL_CURRENT_STATUS_DATE DESC) rn
FROM APPLICANTS
LEFT JOIN APPLICATIONS AS APPL ON APPLICANTS.APPLICANTS_ID = APPL.APPL_APPLICANT
LEFT JOIN APPLICATIONS_OCC ON APPL.APPLICATIONS_ID = APPLICATIONS_OCC.APPLICATIONS_ID
WHERE APPL_ACAD_LEVEL = 'UG'
)
SELECT APPLICANTS_ID,
APPL_CURRENT_STATUS,
APPL_CURRENT_STATUS_DATE
FROM CTE
WHERE rn = 1;
July 27, 2016 at 7:37 am
Hi, Thanks for responding so quickly. I tried using top 1 however it didn't return the results expected. I've attached a *.png file to show what the data in the table looks like. What I'm trying to do is return the APPL_CURRENT_STATUS based on the most recent APPL_CURRENT_STATUS_DATE. A person could have multiple rows.
so, the results should be:
1643399 AT
1661418 AT
1691213 MS
July 27, 2016 at 7:38 am
This is another option:
SELECTAPPLICANTS_ID,
APPL_CURRENT_STATUS,
APPL_CURRENT_STATUS_DATE
FROM APPLICANTS
OUTER APPLY( SELECT TOP 1 *
FROM APPLICATIONS AS APPL
LEFT
JOIN APPLICATIONS_OCC ON APPL.APPLICATIONS_ID = APPLICATIONS_OCC.APPLICATIONS_ID
WHERE APPLICANTS.APPLICANTS_ID = APPL.APPL_APPLICANT
ORDER BY APPL_CURRENT_STATUS_DATE DESC) AP
WHERE APPL_ACAD_LEVEL = 'UG';
July 27, 2016 at 7:42 am
Something tells me you were trying something like this:
DECLARE @myTable TABLE (Applicants_ID INT, APPL_CURRENT_STATUS VARCHAR(2), APPL_CURRENT_STATUS_DATE DATETIME)
INSERT INTO @myTable
VALUES (1111111, 'RF', '2014-07-10 00:00:00.000'), (1111111, 'RW', '2012-06-01 00:00:00.000'), (1111111, 'AT', '2015-05-06 00:00:00.000')
SELECT
Applicants_ID,
APPL_CURRENT_STATUS,
MAX(APPL_CURRENT_STATUS_DATE) AS APPL_CURRENT_STATUS_DATE
FROM @myTable
GROUP BY
Applicants_ID,
APPL_CURRENT_STATUS
Which doesn't seem to be doing anything because of they way it's grouped. If you did it like this instead you then get a hint at what's going on.
SELECT
Applicants_ID,
MAX(APPL_CURRENT_STATUS_DATE) AS APPL_CURRENT_STATUS_DATE
FROM @myTable
GROUP BY
Applicants_ID
But what good is that when you actually want the status too? Well you could use that result to do something like this.
DECLARE @myTable TABLE (Applicants_ID INT, APPL_CURRENT_STATUS VARCHAR(2), APPL_CURRENT_STATUS_DATE DATETIME)
INSERT INTO @myTable
VALUES (1111111, 'RF', '2014-07-10 00:00:00.000'), (1111111, 'RW', '2012-06-01 00:00:00.000'), (1111111, 'AT', '2015-05-06 00:00:00.000')
SELECT
m.*
FROM @myTable m
JOIN (
SELECT
Applicants_ID,
MAX(APPL_CURRENT_STATUS_DATE) AS APPL_CURRENT_STATUS_DATE
FROM @myTable
GROUP BY
Applicants_ID
) x ON x.Applicants_ID = m.Applicants_ID AND x.APPL_CURRENT_STATUS_DATE = m.APPL_CURRENT_STATUS_DATE
Not 100% of course without more information.
July 27, 2016 at 8:00 am
Hi Luis, Thanks for responding so quickly. I tried using top 1 however it didn't return the results expected. I've attached a *.png file to show what the data in the table looks like. What I'm trying to do is return the APPL_CURRENT_STATUS based on the most recent APPL_CURRENT_STATUS_DATE. A person could have multiple rows.
so, the results should be:
1643399 AT
1661418 AT
1691213 MS
July 27, 2016 at 8:02 am
ajspencer (7/27/2016)
Hi Luis, Thanks for responding so quickly. I tried using top 1 however it didn't return the results expected. I've attached a *.png file to show what the data in the table looks like. What I'm trying to do is return the APPL_CURRENT_STATUS based on the most recent APPL_CURRENT_STATUS_DATE. A person could have multiple rows.so, the results should be:
1643399 AT
1661418 AT
1691213 MS
Instead of us guessing what you want can you post the details we need to really be able to help? Here is a great explanation of what would make this a lot easier.
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2016 at 8:09 am
ajspencer (7/27/2016)
Hi Luis, Thanks for responding so quickly. I tried using top 1 however it didn't return the results expected. I've attached a *.png file to show what the data in the table looks like. What I'm trying to do is return the APPL_CURRENT_STATUS based on the most recent APPL_CURRENT_STATUS_DATE. A person could have multiple rows.so, the results should be:
1643399 AT
1661418 AT
1691213 MS
Did you look at my example? It gives you exactly what you want. Here it is again with your test data...
DECLARE @myTable TABLE (Applicants_ID INT, APPL_CURRENT_STATUS VARCHAR(2), APPL_CURRENT_STATUS_DATE DATETIME)
INSERT INTO @myTable
VALUES
(1643399, 'RF', '2014-07-10 00:00:00.000'),
(1643399, 'AT', '2015-05-06 00:00:00.000'),
(1643399, 'RW', '2012-06-01 00:00:00.000'),
(1661418, 'AT', '2014-11-12 00:00:00.000'),
(1661418, 'CA', '2013-04-30 00:00:00.000'),
(1661418, 'AT', '2015-05-06 00:00:00.000'),
(1691213, 'MS', '2015-06-08 00:00:00.000'),
(1691213, 'CA', '2014-05-29 00:00:00.000')
SELECT
m.Applicants_ID,
m.APPL_CURRENT_STATUS
FROM @myTable m
JOIN (
SELECT
Applicants_ID,
MAX(APPL_CURRENT_STATUS_DATE) AS APPL_CURRENT_STATUS_DATE
FROM @myTable
GROUP BY
Applicants_ID
) x ON x.Applicants_ID = m.Applicants_ID AND x.APPL_CURRENT_STATUS_DATE = m.APPL_CURRENT_STATUS_DATE
My example also showed you how you can post DDL for us so that you can get your answer faster.
July 27, 2016 at 8:19 am
Thank you and I will make sure to reply better next time.
July 27, 2016 at 8:32 am
I haven't seen anyone mention LAST_VALUE, yet.
SELECT
mt.Applicants_ID,
mt.APPL_CURRENT_STATUS,
mt.APPL_CURRENT_STATUS_DATE,
LAST_VALUE(mt.APPL_CURRENT_STATUS) OVER(PARTITION BY mt.Applicants_ID ORDER BY mt.APPL_CURRENT_STATUS_DATE ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv
FROM #myTable mt
I use ROW_NUMBER() if I want to limit the output to the last row and LAST_VALUE if I want to spread the value across multiple rows.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply