July 26, 2012 at 4:50 am
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:
Eid status edate
101 cp 2012-07-30 00:00:00.000
101 cp 2012-07-28 00:00:00.000
101 cp 2012-07-31 00:00:00.000
102 cp 2012-07-25 00:00:00.000
102 op 2012-07-22 00:00:00.000
102 cp 2012-07-28 00:00:00.000
Desired Output:
Eid status Edate
101 Cp 2012-07-31 00:00:00.000
102 Cp 2012-07-28 00:00:00.000
102 Op 2012-07-22 00:00:00.000
July 26, 2012 at 5:03 am
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:
Eid status edate
101 cp 2012-07-30 00:00:00.000
101 cp 2012-07-28 00:00:00.000
101 cp 2012-07-31 00:00:00.000
102 cp 2012-07-25 00:00:00.000
102 op 2012-07-22 00:00:00.000
102 cp 2012-07-28 00:00:00.000
Desired Output:
Eid status Edate
101 Cp 2012-07-31 00:00:00.000
102 Cp 2012-07-28 00:00:00.000
102 Op 2012-07-22 00:00:00.000
Try this:
SELECT Eid ,
Status ,
MAX(edate)
FROM #tablea
GROUP BY Eid ,
Status
July 26, 2012 at 5:03 am
Write function as
CREATE FUNCTION InitCap (
@string varchar(255)
)
RETURNS varchar(255) AS
BEGIN
RETURN upper(left(@string, 1)) + right(@string, len(@string) - 1)
END
and then write query as
SELECT
InitCap(Left(Status, CHARINDEX('.', user_name, 1) - 1)) as First_Name,
max(date)
FROM tblCustomers
ORDER BY Last_Name
July 26, 2012 at 5:19 am
Please do not post duplicate threads.
http://www.sqlservercentral.com/Forums/Topic1335685-145-1.aspx?Update=1
amitsingh308 (7/26/2012)
Write function as
CREATE FUNCTION InitCap (@string varchar(255)
)
RETURNS varchar(255) AS
BEGIN
RETURN upper(left(@string, 1)) + right(@string, len(@string) - 1)
END
and then write query as
SELECTInitCap(Left(Status, CHARINDEX('.', user_name, 1) - 1)) as First_Name,
max(date)
FROM tblCustomers
ORDER BY Last_Name
I am not sure how this function solves OPs question.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply