Complex Query!

I have 2 tables:
Timsheet
-TimsheetId
-EmpID
-Date
-HoursForDay
TimsheetActivities
-ActivityId
-TimsheetId
-#ofHours
The following is the query and I agree its stupid:
select
a.ActivityName , coalesce(ta1.ActivityTime,0) as Day1, 0 as Day2, 0 as
ActivityDay3, 0 as ActivityDay4
from
CelTimesheetActivities ta1
join
CelActivities a on a.ActivityId = ta1.ActivityId
join
CelTimesheet t on t.TimesheetId = ta1.TimesheetId
where
t.EmpId = 1
and ta1.ActivityId = 1
and t.TimesheetDate = '2/26/06'
union
select
a.ActivityName, 0 as Day1, coalesce(ta2.ActivityTime, 0) as Day2, 0 as
ActivityDay3, 0 as ActivityDay4
from
CelTimesheetActivities ta2
join
CelActivities a on a.ActivityId = ta2.ActivityId
join
CelTimesheet t on t.TimesheetId = ta2.TimesheetId
where
t.EmpId = 1
and ta2.ActivityId = 1
and t.TimesheetDate = '2/27/06'
union
select
a.ActivityName, 0 as Day1, 0 as Day2, coalesce(ta.ActivityTime, 0) as
ActivityDay3, 0 as ActivityDay4
from
CelTimesheetActivities ta
join
CelActivities a on a.ActivityId = ta.ActivityId
join
CelTimesheet t on t.TimesheetId = ta.TimesheetId
where
t.EmpId = 1
and ta.ActivityId = 1
and t.TimesheetDate = '2/28/06'
union
select
a.ActivityName, 0 as Day1, 0 as Day2, 0 as ActivityDay3,
coalesce(ta.ActivityTime, 0) as ActivityDay4
from
CelTimesheetActivities ta
join
CelActivities a on a.ActivityId = ta.ActivityId
join
CelTimesheet t on t.TimesheetId = ta.TimesheetId
where
t.EmpId = 1
and ta.ActivityId = 1
and t.TimesheetDate = '3/1/06'
The result is as follows:
Day1 Day2 Day3
Day4
Requirements 0.0 0.0 0.0 3.0
Requirements 0.0 0.0 1.0 0.0
Requirements 0.0 1.0 0.0 0.0
Requirements 1.0 0.0 0.0 0.0
What I want is:
Day1 Day2 Day3
Day4
Requirements 1.0 1.0 1.0 3.0
Can anyone help?This should come close. The max() aggregate aggregates the
groups of 4 rows you currently are seeing as 4 separate rows;
the max() will combine the four and give the single non-null
entry where you want it (I changed the 0 you showed to null,
but you could use 0 if ActivityTime is never negative - using
null makes the solution more generalizable).
select
a.ActivityName,
max(case when offset = 1 then tal.ActivityTime end) as Day1,
max(case when offset = 2 then tal.ActivityTime end) as Day2,
max(case when offset = 3 then tal.ActivityTime end) as Day3,
max(case when offset = 4 then tal.ActivityTime end) as Day4
from
CelTimesheetActivities ta1
join
CelActivities a on a.ActivityId = ta1.ActivityId
join
CelTimesheet t on t.TimesheetId = ta1.TimesheetId
join (
select 1 as offset union all 2 union all 3 union all 4
) Ofst
on t.TimesheetDate = dateadd(day,offset,'20060225')
where
t.EmpId = 1
and ta1.ActivityId = 1
group by a.ActivityName
-- Steve Kass
-- Drew University
sucharita.p@.gmail.com wrote:

>I have 2 tables:
>Timsheet
>-TimsheetId
>-EmpID
>-Date
>-HoursForDay
>
>TimsheetActivities
>-ActivityId
>-TimsheetId
>-#ofHours
>The following is the query and I agree its stupid:
>select
> a.ActivityName , coalesce(ta1.ActivityTime,0) as Day1, 0 as Day2, 0 as
>ActivityDay3, 0 as ActivityDay4
>from
> CelTimesheetActivities ta1
>join
> CelActivities a on a.ActivityId = ta1.ActivityId
>join
> CelTimesheet t on t.TimesheetId = ta1.TimesheetId
>where
> t.EmpId = 1
>and ta1.ActivityId = 1
>and t.TimesheetDate = '2/26/06'
>union
>select
> a.ActivityName, 0 as Day1, coalesce(ta2.ActivityTime, 0) as Day2, 0 as
>ActivityDay3, 0 as ActivityDay4
>from
> CelTimesheetActivities ta2
>join
> CelActivities a on a.ActivityId = ta2.ActivityId
>join
> CelTimesheet t on t.TimesheetId = ta2.TimesheetId
>where
> t.EmpId = 1
>and ta2.ActivityId = 1
>and t.TimesheetDate = '2/27/06'
>union
>select
> a.ActivityName, 0 as Day1, 0 as Day2, coalesce(ta.ActivityTime, 0) as
>ActivityDay3, 0 as ActivityDay4
>from
> CelTimesheetActivities ta
>join
> CelActivities a on a.ActivityId = ta.ActivityId
>join
> CelTimesheet t on t.TimesheetId = ta.TimesheetId
>where
> t.EmpId = 1
>and ta.ActivityId = 1
>and t.TimesheetDate = '2/28/06'
>union
>select
> a.ActivityName, 0 as Day1, 0 as Day2, 0 as ActivityDay3,
>coalesce(ta.ActivityTime, 0) as ActivityDay4
>from
> CelTimesheetActivities ta
>join
> CelActivities a on a.ActivityId = ta.ActivityId
>join
> CelTimesheet t on t.TimesheetId = ta.TimesheetId
>where
> t.EmpId = 1
>and ta.ActivityId = 1
>and t.TimesheetDate = '3/1/06'
>The result is as follows:
> Day1 Day2 Day3
>Day4
>Requirements 0.0 0.0 0.0 3.0
>Requirements 0.0 0.0 1.0 0.0
>Requirements 0.0 1.0 0.0 0.0
>Requirements 1.0 0.0 0.0 0.0
>
>What I want is:
> Day1 Day2 Day3
>Day4
>Requirements 1.0 1.0 1.0 3.0
>
>Can anyone help?
>
>|||Thanks, I worked it out with Coalesce and Max.