First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. The table represents completed tasks, the columns are:
TaskID - - - 1. On-site DateStart | 2. Return-to-base DateEnd | 3. Chargeable Type - - - - - 4. Complaint Reference 5. Rebook Requester Approver Cost ...
Reference is used when the customer provides their own ID for the task, typically this is for example "THR#000123123" or "IX#01212". It is always in the format "ABC#123".
Now, I need to categorise these into multiple arbitrary categories and subcategories based on only the type and reference fields:
Chargeable (type = 3)
By company IX (reference like "IX#%")
All other Chargeable
Non-chargeable (type <> 3)
By company THR (reference like "THR#%")
Return-to-base (type = 2)
On-site (type = 1)
Return to base (type = 2)
All other non-Chargeable
A task can only belong to one category, so if a task for company THR was "return-to-base", it must only be under category 2.1.1, not 2.2. I realise these categories seem very arbitrary but this structure represents the most useful break-down of almost any selection of tasks from the table.
My question is, is it possible to create a view that contains the TaskID and its category? Or should I just implement this logic in the front-end after selecting every TaskID, Type and Reference?