Reg: Query

Question

Reg: Query

narendra.babu57

Old Hand

Points: 386
More actions
July 26, 2012 at 4:47 am

#98725

Hi All,
In My Table A , Eid 101 having status all are "cp" then i need latest Edate, but in case 102 it is having different status so i need for Cp status latest Edate and Op as it is date
Table A:
Eidstatusedate
101cp2012-07-30 00:00:00.000
101cp2012-07-28 00:00:00.000
101cp2012-07-31 00:00:00.000
102cp2012-07-25 00:00:00.000
102op2012-07-22 00:00:00.000
102cp2012-07-28 00:00:00.000
Desired Output:
Eid statusEdate
101Cp2012-07-31 00:00:00.000
102Cp2012-07-28 00:00:00.000
102Op2012-07-22 00:00:00.000

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

narendra.babu57 (7/26/2012)
Hi All,
In My Table A , Eid 101 having status all are "cp" then i need latest Edate, but in case 102 it is having different status so i need for Cp status latest Edate and Op as it is date
Table A:
Eidstatusedate
101cp2012-07-30 00:00:00.000
101cp2012-07-28 00:00:00.000
101cp2012-07-31 00:00:00.000
102cp2012-07-25 00:00:00.000
102op2012-07-22 00:00:00.000
102cp2012-07-28 00:00:00.000
Desired Output:
Eid statusEdate
101Cp2012-07-31 00:00:00.000
102Cp2012-07-28 00:00:00.000
102Op2012-07-22 00:00:00.000

Hope this helps:

SELECT EID,[Status],MAX(EDate) AS EDate FROM TheTable

WHERE [Status]='cp' GROUP BY EID,[Status]

UNION ALL

SELECT EID,[Status],EDate FROM TheTable WHERE [Status]='op'

P.S.: Change the table name in the query as your table's name.

Sujeet Singh

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could simply drop the conditions and the union.

SELECT EID,

[Status],

MAX(EDate) AS EDate

FROM TheTable

WHERE [Status]='cp'

GROUP BY EID,[Status]

ORDER BY EID,[Status]

It should give the same output

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

bala.sevva SSC Enthusiast Points: 194 More actions · Answer 3

Luis,

I think your query is right if we remove the where clause as we need to show status 'OP' as well in the output.

SELECT EID,

[Status],

MAX(EDate) AS EDate

FROM TheTable

GROUP BY EID,[Status]

ORDER BY EID,[Status]

- Bala