I need to run a reasonably complex query, and I'm wondering whether it would be better to code it in SQL, or should I try to get the DAL to provide the data (or maybe there's another way). There's no right or wrong answer, but I'd welcome opinion on the best approach.
Simply put, consider two tables, "tasks" and "periods". As well as an id field, tasks has a 'name' field too. Each task has multiple periods (of work) associated with it, and the periods table has id, date, start_time, end_time (and other fields). What I want to do is produce a summary that lists: task_name total_time ...for each task. That is, for each period, find the duration by subtracting the start_time from the end_time, and total all durations for each task in turn. I can write this in SQL, but then I lose the advantages of the DAL, as well as potentially losing database portability. I'm not even sure if this is doable in the DAL. What would be the best approach? -- "You can have everything in life you want if you help enough other people get what they want" - Zig Ziglar. Who did you help today?
