Time Sheet Status – Webi Exercise

It seems to be a good Webi exercise (for beginners).

Employees submit weekly time sheets. Each time sheet has time sheet lines. Time sheet line can have one of three statuses: Submitted, Approved and Rejected.

The status of the time sheet is defined by the statuses of the time sheet lines:

  • When for a week one or more lines are rejected the whole time sheet status is “Rejected”.
  • When for a week one or more lines are submitted, there are no lines rejected, but there can be lines approved, the time sheet status is “Submitted”.
  • When all lines are approved the time sheet status is “Approved”.
  • When there are no time sheet lines, the status is “Missing”.

Solution…

Status Code

= If [Status]="Rejected" Then 1
  Else If [Status]="Submitted" Then 2
  Else If [Status]="Approved" Then 3

Timesheet Status

= If Min([Status Code])=1 Then "Rejected"
  Else If Min([Status Code])=2 Then "Submitted"
  Else If Min([Status Code])=3 and Max([Status Code])=3 Then "Approved"
  Else "Missing"

2 thoughts on “Time Sheet Status – Webi Exercise

  1. Maria

    Agree except status Missing: by your logic timesheet is missing if timesheet exists, but timesheetlines are missing. But in reality it is missing if there is no timesheet for particular week. So I would just replace column Timesheet with column Week in your example and point that there should be another query (select Week from D_DATE group by Week) which must be merged with your original query by Week.

    Like

    Reply
    1. dmytro Post author

      Yes this is a good idea to replace column “Timesheet” with “Week”. For the the exercise it would be ok to emulate missing time sheet with missing values in “Line” and “Status”. Thank you!

      Like

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s