Max Date/Case Statement

Question

I am trying to pull back a row of detail based on the max date of one of the columns. Doing a simple max(date.field) does not work for my table. I originally solved the issue by utilizing this query that I built using this resource See the entry by Tom H.:

SELECT
    HBM_CLIENT.CLIENT_CODE,
    HBM_NAME.NAME AS CLIENT_NAME,
    PART_BILL1.PART_CAT_CODE AS BILLING_CODE,
    PART_BILL1.EMPL_UNO AS BILLING_NAME_UNO,
    HBM_PERSNL_BILL.EMPLOYEE_NAME AS BILLING_NAME,
    PART_BILL1.PERCENTAGE AS BILLING_PERCENTAGE
FROM 
    HBM_CLIENT
INNER JOIN 
    HBM_NAME ON HBM_CLIENT.NAME_UNO = HBM_NAME.NAME_UNO
LEFT OUTER JOIN 
    TBM_CLMAT_PART AS PART_BILL1 ON PART_BILL1.CLIENT_UNO = HBM_CLIENT.CLIENT_UNO
                                 AND PART_BILL1.PART_CAT_CODE = 'BILL'
LEFT OUTER JOIN 
    TBM_CLMAT_PART AS PART_BILL2 ON PART_BILL2.CLIENT_UNO = HBM_CLIENT.CLIENT_UNO
                                 AND PART_BILL2.EFF_DATE > PART_BILL1.EFF_DATE
                                 AND PART_BILL1.PART_CAT_CODE = 'BILL'
LEFT OUTER JOIN 
    HBM_PERSNL AS HBM_PERSNL_BILL ON PART_BILL1.EMPL_UNO = HBM_PERSNL_BILL.EMPL_UNO
GROUP BY 
    HBM_CLIENT.CLIENT_CODE, HBM_NAME.NAME,
    PART_BILL1.PART_CAT_CODE, PART_BILL1.EMPL_UNO,
    HBM_PERSNL_BILL.EMPLOYEE_NAME,
    PART_BILL1.PERCENTAGE,
ORDER BY 
    HBM_CLIENT.CLIENT_CODE

The problem with this query is that for some reason, it doesn't pull back all of the results, and the reason is because of the solution I used.

I'm wondering if using a case statement makes more sense, but I'm not very familiar with case statements.

Here is the query I'm working on:

`SELECT
  HBM_CLIENT.CLIENT_CODE,
  HBM_NAME.NAME AS CLIENT_NAME,
  PART_BILL1.PART_CAT_CODE AS BILLING_CODE,
  PART_BILL1.EMPL_UNO AS BILLING_NAME_UNO,
  HBM_PERSNL_BILL.EMPLOYEE_NAME AS BILLING_NAME,
  PART_BILL1.PERCENTAGE AS BILLING_PERCENTAGE,
  Part_BILL1.EFF_DATE,
 CASE
    WHEN 
    MAX(Part_BILL1.EFF_DATE) > Part_BILL1.EFF_DATE THEN max(Part_BILL1.EFF_DATE) ELSE Part_BILL1.EFF_DATE END  
FROM HBM_CLIENT
INNER JOIN HBM_MATTER
  ON HBM_CLIENT.CLIENT_UNO = HBM_MATTER.CLIENT_UNO
INNER JOIN HBM_NAME
  ON HBM_CLIENT.NAME_UNO = HBM_NAME.NAME_UNO
LEFT OUTER JOIN TBM_CLMAT_PART AS PART_BILL1
  ON PART_BILL1.CLIENT_UNO = HBM_CLIENT.CLIENT_UNO
  AND PART_BILL1.PART_CAT_CODE = 'BILL'
LEFT OUTER JOIN HBM_PERSNL AS HBM_PERSNL_BILL
  ON PART_BILL1.EMPL_UNO = HBM_PERSNL_BILL.EMPL_UNO
WHERE 
(HBM_CLIENT.CLIENT_CODE = '065011') 
GROUP BY
  HBM_CLIENT.CLIENT_CODE,
  HBM_NAME.NAME,
  PART_BILL1.PART_CAT_CODE,
  PART_BILL1.EMPL_UNO,
  HBM_PERSNL_BILL.EMPLOYEE_NAME,
  PART_BILL1.PERCENTAGE,
  Part_BILL1.EFF_DATE
HAVING 
(Part_BILL1.EFF_DATE = CASE
WHEN MAX(Part_BILL1.EFF_DATE) > Part_BILL1.EFF_DATE THEN max(Part_BILL1.EFF_DATE) ELSE Part_BILL1.EFF_DATE END)`

