how to get the max date with join tables?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to get the max date with join tables??

Author

Topic

Biffothebear0
Starting Member

1 Post

Posted - 2013-10-24 : 09:51:41

Hi all,
Please help!!!
I'm using SQL Dev 2.1 and i want to find the latest date and also joining tables. My query is below:-

I want to get the latest DEBT_COLLECTION_STEPS_V.TRANSACTION_DATE
as there are more that one date relating to the same invoice number but different comments posted against the DEBT_COLLECTION_STEPS_V.DESCRIPTION.

SELECT AR_INVOICE_INQ_V.CLIENT CLIENT,
AR_INVOICE_INQ_V.CLI_NAME CLI_NAME,
AR_INVOICE_INQ_V.PARTNER PARTNER,
AR_INVOICE_INQ_V.PAR_NAME PAR_NAME,
AR_INVOICE_INQ_V.MANAGER MANAGER,
AR_INVOICE_INQ_V.REFERENCE REFERENCE,
AR_INVOICE_INQ_V.STATUS_CODE STATUS_CODE,
AR_INVOICE_INQ_V.INVOICE_DATE INVOICE_DATE,
AR_INVOICE_INQ_V.TIME_BILLED TIME_BILLED,
AR_INVOICE_INQ_V.GROUP_CLIENT_CODE GROUP_CLIENT_CODE,
AR_INVOICE_INQ_V.GRPCLI_NAME GRPCLI_NAME,
AR_INVOICE_INQ_V.INVOICE_BALANCE,
SUBSTR(AR_INVOICE_INQ_V.MANAGER, 3, 2) CENTRE,
DEBT_COLLECTION_STEPS_V.DESCRIPTION,
MAX (BILLING_TRANSACTIONS_V.SEQUENCE_CODE),
DEBT_COLLECTION_STEPS_V.TRANSACTION_DATE
FROM AR_INVOICE_INQ_V
LEFT JOIN DEBT_COLLECTION_STEPS_V
ON DEBT_COLLECTION_STEPS_V.INVOICE_NUMBER = AR_INVOICE_INQ_V.REFERENCE
JOIN BILLING_TRANSACTIONS_V
ON BILLING_TRANSACTIONS_V.INVOICE_OR_CN_NUMBER = AR_INVOICE_INQ_V.REFERENCE
WHERE AR_INVOICE_INQ_V.INVOICE_BALANCE >0
AND AR_INVOICE_INQ_V.MANAGER BETWEEN 'AAAA' AND 'AZZZ'
GROUP BY AR_INVOICE_INQ_V.CLIENT,
AR_INVOICE_INQ_V.CLI_NAME,
AR_INVOICE_INQ_V.PARTNER,
AR_INVOICE_INQ_V.PAR_NAME,
AR_INVOICE_INQ_V.MANAGER,
AR_INVOICE_INQ_V.REFERENCE,
AR_INVOICE_INQ_V.STATUS_CODE,
AR_INVOICE_INQ_V.INVOICE_DATE,
AR_INVOICE_INQ_V.TIME_BILLED,
AR_INVOICE_INQ_V.GROUP_CLIENT_CODE,
AR_INVOICE_INQ_V.GRPCLI_NAME,
AR_INVOICE_INQ_V.INVOICE_BALANCE,
SUBSTR(AR_INVOICE_INQ_V.MANAGER, 3, 2),
DEBT_COLLECTION_STEPS_V.DESCRIPTION,
DEBT_COLLECTION_STEPS_V.TRANSACTION_DATE;

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 10:22:21

This is a forum for Microsoft SQL Server; so you may find very few if any experts on other RDBMS systems.

If you are using Microsoft SQL Server, you can do something like shown below, (although you would need to make changes to grouping from what I can tell)

SELECT * FROM (
SELECT  AR_INVOICE_INQ_V.CLIENT CLIENT ,
        AR_INVOICE_INQ_V.CLI_NAME CLI_NAME ,
        AR_INVOICE_INQ_V.PARTNER PARTNER ,
        AR_INVOICE_INQ_V.PAR_NAME PAR_NAME ,
        AR_INVOICE_INQ_V.MANAGER MANAGER ,
        AR_INVOICE_INQ_V.REFERENCE REFERENCE ,
        AR_INVOICE_INQ_V.STATUS_CODE STATUS_CODE ,
        AR_INVOICE_INQ_V.INVOICE_DATE INVOICE_DATE ,
        AR_INVOICE_INQ_V.TIME_BILLED TIME_BILLED ,
        AR_INVOICE_INQ_V.GROUP_CLIENT_CODE GROUP_CLIENT_CODE ,
        AR_INVOICE_INQ_V.GRPCLI_NAME GRPCLI_NAME ,
        AR_INVOICE_INQ_V.INVOICE_BALANCE ,
        SUBSTR(AR_INVOICE_INQ_V.MANAGER, 3, 2) CENTRE ,
        DEBT_COLLECTION_STEPS_V.DESCRIPTION ,
        MAX(BILLING_TRANSACTIONS_V.SEQUENCE_CODE) ,
        DEBT_COLLECTION_STEPS_V.TRANSACTION_DATE,
        ROW_NUMBER() OVER (PARTITION BY AR_INVOICE_INQ_V.REFERENCE
			ORDER BY DEBT_COMMS_DATE ) AS N
FROM    AR_INVOICE_INQ_V
        LEFT JOIN DEBT_COLLECTION_STEPS_V ON DEBT_COLLECTION_STEPS_V.INVOICE_NUMBER = AR_INVOICE_INQ_V.REFERENCE
        JOIN BILLING_TRANSACTIONS_V ON BILLING_TRANSACTIONS_V.INVOICE_OR_CN_NUMBER = AR_INVOICE_INQ_V.REFERENCE
WHERE   AR_INVOICE_INQ_V.INVOICE_BALANCE > 0
        AND AR_INVOICE_INQ_V.MANAGER BETWEEN 'AAAA'
                                     AND     'AZZZ'
GROUP BY AR_INVOICE_INQ_V.CLIENT ,
        AR_INVOICE_INQ_V.CLI_NAME ,
        AR_INVOICE_INQ_V.PARTNER ,
        AR_INVOICE_INQ_V.PAR_NAME ,
        AR_INVOICE_INQ_V.MANAGER ,
        AR_INVOICE_INQ_V.REFERENCE ,
        AR_INVOICE_INQ_V.STATUS_CODE ,
        AR_INVOICE_INQ_V.INVOICE_DATE ,
        AR_INVOICE_INQ_V.TIME_BILLED ,
        AR_INVOICE_INQ_V.GROUP_CLIENT_CODE ,
        AR_INVOICE_INQ_V.GRPCLI_NAME ,
        AR_INVOICE_INQ_V.INVOICE_BALANCE ,
        SUBSTR(AR_INVOICE_INQ_V.MANAGER, 3, 2) ,
        DEBT_COLLECTION_STEPS_V.DESCRIPTION ,
        DEBT_COLLECTION_STEPS_V.TRANSACTION_DATE
) s WHERE N=1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources