Earliest Date and grouping - 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

Earliest Date and grouping

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-05-07 : 14:06:39

Hi I am having trouble and I don't know why.

I have this query which I pasted below. I need to find the earlist effective date (pcsp_eff), but I need to show all of the fields below in my report like a flat file. I know ususaly when you use Max/Min you have to have a group by, would I group by everything that is in my select statement?

SELECT Distinct
dbo.pcs.pcs_id1,
dbo.pcs.pcs_lname AS [Last Name],
dbo.pcs.pcs_fname AS [First Name],
dbo.pcsp.pcsp_eff AS [Effective Date for Provider],
Min(pcsp_eff) as "earliestEffectivedate",
dbo.pcst.pcst_trm1 AS [Tracking Thru Date],
dbo.pcst.pcst_dat3 AS [Re-cred Letter Sent Date],
dbo.pcst.pcst_dat7 AS [Re-cred Complete Date],
PRO_STATE as "State",
PRO_COUNTY as "County"--, PCSP_NET

FROM dbo.pcs INNER JOIN
dbo.pcst ON dbo.pcs.pcs_id1 = dbo.pcst.pcst_id1 INNER JOIN
pcsp ON pcs.pcs_id1 = pcsp.pcsp_id1 left Join
dbo.pro ON pcs.pcs_id1 = pro.pro_id1

WHERE
(CONVERT(CHAR(10), dbo.pcst.pcst_dat3, 110) <>'03-23-1977') and
(CONVERT(CHAR(10), dbo.pcst.pcst_dat7, 110) = '03-23-1977') and
(pcsp.pcsp_prd = 'DGH') AND
--(pcsp.pcsp_id2 = '0001') and
PCS_CTL = 'I' and
pcsp_NET <> 'DACFP' and
pcs_id1 = '00004307'

Group by pcs_id1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 14:11:46

[code]SELECT Distinct
dbo.pcs.pcs_id1,
dbo.pcs.pcs_lname AS [Last Name],
dbo.pcs.pcs_fname AS [First Name],
dbo.pcsp.pcsp_eff AS [Effective Date for Provider],
t.MinDate as "earliestEffectivedate",
dbo.pcst.pcst_trm1 AS [Tracking Thru Date],
dbo.pcst.pcst_dat3 AS [Re-cred Letter Sent Date],
dbo.pcst.pcst_dat7 AS [Re-cred Complete Date],
PRO_STATE as "State",
PRO_COUNTY as "County"--, PCSP_NET

FROM dbo.pcs INNER JOIN
dbo.pcst ON dbo.pcs.pcs_id1 = dbo.pcst.pcst_id1 INNER JOIN
pcsp ON pcs.pcs_id1 = pcsp.pcsp_id1 INNER JOIN
(SELECT pcsp_id1,Min(pcsp_eff)as MinDate
FROM pcsp
GROUP BY pcsp_id1)t
ON t.pcsp_id1=pcsp.pcsp_id1 left Join
dbo.pro ON pcs.pcs_id1 = pro.pro_id1

WHERE
(CONVERT(CHAR(10), dbo.pcst.pcst_dat3, 110) <>'03-23-1977') and
(CONVERT(CHAR(10), dbo.pcst.pcst_dat7, 110) = '03-23-1977') and
(pcsp.pcsp_prd = 'DGH') AND
--(pcsp.pcsp_id2 = '0001') and
PCS_CTL = 'I' and
pcsp_NET <> 'DACFP' and
pcs_id1 = '00004307'

Group by pcs_id1[/code]

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-05-08 : 09:11:44

You are the best! It worked out perfectly for me. Thank you SO much Visakh16.

quote:

Originally posted by visakh16

SELECT Distinct 
dbo.pcs.pcs_id1, 
dbo.pcs.pcs_lname AS [Last Name], 
dbo.pcs.pcs_fname AS [First Name],
dbo.pcsp.pcsp_eff AS [Effective Date for Provider], 
t.MinDate as "earliestEffectivedate",
dbo.pcst.pcst_trm1 AS [Tracking Thru Date], 
dbo.pcst.pcst_dat3 AS [Re-cred Letter Sent Date], 
dbo.pcst.pcst_dat7 AS [Re-cred Complete Date], 
PRO_STATE as "State", 
PRO_COUNTY as "County"--, PCSP_NET

FROM dbo.pcs INNER JOIN
dbo.pcst ON dbo.pcs.pcs_id1 = dbo.pcst.pcst_id1 INNER JOIN
pcsp ON pcs.pcs_id1 = pcsp.pcsp_id1 INNER JOIN
(SELECT pcsp_id1,Min(pcsp_eff)as MinDate
FROM pcsp
GROUP BY pcsp_id1)t
ON t.pcsp_id1=pcsp.pcsp_id1  left Join 
dbo.pro ON pcs.pcs_id1 = pro.pro_id1

WHERE 
(CONVERT(CHAR(10), dbo.pcst.pcst_dat3, 110) <>'03-23-1977') and 
(CONVERT(CHAR(10), dbo.pcst.pcst_dat7, 110) = '03-23-1977') and 
(pcsp.pcsp_prd = 'DGH') AND
--(pcsp.pcsp_id2 = '0001') and
PCS_CTL = 'I' and
pcsp_NET <> 'DACFP' and
pcs_id1 = '00004307'

Group by pcs_id1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources