Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
|
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.
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
Or maybe
WHERE
NOT ( [Subspace Expire] < today() ) //Already expired excluded
AND
(
( [Subspace Expire] <= addyears(today(),1) ) //include
OR
( [Subspace Commence] <= addyears(today(),1) ) // include
)
;