1 Reply Latest reply on Dec 12, 2019 4:33 PM by swaroop.gantela

# Calculation of monthly totals using End Date and Start Date that can be before and after that month

We are trying to calculate certain Bionutrition activities for a report and the activity is logged as follows

Participants are entered with a start date and end date, then for every day within that time window they receive a number of Meals and Snacks depending on what is outlined in the protocol (ex Outpatient Phase A receives 2 meals 1 snack each day, while Inpatient Phase B receives 4 meals 2 snacks each day).

Below is how we are calculated the number of Meals and Snacks (Meals is the first calculation, Snacks is second)

The issue we are having is that we cannot use the below to calculate a specific month's Meals/Snacks as the only date fields captured in this database table are the start and end date. So if the start date were 1/1/19 and end date were 4/1/19, how can we calculate the Meals and Snacks for February for example?  Is there a way to create an arbitrary month variable to use?

MEALS

sum (

if not ISNULL([fend_dt]) and not ISNULL([fstart_dt])and
source = "food" then
if [outp_phase_a] ==  "YES" then

[outp_per_person_meal_a] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

elseif [outp_phase_b] ==
"YES" then

[outp_per_person_meal_b] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

elseif [outp_phase_c] ==
"YES" then

[outp_per_person_meal] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) +
1)

elseif [inp_phase_a] ==
"YES" then

[inp_per_person_meal_a] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) +
1)

elseif [inp_phase_b] ==
"YES" then

[inp_per_person_meal_b] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

elseif [inp_phase_c] ==
"YES" then

[inp_per_person_meal_c] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

END

END

)

SNACKS

sum (

if not ISNULL([fend_dt]) and not ISNULL([fstart_dt])and
source = "food" then
if [outp_phase_a] ==  "YES" then

[outp_per_person_snack_a] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

elseif [outp_phase_b] ==
"YES" then

[outp_per_person_snack_b] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

elseif [outp_phase_c] ==
"YES" then

[outp_per_person_snack_c] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) +
1)

elseif [inp_phase_a] ==
"YES" then

[inp_per_person_snack_a] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) +
1)

elseif [inp_phase_b] ==
"YES" then

[inp_per_person_snack_b] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

elseif [inp_phase_c] ==
"YES" then

[inp_per_person_snack_c] *
(DATEDIFF("day",[fstart_dt],[fend_dt]) + 1)

END

END

)

Tripp,