Get MaxDate using Join

1

Hello Masters,

I need to grab the max date for each taxcode in two tables. Here is the query:

SELECT T.MTTTAXCODE
, T.MTTTAXPERCENTOVERBASE
, T.MTTNONRESIDENTTAXRATE
, T.MTTAMTOVERWAGEBASE
,T.MTTDATETIMECHANGED
, T.MTTIFWAGEBASEISOVER
, T.MTTIFWAGEBASEISOVER
, C.MTCRECIPROCITYLIMIT
FROM TXTBMAST T JOIN TXCDMAST C
ON T.MTTTAXCODE = C.MTCTAXCODE
WHERE T.MTTTAXCODE
IN
(
'MI001','MI002','MI007','MI008','MI013','MI015','MI016','MI019','MI020','MI026','MI027','MI028','MI034','MI037','NMWCEE'
,'NMWCER','NY001','NY006','PA100018','PA102092','PA103015','PA112826','PA113485','PA113628','PA117759','OR001','ORWCEE','WALIEE'
,'WALIER','WASEAER' ,'PA100045','PA108094','PA111185','NMWCEE','NMWCER','PA104041','WALIEE','WALIER','IN174'
,'MD011','MD016','OH1005','OH1034','OH1055','OH1102','OH1172','OH1229','OH1278','OH1445','OH1541'
,'OH1762','OH1794','OH181','OH1844','PA100292'
)

I'd like to find the distinct MTTTAXCODE with the most recent MTTDATETIMECHANGED

Thanks as always !

2 
SELECT T.MTTTAXCODE
, T.MTTTAXPERCENTOVERBASE
, T.MTTNONRESIDENTTAXRATE
, T.MTTAMTOVERWAGEBASE
, T.MTTDATETIMECHANGED
, T.MTTIFWAGEBASEISOVER
, T.MTTIFWAGEBASEISOVER
, C.MTCRECIPROCITYLIMIT 
FROM (
    SELECT MTTTAXCODE
    , MTTTAXPERCENTOVERBASE
    , MTTNONRESIDENTTAXRATE
    , MTTAMTOVERWAGEBASE
    , MTTDATETIMECHANGED
    , MTTIFWAGEBASEISOVER
    , MTTIFWAGEBASEISOVER
    , ROW_NUMBER() OVER(PARTITION BY MTTAXCODE ORDER BY MTTDATETIMECHANGED DESC) AS row_num
    FROM TXTBMAST
    WHERE T.MTTTAXCODE 
    IN 
    (
    'MI001','MI002','MI007','MI008','MI013','MI015','MI016','MI019','MI020','MI026','MI027','MI028','MI034','MI037','NMWCEE'
    ,'NMWCER','NY001','NY006','PA100018','PA102092','PA103015','PA112826','PA113485','PA113628','PA117759','OR001','ORWCEE','WALIEE'
    ,'WALIER','WASEAER' ,'PA100045','PA108094','PA111185','NMWCEE','NMWCER','PA104041','WALIEE','WALIER','IN174' 
    ,'MD011','MD016','OH1005','OH1034','OH1055','OH1102','OH1172','OH1229','OH1278','OH1445','OH1541'
    ,'OH1762','OH1794','OH181','OH1844','PA100292'
    )
) AS T
INNER JOIN TXCDMAST C 
ON T.MTTTAXCODE = C.MTCTAXCODE
WHERE
    T.row_num = 1
--ORDER BY MTTAXCODE
1 Like
3

Thanks for the quick response. I get this error when I run that:

Msg 4104, Level 16, State 1, Line 19
The multi-part identifier "T.MTTTAXCODE" could not be bound.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'MTTAXCODE'.
Msg 8156, Level 16, State 1, Line 29
The column 'MTTIFWAGEBASEISOVER' was specified multiple times for 'T'.

