help with case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with case statement

Author

Topic

Novice2Sql
Starting Member

33 Posts

Posted - 2012-09-24 : 05:03:25

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-24 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-24 : 06:51:22

Can you just provide Sample Input and Expected Output?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-24 : 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

52326 Posts

Posted - 2012-09-24 : 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 - 2012-09-25 : 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

52326 Posts

Posted - 2012-09-25 : 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 - 2012-09-27 : 00:49:40

Yes, it is the data in the workdate field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 10:01:34

then you could do like
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-01 : 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

52326 Posts

Posted - 2012-10-01 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources