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 ..A200501111A200611100B200701100I would really appreciate your help and clear instructions :)
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.
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
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.
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