1 Reply Latest reply: Oct 27, 2009 11:47 PM by John Witherspoon RSS

    The best way to write SQL for analysing utilisation

      Hi guys,

      I am preparing a utilisation dashboard, where I need to show utilised chargeable time and non-chargeable, including breakdown of annual leave, sickness, training etc.

      I'm thinking there are two ways to wrtie my SQL.

      1. create a new column for each category of work, using case or decode statements
      2. use a case statement to produce a flag for each row to indicate what that line is (holiday, sick, training - or maybe two to show if it is chargeable, as well as a breakdown of cetegories)

      Has anyone out there produced similar figures? If so, is there a better way of doing it?

      Would it be easier to generate a pie chart if each row has an attribute, rather than creating lots of columns for each row (where the time will be in one of those columns)

       

      any advice is appreciated.

      Regards,

      Matt

        • The best way to write SQL for analysing utilisation
          John Witherspoon

          OK, so you have a table of work records, presumably with things like the employee, the number of hours, the job done, and so on. The hours on a row fall into single a category - annual leave, sick, training, holiday, regular work, and so on. A category either is or is not chargeable. For instance, all regular work is chargeable, all sick time is not chargeable. Or are the rules more complex?

          I would personally, at least in QlikView, add two fields to the work table - "Category" and "Chargeable?". I would use nice descriptive fields for the Category, and dual('N',0) and dual('Y',1) for "Chargeable?". Chargeable is then multi-purpose. In a list box, it will display 'Y' and 'N' for human readability. But you can use it directly in an if statement: if(Chargeable?,blah blah blah, something else). You can use it in multiplication: sum(Chargeable? * Hours * Rate). You can use it in set analysis: sum({<Chargeable?={Y}>} Hours * Rate).

          I probably wouldn't create the field in the SQL, though. I'd just load basic data out of SQL, and do the field manipulation in QlikView. I guess I figure that if all of the SQL is simple, that's one less language that someone needs to be an expert in to maintain the application.

          I wouldn't use separate flags for each category because that becomes a nightmare when you just want to display hours by category. I wouldn't use separate hours for each category ("Sick Hours" and so on) because it would be very difficult when you just want to add up all hours, or all chargeable hours. And it should be simple and fast enough to extract sick hours, for instance, given a category field: sum({<Category={Sick}>} Hours). Mind you, QlikView's compression will probably eliminate most of the potential problems if you want to have a category AND flags, and a generic Hours field PLUS sick hours, vacation hours and so on. Just be careful when coding to make sure everything is in sync.