Complicated date filters

[#1]

Lease_ID	Subspace_ID	Subspace Commence	Subspace Expire

436	500	11/20/2006	12/31/2015
436	638	1/1/2016	5/31/2017
436	639	6/1/2016	5/31/2017
436	640	6/1/2016	12/31/2026

I have 2 dates Commence date and Expire date.

From Subspace Expire exclude everything which is already expired and include only Subspace Expire date which falls within the rolling next 12 months.

From Subspace Commence date include everything less than today also include Subspace Commence date which falls within the rolling next 12 months.

So here I should get 638,639,640 as Subspace Coomence date falls in between rolling 12 months eventhough Subspace Expire for 640 doesnt fall between rolling next 12 months.Is it doable ?

Thank you.

[martinpohl]

Hello,

so your where statement should be

where ([Subspace Expire] > today()                //only in future

and {Subspace Expire] <= date(addyears(today(),1))) and          //only until next 12 months

[Subspace Commence] <= date(addyears(today(),1))               //only dates less than next 12 months

addyears is by day, so from today (3/24/2016) addyears 1 gets 3/24/2017

so check if you need additionally monthstart or monthend

Regards

[swuehl]

Or maybe

WHERE

     NOT ( [Subspace Expire] < today() )           //Already expired excluded

     AND

     (

          ( [Subspace Expire] <= addyears(today(),1) )   //include

          OR

          (  [Subspace Commence] <= addyears(today(),1) )   // include     

     )

;