It runs, but it gives me back two rows instead of one, i.e. it's not pulling just the max. I'm using T-SQL and working out of Visual Studio to write SSRS.

I'm happy to answer any questions I can. I apologize in advance if my explanations aren't very good.

| CLIENT_CODE | CLIENT_NAME  | BILLING_NAME | EFF_DATE | PERCENTAGE |
| ------------+--------------+--------------+----------+------------|
| 123456      | Entity, Inc. | Attorney A   | 1/1/1990 |50%         |
| 123456      | Entity, Inc. | Attorney B   | 1/1/1990 |50%         |
| 123456      | Entity, Inc. | Attorney B   | 1/1/2017 |50%         |
| 123456      | Entity, Inc. | Attorney C   | 1/1/2017 |50%         |

In the reference you gave you had a specific question with data given. You are giving a ton of tables without knowing what is in them. Is there any way you could give a more simplified version in test data? It is hard to help with so much detail that is specific without seeing the underlying data. — djangojazz, Feb 14 '17 at 18:50
@djangojazz that's reasonable enough! I've removed the extra stuff so it's mostly the same. The only tables now showing are ones needed to run the queries with the fields I've selected. — T. Meyer, Feb 14 '17 at 19:08

3BK · Accepted Answer · 2017-02-19T09:49:58.857

Could this not be simplified by using a SELECT TOP 1 query with an ORDER BY clause?

Something like this:

SELECT TOP 1
  HBM_CLIENT.CLIENT_CODE,
  HBM_NAME.NAME AS CLIENT_NAME,
  PART_BILL1.PART_CAT_CODE AS BILLING_CODE,
  PART_BILL1.EMPL_UNO AS BILLING_NAME_UNO,
  HBM_PERSNL_BILL.EMPLOYEE_NAME AS BILLING_NAME,
  PART_BILL1.PERCENTAGE AS BILLING_PERCENTAGE,
  Part_BILL1.EFF_DATE,
  ....
FROM HBM_CLIENT
INNER JOIN HBM_MATTER
  ON HBM_CLIENT.CLIENT_UNO = HBM_MATTER.CLIENT_UNO
INNER JOIN HBM_NAME
  ON HBM_CLIENT.NAME_UNO = HBM_NAME.NAME_UNO
LEFT OUTER JOIN TBM_CLMAT_PART AS PART_BILL1
  ON PART_BILL1.CLIENT_UNO = HBM_CLIENT.CLIENT_UNO
  AND PART_BILL1.PART_CAT_CODE = 'BILL'
LEFT OUTER JOIN HBM_PERSNL AS HBM_PERSNL_BILL
  ON PART_BILL1.EMPL_UNO = HBM_PERSNL_BILL.EMPL_UNO
WHERE 
(HBM_CLIENT.CLIENT_CODE = '065011') 
ORDER BY Part_BILL1.EFF_DATE DESC

This should give you only the most recent row by EFF_DATE, without requiring any special grouping or case logic.

EDIT: In my opinion, the best way to get the result you want is by taking advantage of the TOP clause. Generally, when writing a query like this, you would write SELECT TOP 1 or SELECT TOP 1000, but the TOPclause also supports various functionalities. You can use sub-queries, calculations, variables, and also the WITH TIES functionality to get what you want.

In your case, you want the query to return any number of rows, so long as all of them have the same EFF_DATE value, which will also be the maximum EFF_DATE value in the table for a given CLIENT_CODE.

As such, I would recommend simply adding the WITH TIES argument to the TOP clause to get the functionality that you're looking for:
SELECT TOP 1 WITH TIES
  HBM_CLIENT.CLIENT_CODE,
  HBM_NAME.NAME AS CLIENT_NAME,
  PART_BILL1.PART_CAT_CODE AS BILLING_CODE,
  PART_BILL1.EMPL_UNO AS BILLING_NAME_UNO,
  HBM_PERSNL_BILL.EMPLOYEE_NAME AS BILLING_NAME,
  PART_BILL1.PERCENTAGE AS BILLING_PERCENTAGE,
  Part_BILL1.EFF_DATE,
  ....