4 
...
FROM (
SELECT MTTTAXCODE
, MTTTAXPERCENTOVERBASE
, MTTNONRESIDENTTAXRATE
, MTTAMTOVERWAGEBASE
, MTTDATETIMECHANGED
, MTTIFWAGEBASEISOVER **--<<-- remove one of the MTTIFWAGEBASEISOVER columns**
, ROW_NUMBER() OVER(PARTITION BY MTTAXCODE ORDER BY MTTDATETIMECHANGED DESC) AS row_num
FROM TXTBMAST
WHERE T.MTTTAXCODE 
, ROW_NUMBER() OVER(PARTITION BY MTTAXCODE ORDER BY MTTDATETIMECHANGED DESC) AS row_num
FROM TXTBMAST
WHERE MTTTAXCODE --<<-- **remove T.**
IN
...
5

That resovled 2 errors, but am still getting one last error, Sir:

Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "T.MTTTAXCODE" could not be bound.

6 
SELECT T.MTTTAXCODE
, T.MTTTAXPERCENTOVERBASE
, T.MTTNONRESIDENTTAXRATE
, T.MTTAMTOVERWAGEBASE
, T.MTTDATETIMECHANGED
, T.MTTIFWAGEBASEISOVER
, T.MTTIFWAGEBASEISOVER
, C.MTCRECIPROCITYLIMIT 
FROM (
    SELECT MTTTAXCODE
    , MTTTAXPERCENTOVERBASE
    , MTTNONRESIDENTTAXRATE
    , MTTAMTOVERWAGEBASE
    , MTTDATETIMECHANGED
    , MTTIFWAGEBASEISOVER
    , ROW_NUMBER() OVER(PARTITION BY MTTAXCODE ORDER BY MTTDATETIMECHANGED DESC) AS row_num
    FROM TXTBMAST
    WHERE MTTTAXCODE 
    IN 
    (
    'MI001','MI002','MI007','MI008','MI013','MI015','MI016','MI019','MI020','MI026','MI027','MI028','MI034','MI037','NMWCEE'
    ,'NMWCER','NY001','NY006','PA100018','PA102092','PA103015','PA112826','PA113485','PA113628','PA117759','OR001','ORWCEE','WALIEE'
    ,'WALIER','WASEAER' ,'PA100045','PA108094','PA111185','NMWCEE','NMWCER','PA104041','WALIEE','WALIER','IN174' 
    ,'MD011','MD016','OH1005','OH1034','OH1055','OH1102','OH1172','OH1229','OH1278','OH1445','OH1541'
    ,'OH1762','OH1794','OH181','OH1844','PA100292'
    )
) AS T
INNER JOIN TXCDMAST C 
ON T.MTTTAXCODE = C.MTCTAXCODE
WHERE
    T.row_num = 1
--ORDER BY MTTAXCODE
1 Like
7

That worked, but I am noticing something now. A tax code can have multiple records with the same exact datetime stamp. For example, tMTTTAXCODE 'ABC' has 10 rows with the same exact time stamp. Is there a way to sneak a DISTINCT on the MTTTAXCODE column ?

8

Got it. Here is what works:

SELECT Distinct T.MTTTAXCODE
, T.MTTTAXPERCENTOVERBASE
, T.MTTNONRESIDENTTAXRATE
, T.MTTAMTOVERWAGEBASE
, T.MTTDATETIMECHANGED
, T.MTTIFWAGEBASEISOVER
, T.MTTIFWAGEBASEISOVER
, C.MTCRECIPROCITYLIMIT
FROM (
SELECT MTTTAXCODE
, MTTTAXPERCENTOVERBASE
, MTTNONRESIDENTTAXRATE
, MTTAMTOVERWAGEBASE
, MTTDATETIMECHANGED
, MTTIFWAGEBASEISOVER
, ROW_NUMBER() OVER(PARTITION BY MTTTAXCODE ORDER BY MTTDATETIMECHANGED DESC) AS row_num
FROM TXTBMAST
WHERE MTTTAXCODE
IN
(
'MI001','MI002','MI007','MI008','MI013','MI015','MI016','MI019','MI020','MI026','MI027','MI028','MI034','MI037','NMWCEE'
,'NMWCER','NY001','NY006','PA100018','PA102092','PA103015','PA112826','PA113485','PA113628','PA117759','OR001','ORWCEE','WALIEE'
,'WALIER','WASEAER' ,'PA100045','PA108094','PA111185','NMWCEE','NMWCER','PA104041','WALIEE','WALIER','IN174'
,'MD011','MD016','OH1005','OH1034','OH1055','OH1102','OH1172','OH1229','OH1278','OH1445','OH1541'
,'OH1762','OH1794','OH181','OH1844','PA100292'
)
) AS T
INNER JOIN TXCDMAST C
ON T.MTTTAXCODE = C.MTCTAXCODE
WHERE
T.row_num = 1

Thanks so much for all your support.

9

Only one "T" row will be returned. No matter how many duplicate rows there are, only 1 will get assigned ROW_NUMBER = 1.

I guess the TXCDMAST can contain multiple rows with the same MTCTAXCODE. You could change to do a CROSS APPLY only to the first matching row of that table:

...
) AS T
CROSS APPLY (
    SELECT TOP (1) *
    FROM TXCDMAST C2
    WHERE T.MTTTAXCODE = C2.MTCTAXCODE
) AS C
WHERE
T.row_num = 1
1 Like