December 10, 2008 at 7:35 am
Hi,
Currently my query returns 6 rows; it returns the first and last row for each ID_NUMBER...
But what I need is to leave out with ID_NUMBER 2 is the last row because it's MATURITY_DATE is not greater than the NEXT_PAYMENT_DATE...
Thus my query would return the first and last row for ID_NUMBER 1 and 3... and then the first row for ID_NUMBER 2...
When I change my query from an INNER JOIN to a LEFT JOIN and uncomment the WHERE CLAUSE, I get nothing back...
Thanks,
John
CREATE TABLE #TEST_JAS
(
[ID_NUMBER]INT,
[LAST_PAYMENT_DATE]DATETIME,
[NEXT_PAYMENT_DATE]DATETIME,
[MATURITY_DATE]DATETIME,
[GrpCnt]INT
)
INSERT INTO #TEST_JAS ([ID_NUMBER],[LAST_PAYMENT_DATE],[NEXT_PAYMENT_DATE],[MATURITY_DATE],[GrpCnt])
SELECT 1, '2023-03-31', '2023-04-30', '2023-07-31', 1
UNION ALL
SELECT 1, '2023-03-31', '2023-05-31', '2023-07-31', 2
UNION ALL
SELECT 1, '2023-03-31', '2023-06-30', '2023-07-31', 3
UNION ALL
SELECT 1, '2023-03-31', '2023-07-31', '2023-08-02', 4
UNION ALL
SELECT 2, '2023-03-31', '2023-04-30', '2023-06-30', 1
UNION ALL
SELECT 2, '2023-03-31', '2023-05-31', '2023-06-30', 2
UNION ALL
SELECT 2, '2023-03-31', '2023-06-30', '2023-06-30', 3
UNION ALL
SELECT 3, '2023-03-31', '2023-04-30', '2023-07-31', 1
UNION ALL
SELECT 3, '2023-03-31', '2023-05-31', '2023-07-31', 2
UNION ALL
SELECT 3, '2023-03-31', '2023-06-30', '2023-07-31', 3
UNION ALL
SELECT 3, '2023-03-31', '2023-07-31', '2023-08-02', 4
SELECT
[A].[ID_NUMBER], [A].[GrpCnt]
FROM
#TEST_JAS [A]
INNER JOIN
(
SELECT
.[ID_NUMBER], MAX(.[GrpCnt]) [MAXCNT]
FROM
#TEST_JAS
--WHERE
--.[NEXT_PAYMENT_DATE] > .[MATURITY_DATE]
GROUP BY
.[ID_NUMBER]
)
ON [A].[ID_NUMBER] = .[ID_NUMBER]
WHERE [A].[GrpCnt] IN (1, .[MAXCNT])
December 10, 2008 at 8:01 am
Two main problems.
1. You're using the wrong sign. Next Payment date is never > Maturity date, so you're not getting anything with the where. Switched it to <.
2. You're always going to get a record for 2 unless you compare the max where it's not < to the actual max. Changing only #1 would have given you a max value of 2 for ID 2. The third value doesn't meet your criteria, but the second one still does, and it would have been included.
Try this:
SELECT
[A].[ID_NUMBER], [A].[GrpCnt]
FROM
#TEST_JAS [A]
LEFT JOIN ( -- Grab the Max GrpCount per ID for Comparison
SELECT .[ID_NUMBER], MAX(.[GrpCnt]) [MAXCNT]
FROM #TEST_JAS
GROUP BY .[ID_NUMBER]) [C]ON [A].[ID_NUMBER] = [C].[ID_NUMBER]
LEFT JOIN ( -- Grab the Max GrpCount per ID Where NPD < MD
SELECT .[ID_NUMBER], MAX(.[GrpCnt]) [MAXCNT]
FROM #TEST_JAS
WHERE .[NEXT_PAYMENT_DATE] < .[MATURITY_DATE]
GROUP BY .[ID_NUMBER]) ON [A].[ID_NUMBER] = .[ID_NUMBER] AND C.MAXCNT=B.MAXCNT
WHERE [A].[GrpCnt] IN (1, .[MAXCNT])
December 10, 2008 at 9:47 am
Seth,
Thanks for the info and code...
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply