"ninel g via SQLMonster.com" wrote:

> I have a table TABLE1. My company has 2 sites. This table contains employees
> with the amount of hours they worked on which project at which sites.
>
> [CODE]
> CREATE TABLE #TABLE1 (
>     Calldate varchar(10) NULL,
>     Employee varchar(10) NULL,
>     Project varchar(10) NULL,
>     Hours decimal(10,4) NULL,
>     Site varchar(1) NULL)
>
> INSERT #TABLE1 (calldate, employee, project, hours, site)
> VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
>
> INSERT #TABLE1 (calldate, employee, project, hours, site)
> VALUES ('20060217', '246', 'EACQ5', 3, '2')
>
> INSERT #TABLE1 (calldate, employee, project, hours, site)
> VALUES ('20060217', '369', 'EACQ5', 2, '1')
>
> INSERT #TABLE1 (calldate, employee, project, hours, site)
> VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
>
> INSERT #TABLE1 (calldate, employee, project, hours, site)
> VALUES ('20060217', '369', 'EACQ6', 5, '2')
> [/code]
>
> I need to figure out the following:
> I need the total hours of employees from both sites ONLY if they worked on a
> project that ended in a 5. If employees worked on projects that did not end
> in 5 I need the totals for their site only. A parameter of site will be
> passed to the stored procedure.
>
> So for example: If site parameter of 1 is passed.
> I need to see the following results:
>
> Calldate      Project     TotalHours
> 20060217    EAUD5      2.5
> 20060217    EACQ5      5
> 20060217    EACQ6      1.5
>
> If site parameter of 2 is passed.
> I need to see the following results:
>
> Calldate      Project     TotalHours
> 20060217    EAUD5      2.5
> 20060217    EACQ5      5
> 20060217    EACQ6      5
>
> Any help would be greatly appreciated,
>
> Thanks,
> ninel
>
> --
> Message posted via http://www.sqlmonster.com
>

>Thanks for posting DDL and sample data.
>
>declare @site varchar(1)
>set @site = '1'
>
>select calldate, project,
>sum(case when site = @site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>
>set @site = '2'
>
>select calldate, project,
>sum(case when site = @site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>
>> I have a table TABLE1. My company has 2 sites. This table contains employees
>> with the amount of hours they worked on which project at which sites.
>[quoted text clipped - 49 lines]
>> Thanks,
>> ninel