SQL Query to return Month days as Columns and Total Hours as Row

Question

i am working on an Attendance report were I need to create a SQL Query from one table to return the attendance of employees net hours worked over the month. Day of the month should be as a column and in the rows should be the total Hours of employee.

The Table is having 6 Columns ( Employee Name, Dept , Position, Time In , Time Out and Total Hours

Picture for Selecting * From the Attendance Table

i want to return the values as the following:

EmployeeName | 1st | 2nd | 3rd | 4th | ...... |30 June

---

Emp 1 | 10:30 | | 10:40 | | 10:10 | | 10:21 |

The Days column should be returned in a parameter so i can add it to crystal report.

Table Structure if you can advise please.

Thanks in advance

Answer 1

You can use CASE statment like this:

        SELECT EmployeeName,
               (CASE WHEN EXTRACT(YEAR FROM DATE) = 2017 AND EXTRACT(MONTH FROM DATE) = 6 AND EXTRACT(DAY FROM DATE) = 1 then totalHours ELSE NULL END) AS "01/06",
               (CASE WHEN EXTRACT(YEAR FROM DATE) = 2017 AND EXTRACT(MONTH FROM DATE) = 6 AND EXTRACT(DAY FROM DATE) = 2 then totalHours ELSE NULL END) AS "02/06",
               .
               .
               .
               (CASE WHEN EXTRACT(YEAR FROM DATE) = 2017 AND  EXTRACT(MONTH FROM DATE) = 6 AND EXTRACT(DAY FROM DATE) = 30 then totalHours ELSE NULL END) AS "30/06"

FROM Attendance

So, for each day a new column will be created.

Answer 2

I used something like this

        CREATE TABLE `AxsLog` (
`id`    integer NOT NULL UNIQUE,
`Logon` text NOT NULL DEFAULT current_timestamp,
`Logoff`    text NOT NULL DEFAULT current_timestamp,
`Duration`  text NOT NULL DEFAULT 0,
`SysDat`    text NOT NULL DEFAULT current_timestamp,
PRIMARY KEY(`id`) );

You can easily add an FK column for each row in your user table.

Keep the logon id for each entry, then update that line on logoff

UPDATE  AxsLog
Set Duration= (SELECT sum( strftime('%s', logoff) - strftime('%s', logon) ) 
/60 FROM AxsLog WHERE id= 1 )
WHERE id= 1 ; 

To build a report, use something like this. This query only gives a total per month.

select total(Duration)  
FROM AxsLog where substr(sysdat,6,2) = 'month' 

Answer 3

your requirement can be fulfill by using crosstab report or if u want to achieve in sql then use pivot