SQLISHARD SQLISHARD - 3 months ago 12
SQL Question

SQL code not pulling highest date before defined date: Revised

I've decided to edit this for clarity.

Below is my data with the tables.

BLDGID, LEASID, STARTDT, and BRK are in table BRKP

OCCPSTAT and SUITID are in another table called STATUS

INCATT is in another table called BILL

BLDGID LEASID STARTDT BRK OCCPSTAT SUITID INCCAT
9999 100 8/5/2011 100000 C AZ847 AVD
9999 100 8/5/2013 200000 C AZ847 AVD
9999 100 8/5/2015 300000 C AZ847 AVD
9999 100 8/5/2017 400000 C AZ847 AVD
9999 250 12/1/2012 200000 C BK497 TIL
9999 250 12/1/2016 400000 C BK497 TIL
9999 250 12/7/2020 500000 C BK497 TIL
9999 250 1/31/2023 600000 C BK497 TIL
7000 987 2/19/2016 0 C JT127 MTU
7000 987 5/19/2020 10000 C JT127 MTU
7000 987 3/18/2021 20000 C JT127 MTU
7000 987 9/4/2023 30000 C JT127 MTU


What I want is to pull ONE row for each suite ID (there are about 100 unique SUITID's per BLDGID).

In that row, I need the STARTDT to be the highest value BEFORE the date I define.

If I define 20220831 (08/31/2022) it should pull a single row in this case, that would be row 2.

If I define 20160831 (8/31/2016) it should pull only row 1.

I cannot figure out how to get it to pull just 1 row. I can get many.

I've been trying STARTDT=(max(STARTDT)<'20160831') and other variations.

The issue I'm having with no date entered, it would pull 7/5/2022 but, when I enter my date, it pulls nothing.

EDIT Again: I've tried adding the below but, it does not change the number of records returned.

ORDER BY SUITID, STARTDT DESC

SELECT TOP 1 * FROM SUITID, STARTDT

EDIT FURTHER: Example are first first 4 lines of the table. Expanded to show I'm working with a bigger table.

Answer

Often when someone wants data related to a "maximum date" the best technique is to use ROW_NUMBER() OVER()

I think this may produce what you are seeking:

SELECT
        BLDGID
      , LEASID
      , STARTDT
      , BRKPNT1
      , OCCPSTAT
      , SUITID
      , INCCAT
FROM (
        SELECT
                br.BLDGID
              , br.LEASID
              , br.STARTDT
              , br.BRKPNT1
              , le.OCCPSTAT
              , le.SUITID
              , rb.INCCAT
              , ROW_NUMBER() OVER(PARTITION BY br.BLDGID, br.LEASID, le.OCCPSTAT, le.SUITID, rb.INCCAT
                                  ORDER BY br.STARTDT DESC) AS rowno
        FROM SQLDATA.dbo.CMLEDG cm
        INNER JOIN SQLDATA.dbo.LEAS le   ON cm.BLDGID = le.BLDGID AND cm.LEASID = le.LEASID
        INNER JOIN SQLDATA.dbo.RTBILL rb ON cm.BLDGID = rb.BLDGID AND cm.LEASID = rb.LEASID
        INNER JOIN SQLDATA.dbo.BRKP br   ON cm.BLDGID = br.BLDGID AND cm.LEASID = br.LEASID
        WHERE br.STARTDT < '20160831'
        AND le.OCCPSTAT = 'c'
     ) AS d
 WHERE rowno = 1

Inside the OVER() the columns listed for the "partition by" acts a little like your "group by" in that it re-starts counting at one when that combination of values changes. So, because this is also uses a descending "order by" of the wanted date you get a value of 1 for the "latest date". When also combined with the where clause you get: the most recent date before '20160831'

There are several additional notes I would like to make:

  1. The query provided in the question has syntax errors, an AND is missing in this "= BRKP2.LEASID AND SQLDATA.DBO.BRKP.STARTDT"
  2. I don't follow this: "HAVING SQLDATA.dbo.BRKP.BLDGID & SPARM03
  3. When providing columns with an alias don't use single quotes, either use [] or "" (i.e. I suggest you reserve use of single quotes only for literals). But, also, there is no need to provide a column alias in this query because you don't actually change the column names anyway.
  4. Using tables aliases helps make queries easier to read
  5. there is no apparent reason for using GROUP BY because there are no aggregate functions being used
  6. a HAVING clause is used to filter for aggregated values that exist only after a group by, There are no aggregated values in this query so there should be no having clause. (A having clause in not a substitute for a where clause.)
  7. There is no reason I could find for joining SQLDATA.dbo.BRKP twice