SELECT Ship,
COALESCE(MAX(CASE WHEN Status='POD' THEN date END),MAX(CASE WHEN Status='REC' THEN date END))
FROM Table
GROUP BY Ship

SET DATEFORMAT DMY

DECLARE @foo TABLE (
	[Ship] INT
	, [Status] CHAR(3)
	, [Date] DATETIME

	PRIMARY KEY ([Ship], [Status])
	)

INSERT @foo ([Ship], [Status], [Date])
      SELECT 1, 'PUC', '1/1/2010'
UNION SELECT 1, 'DEL', '1/2/2010'
UNION SELECT 1, 'POD', '1/2/2010'
UNION SELECT 1, 'REC', '1/2/2010'

UNION SELECT 2, 'PUC', '1/3/2010'
UNION SELECT 2, 'DEL', '1/4/2010'
UNION SELECT 2, 'REC', '1/4/2010'


SELECT * FROM @foo

SELECT
	[Ship]
	, [Date]
FROM
	(
	SELECT
		[Ship] AS [Ship]
		, [Date] AS [Date]
		, ROW_NUMBER() OVER ( PARTITION BY [Ship] ORDER BY [Status] ASC) AS [Pos]
	FROM
		@foo
	WHERE
		[status] IN ('POD', 'REC')
	)
	f
WHERE
	f.[pos] = 1

quote:

---

Originally posted by TPie9

One more thing I forgot to include. What if Ship 1 has 2 POD status dates and I want the earliest date?

Ship--Status--Date

1--PUC--1/1/2010
1--DEL--1/2/2010
1--POD--1/2/2010
1--REC--1/2/2010
1--POD--1/10/2010

---

SELECT Ship,
COALESCE(MIN(CASE WHEN Status='POD' THEN date END),MAX(CASE WHEN Status='REC' THEN date END))
FROM Table
GROUP BY Ship

quote:

---

Originally posted by TPie9

How would I pull the info just for status POD? When I run the query I get all the status' showing up, but only the date associated with status POD and all I want to return is one line with statu POD and the date associated with it.

---

quote:

---

Originally posted by TPie9

I must be doing something wrong because I am using your query and I'm getting all the status' per ship, but only the date associated with POD when present.

---

SET DATEFORMAT DMY

DECLARE @foo TABLE (
	[Ship] INT
	, [Status] CHAR(3)
	, [Date] DATETIME

	PRIMARY KEY ([Ship], [Status], [Date])
	)

INSERT @foo ([Ship], [Status], [Date])
      SELECT 1, 'PUC', '1/1/2010'
UNION SELECT 1, 'DEL', '1/2/2010'
UNION SELECT 1, 'POD', '1/2/2010'
UNION SELECT 1, 'REC', '1/2/2010'
UNION SELECT 1, 'POD', '1/10/2010'

UNION SELECT 2, 'PUC', '1/3/2010'
UNION SELECT 2, 'DEL', '1/4/2010'
UNION SELECT 2, 'REC', '1/4/2010'

-- Raw Data
SELECT * FROM @foo

-- Row_Number Method
SELECT
	[Ship]
	, [Date]
FROM
	(
	SELECT
		[Ship] AS [Ship]
		, [Date] AS [Date]
		, ROW_NUMBER() OVER ( PARTITION BY [Ship] ORDER BY [Status] ASC, [Date] ASC) AS [Pos]
	FROM
		@foo
	WHERE
		[status] IN ('POD', 'REC')
	)
	f
WHERE
	f.[pos] = 1

-- COALESCE Method
SELECT Ship,
COALESCE(MIN(CASE WHEN Status='POD' THEN date END),MAX(CASE WHEN Status='REC' THEN date END))
FROM @foo
GROUP BY Ship

Ship	Date
1	2010-02-01 00:00:00.000
2	2010-04-01 00:00:00.000

quote:

---

Originally posted by TPie9

Here's the query:
SELECT h1.hawbnum, s1.stsgroupcode,
COALESCE(MIN(CASE WHEN s1.stsgroupcode='DST' THEN (convert (varchar(10), s1.stsdatetime, 101)) END),
MIN(CASE WHEN s1.stsgroupcode='REC' THEN (convert (varchar(10), s1.stsdatetime, 101)) END)) as Date
FROM hawb as h1 left join statushistory as s1 on h1.hawbnum=s1.docnumlong
where h1.hawbnum=' 11796389'
GROUP BY h1.hawbnum, s1.stsgroupcode

The results:
11796389 NULL
11796389 AMB NULL
11796389 COB NULL
11796389 DST 11/30/2009
11796389 OCN NULL
11796389 OFD NULL
11796389 POD NULL
11796389 PUC NULL
11796389 REC 12/08/2009

---

quote:

---

Originally posted by TPie9

I just posted my exact query and results.

---

quote:

---

Originally posted by TPie9

Yes using DST was intentional. How do I know what status the date is for then if I don't select the status in the query?

---