Max Date Select not working properly

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

Max Date Select not working properly

Author

Topic

Damian39
Starting Member

33 Posts

Posted - 2014-12-17 : 11:20:38

Hello all,
I'm trying to retrieve the max date from one of the temp tables within my query, but instead of retrieving the max date I'm retrieving multiple dates within the date range I've assigned. The code I am using is



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

select T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON, T.TRAN_AMT_MON 
INTO #TMP1C
from Vantage.dbo.MON_TRAN_BASE AS T (nolock) 

where T.TRAN_CDE_MON = '271A'
AND T.PRIN_MON IN ('6000','7500')
AND T.TRAN_DTE_MON BETWEEN '2014-10-01 00:00:00' AND GETDATE()
AND T.SUB_ACCT_NO_MON = '8495600440611731'
--AND T.TRAN_DTE_MON = (SELECT MAX(TRAN_DTE_MON) AS TRAN_DTE_MON FROM Vantage.dbo.MON_TRAN_BASE AS E 
--				  WHERE E.TRAN_DTE_MON = T.TRAN_DTE_MON)

--GROUP BY T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON, T.TRAN_AMT_MON 

--SELECT DISTINCT * FROM #TMP1C

select A.PRIN_MON, A.AGNT_MON, A.SUB_ACCT_NO_MON, A.TRAN_DTE_MON AS PAY_DATE, A.TRAN_CDE_MON AS PAY_CODE,
A.TRAN_AMT_MON AS PAY_AMT, B.TRAN_CDE_MON AS REF_CODE, B.TRAN_DTE_MON AS REF_DATE, B.TRAN_AMT_MON AS REF_AMT

INTO #TMP2C


from #TMP1C AS A (NOLOCK) INNER JOIN Vantage.dbo.MON_TRAN_BASE AS B (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

where B.TRAN_CDE_MON = '287'
AND B.PRIN_MON IN ('6000','7500')
AND B.TRAN_DTE_MON BETWEEN '2014-10-01 00:00:00' AND GETDATE()
AND A.TRAN_DTE_MON = (SELECT MAX(TRAN_DTE_MON) AS TRAN_DTE_MON FROM Vantage.dbo.MON_TRAN_BASE AS E 
				  WHERE E.TRAN_DTE_MON = A.TRAN_DTE_MON)

GROUP BY A.PRIN_MON, A.AGNT_MON, A.SUB_ACCT_NO_MON, A.TRAN_DTE_MON, A.TRAN_CDE_MON,
A.TRAN_AMT_MON, B.TRAN_CDE_MON, B.TRAN_DTE_MON, B.TRAN_AMT_MON

--SELECT * FROM #TMP2C


SELECT		 C.PRIN_MON
			,C.AGNT_MON
			,C.SUB_ACCT_NO_MON
			,HI.CUST_ACCT_NO_OHI
			,S.RES_NAME_SBB
			,H.ADDR1_HSE
			,H.RES_CITY_HSE
			,H.POSTAL_CDE_HSE
			,C.PAY_DATE
			,C.PAY_CODE
			,C.PAY_AMT
			,S.CUR_BAL_SBB
			,C.REF_DATE
			,C.REF_CODE
			,C.REF_AMT			
			,HI.DISCO_DTE_OHI
			,S.EXT_STAT_SBB
			--,S.EXT_STAT_SBB
			,S.CONNECT_DTE_SBB
			
			--,DATEDIFF (DD,A.TRAN_DTE_MON,HI.DISCO_DTE_OHI) AS DATE_DIFF			
			
INTO #TMP3C			

FROM #TMP2C AS C (NOLOCK), Vantage.dbo.SBB_BASE AS S (NOLOCK),
Vantage.dbo.HSE_BASE AS H (NOLOCK),Vantage.dbo.OHI_HIST_ITEM AS HI (NOLOCK)

