Help with sequel statement please. Thanks.

Sorry for dup. Please ignore the previous one. Thanks.
Hi all,
How can I return the results below. Any help would greatly appreciate. The
business rule is show below.
IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
DROP TABLE #Policy_nb
GO
CREATE TABLE #Policy_nb
(
Policy_Id INT NULL,
CurrAgent_id INT NULL,
UploadTp_id INT NULL
)
GO
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
4894, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
614, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
4710, 2)
GO
IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
DROP TABLE #HuonUpload_nb
GO
CREATE TABLE #HuonUpload_nb
(
Policy_id INT NULL,
UploadTp_Id INT NULL,
UploadStatus_dt DATETIME NULL
)
GO
INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
go
SELECT *
FROM #Policy_nb
GO
Policy_Id CurrAgent_id UploadTp_id
-- -- --
382099 4894 3
374943 614 3
376279 4710 2
SELECT *
FROM #HuonUpload_nb
GO
Policy_id UploadTp_Id UploadStatus_dt
-- -- --
382099 3 2005-10-03 11:27:00.000
382099 2 2005-10-03 11:25:00.000
382099 1 2005-10-03 11:21:00.000
382099 4 2005-09-30 09:24:00.000
382099 1 2005-09-30 09:22:00.000
382099 2 2005-09-30 09:21:00.000
382099 2 2005-09-30 09:12:00.000
382099 1 2005-09-29 23:36:00.000
382099 4 2005-09-29 09:45:00.000
382099 2 2005-09-28 13:18:00.000
374943 3 2005-10-03 11:26:00.000
374943 2 2005-10-03 10:20:00.000
374943 1 2005-10-03 10:15:00.000
376279 1 2005-09-13 14:19:00.000
376279 2 2005-09-13 14:36:00.000
-- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
--Testing... Not working...
SELECT a.CurrAgent_id,
b.Policy_id,
b.UploadTp_Id,
b.UploadStatus_dt
FROM #Policy_nb AS a
JOIN #HuonUpload_nb AS b
ON a.Policy_id = b.Policy_id
JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
MAX(UploadStatus_dt) AS 'UploadStatus_dt'
FROM #HuonUpload_nb
WHERE UploadTp_id IN (1, 2, 3)
GROUP BY Policy_id, UploadTp_id
ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
ON c.Policy_id = b.Policy_id
AND c.UploadTp_id = b.UploadTp_id
AND c.UploadStatus_dt = b.UploadStatus_dt
ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
GO
--Result want:
CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
-- -- -- --
4894 382099 3 2005-10-03 11:27:00.000
4894 382099 2 2005-10-03 11:25:00.000
4894 382099 1 2005-10-03 11:21:00.000
614 374943 3 2005-10-03 11:26:00.000
614 374943 2 2005-10-03 10:20:00.000
614 374943 1 2005-10-03 10:15:00.000There is no duplicate (unless you mean your post from 8/29).
Please consider using a newsreader, which doesn't have as many
synchronization issues as the web-based interfaces.
http://www.aspfaq.com/5007|||Try this,
SELECT p.CurrAgent_id, h.Policy_id, h.UploadTp_Id, max(h.UploadStatus_dt) AS
UploadStatus_dt
FROM HuonUpload_nb h inner join Policy_nb p on h.policy_id = p.policy_id
WHERE h.UploadTp_Id <=3 AND p.CurrAgent_id IN
(SELECT currAgent_id
FROM (SELECT PN.currAgent_id
FROM HuonUpload_nb NB INNER JOIN POLICY_NB PN ON NB.policy_ID =
PN.policy_ID
WHERE (nb.UploadTp_Id <=3)
GROUP BY pn.currAgent_id, nb.Policy_id, nb.UploadTp_id) AS X
GROUP BY currAgent_id
HAVING count(currAgent_id) = 3)
GROUP BY p.CurrAgent_id, h.Policy_id, h.UploadTp_Id
ORDER BY h.Policy_id DESC, h.UploadTP_Id DESC
Note: # were removed
Regards,
David
"Lam Nguyen" wrote:

> Sorry for dup. Please ignore the previous one. Thanks.
> Hi all,
> How can I return the results below. Any help would greatly appreciate. T
he
> business rule is show below.
>
> IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
> DROP TABLE #Policy_nb
> GO
> CREATE TABLE #Policy_nb
> (
> Policy_Id INT NULL,
> CurrAgent_id INT NULL,
> UploadTp_id INT NULL
> )
> GO
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
> 4894, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
> 614, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
> 4710, 2)
> GO
> IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
> DROP TABLE #HuonUpload_nb
> GO
> CREATE TABLE #HuonUpload_nb
> (
> Policy_id INT NULL,
> UploadTp_Id INT NULL,
> UploadStatus_dt DATETIME NULL
> )
> GO
> INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
> INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
> INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
> INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
> INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
> INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
> go
> SELECT *
> FROM #Policy_nb
> GO
> Policy_Id CurrAgent_id UploadTp_id
> -- -- --
> 382099 4894 3
> 374943 614 3
> 376279 4710 2
> SELECT *
> FROM #HuonUpload_nb
> GO
> Policy_id UploadTp_Id UploadStatus_dt
> -- -- --
> 382099 3 2005-10-03 11:27:00.000
> 382099 2 2005-10-03 11:25:00.000
> 382099 1 2005-10-03 11:21:00.000
> 382099 4 2005-09-30 09:24:00.000
> 382099 1 2005-09-30 09:22:00.000
> 382099 2 2005-09-30 09:21:00.000
> 382099 2 2005-09-30 09:12:00.000
> 382099 1 2005-09-29 23:36:00.000
> 382099 4 2005-09-29 09:45:00.000
> 382099 2 2005-09-28 13:18:00.000
> 374943 3 2005-10-03 11:26:00.000
> 374943 2 2005-10-03 10:20:00.000
> 374943 1 2005-10-03 10:15:00.000
> 376279 1 2005-09-13 14:19:00.000
> 376279 2 2005-09-13 14:36:00.000
>
> -- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
> --Testing... Not working...
> SELECT a.CurrAgent_id,
> b.Policy_id,
> b.UploadTp_Id,
> b.UploadStatus_dt
> FROM #Policy_nb AS a
> JOIN #HuonUpload_nb AS b
> ON a.Policy_id = b.Policy_id
> JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
> MAX(UploadStatus_dt) AS 'UploadStatus_dt'
> FROM #HuonUpload_nb
> WHERE UploadTp_id IN (1, 2, 3)
> GROUP BY Policy_id, UploadTp_id
> ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
> ON c.Policy_id = b.Policy_id
> AND c.UploadTp_id = b.UploadTp_id
> AND c.UploadStatus_dt = b.UploadStatus_dt
> ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
> GO
> --Result want:
> CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
> -- -- -- --
> 4894 382099 3 2005-10-03 11:27:00.000
> 4894 382099 2 2005-10-03 11:25:00.000
> 4894 382099 1 2005-10-03 11:21:00.000
> 614 374943 3 2005-10-03 11:26:00.000
> 614 374943 2 2005-10-03 10:20:00.000
> 614 374943 1 2005-10-03 10:15:00.000
>