Second Max function not working

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

Second Max function not working

Author

Topic

Damian39
Starting Member

33 Posts

Posted - 2015-01-21 : 14:55:44

I've been trying to get the max disconnect date within my query, but my issue is this is the second column in which I am trying to only retrieve the maximum date for. The initial max date I was looking for was the max date on refunds issued to customers. I've managed to get that to work, but now I am retrieving multiple dates for disconnection. I've tried a few different things, but nothing seems to work. Here is the code I have thus far:


IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
IF OBJECT_ID('TEMPDB..#TMP3C') IS NOT NULL DROP TABLE #TMP3C
IF OBJECT_ID('TEMPDB..#TMP4C') IS NOT NULL DROP TABLE #TMP4C


SELECT DISTINCT T.PRIN_MON,
                        T.AGNT_MON,
                        T.SUB_ACCT_NO_MON,
                        S.CUST_ACCT_NO_SBB,
                        S.HSE_KEY_SBB,
                        S.EXT_STAT_SBB,
                        S.DISCO_RSN_SBB,
                        T.TRAN_DTE_MON,
                        T.TRAN_CDE_MON,
                        T.TRAN_AMT_MON 
INTO  #TMP1C
FROM  Vantage.dbo.MON_TRAN_BASE AS T (NOLOCK) LEFT JOIN Vantage.dbo.SBB_BASE AS S (NOLOCK)
ON          T.PRIN_MON=S.PRIN_SBB AND
            T.AGNT_MON=S.AGNT_SBB AND
            T.SUB_ACCT_NO_MON=S.SUB_ACCT_NO_SBB AND S.PRIN_SBB IN ('6000','7500')

WHERE T.PRIN_MON IN ('6000','7500') AND
            T.TRAN_CDE_MON IN ('287') AND --,'257') 
            T.TRAN_DTE_MON BETWEEN '2014-11-26 00:00:00' AND '2014-12-30 00:00:00' AND
            T.SUB_ACCT_NO_MON = '8495600010018713'


---MAX DATE---
SELECT            PRIN_MON,AGNT_MON,
                  SUB_ACCT_NO_MON,
                  CUST_ACCT_NO_SBB,
                  HSE_KEY_SBB,
                  EXT_STAT_SBB,
                  DISCO_RSN_SBB,
                  TRAN_CDE_MON,
                  MAX(TRAN_DTE_MON) AS MaxDate
INTO        #TMP2C
FROM        #TMP1C WITH(NOLOCK)
GROUP BY      PRIN_MON,AGNT_MON,SUB_ACCT_NO_MON,CUST_ACCT_NO_SBB,HSE_KEY_SBB,EXT_STAT_SBB,DISCO_RSN_SBB,TRAN_CDE_MON


SELECT            A.PRIN_MON,
                  A.AGNT_MON,
                  A.SUB_ACCT_NO_MON,
                  A.CUST_ACCT_NO_SBB,
                  A.HSE_KEY_SBB,
                  A.EXT_STAT_SBB,
                  A.DISCO_RSN_SBB,
                  B.[MaxDate],
                  A.TRAN_CDE_MON,
                  A.TRAN_AMT_MON

INTO        #TMP3C

FROM        #TMP1C A WITH(NOLOCK) INNER JOIN #TMP2C B WITH(NOLOCK)
ON                A.PRIN_MON=B.PRIN_MON AND
                  A.AGNT_MON=B.AGNT_MON AND
                  A.SUB_ACCT_NO_MON=B.SUB_ACCT_NO_MON AND
                  A.TRAN_DTE_MON=B.[MaxDate]


SELECT DISTINCT			A.PRIN_MON, 
                        A.AGNT_MON,
                        A.SUB_ACCT_NO_MON,
                        A.CUST_ACCT_NO_SBB,
                        A.HSE_KEY_SBB,
                        --A.EXT_STAT_SBB,
                        A.DISCO_RSN_SBB,
                        --OC.CUST_ACCT_NO_OCR,
                        C.RES_NAME_SBB,
                        H.ADDR1_HSE,
                        H.RES_CITY_HSE,
                        H.RES_STATE_HSE,
                        H.POSTAL_CDE_HSE,
                        C.CUR_BAL_SBB AS CURRENT_BALANCE,
                        C.LS_PAY_DTE_SBB AS LAST_PYMT_DATE,
                        C.LS_PAY_AMT_SBB AS LAST_PYMT_AMOUNT,
                        A.[MaxDate] AS REFUND_DATE,
                        A.TRAN_CDE_MON AS REFUND_CODE,
                        A.TRAN_AMT_MON AS REFUND_AMOUNT,
                        MAX(OC.LS_CHG_DTE_OCR) AS DISCO_DATE,
                        A.EXT_STAT_SBB,
                        C.CONNECT_DTE_SBB

INTO #TMP4C

FROM        #TMP3C AS A (NOLOCK) INNER JOIN Vantage.dbo.SBB_BASE AS C (NOLOCK)
ON          A.PRIN_MON=C.PRIN_SBB AND
            A.AGNT_MON=C.AGNT_SBB AND
            A.SUB_ACCT_NO_MON=C.SUB_ACCT_NO_SBB AND C.PRIN_SBB IN ('6000','7500') AND A.EXT_STAT_SBB IN ('C','E','Z')

            LEFT JOIN Vantage.dbo.HSE_BASE AS H (NOLOCK)
ON          C.PRIN_SBB=H.PRIN_HSE AND
            C.AGNT_SBB=H.AGNT_HSE AND
            C.HSE_KEY_SBB=H.HSE_KEY_HSE AND H.PRIN_HSE IN ('6000','7500')


            INNER JOIN Vantage.dbo.OCR_ORDER_COMP AS OC (NOLOCK)
