SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help with case statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Novice2Sql
Starting Member

33 Posts

Posted - 09/24/2012 :  05:03:25  Show Profile  Reply with Quote
k

Edited by - Novice2Sql on 10/05/2012 03:07:46

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 09/24/2012 :  05:49:37  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 09/24/2012 :  06:51:22  Show Profile  Reply with Quote
Can you just provide Sample Input and Expected Output?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/24/2012 :  13:19:48  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/24/2012 :  13:23:25  Show Profile  Reply with Quote
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/

Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 09/25/2012 :  01:54:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/25/2012 :  12:12:15  Show Profile  Reply with Quote
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/

Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 09/27/2012 :  00:49:40  Show Profile  Reply with Quote
Yes, it is the data in the workdate field.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  10:01:34  Show Profile  Reply with Quote
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/

Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 10/01/2012 :  02:34:21  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/01/2012 :  10:23:18  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000