help with query syntax

Hi all, I have been fighting with this query and would like some advice. Please consider the following tables;

prod_table
widget_number
shift
date
production_time (in minutes)
down_table
rec_id (ident key)
down_shift
down_date
down_minutes

Prod_table (data)

widget_number shift date production_time
0001 1 08/02/06 5.00
0002 1 08/02/06 10.00
0003 1 08/02/06 7.00
0004 2 08/02/06 5.00
0005 2 07/31/06 3.00
Down_table (data)
rec_id down_shift down_date down_minutes
1 1 08/02/06 3.00
2 1 08/02/06 20.00
3 2 07/31/06 10.00
I would like to combine the production times and down times into one summary where the down time is in the same date and shift as the production time.
As you can see in my results below, I can group them correctly, but the down totals obviously repeat for each match. Is there any way of getting to the "Desired results"?
My Results
widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 23.00
0003 1 08/02/06 7.00 23.00
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00

Desired results
Widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 NULL
0003 1 08/02/06 7.00 NULL
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00

thank you in advance.

Try this:

UPDATE <your result table> SET down_time = null
FROM (SELECT min(widget_number) as widget_number, shift, date, down_time
FROM <your result table>
group by shift, date, down_time) a left join <your result table> b
on a.widget_number = b.widget_number
WHERE b.widget_number is null

|||I dont have an actual result table. This is a SQL query returning the result set to a report.|||What version of SQL Server are you using? The result is slightly easier to achieve in SQL Server 2005. But this is really a reporting / formatting application so you may be better off doing it in the client side (avoiding repeating groups for instance).|||I am currently using SQL 2000. I will be moving to 2005 soon, but I need this before then. I am using reporting services for SQL , so I really dont see how I can do it client side. Thats where I run into my issue.|||If you have access to the report, then change the datasource of the report, add the update statement in. Otherwise, nothing you can do.|||Why do you need to update anything? There is no table that contains the end result. This is just a read-only operation.|||I don't know about the capabilities of Reporting Services. I have used Crystal Reports in the past and there is an option to suppress repeating values in a column. And that will achieve what you want. So you might want to post this question in the Reporting Services forum since the experts for that product do not participate in this forum.|||Thanks for the response. I just wanted to see if there was a way to do it via T-SQL. I did not think so, but it was worth a shot. Thanks for your time.|||

Seems like you gave up too soon. The Update method will work if you put your query in a stored procedure and use a table variable.

Also, it seems like if you created a Derived table that joined the Downtime with the Min Widget for the shift you could then use a CASE to supress the downtime on the additional rows.