WHERE	C.PRIN_MON = S.PRIN_SBB
AND		C.SUB_ACCT_NO_MON = S.SUB_ACCT_NO_SBB
AND		S.HSE_KEY_SBB = H.HSE_KEY_HSE
AND		S.SUB_ACCT_NO_SBB = HI.SUB_ACCT_NO_OHI
AND S.EXT_STAT_SBB IN ('C','E','Z')
AND HI.ITEM_STATUS_OHI IN ('C','O','P','B')
--AND HI.DISCO_DTE_OHI BETWEEN '2014-07-21' AND GETDATE()
AND C.REF_DATE > HI.DISCO_DTE_OHI
AND		HI.DISCO_DTE_OHI = (SELECT MAX(DISCO_DTE_OHI) AS DISCO_DTE_OHI FROM
		Vantage.dbo.OHI_HIST_ITEM AS B WHERE B.SUB_ACCT_NO_OHI = HI.SUB_ACCT_NO_OHI)

AND C.PAY_DATE = (SELECT MAX(PAY_DATE) AS PAY_DATE FROM #TMP2C AS E 
				  WHERE E.PAY_DATE = C.PAY_DATE)
--AND S.SUB_ACCT_NO_SBB = '8495600440611731'

GROUP BY	 C.PRIN_MON
			,C.AGNT_MON
			,C.SUB_ACCT_NO_MON
			,HI.CUST_ACCT_NO_OHI
			,S.RES_NAME_SBB
			,H.ADDR1_HSE
			,H.RES_CITY_HSE
			,H.POSTAL_CDE_HSE
			,C.PAY_DATE
			,C.PAY_CODE
			,C.PAY_AMT
			,S.CUR_BAL_SBB
			,C.REF_DATE
			,C.REF_CODE
			,C.REF_AMT	
			,HI.DISCO_DTE_OHI
			,S.EXT_STAT_SBB
			,S.CONNECT_DTE_SBB
			
SELECT DISTINCT F.PRIN_MON
			,F.AGNT_MON
			,F.SUB_ACCT_NO_MON
			,F.CUST_ACCT_NO_OHI
			,F.RES_NAME_SBB
			,F.ADDR1_HSE
			,F.RES_CITY_HSE
			,F.POSTAL_CDE_HSE
			,F.PAY_DATE
			,F.PAY_CODE
			,F.PAY_AMT
			,F.CUR_BAL_SBB
			,F.REF_DATE
			,F.REF_CODE
			,F.REF_AMT			
			,F.DISCO_DTE_OHI
			,CASE WHEN F.EXT_STAT_SBB = 'C' THEN 'VOL_DISCO'
				  WHEN F.EXT_STAT_SBB = 'E' THEN 'NON_PAY_DISCO'
				  WHEN F.EXT_STAT_SBB = 'Z' THEN 'CHARGED_OFF'
			 ELSE ' ' END AS 'CUSTOMER_STATUS'
			--,S.EXT_STAT_SBB
			,F.CONNECT_DTE_SBB
			
			
 FROM #TMP3C AS F (NOLOCK)

GROUP BY 
F.PRIN_MON
			,F.AGNT_MON
			,F.SUB_ACCT_NO_MON
			,F.CUST_ACCT_NO_OHI
			,F.RES_NAME_SBB
			,F.ADDR1_HSE
			,F.RES_CITY_HSE
			,F.POSTAL_CDE_HSE
			,F.PAY_DATE
			,F.PAY_CODE
			,F.PAY_AMT
			,F.CUR_BAL_SBB
			,F.REF_DATE
			,F.REF_CODE
			,F.REF_AMT			
			,F.DISCO_DTE_OHI
			,F.EXT_STAT_SBB
			--,S.EXT_STAT_SBB
			,F.CONNECT_DTE_SBB
			
			
ORDER BY F.PAY_DATE

Usually this works. I can't seem to figure out why my sub-query is pulling multiple dates instead of the most recent date. I've even tried placing the sub-query in different places within the query, and I've also tried placing sub-queries within multiple temp tables. Lastly I tried to use Order By to see if that would do the trick, but it hasn't. If anyone can shed some light on this I would very much appreciate the help. Thanks all!

Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 11:38:52

