get list of working hours into a readable report

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

get list of working hours into a readable report

Author

Topic

kman
Starting Member

6 Posts

Posted - 2006-11-20 : 17:00:42

Hi guys, im having bigtime problems transfomring a list of working hours into a readable report.

I got a db with a table that looks like this:

Userid Login Logout
2 2006-11-20 13:22:00 2006-11-20 22:31:00
2 2006-11-19 12:31:00 2006-11-19 17:31:00
2 2006-11-18 10:31:00 2006-11-18 17:31:00

I need to transform that data into this:

Weeknumber Monday Tuesday Wednesday Thursday Friday Saturday Sunday
46 0 0 0 0 0 7 0
47 9.15 0 0 0 0 0 5

All i got so far is this query:
SELECT DATEPART(ww, login) as weeknumber , DATENAME(dw, login) AS day, DATEDIFF(minute, login, logout) / 60.0 as hours
FROM timeliste
WHERE userid = '2'

Which isnt even close to what I want, can anyone give me some clues on what I should do to get it right?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-20 : 17:38:00

Try this

declare @timelist table
(UserId int, Login datetime, Logout datetime)
insert @timelist
select 2, '2006-11-20 13:22:00', '2006-11-20 22:31:00' union all
select 2, '2006-11-19 12:31:00', '2006-11-19 17:31:00' union all
select 2, '2006-11-18 10:31:00', '2006-11-18 17:31:00'

select [Week]
	, cast(isnull((select cast(datediff(n, Login, Logout) as decimal (5, 2)) / 60 from @timelist where datepart(ww, Login) = Weeks.[Week] and datepart(dw, Login) = 2), 0) as decimal(5,2)) as Monday
	, cast(isnull((select cast(datediff(n, Login, Logout) as decimal (5, 2)) / 60 from @timelist where datepart(ww, Login) = Weeks.[Week] and datepart(dw, Login) = 3), 0) as decimal(5,2)) as Tuesday
	, cast(isnull((select cast(datediff(n, Login, Logout) as decimal (5, 2)) / 60 from @timelist where datepart(ww, Login) = Weeks.[Week] and datepart(dw, Login) = 4), 0) as decimal(5,2)) as Wednesday
	, cast(isnull((select cast(datediff(n, Login, Logout) as decimal (5, 2)) / 60 from @timelist where datepart(ww, Login) = Weeks.[Week] and datepart(dw, Login) = 5), 0) as decimal(5,2)) as Thursday
	, cast(isnull((select cast(datediff(n, Login, Logout) as decimal (5, 2)) / 60 from @timelist where datepart(ww, Login) = Weeks.[Week] and datepart(dw, Login) = 6), 0) as decimal(5,2)) as Friday
	, cast(isnull((select cast(datediff(n, Login, Logout) as decimal (5, 2)) / 60 from @timelist where datepart(ww, Login) = Weeks.[Week] and datepart(dw, Login) = 7), 0) as decimal(5,2)) as Saturday
	, cast(isnull((select cast(datediff(n, Login, Logout) as decimal (5, 2)) / 60 from @timelist where datepart(ww, Login) = Weeks.[Week] and datepart(dw, Login) = 1), 0) as decimal(5,2)) as Sunday
from (select distinct UserId, datepart(ww, Login) [Week] from @timelist) as Weeks
where UserId = 2
order by [Week]

kman
Starting Member

6 Posts

Posted - 2006-11-20 : 18:25:51

Superb! Works like a charm

Thanks alot snSQL!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 01:22:33

The example above seems a little overworked and bloated.
This produces the same thing but with better performance and execution plan.

select		datepart(ww, login) Week,
		sum(case when datepart(dw, login) = 2 then datediff(minute, login, logout) / 60.0 else 0.0 end) 'Monday',
		sum(case when datepart(dw, login) = 3 then datediff(minute, login, logout) / 60.0 else 0.0 end) 'Tuesday',
		sum(case when datepart(dw, login) = 4 then datediff(minute, login, logout) / 60.0 else 0.0 end) 'Wednesday',
		sum(case when datepart(dw, login) = 5 then datediff(minute, login, logout) / 60.0 else 0.0 end) 'Thursay',
		sum(case when datepart(dw, login) = 6 then datediff(minute, login, logout) / 60.0 else 0.0 end) 'Friday',
		sum(case when datepart(dw, login) = 7 then datediff(minute, login, logout) / 60.0 else 0.0 end) 'Saturday',
		sum(case when datepart(dw, login) = 1 then datediff(minute, login, logout) / 60.0 else 0.0 end) 'Sunday'
from		@timelist
where		userid = 2
group by	datepart(ww, login)
order by	datepart(ww, login)

Or this variation

select		s.Week,
		sum(case when s.Weekday = 2 then s.Hours else 0.0 end) 'Monday',
		sum(case when s.Weekday = 3 then s.Hours else 0.0 end) 'Tuesday',
		sum(case when s.Weekday = 4 then s.Hours else 0.0 end) 'Wednesday',
		sum(case when s.Weekday = 5 then s.Hours else 0.0 end) 'Thursay',
		sum(case when s.Weekday = 6 then s.Hours else 0.0 end) 'Friday',
		sum(case when s.Weekday = 7 then s.Hours else 0.0 end) 'Saturday',
		sum(case when s.Weekday = 1 then s.Hours else 0.0 end) 'Sunday'
from		(
			select	datepart(ww, login) Week,
				datepart(dw, login) Weekday,
				datediff(minute, login, logout) / 60.0 Hours
			from	@timelist
			where	userid = 2
		) s
group by	s.Week
order by	s.Week

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-21 : 02:32:34

and be aware that DATEPART(dw,) and DATEPART(ww,) are language dependant:

SET LANGUAGE danish
SELECT DATEPART(dw, '20061121'), DATEPART(ww, '20061121')

Changed language setting to Dansk.
            
----------- -----------
2           48

SET LANGUAGE us_english
SELECT DATEPART(dw, '20061121'), DATEPART(ww, '20061121')

Changed language setting to us_english.
            
----------- -----------
3           47

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 02:35:23

Michael "Define your week" Jones... Where are you?

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-21 : 07:49:26

quote:
Originally posted by Peso

Michael "Define your week" Jones... Where are you?

Peter Larsson
Helsingborg, Sweden

No one ever answers when I ask that question, so why bother?

When you don't know where you're going, any road will get you there.

CODO ERGO SUM

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 09:49:30

quote:
The example above seems a little overworked and bloated.

Yeah, sorry, I got lazy, that's much better.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 11:58:35

Give this version a spin ... I'm showing it outperforms the other solutions ...


select
    Week,
    isnull([2], 0) as 'Monday',
    isnull([3], 0) as 'Tuesday',
    isnull([4], 0) as 'Wednesday',
    isnull([5], 0) as 'Thursday',
    isnull([6], 0) as 'Friday',
    isnull([7], 0) as 'Saturday',
    isnull([1], 0) as 'Sunday'
from (
    select
        datepart(ww, Login) as 'Week',
        datepart(dw, Login) as 'Day',
        datediff(minute, Login, Logout) / 60.0 as 'Hours'
    from
        @timelist) tl
pivot
(
    sum(tl.Hours)
    for tl.Day in ( [1], [2], [3], [4], [5], [6], [7] )
) as pvt
order by
    Week

Jay White

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 12:03:35

Assuming kman is using SQL Server 2005.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources