Can't figure out how to write query

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')

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.webservertalk.comThanks 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
"ninel g via webservertalk.com" wrote:

>
I have a table TABLE1. My company has 2 sites. This table contains employe
es
>
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')
>

>
>
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 en
d
>
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.webservertalk.com
>
|||Thnak you so much for teh quick response.
Mark Williams wrote:
>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
>
>[quoted text clipped - 49 lines]
Message posted via http://www.webservertalk.comsql

cant do that request

Hi,
I'm going to explain as clearly as possible:

I have two tables:

Relationships(relation_id, table1, table2)
Relationfields(relation_id, field1, field2)

In Relationships, relation_id is the primary key
In Relationfields, relation_id is the foreign key

I have a front-end interface that allows the user to add records to
Relationships and Relationfields as followed:

The user selects a table1 and table2 values from listboxes. These are
real table names from sys.objects, so then the user can select fields
of these tables on which he wants to create a JOIN.
Anyway, I can easily insert the table1 and table2 into Relationships
(relation_id is an auto-increment). Then I need to get the relation_id
of this new Relationship (easy since I know which values I've inserted
and table1-table2 associations are unique.

Now the PROBLEM :
I need to insert into Relationfields all the fields selectioned by the
user for each of the two tables . But the user might have selected
several fields from table1 and table2, so I need to pass A LIST
PARAMETER to my Stored Procedure as I don't know how many values of
field1 and field2 there is going to be.

I hope this is clear enough. Is it possible to achieve what I want ?
Should I pass an entire concatenated string with values separated by
comma or whatever and then decrypt it in the stored procedure ?

ThxCould anyone help please ?|||If you're asking how to pass a list into a procedure as a parameter,
then see here:

http://www.sommarskog.se/arrays-in-sql.html

If that doesn't help, I suggest you post CREATE TABLE and INSERT
statements to clarify exactly what you need:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||thanks. I think the first link should help me doing what I want.
Regards|||Why are you putting metadata in your schema, in total violation of the
basic principles of a relational database? Why don't you know the
differences between rows and records, fields and columns, data and
metadata?

You can kludge this with a lot of dynamic SQL that will run like cold
glue, have no data integrity and be unmaintainable. Someone will
probably help you do this. Please read a few books before you destroy
your employers data.|||The best way to pass multiple-row data into a stored procedure is using
OPENXML, any other method is a kludge.|||I put meta data in my shema because my employer pays me to do so... I'm
developping an administration tool for maintaining dynamic metadata of
a website.
Now I'm not sure in what way your comment is of any use to me...|||Ellen K > Thanks I'll look at this in the future. But I've found a
better way this time to avoid passing multiple-row data to my stored
procedure:)
Thx!