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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -