SQL Server Performance Forum – Threads Archive
Stored Procedure Day Wise
I want to develop a Stored procedure for -Display a list of user and the total hour which spent on each day including day(e.g. Monday, TuesDay….) with date range. Table name is : JobLogfields are : JobNo, UserCode, CheckInDataTime, CheckOutDateTime
and I want the result on
*Total hour calc base on Check In and Check out.
*Need to allow to set the Date From and Date To.
eg 2007-08-01 -> 2007-08-31. Query should display out 31 colum for each user and count the total hour spent on each day including day(e.g. Monday, TuesDay….). Like Following Format :
————————————————————————
UserCode | Monday | TuesDay | Wednesday | Thursday | Friday | Saturday
| 08/01 | 08/02 | 08/03 | 08/04 | 08/05 | 08/06
————————————————————————
XXXXXX | 4 8 3 5 1 0 YYYYYY | 10 7 6 4 8 11
Like that. Can anybody help me to build this query/Stored Procedure? Regards
Ranjan
Why not continue over therehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=20349 The day (Monday, TuesdDay) etc is based on CheckInDateTime or CheckOutDateTime ?
KH
select UserCode,
[Monday] = sum(case when datename(weekday, CheckInDateTime = ‘Monday’)
then datediff(hour, CheckInDateTime, CheckOutDateTime) else 0 end),
[Tuesday] = sum(case when datename(weekday, CheckInDateTime = ‘Tuesday’)
then datediff(hour, CheckInDateTime, CheckOutDateTime) else 0 end),
. . .
from JobLog
group by UserCode KH
This is on CheckInDateTime. Regards
Ranjan
In order for us to be able to help you, you should provide more information. Check this out:http://www.aspfaq.com/etiquette.asp?id=5006 —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
]]>