Adding two max statements - 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

Adding two max statements

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-09 : 08:55:22

I am trying to add another max statement to this query the first one is fine. I added the second one in bold and getting "incorrect syntax near the keyword 'Group' (in bold). What am I doing wrong?


Select Doc,  clm, isnull(count([Clm]),0) as pendinpsc, App_rcpdt, Dib_mvt_seq_num, Juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM,  d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
Doc, d.CLM, App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, MAX(juris_mvt_seq_num) juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, 
CASE WHEN j.ORG_ID not like 'P%' 
    THEN CASE WHEN d.org_id1 like 'S%' or d.org_id1 like 'R%' or d.org_id1 like 'P%'
        THEN 'DDS' 
        ELSE 'FO' END 
    ELSE j.ORG_ID END Location 
from (SELECT  CLM, Max(DIB_MVT_SEQ_NUM) DIB 
        FROM T2dibPend d2 
        GROUP BY CLM) as d1

JOIN T2dibPend d ON
    d1.CLM = d.CLM
    AND d1.DIB = d.DIB_MVT_SEQ_NUM
JOIN 
(SELECT  CLM, Max(juris_mvt_seq_num) juris_mvt_seq_num
        FROM T2PendJuris d3 
        GROUP BY CLM) as d4 
    GROUP BY DOC, d.CLM, d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1 and location like 'p%' 
GROUP BY A.Doc, a.clm, a.App_rcpdt, a.Dib_mvt_seq_num, a.juris_mvt_seq_num, a.Lorec4, a.Org_ID1, a.juris_mvt_typ, a.Location

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-09 : 09:15:42

Your indentation is making it hard to read. If you indented better the error would become obvious (marked in red):

Select Doc,  clm, isnull(count([Clm]),0) as pendinpsc, App_rcpdt, Dib_mvt_seq_num, Juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
	Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM,  d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
		Doc, d.CLM, App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, MAX(juris_mvt_seq_num) juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, 
		CASE WHEN j.ORG_ID not like 'P%' 
			THEN CASE WHEN d.org_id1 like 'S%' or d.org_id1 like 'R%' or d.org_id1 like 'P%'
				THEN 'DDS' 
				ELSE 'FO' 
				END 
		ELSE j.ORG_ID 
		END Location 
	from 
			(SELECT  CLM, Max(DIB_MVT_SEQ_NUM) DIB 
			FROM T2dibPend d2 
			GROUP BY CLM) as d1
	JOIN T2dibPend d 
		ON d1.CLM = d.CLM
			AND d1.DIB = d.DIB_MVT_SEQ_NUM
	JOIN 
			(SELECT  CLM, Max(juris_mvt_seq_num) juris_mvt_seq_num
			FROM T2PendJuris d3 
			GROUP BY CLM) as d4 
		ON ...
        GROUP BY DOC, d.CLM, d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
    ) as A 
WHERE RowNum = 1 
    and location like 'p%' 
GROUP BY A.Doc, a.clm, a.App_rcpdt, a.Dib_mvt_seq_num, a.juris_mvt_seq_num, a.Lorec4, a.Org_ID1, a.juris_mvt_typ, a.Location

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-09 : 09:34:51

Thanks Lumbago. I added this but getting

Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.


Select Doc,  clm, isnull(count([Clm]),0) as pendinpsc, App_rcpdt, Dib_mvt_seq_num, Juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
	Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM,  d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
		Doc, d.CLM, App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, MAX(juris_mvt_seq_num) juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, 
		CASE WHEN j.ORG_ID not like 'P%' 
			THEN CASE WHEN d.org_id1 like 'S%' or d.org_id1 like 'R%' or d.org_id1 like 'P%'
				THEN 'DDS' 
				ELSE 'FO' 
				END 
		ELSE j.ORG_ID 
		END Location 
	from 
			(SELECT  CLM, Max(DIB_MVT_SEQ_NUM) DIB 
			FROM T2dibPend d2 
			GROUP BY CLM) as d1
	JOIN T2dibPend d 
		ON d1.CLM = d.CLM
			AND d1.DIB = d.DIB_MVT_SEQ_NUM
	JOIN 
		(SELECT  CLM, Max(juris_mvt_seq_num) juris_mvt_seq_num
        FROM T2PendJuris d3
        GROUP BY CLM) as d4
       Join t2pendjuris j on 
           on d.clm = d3.clm
        GROUP BY DOC, d.CLM, d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
    ) as A 
WHERE RowNum = 1 
    and location like 'p%' 
GROUP BY A.Doc, a.clm, a.App_rcpdt, a.Dib_mvt_seq_num, a.juris_mvt_seq_num, a.Lorec4, a.Org_ID1, a.juris_mvt_typ, a.Location

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-12 : 02:06:57

I would guess you've solved this by now but if not you are still missing the ON-clause after the "...GROUP BY CLM) as d4".

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources