Set flag for a set of records - 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

General SQL Server Forums

New to SQL Server Programming

Set flag for a set of records

Author

Topic

Bigben12
Starting Member

2 Posts

Posted - 2012-01-26 : 12:03:55

Hi
I have a table that stores requests raised by employees as below

name Request_ID date status
ABC 112 05-Dec-11 rejected
ABC 786 06-Dec-11 approved
ABC 987 07-Dec-11 rejected
ABC 119 08-Dec-11 approved
MNP 221 09-Nov-11 rejected
MNP 666 10-Nov-11 approved
MNP 221 11-Nov-11 rejected
MNP 999 12-Nov-11 approved
RST 99 23-Dec-11 rejected
RST 101 24-Dec-11 approved
RST 876 25-Dec-11 rejected
RST 127 26-Dec-11 approved

I need to check the status of the latest request of each employee and set the flag of all his requests as -
‘A’ if the latest request status is ‘approved’
‘B’ if the latest request status is ‘rejected’

name Request_ID date status Flag
ABC 112 05-Dec-11 rejected A
ABC 786 06-Dec-11 approved A
ABC 987 07-Dec-11 rejected A
ABC 119 08-Dec-11 approved A
MNP 221 09-Nov-11 rejected B
MNP 666 10-Nov-11 approved B
MNP 221 11-Nov-11 approved B
MNP 999 12-Nov-11 rejected B
RST 99 23-Dec-11 approved A
RST 101 24-Dec-11 approved A
RST 876 25-Dec-11 rejected A
RST 127 26-Dec-11 approved A

Could you please help me in framing this SQL. Do we need to use any analytical function?

Regards
-Bigben

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-26 : 12:40:00

[code]CASE <Yourcolumn> WHEN 'rejected' THEN 'B' WHEN 'approved' THEN 'A' ELSE 'SOL' AS <whateveryouwanttocallit>[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 13:24:08

you need to use like below


;With Temp_CTE
AS
(
SELECT ...,ROW_NUMBER() OVER(PARTITION BY name ORDER BY date DESC) AS Rn,....
FROM your current query
)

SELECT l.*,
CASE m.status WHEN 'rejected' THEN 'B' WHEN 'approved' THEN 'A' END AS Flag
FROM Temp_CTE l
CROSS APPLY (SELECT status 
             FROM Temp_CTE
             WHERE name = l.name 
             AND Rn=1
             )m

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-26 : 13:37:15

You and your cross apply

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-26 : 13:57:53

Don,

I believe he wants ALL of an employees records modified based on the last date

So to find the last date for the employee

SELECT * FROM myTable o
WHERE Exists (
SELECT * FROM myTable i
WHERE o.name = i.name
GROUP BY iname
HAVING o.date = MAX(i.date))

With that you can formulate an UPDATE statement

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Bigben12
Starting Member

2 Posts

Posted - 2012-01-28 : 05:25:55

Thanks Visakh ! It was helpful

Regards
-Bigben12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-28 : 17:48:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-29 : 18:52:01

<slaps forehead>water closet</slaps forehead>

WHY not TEACH them Fundamentals?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources