sql function
-
I have created sql stored procedure to calculate work hours of employees in a week.EmployeeID,FromDate,ToDate,Department,Designation are the parameters passed.My output is: Day EmpID EmpName FirstTimeIn LastTimeOut WorkHours Break ------------------------------------------------------------------------- Wednesday 2855747 AlexanderMathew 9:01:09 AM 6:15:14PM 8:59:05 Thursday 2855747 AlexanderMathew 9:45:09 AM 6:45:09 PM 8:00:00 Friday 2855747 AlexanderMathew 9:08:09 AM 7:20:09 PM 9:00:00 Saturday 2855747 AlexanderMathew 9:30:09 AM 6:30:09 PM 8:00:00 Sunday 2855747 AlexanderMathew 9:32:09 AM 6:28:09 PM 8:00:00 Monday 2855747 AlexanderMathew 9:20:09 AM 6:25:09 PM 8:05:00 Tuesday 2855747 AlexanderMathew 9:02:09 AM 6:02:09 PM 8:30:00 But the problem is that,in my output 'day' is shown as row.But is there any way to display day as columns.ie: eg: EmpName Friday Saturday Sunday Monday Tuesday Wednesday WorkHours ------------------------------------------------------------------------- Alexander 07:11:18 Saturday Sunday 06:20:41 08:06:28 03:23:27 25:01:54 This stored procedure is for reporting.Hope you will help me to overcome this problem.. Thanks in advance
-
I have created sql stored procedure to calculate work hours of employees in a week.EmployeeID,FromDate,ToDate,Department,Designation are the parameters passed.My output is: Day EmpID EmpName FirstTimeIn LastTimeOut WorkHours Break ------------------------------------------------------------------------- Wednesday 2855747 AlexanderMathew 9:01:09 AM 6:15:14PM 8:59:05 Thursday 2855747 AlexanderMathew 9:45:09 AM 6:45:09 PM 8:00:00 Friday 2855747 AlexanderMathew 9:08:09 AM 7:20:09 PM 9:00:00 Saturday 2855747 AlexanderMathew 9:30:09 AM 6:30:09 PM 8:00:00 Sunday 2855747 AlexanderMathew 9:32:09 AM 6:28:09 PM 8:00:00 Monday 2855747 AlexanderMathew 9:20:09 AM 6:25:09 PM 8:05:00 Tuesday 2855747 AlexanderMathew 9:02:09 AM 6:02:09 PM 8:30:00 But the problem is that,in my output 'day' is shown as row.But is there any way to display day as columns.ie: eg: EmpName Friday Saturday Sunday Monday Tuesday Wednesday WorkHours ------------------------------------------------------------------------- Alexander 07:11:18 Saturday Sunday 06:20:41 08:06:28 03:23:27 25:01:54 This stored procedure is for reporting.Hope you will help me to overcome this problem.. Thanks in advance
Just have a look through and see whether it solves your need, Approach - 1 (using subquery) sample table, create table #tm (days varchar(10), empid varchar(10), empname varchar(100), intime varchar(20), outtime varchar(20), workhours varchar(20)) sample records, insert into #tm select 'Wednesday', '2855747', 'AlexanderMathew', '9:01:09 AM', '6:15:14PM', '8:59:05' union select 'Thursday','2855747','AlexanderMathew','9:45:09 AM','6:45:09 PM','8:00:00' union select 'Friday','2855747','AlexanderMathew','9:08:09 AM','7:20:09 PM','9:00:00' union select 'Saturday','2855747','AlexanderMathew','9:30:09 AM','6:30:09 PM','8:00:00' union select 'Sunday','2855747','AlexanderMathew','9:32:09 AM','6:28:09 PM','8:00:00' union select 'Monday','2855747','AlexanderMathew','9:20:09 AM','6:25:09 PM','8:05:00' union select 'Tuesday','2855747','AlexanderMathew','9:02:09 AM','6:02:09 PM','8:30:00' select distinct empid, empname, (select workhours from #tm b where a.empid = b.empid and days = 'Sunday') sunday, (select workhours from #tm b where a.empid = b.empid and days = 'Monday') Monday, (select workhours from #tm b where a.empid = b.empid and days = 'Tuesday') Tuesday, (select workhours from #tm b where a.empid = b.empid and days = 'Wednesday') Wednesday, (select workhours from #tm b where a.empid = b.empid and days = 'Thursday') Thursday, (select workhours from #tm b where a.empid = b.empid and days = 'Friday') Friday, (select workhours from #tm b where a.empid = b.empid and days = 'Saturday') Saturday from #tm a disadvantage of approach-1: --------------------------- 1) if the table has huge records then the above will take time for execution Approach - 2 ------------- create table #tm1 (empid varchar(10), empname varchar(100), sunday varchar(20), monday varchar(20), tuesday varchar(20), wednesday varchar(20), thursday varchar(20), friday varchar(20), saturday varchar(10)) insert into #tm1 (empid, empname) select distinct empid, empname from #tm update a set a.sunday = b.workhours from #tm1 a join #tm b on a.empid = b.empid and b.days = 'Sunday' update a set a.monday = b.workhours from #tm1 a join #tm b on a.empid = b.empid and b.days = 'Monday' update a set a.tuesday = b.workhours from #tm1 a join #tm b on a.empid = b.empid and b.days = 'Tuesday' update a set a.wednesday = b.workhours from #tm1 a join #tm b on a.empid = b.empid and b.days = 'Wednesday' update a set a.thursday = b
-
I have created sql stored procedure to calculate work hours of employees in a week.EmployeeID,FromDate,ToDate,Department,Designation are the parameters passed.My output is: Day EmpID EmpName FirstTimeIn LastTimeOut WorkHours Break ------------------------------------------------------------------------- Wednesday 2855747 AlexanderMathew 9:01:09 AM 6:15:14PM 8:59:05 Thursday 2855747 AlexanderMathew 9:45:09 AM 6:45:09 PM 8:00:00 Friday 2855747 AlexanderMathew 9:08:09 AM 7:20:09 PM 9:00:00 Saturday 2855747 AlexanderMathew 9:30:09 AM 6:30:09 PM 8:00:00 Sunday 2855747 AlexanderMathew 9:32:09 AM 6:28:09 PM 8:00:00 Monday 2855747 AlexanderMathew 9:20:09 AM 6:25:09 PM 8:05:00 Tuesday 2855747 AlexanderMathew 9:02:09 AM 6:02:09 PM 8:30:00 But the problem is that,in my output 'day' is shown as row.But is there any way to display day as columns.ie: eg: EmpName Friday Saturday Sunday Monday Tuesday Wednesday WorkHours ------------------------------------------------------------------------- Alexander 07:11:18 Saturday Sunday 06:20:41 08:06:28 03:23:27 25:01:54 This stored procedure is for reporting.Hope you will help me to overcome this problem.. Thanks in advance
You need to use the pivot function[^], assuming you are using sql server of course.
Never underestimate the power of human stupidity RAH