0

I have the following result set generated from the query below

PATIENTID  INVOICENO  SVCDATE
14140      61531      2021-11-15 00:00:00.000
14140      64140      2021-12-09 00:00:00.000
14140      64478      2021-12-15 00:00:00.000


SELECT 
    WO.PATIENTID, 
    WO.INVOICENO, 
    WO.SVCDATE 
FROM AR1ORDW WO 
WHERE WO.BILLTYPE = 'P' 
AND WO.SVCDATE <= '12/31/2021' 
AND WO.PATIENTID = 14140
GROUP BY WO.PATIENTID, WO.SVCDATE, WO.INVOICENO 
ORDER BY WO.PATIENTID, WO.SVCDATE;

Please notice the 2 records returned for the month of December (current month).

I need to select/show only one record for the current month and it has to be the last date (max date), as follows:

PATIENTID  INVOICENO  SVCDATE
14140      61531      2021-11-15 00:00:00.000
14140      64478      2021-12-15 00:00:00.000

I would like to modify the query I have to return only one record for the current month and it should be MAX(DATE)

Dale K
  • 25,246
  • 15
  • 42
  • 71
Luis Cabrera
  • 569
  • 1
  • 7
  • 18
  • Use `ROW_NUMBER() OVER (PARTITION BY EOMONTH(SVCDATE) ORDER BY SVCDATE DESC)` – Charlieface Dec 19 '21 at 02:55
  • Should I add the ROW_NUMBER()... after the GROUP BY ?? – Luis Cabrera Dec 19 '21 at 10:58
  • In the `SELECT`. Then put the whole thing in a derived table/subquery and filter it `WHERE rownum =1` – Charlieface Dec 19 '21 at 10:59
  • `AND WO.SVCDATE <= '12/31/2021' ` This filter might be sufficient for a small set of development data but it will eventually NOT be efficient (or correct) when your table has many rows over many years. You need a better filter for "this month". Don't create a problem for someone else to fix at a later date. – SMor Dec 19 '21 at 12:15
  • @SMor you are correct. Can you suggest how to use a more efficient filter for this month? – Luis Cabrera Dec 19 '21 at 14:33
  • Use an open-range instead: AND WO.SVCDATE < DATEADD(day, 1, EOMONTH(getdate())) – Jeff Dec 19 '21 at 17:02

0 Answers0