ON          A.PRIN_MON=OC.PRIN_OCR AND
            A.AGNT_MON=OC.AGNT_OCR AND
            A.CUST_ACCT_NO_SBB=OC.CUST_ACCT_NO_OCR AND
            A.DISCO_RSN_SBB=SUBSTRING(OC.ORD_RSN_OCR,1,2) AND
            A.[MaxDate] > OC.LS_CHG_DTE_OCR AND OC.PRIN_OCR IN ('6000','7500') AND 
            OC.ORD_STAT_OCR = 'C' --AND
 
GROUP BY A.PRIN_MON, 
                        A.AGNT_MON,
                        A.SUB_ACCT_NO_MON,
                        A.CUST_ACCT_NO_SBB,
                        A.HSE_KEY_SBB,
                        --A.EXT_STAT_SBB,
                        A.DISCO_RSN_SBB,
                        --OC.CUST_ACCT_NO_OCR,
                        C.RES_NAME_SBB,
                        H.ADDR1_HSE,
                        H.RES_CITY_HSE,
                        H.RES_STATE_HSE,
                        H.POSTAL_CDE_HSE,
                        C.CUR_BAL_SBB,
                        C.LS_PAY_DTE_SBB,
                        C.LS_PAY_AMT_SBB,
                        A.[MaxDate],
                        A.TRAN_CDE_MON,
                        A.TRAN_AMT_MON,
                        OC.LS_CHG_DTE_OCR,
                        A.EXT_STAT_SBB,
                        C.CONNECT_DTE_SBB


SELECT DISTINCT * FROM #TMP4C

Here are the results I'm getting back


PRIN_MON	AGNT_MON	SUB_ACCT_NO_MON	CUST_ACCT_NO_SBB	HSE_KEY_SBB	DISCO_RSN_SBB	RES_NAME_SBB	ADDR1_HSE	RES_CITY_HSE	RES_STATE_HSE	POSTAL_CDE_HSE	 CURRENT_BALANCE 	LAST_PYMT_DATE	 LAST_PYMT_AMOUNT 	REFUND_DATE	REFUND_CODE	 REFUND_AMOUNT 	DISCO_DATE	EXT_STAT_SBB	CONNECT_DTE_SBB
6000	10	8495600010018713	1203021548804	33162141105097	NT	ANILLO,ALEJANDRA	1587 NE 180TH ST	NORTH MIAMI BEACH	FL	33162-1411	 $-   	11/1/2014 0:00	 $103.60 	12/5/2014 0:00	287	 $23.70 	8/17/2012 0:00	C	2/18/2000 0:00
6000	10	8495600010018713	1203021548804	33162141105097	NT	ANILLO,ALEJANDRA	1587 NE 180TH ST	NORTH MIAMI BEACH	FL	33162-1411	 $-   	11/1/2014 0:00	 $103.60 	12/5/2014 0:00	287	 $23.70 	1/18/2014 0:00	C	2/18/2000 0:00
6000	10	8495600010018713	1203021548804	33162141105097	NT	ANILLO,ALEJANDRA	1587 NE 180TH ST	NORTH MIAMI BEACH	FL	33162-1411	 $-   	11/1/2014 0:00	 $103.60 	12/5/2014 0:00	287	 $23.70 	2/4/2014 0:00	C	2/18/2000 0:00
6000	10	8495600010018713	1203021548804	33162141105097	NT	ANILLO,ALEJANDRA	1587 NE 180TH ST	NORTH MIAMI BEACH	FL	33162-1411	 $-   	11/1/2014 0:00	 $103.60 	12/5/2014 0:00	287	 $23.70 	2/19/2014 0:00	C	2/18/2000 0:00
6000	10	8495600010018713	1203021548804	33162141105097	NT	ANILLO,ALEJANDRA	1587 NE 180TH ST	NORTH MIAMI BEACH	FL	33162-1411	 $-   	11/1/2014 0:00	 $103.60 	12/5/2014 0:00	287	 $23.70 	3/5/2014 0:00	C	2/18/2000 0:00
6000	10	8495600010018713	1203021548804	33162141105097	NT	ANILLO,ALEJANDRA	1587 NE 180TH ST	NORTH MIAMI BEACH	FL	33162-1411	 $-   	11/1/2014 0:00	 $103.60 	12/5/2014 0:00	287	 $23.70 	11/11/2014 0:00	C	2/18/2000 0:00

I'm not sure how to proceed. Any help is greatly appreciated!

Damian

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-22 : 05:25:14

Try to achieve the same thing in subquery by using self join. in the sub query retrieve the max value

Regards
Viggneshwar A

Damian39
Starting Member

33 Posts

Posted - 2015-01-22 : 11:27:03

quote:
Originally posted by viggneshwar

Try to achieve the same thing in subquery by using self join. in the sub query retrieve the max value

Regards
Viggneshwar A

Thank you for the suggestion Viggneshwar. I believe the following code is what you're suggesting:


FROM #TMP4C AS F (NOLOCK) INNER JOIN (SELECT SUB_ACCT_NO_MON, MAX(DISCO_DATE) AS MAX_DISCO_DATE
                      FROM #TMP3C GROUP BY SUB_ACCT_NO_MON) G ON F.SUB_ACCT_NO_MON = G.SUB_ACCT_NO_MON
                      INNER JOIN #TMP3C AS H1 ON G.SUB_ACCT_NO_MON = F.SUB_ACCT_NO_MON
										AND H1.DISCO_DATE = G.MAX_DISCO_DATE

I tried adding this to my query, but I was still receiving multiple disconnect dates. Did I perhaps write it incorrectly?

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources