Author Topic: Letter to 18 year olds the following week  (Read 22878 times)

Offline georgec

  • Associate
  • **
  • Join Date: Aug 2019
  • Posts: 2
  • Forum Citizenship: +0/-0
Letter to 18 year olds the following week
« on: 29 Aug 2019 04:26:19 am »
I would like some help filtering for customers turning 18 years old next week.

Offline oscarca

  • Community Leader
  • *****
  • Join Date: Jul 2018
  • Posts: 210
  • Forum Citizenship: +0/-0
Re: Letter to 18 year olds the following week
« Reply #1 on: 29 Aug 2019 08:46:11 am »
Do you have a column which specifies what birthdate ?

Birthdate in_range{20010902:20010908)

might not be the best solution thoug, since Days can vary..
« Last Edit: 29 Aug 2019 09:04:02 am by oscarca »

Offline georgec

  • Associate
  • **
  • Join Date: Aug 2019
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Letter to 18 year olds the following week
« Reply #2 on: 29 Aug 2019 10:09:59 am »
My dob field shows as "Date of Birth (MAST)"

This will be part of my filter so that the report (letter) scheduled for each Monday at 7:00AM, will select and print individuals whose 18th birthday falls on one day that week.

Offline oscarca

  • Community Leader
  • *****
  • Join Date: Jul 2018
  • Posts: 210
  • Forum Citizenship: +0/-0
Re: Letter to 18 year olds the following week
« Reply #3 on: 02 Sep 2019 03:55:17 am »
You have to create a data item that iterates through all the birth dates and matches the ones that are 18 years old in the upcoming week. So you have to convert all the Birth days into birth weeks instead. For exempel if your birth day is 1985-09-02 then the birth week would be 1985-36. To accomplish this you have to extract year and week from Birth day and the same for current_date. After that you create a case statement to find matches, so when you have a match, it will return a 1 otherwise it will return a 0. Last of all you filter the report on [Birthdays next week] = 1.

case
when
cast(extract(year,[Birthdate]);varchar(4)) + '-' + cast(_week_of_year ([Birthdate]);varchar(2)) = cast(_add_years(current_date,-18);varchar(4)) + '-' + cast(cast(_week_of_year (current_date);integer)+1;varchar(2))
then 1
else 0
end

 


       
Twittear