SQL Query Construction!!! Pls Help

[Forri]

HI all

I have a table to store an employee's Leave application. It stores the first day as a date, the duration in days and a flag as approved or not!

I would like to show these in a table in 3 section, being: Approved (if the flag approved is signed as 1), Waiting (if the flag approved is 0 and the first day is greater than current date) and Not Approved( if the flag is 0 and the first Day is less than the current Date).

How can i construct a query for this, so i can show the 3 sections above mentioned in order of first day and grouped by aproval!? Any help please! very much appreciated

Thanks
Nick

 

[rac2]

Different RDBMS systems have different solutions to this problem but ANSI SQL offers the CASE expression. I use MS SQL Server.

SELECT 
    CASE
       WHEN flagApproval = 0 AND date_leave_starts < getdate() THEN 'Not Approved'
       WHEN flagApproval = 0 AND date_leave_starts > getdate() THEN 'Waiting'
       WHEN flagApproval = 1 THEN 'Approved'
       ELSE 'What the heck?'
    END
FROM LeaveRequests

 

[emblem]

Hi Nick,

Please be aware of a subtle data problem in your table, If the first day off is a Friday and the duration = 3, does that mean the employee is returning to work on Monday or Wednesday? You cannot necessarily add duration to start date and get return date.

 

[Forri]

yes that's true! How can i skip the weekends so i would not include them within the result!