Forum Discussion

Hariharan_R's avatar
Hariharan_R
Solution Sage
7 years ago

Filter and Dynamic Result

Hi      I have below tables and i need to get "Default/Bonus" (red colour columns) column on Trans table. Also mentioned the logic for the columns.     We will be using RLS to filter the student....
Need Help
Hariharan_R's avatar
Hariharan_R
Solution Sage
7 years ago

Hi

 

Table: Subject

Subject
StudentSubject
AS1
BS2
CS1

 

Score
StartDateEndDateYearDefaultBonusSubject
3/1/2019 1:003/3/2019 4:00201956S1
2/1/2018 1:004/4/2018 4:0020181110S1
4/1/2019 1:004/3/2019 4:00201942S2
2/1/2018 1:003/6/2018 4:0020181213S2

 

IDCourseDateTime
13/1/2019 2:00
23/1/2019 3:00
13/3/2019 3:00
23/4/2019 3:00
33/4/2019 0:00
43/4/2018 0:00
51/1/2018 1:00


If Student A logs in the below result.
Expected result is as like below.

IDCourseDateTimeDefault/BonusCourseDateTime + (Default/Bonus)
13/1/2019 2:0063/1/2019 8:00
23/1/2019 3:0063/1/2019 9:00
13/3/2019 3:0063/1/2019 9:00
23/4/2019 3:0053/4/2019 9:00
33/4/2019 0:0053/4/2019 5:00
43/4/2018 0:00103/4/2018 10:00
51/1/2018 1:00111/1/2018 0:00

 

 

If Student B logs in

IDCourseDateTimeDefault/BonusCourseDateTime + (Default/Bonus)
13/1/2019 2:0043/1/2019 6:00
23/1/2019 3:0043/1/2019 7:00
13/3/2019 3:0043/1/2019 7:00
23/4/2019 3:0043/4/2019 7:00
33/4/2019 0:0043/4/2019 4:00
43/4/2018 0:00133/4/2018 13:00
51/1/2018 1:00121/1/2018 13:00

 

The logic for the Default /Bonus column should be

 

if(CourseDateTime>Score.StartDate
&& CourseDateTime<Score.EndDate, Bonus, Default)

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Are the dates in dd/mm/yyyy or mm/dd/yyyy format?

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    7 years ago

    Hi 

     

      dates are in mm/dd/yyyy format.

     

    Thanks

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      In the third table, there is no Student/Subject column.  So then how would we know what default/bonus column rule to apply.

      • Hariharan_R's avatar
        Hariharan_R
        Solution Sage
        7 years ago

        Hi

         

           Third tables datetime column should compare with score table.

         

        For example, if third table's datetime value is between the score.StartDate and Score.EndDate then Bonus value else default value. It also should consider the year of third table and year of score table.

         

        condition - if(CourseDateTime>Score.StartDate
        && CourseDateTime<Score.EndDate, Bonus, Default)