Question

Boolean for data availability through date range

  • 15 August 2016
  • 2 replies
  • 0 views

Badge

I need to create a Boolean to define data available (by month) between a start and end date, with the years listed in a separate column.

As an example:

A has a start date of 20050201000000 and end date of 20060301000000

B has a start date of 20070201000000 and end date of 20070301000000

The final table should look like (1 being data available):

IDYEARJANFEBMARAPRetc ..A200501111A200611100B200701100

I would really appreciate your help and clear instructions :)


2 replies

Userlevel 2
Badge +17

Hi @heatha_featha, if you represent the dates with "%Y%m" format (e.g. 200501, 200502), they can be compared as integer values. My first inspiration is InlineQuerier use.

0684Q00000ArL31QAF.png

0684Q00000ArL6iQAF.png

SQL Query:

select a.YEAR, a.MONTH,
case when b.start_date is null then 0 else 1 end as flag
from months as a left outer join periods as b
on a.YEAR*100+a.MONTH between b.start_date and b.end_date
order by a.YEAR, a.MONTH
Userlevel 2
Badge +17

Hi @heatha_featha, if you represent the dates with "%Y%m" format (e.g. 200501, 200502), they can be compared as integer values. My first inspiration is InlineQuerier use.

0684Q00000ArL31QAF.png

0684Q00000ArL6iQAF.png

SQL Query:

select a.YEAR, a.MONTH,
case when b.start_date is null then 0 else 1 end as flag
from months as a left outer join periods as b
on a.YEAR*100+a.MONTH between b.start_date and b.end_date
order by a.YEAR, a.MONTH
this is the workspace example (FME 2016.1.1): none2none.fmw 

 

 

Reply