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

    Tripp Henderson

      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

      )