Forum Discussion

Hariharan_R's avatar
Hariharan_R
Icon for Solution Sage rankSolution 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
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

Share data from both tables such that i can paste those in an Excel file.  Also, show the exact result you are expecting.

Hariharan_R's avatar
Hariharan_R
Icon for Solution Sage rankSolution 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
    Icon for Super User rankSuper User
    7 years ago

    Hi,

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

    • Hariharan_R's avatar
      Hariharan_R
      Icon for Solution Sage rankSolution Sage
      7 years ago

      Hi 

       

        dates are in mm/dd/yyyy format.

       

      Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper 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.