SQL Max with Fields from 2 Other Tables

[sardine53]

I am using SQL 2008R2 and need some assistance.

This query gives me the result I need except the CLIENT_CODE (in the C table) that corresponds with the LASTEST_TRANS:

SELECT
max(T.TRANS_ENTRY_DATE)AS LATEST_TRANS,
P.PAYEE_CODE,
P. PAYEE_NAME
FROM
{PREMIUMS_TRANSACTION] TRANS
INNER JOIN {PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL AND
(P.DATE_OFF IS NULL)
GROUP BY
P. PAYEE_NAME,P.PAYEE_CODE

Desired results:
LATEST_TRANS PAYEE PAYEE_NAME CLIENT_CODE
5/14/2020 ABC ABC Ins CLIENTA
6/18/2018 AAC Safety CLIENTB
7/23/2019 AAP General CLIENTF
5/15/2020 ARC Arch CLIENTA

I've been searching and it seems I need a subquery, but I can't seem to translate their query to mine. Any help you could provide would be appreciated!

 

[Andrzejek]

So, the same CLIENT_CODE may have one or more PAYEE and/or PAYEE_NAME:

[pre]
LATEST_TRANS PAYEE PAYEE_NAME CLIENT_CODE
5/14/2020 [blue]ABC ABC Ins CLIENTA[/blue]
6/18/2018 AAC Safety CLIENTB
7/23/2019 AAP General CLIENTF
5/15/2020 [blue]ARC Arch CLIENTA[/blue]
[/pre]

How about:

[tt][blue]SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE AS PAYEE,
PAYEE_NAME,
CLIENT_CODE
FROM ([/blue]
SELECT
T.TRANS_ENTRY_DATE,
P.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
{PREMIUMS_TRANSACTION] TRANS
INNER JOIN {PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND(P.DATE_OFF IS NULL)[blue])
GROUP BY PAYEE, PAYEE_NAME, CLIENT_CODE[/blue][/tt]

---- Andy

There is a great need for a sarcasm font.

 

[sardine53]

Thanks for your speedy reply Andy

I corrected my error:
FROM {PREMIUMS_TRANSACTION] TRANS to T and removed the where clause but I am still getting
Incorrect syntax near 'CLIENT_CODE' the last line.

What am I missing?

 

[Andrzejek]

Post your current SQL


---- Andy

There is a great need for a sarcasm font.

 

[sardine53]

The query that works:

SELECT
max(T.TRANS_ENTRY_DATE)AS LATEST_TRANS,
T.PAYEE_CODE,
P.PAYEE_NAME
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL AND
(P.DATE_OFF IS NULL)
GROUP BY
P.PAYEE_NAME,T.PAYEE_CODE

The modified query that doesn't work:

SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE AS PAYEE,
PAYEE_NAME,
CLIENT_CODE
FROM (
SELECT
T.TRANS_ENTRY_DATE,
P.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P
ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C
ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND(P.DATE_OFF IS NULL))
GROUP BY PAYEE, PAYEE_NAME, CLIENT_CODE

 

[Andrzejek]

What do you get when you run this:
[tt]
SELECT
T.TRANS_ENTRY_DATE,
P.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P
ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C
ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT (T.TRANS_ENTRY_DATE) IS NULL
AND (P.DATE_OFF IS NULL)
[/tt]
I know you will not get the MAX date (yet...)


---- Andy

There is a great need for a sarcasm font.

 

[sardine53]

P.PAYEE_CODE can't be used but when I use either P.PAYEES_ID or T.PAYEE_CODE it returns the correct columns but 1M+ rows and I am expecting 610 rows.

Thanks

 

[MarkSweetland]

The error you're getting is probably because you need to alias the derived table.

SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE AS PAYEE,
PAYEE_NAME,
CLIENT_CODE
FROM (
[indent]SELECT
T.TRANS_ENTRY_DATE,
P.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P
ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C
ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND(P.DATE_OFF IS NULL)[/indent]
) [red]as alias[/red]
GROUP BY PAYEE, PAYEE_NAME, CLIENT_CODE

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[sardine53]

I tried the above but substituted T.PAYEE CODE - error "Invalid column name 'PAYEE'"

Then I removed the AS PAYEE:

SELECT MAX(TRANS_ENTRY_DATE) AS LATEST_TRANS,
PAYEE_CODE,
PAYEE_NAME,
CLIENT_CODE
FROM (
SELECT
T.TRANS_ENTRY_DATE,
T.PAYEE_CODE,
P.PAYEE_NAME,
C.CLIENT_CODE
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P
ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C
ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND(P.DATE_OFF IS NULL)
) as alias
GROUP BY PAYEE_CODE, PAYEE_NAME, CLIENT_CODE

It ran but it still returned duplicates - 24,320 vs 610

This is really a puzzle for me. Thanks for all the help.

 

[sardine53]

Any ideas with my new info?? Thanks!

 

[Andrzejek]

>The query that works:
[tt]
SELECT
max(T.TRANS_ENTRY_DATE)AS LATEST_TRANS,
T.PAYEE_CODE,
P.PAYEE_NAME
FROM
[xx].[xx].[dbo].[SQL_PREMIUMS_TRANSACTION_CODE] T
INNER JOIN [xx].[xx].[dbo].[SQL_PAYEES] P ON T.PAYEE_CODE = P.PAYEES_ID
INNER JOIN [xx].[xx].[dbo].[SQL_CLIENTS] C ON T.BILLTO_ID = C.CLIENTS_ID
WHERE
NOT(T.TRANS_ENTRY_DATE) IS NULL
AND (P.DATE_OFF IS NULL)
GROUP BY P.PAYEE_NAME,T.PAYEE_CODE
[/tt]
If this is what works for you, and you want to add CLIENT_CODE field from SQL_CLIENTS table to your outcome, how does this new field relate to any table(s) already in use?

>it seems I need a subquery
You may be right...

---- Andy

There is a great need for a sarcasm font.