CREATE view in Geoserver based on current timestamp

Question

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?

Answer 1

Yes - you can create a view based on anything table(s) you like assuming you have permission to see them. I expect you will end up with something like:

select * from table1,table2 where table1.id = table2.id and digperiod > GETDATE();