please post some sample data, the output you are getting and the output you want

Damian39
Starting Member

33 Posts

Posted - 2014-12-17 : 11:49:57

quote:
Originally posted by gbritton

please post some sample data, the output you are getting and the output you want

Hi gbritton,
This is the data I am getting back:

6000 440 8495600440611731 1203513859768 VAZQUEZ,GASTON 151 SE 15TH RD APT 1902 MIAMI 33129-1278 2014-10-07 00:00:00 271A -130.00 0.00 2014-11-05 00:00:00 287 131.51 2014-10-30 00:00:00 VOL_DISCO 2004-06-24 00:00:00
6000 440 8495600440611731 1203513859768 VAZQUEZ,GASTON 151 SE 15TH RD APT 1902 MIAMI 33129-1278 2014-10-10 00:00:00 271A -50.00 0.00 2014-11-05 00:00:00 287 131.51 2014-10-30 00:00:00 VOL_DISCO 2004-06-24 00:00:00
6000 440 8495600440611731 1203513859768 VAZQUEZ,GASTON 151 SE 15TH RD APT 1902 MIAMI 33129-1278 2014-10-23 00:00:00 271A -21.67 0.00 2014-11-05 00:00:00 287 131.51 2014-10-30 00:00:00 VOL_DISCO 2004-06-24 00:00:00

As you can see I'm getting three dates back for October, but all I want is the last date a payment was made in October which is 10/23/2014.

6000 440 8495600440611731 1203513859768 VAZQUEZ,GASTON 151 SE 15TH RD APT 1902 MIAMI 33129-1278 2014-10-23 00:00:00 271A -21.67 0.00 2014-11-05 00:00:00 287 131.51 2014-10-30 00:00:00 VOL_DISCO 2004-06-24 00:00:00

Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 13:09:56

Still need some sample input data, posted as INSERT INTO statements

Damian39
Starting Member

33 Posts

Posted - 2014-12-17 : 13:58:06

quote:
Originally posted by gbritton

Still need some sample input data, posted as INSERT INTO statements

I'm not sure I follow. I'm not using insert into for my temp tables, just into.

[code]
select T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON, T.TRAN_AMT_MON
INTO #TMP1C
from Vantage.dbo.MON_TRAN_BASE AS T (nolock)

where T.TRAN_CDE_MON = '271A'
AND T.PRIN_MON IN ('6000','7500')
AND T.TRAN_DTE_MON BETWEEN '2014-10-01 00:00:00' AND GETDATE()
AND T.SUB_ACCT_NO_MON = '8495600440611731'
--AND T.TRAN_DTE_MON = (SELECT MAX(TRAN_DTE_MON) AS TRAN_DTE_MON FROM Vantage.dbo.MON_TRAN_BASE AS E
-- WHERE E.TRAN_DTE_MON = T.TRAN_DTE_MON)

--GROUP BY T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON, T.TRAN_AMT_MON

--SELECT DISTINCT * FROM #TMP1C

Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 14:07:52

Well. I don't have the Vantage.dbo.MON_TRAN_BASE table, so I can't do that. I need to see some rows of source data to recreate your scenario for testing.

Damian39
Starting Member

33 Posts

Posted - 2014-12-17 : 15:12:37

quote:
Originally posted by gbritton

Well. I don't have the Vantage.dbo.MON_TRAN_BASE table, so I can't do that. I need to see some rows of source data to recreate your scenario for testing.

Please bear with me. The temp query that I sent you is my source table. Not sure how I would give you some rows of source data. The results that I posted earlier is the data I am getting back, and as you can see, it's giving me all of the payment dates this subscriber had for October instead of giving me the most current date. I've tried using a sub-query for a max date, and I've also tried using the order by to see if that would give me the most current date, but nothing seems to be working.

Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 15:18:49

post the source data like this:

INSERT INTO Vantage.dbo.MON_TRAN_BASE (T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON, T.TRAN_AMT_MON)
VALUES
(data for first row),
(data for second row),
...

Then post the expected output using that data as input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources