I am trying to use the "Create NEW SQL view" function in Geoserver to create a new view, in which I wish to show a subset of records from a table in MSSQL based on a value in another table. I have two layers in the same MSSQL DATABASE one with some geometries ("LER_REQUEST_MAP") and another with some needed information ("LER_REQUEST_V2"). One of the informations in the later is an ESRI based timestamp field called "DIG_PERIOD_FROM".
I wish to use my view to:
a) join these two tables (INNER JOIN?), so that I get the information on DIG_Period_FROM to my geometry layer (LER_REQUEST_MAP) without compromising the original table.
b) SELECT * from "the new joined table" where the field DIG_PERIOD_FROM > CURRENT_DATE
Is this possible?