FROM HBM_CLIENT
INNER JOIN HBM_MATTER
  ON HBM_CLIENT.CLIENT_UNO = HBM_MATTER.CLIENT_UNO
INNER JOIN HBM_NAME
  ON HBM_CLIENT.NAME_UNO = HBM_NAME.NAME_UNO
LEFT OUTER JOIN TBM_CLMAT_PART AS PART_BILL1
  ON PART_BILL1.CLIENT_UNO = HBM_CLIENT.CLIENT_UNO
  AND PART_BILL1.PART_CAT_CODE = 'BILL'
LEFT OUTER JOIN HBM_PERSNL AS HBM_PERSNL_BILL
  ON PART_BILL1.EMPL_UNO = HBM_PERSNL_BILL.EMPL_UNO
WHERE 
(HBM_CLIENT.CLIENT_CODE = '065011') 
ORDER BY Part_BILL1.EFF_DATE DESC
A complete explanation of the various uses of the TOP clause can be found here.

To briefly explain, the WITH TIES argument means that you want the query to return the TOP X rows of a data set based upon the value(s) in the ORDER BY clause - in your case, EFF_DATE. So if there are two rows with the same value in EFF_DATE, or three rows, or five hundred, they will all be returned, because you are effectively searching not for the TOP 1 ROW, but rather for the TOP 1 VALUE of EFF_DATE

Hope that helps.

If I remember correctly, it won't work because there could be two attorneys listed on the same date. There's a field called Part_bill1.percentage. In that column each of those attorneys would have 50 percent credit. I'm on mobile so I can revise my table above, but imagine on the 1999 date attorney a and attorney b both get 50 50 credit. In 2017, attorney b retires so attorney a adds attorney ç as the new co-biller. So in that instance I need to pull back two rows show attorney a and attorney ç. I'll try your suggestion as soon as I can, but I think top and max were the first things I did. — T. Meyer, Feb 15 '17 at 16:00
@T.Meyer I didn't understand from your question that you were looking for more than one row. You specifically stated that you wanted "a row of detail based on the max date of one of the columns". In the instance you described, you're talking about an aggregated row - i.e., you want two different attorneys listed in a single row, which should be chosen by maximum date? — 3BK, Feb 16 '17 at 07:26
@T.Meyer Just to be especially clear: Do you want one row back, or two? And if one, how would you want the aggregated data to be displayed? For example, two attorneys (B & C) worked on the case of client 123456 on 01/01/2017, and therefore each receives 50% credit. So would you want to see Client_Code, Client_Name, Billing_Name(Aggregated, maybe with the percentage included as part of the value), Eff_Date? — 3BK, Feb 16 '17 at 07:34
Sorry. I'm self teaching this stuff so it's hard for me to explain. I update the table above. I want to grab the rows based on max date, which means in the above example it would pull 2 rows. If we did top 1, then it only returns one of those two rows I need. Again, sorry for the confusion. — T. Meyer, Feb 16 '17 at 07:59
I forgot to answer the second part of your question. I want to return each column with the Percentage as a separate column, not combined with the aggregated max date. — T. Meyer, Feb 16 '17 at 16:08
@T.Meyer Sorry for the delay in my reply. I had a very busy weekend. I've updated my answer to explain how you can use the full functionality of the `TOP` clause to get what you're after. — 3BK, Feb 19 '17 at 07:51
This is brilliant! It seems to work. I need to vet some of my results and make sure, but this seems SO MUCH simpler than what I was doing. I really appreciate the help. I've never seen TOP WITH TIES before, so this must be way beyond where I'm at with sql. — T. Meyer, Feb 21 '17 at 20:31
If I wanted to change the query so that (HBM_CLIENT.CLIENT_CODE = '065011') was (HBM_CLIENT.CLIENT_CODE LIKE @ClientNumber + '%') , would this still work. So if the end user wanted to search for 06501* to grab any client number with a different last digit, it would pull up that. Right now, it works, but it only shows me one client's data. But there might be clients with the number 065010, 065011, 065012, etc. — T. Meyer, Feb 21 '17 at 23:54
At the moment, it's built to work for one customer, because that was what I understood from the original question. You could, of course, play around with this, and I'm sure you could also find a simple solution to allow recursive or dynamic searching. — 3BK, Feb 22 '17 at 06:10

Max Date/Case Statement

1 Answers1