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

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

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

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

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

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

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

- Advertisement -