Combining record sets from two tables

Friends,
Can anyone give me the best way to solve the following challenge.
I have two tables in my MS SQLserver database. Table A has this dataset:
code_id code operations_descr
1 M Mob/Demob
2 T Transit
3 O Operation
4 S Standby
5 W WOW
6 C Crew Change
7 B Breakdown
8 R Maintenance
Table B has this dataset:
code_id operations_duration
3 125
3 10
2 1304
I want a new recordset where the sum of the operations_duration from table B
grouped on code_id is combined with the right code_id in table A, like this:
code_id code operations_descr total_operations_duration
1 M Mob/Demob 0
2 T Transit 1304
3 O Operation 135
4 S Standby 0
5 W WOW 0
6 C Crew Change 0
7 B Breakdown 0
8 R Maintenance 0
Thank you for any assistance!
/Leif S
--
Systems AnalystI've assumed that the first table is called "Operations" and that the second
one is called "OperationsDuration". Here is the query:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(OD.Total_Operations_Duration)
From Operations O, OperationsDuration OD
Where O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
Best regards,
Sami Samir
ITWorx
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi John,
The code you gave me works well. But it is more to it.
The data is collected from a survey vessel working in the North Sea and the
tables contains data from daily reports sent from the vessel. Table B stores
operations duration for each day where records from a given day share the
same report_id. When I take your code and add: "Where OD.report_id =
@.reportId" and supply report_id = 2, the result set exclude code lines from
table A that are not in table B like this:
code_id code operations_descr Total_Oper
ations_Duration
2 T Transit 1304
3 O Operation 135
Your advice is appreciated!
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>|||See if this works...
SELECT code_id, code, operations_descr,
operations_duration = (SELECT SUM(total_operations_duration)
FROM TABLEB
WHERE code_id = TABLEA.code_id
GROUP BY code_id
)
FROM TABLEA
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:5E01F636-997E-47A6-A844-206B6DBBE0CA@.microsoft.com...
> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
> B
> grouped on code_id is combined with the right code_id in table A, like
> this:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
I am not sure where report_id comes from as it was not mentioned in the
original post. You may want to check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data.
Try:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.reporti
d
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:
> Hi John,
> The code you gave me works well. But it is more to it.
> The data is collected from a survey vessel working in the North Sea and th
e
> tables contains data from daily reports sent from the vessel. Table B stor
es
> operations duration for each day where records from a given day share the
> same report_id. When I take your code and add: "Where OD.report_id =
> @.reportId" and supply report_id = 2, the result set exclude code lines fro
m
> table A that are not in table B like this:
> code_id code operations_descr Total_Oper
ations_Duration
> 2 T Transit 1304
> 3 O Operation 135
> Your advice is appreciated!
> /Leif S.
> --
> Systems Analyst
>
> "John Bell" wrote:
>|||Thanks, John. So easy!
When I moved the report_id qualifyer away from the "Where" clause and into
the Join statement I got what I wanted.
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> I am not sure where report_id comes from as it was not mentioned in the
> original post. You may want to check out
> http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
> data.
> Try:
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.repor
tid
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>

Combing 2 Queries into 1

I have spent several hours hurting my brain tring to solve the following. I am hoping one of you SQL gurus can help me out.

I have two tables (each with two fields):

Group with GroupName and GroupDescription

Here is some sample data:

HR HumanResources
IT Information Technology
Boston BostonOffice
NJ NewJerseyOffice

GroupMember with GroupName and UserID

Here is some sample data:

IT CMessineo
NJ CMessineo
Boston JSmith
IT JSmith

What I want is a single stored procedure that when passed a UserID will return a result set that lists all the groups and a 1 or ) if the UserID is a member of that group.

For example calling the procedure and passing CMessineo would produce this result:

HR 0
IT 1
Boston 0
NJ 1

Can this be done in a stored procedure?

This is what I have so far, but I am in over my head:

(
@.UserID varChar(40)
)
As
SELECT"GROUP".GROUPNAME, (SELECT ?
FROM"Group", GroupMember
Where"Group".GroupName = GroupMember.GroupName and GroupMember.UserID = @.UserID)

FROM"GROUP"

Thanks in advance,

ChrisI figured it out - it required an outer join (my first):

SELECT"Group".GroupName, COUNT(UserID) AS MEMBER
FROM"Group" Left Outer Join GroupMember
ON"Group".GroupName = GroupMember.GroupName and GroupMember.UserID=@.UserID
GROUP BY"Group".GroupName

Color bleeding into next columns when exported to excel

I have one issue left to solve before releasing my drilldown report to production. The report displays fine in report manager; however, when it is exported to excel, the color (Coral) in the last column on the right is bleeding into the next three columns in excel. I have looked at properties, sizing and searched the archives. Anyone have an idea how I can fix this to render properly?

I fixed this one myself. We had a hidden logo that has pushed the page size wider than anticipated. We deleted the logo and reduced the page width on the layout and it works fine now.