SQL query not returning the max date

Question

Can someone please review my query and let me know why is it not pulling the max(latest date)?

I want the cst_at by lastest dts_srv_ts(datetime).

Here is my code:

SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
--max(dbo.TRX202_ITEM_CHARGE.cst_at),
dbo.TRX202_ITEM_CHARGE.cst_at,
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  

GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id,
dbo.TRX202_ITEM_CHARGE.cst_at
order by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id

This is the results I got:

chg_cod_ext_id     cst_at     dts_srv_ts
440001              1.6500   2014-06-24 15:26:52.000
440001              8.8440   2015-01-26 12:22:24.000
440001              13.6070  2015-12-23 02:12:10.000
440001              16.7080  2017-04-30 07:56:04.000
440001              16.7310  2016-09-04 21:25:12.000
440001              16.9080  2017-08-26 05:14:27.000
440001              17.0090  2016-06-03 07:52:29.000
440001              17.0580  2018-03-26 11:15:29.000
440001              17.5460  2017-12-19 11:11:13.000

This is what I want:

chg_cod_ext_id     cst_at     dts_srv_ts
440001             17.0580    2018-03-26 11:15:29.000

Your help will be greatly appreciated!

Your joins are probably returning multiple rows for one `chg_cod_ext_id`. Select all columns and figure out which table is providing the duplicates. — Andomar, Mar 30 '18 at 13:45
Am I missing something or should you just use MAX(dts_srv_ts) and group by chg_cod_ext_id? — dfundako, Mar 30 '18 at 13:46
Wow those column names are rough. You really should start using aliases. It would make your code so much more manageable, and you don't have to type a zillion characters for a simple query like this. — Sean Lange, Mar 30 '18 at 13:47
I have tired Max(dts_srv_ts) and group by chg_cod_ext_id and it totally works fine but it didn't seem to work when I did subquery as above — Milan Pk, Mar 30 '18 at 13:49

score 1 · Accepted Answer · answered Mar 30 '18 at 15:30

The problem here is you are grouping on the cst_at. You have to group on this if you are using max on another column, but it changes the grouping naturally and doesn't return the results that you want. A quick fix is row_number(). You could also do this with an inner join, or correlated sub-query. Good luck with Paragon!

;with cte as(
SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
dbo.TRX202_ITEM_CHARGE.cst_at,
dbo.TRX111_ITEM_DISPENSE.dts_srv_ts,
RN = row_number() over (partition by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id order by dbo.TRX111_ITEM_DISPENSE.dts_srv_ts desc)

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001')

select * from cte where RN = 1

You rock! Thanks for the help. – Milan Pk Mar 30 '18 at 16:07 — Milan Pk, Mar 30 '18 at 16:07

score 0 · Answer 2 · answered Mar 30 '18 at 13:48

0

If you are looking for the latest date by ID, you could try a straight max()?

SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  
GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id
order by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id

answered Mar 30 '18 at 13:48

dfundako

8,022
3
18
34

LOL. You beat me to it. :) I will delete mine. – Sean Lange Mar 30 '18 at 13:50
2

@SeanLange Fast fingers. I'm on my 3rd cup of coffee. – dfundako Mar 30 '18 at 13:50
Thank you so much for making it more simpler. I have a cost column that I need to pull and there are different cost $ in different date so I need the cost $ by lastest column. I tried adding dbo.TRX202_ITEM_CHARGE.cst_at above MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts but it still won't give me right result – Milan Pk Mar 30 '18 at 14:01
1

Well yeah, if you start adding more stuff, the solution will change. Add it to the original post so it is easier to read and I can update my answer. – dfundako Mar 30 '18 at 14:21
Can you please take a look at it, I have updated the question. – Milan Pk Mar 30 '18 at 14:32

score 0 · Answer 3 · answered Mar 30 '18 at 15:40

When you include the other two columns you return the max datetime with respect to those column combinations.

One method is to take the MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) without including the other two columns. This returns the Max Datetime. The full query can be filtered on this aggregate value using HAVING. Here is one method using a variable to store the max datetime:

--Save the maxdatetime to a variable
DECLARE @maxdatetime datetime = 
(
SELECT 
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  

GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id,
dbo.TRX202_ITEM_CHARGE.cst_at
)

--Query, filtering the aggregate with HAVING
SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
dbo.TRX202_ITEM_CHARGE.cst_at,
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  

GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id,
dbo.TRX202_ITEM_CHARGE.cst_at

HAVING MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) = @maxdatetime

order by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id

SQL query not returning the max date

3 Answers3