| Author |
 Topic  |
|
|
Novice2Sql
Starting Member
33 Posts |
 Posted - 09/24/2012 : 05:03:25
|
| k |
Edited by - Novice2Sql on 10/05/2012 03:07:46
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1715 Posts |
Posted - 09/24/2012 : 05:49:37
|
Hi, erase that highlighted part..........
SELECT shopcode, ID, EMPLOYEE, SUM(Mon) as Monday, SUM(Tue) AS Tuesday , SUM(Wed) AS Wednesday , SUM(Thu) AS Thursday , SUM(Fri) AS Friday , SUM(Sat) AS Saturday, SUM(Sun) AS Sunday,SUM (ISNULL(VAL3,0)) TotalDirectForWeek
From (SELECT WorkOrder.ShopCode SHOPCODE, contractor_employees.Employee_NO AS ID,
Contractor_Employees.Last_Name + ' ' + Contractor_Employees.First_Name EMPLOYEE,
CASE WHEN DATENAME(dw,WORKDATE ,getdate())='Monday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Mon,
CASE WHEN DATENAME(dw,WORKDATE)='Tuesday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Tue,
CASE WHEN DATENAME(dw,WORKDATE)='Wednesday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Wed,
CASE WHEN DATENAME(dw,WORKDATE)='Thursday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Thu,
CASE WHEN DATENAME(dw,WORKDATE)='Friday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Fri,
CASE WHEN DATENAME(dw,WORKDATE)='Saturday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Sat,
CASE WHEN DATENAME(dw,WORKDATE)='Sunday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Sun,
0 AS VAL3
FROM Contractor_Employees Contractor_Employees ,
--
Chandu |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1715 Posts |
Posted - 09/24/2012 : 06:51:22
|
Can you just provide Sample Input and Expected Output?
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48087 Posts |
Posted - 09/24/2012 : 13:19:48
|
so you want dates for week as columns? so what if data retrieved has more than one week involed? or is it like taking data for entire date range and show all dates in column?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48087 Posts |
Posted - 09/24/2012 : 13:23:25
|
quote:
Originally posted by Novice2Sql
Oh thanks, I was testing and left that in by mistake...the original query doesn't include the getdate..below is the query that is working but I would like to see dates versus the day of the week..
SELECT shopcode, ID, EMPLOYEE, SUM(Mon) as Monday, SUM(Tue) AS Tuesday , SUM(Wed) AS Wednesday , SUM(Thu) AS Thursday , SUM(Fri) AS Friday , SUM(Sat) AS Saturday, SUM(Sun) AS Sunday,SUM (ISNULL(VAL3,0)) TotalDirectForWeek
From (SELECT WorkOrder.ShopCode SHOPCODE, contractor_employees.Employee_NO AS ID,
Contractor_Employees.Last_Name + ' ' + Contractor_Employees.First_Name EMPLOYEE,
CASE WHEN DATENAME(dw,WORKDATE)='Monday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Mon,
CASE WHEN DATENAME(dw,WORKDATE)='Tuesday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Tue,
CASE WHEN DATENAME(dw,WORKDATE)='Wednesday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Wed,
CASE WHEN DATENAME(dw,WORKDATE)='Thursday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Thu,
CASE WHEN DATENAME(dw,WORKDATE)='Friday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Fri,
CASE WHEN DATENAME(dw,WORKDATE)='Saturday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Sat,
CASE WHEN DATENAME(dw,WORKDATE)='Sunday' THEN SUM(workOrder_ManHours_Detail.MH_Regular + WorkOrder_ManHours_Detail.MH_Overtime) END AS Sun,
0 AS VAL3
FROM Contractor_Employees Contractor_Employees ,
using conditions based on datepart of dw are dependent on server regional locale settings
see how can implement same logic in server independent way
http://visakhm.blogspot.com/2012/08/creating-server-independent-day.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 09/25/2012 : 01:54:34
|
| The report should show the employee daily hours work, then the last column shows the total for the week. I prefer to have the dates in the column versus the day of the week.. thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48087 Posts |
Posted - 09/25/2012 : 12:12:15
|
quote:
Originally posted by Novice2Sql
shopcode EMPLOYEE 9/10/2012 9/11/2012 9/13/2012 9/14/2012 9/15/2012 9/16/2012 9/17/2012 TotalDirectForWeek
x John Doe 0 0 0 0 0 3.00 3.00 6.00
This is what I would like the output to look like
so is week date range the inputs?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 09/27/2012 : 00:49:40
|
| Yes, it is the data in the workdate field. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48087 Posts |
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 10/01/2012 : 02:34:21
|
Unfortunately, I don't have the rights to create any types of table on this system.. so I need a solution that would not require me to create a table..temp or perm..
so do you have another suggestion?
Thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48087 Posts |
Posted - 10/01/2012 : 10:23:18
|
quote:
Originally posted by Novice2Sql
Unfortunately, I don't have the rights to create any types of table on this system.. so I need a solution that would not require me to create a table..temp or perm..
so do you have another suggestion?
Thanks
The example in the link doesnt create any table. its just an inline query which does the pivot
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
help with case statement
