Cross-Tab problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Cross-Tab problem

Author

Topic

Antonio
Posting Yak Master

168 Posts

Posted - 2007-03-15 : 09:28:03

Greetings all,

I have the following data :


declare @table  table (
	TimeEntryID int,
	TimeEntryUserID char(3),
	ProjectID int,
	CategoryID int,
	TimeEntryDate datetime,
	TimeEntryDuration int)
insert into @table 
select 1, 'AAA', 1, 1, '2007.03.12', 4 union all
select 2, 'AAA', 2, 2, '2007.03.12', 4 union all
select 3, 'BBB', 3, 3, '2007.03.13', 4 union all
select 4, 'CCC', 2, 2, '2007.03.14',  4 union all
select 5, 'CCC', 1, 1, '2007.03.14', 4 union all
select 6, 'AAA', 3, 3, '2007.03.15', 8 union all
select 7, 'BBB', 2, 2, '2007.03.16', 8 union all
select 8, 'DDD', 1, 1, '2007.03.17', 8 

select * from @table

What I would like to have is

TimeEntryUserID, WeekCommencingDate, Moday, Tuesday, Wednesday, Thursday, Friday

Each day column should have the TimeDurationEntry.

I know I need to do a cross-tab but I have no idea how to do it.

Can someone help me please?

Thanks in advance.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 09:35:42

[code]
select TimeEntryUserID,
WeekCommencingDate = dateadd(week, datediff(week, 0, TimeEntryDate), 0),
Moday = max(case when datename(weekday, TimeEntryDate) = 'Monday' then TimeEntryDuration end),
Tuesday = max(case when datename(weekday, TimeEntryDate) = 'Tuesday' then TimeEntryDuration end),
Wednesday = max(case when datename(weekday, TimeEntryDate) = 'Wednesday' then TimeEntryDuration end),
Thursday = max(case when datename(weekday, TimeEntryDate) = 'Thursday' then TimeEntryDuration end),
Friday = max(case when datename(weekday, TimeEntryDate) = 'Friday' then TimeEntryDuration end)
from @table
group by TimeEntryUserID, dateadd(week, datediff(week, 0, TimeEntryDate), 0)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 09:37:09

[code]-- prepare sample data
declare @table table
(
TimeEntryID int,
TimeEntryUserID char(3),
ProjectID int,
CategoryID int,
TimeEntryDate datetime,
TimeEntryDuration int
)

insert @table
select 1, 'AAA', 1, 1, '2007.03.12', 4 union all
select 2, 'AAA', 2, 2, '2007.03.12', 4 union all
select 3, 'BBB', 3, 3, '2007.03.13', 4 union all
select 4, 'CCC', 2, 2, '2007.03.14', 4 union all
select 5, 'CCC', 1, 1, '2007.03.14', 4 union all
select 6, 'AAA', 3, 3, '2007.03.15', 8 union all
select 7, 'BBB', 2, 2, '2007.03.16', 8 union all
select 8, 'DDD', 1, 1, '2007.03.17', 8

-- Show the expected output
SELECT TimeEntryUserID,
DATEADD(WEEK, DATEDIFF(WEEK, 0, TimeEntryDate), 0) AS WeekCommencingDate,
SUM(CASE WHEN DATENAME(WEEKDAY, TimeEntryDate) = 'Monday' THEN TimeEntryDuration ELSE 0 END) AS Monday,
SUM(CASE WHEN DATENAME(WEEKDAY, TimeEntryDate) = 'Tuesday' THEN TimeEntryDuration ELSE 0 END) AS Tuesday,
SUM(CASE WHEN DATENAME(WEEKDAY, TimeEntryDate) = 'Wednesday' THEN TimeEntryDuration ELSE 0 END) AS Wednesday,
SUM(CASE WHEN DATENAME(WEEKDAY, TimeEntryDate) = 'Thursday' THEN TimeEntryDuration ELSE 0 END) AS Thursday,
SUM(CASE WHEN DATENAME(WEEKDAY, TimeEntryDate) = 'Friday' THEN TimeEntryDuration ELSE 0 END) AS Friday
FROM @Table
GROUP BY TimeEntryUserID,
DATEADD(WEEK, DATEDIFF(WEEK, 0, TimeEntryDate), 0)
ORDER BY 1,
2[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 09:37:31

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 09:39:30

quote:
Originally posted by Peso

Peter Larsson
Helsingborg, Sweden

Your query is correct. Looking at the data again, I should have use SUM() instead of MAX()

Antonio
Posting Yak Master

168 Posts

Posted - 2007-03-15 : 10:18:52

Thanks to you both. Yes, I did spot the MAX and changed it to SUM accordingly.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources