Problem with SQL Stored Procedure

[susanbl]

I am building a stored procedure in an Access ADP front end to a SQL Server backend referencing a table that lists trade rates. The table includes the trade, the rate and the project number and is linked to a HoursTable which also includes the trade and project number. Because there are approximately 30 trades and 1000 projects, only the exceptions are represented by the actual project number in the Rates table and everything else is labeled “ALL”. For example:

Trade Rate Project
Apprentice 20.00 ALL
Apprentice 21.00 529

The table is linked to the HoursTable on the Trade field and so I am getting two rows for the Apprentice trade. I understand why it’s not working and have been playing around with CASE WHEN but am new to SQL and not having much luck.

Thanks

Susan

 

[cmmrfrds]

Please show an example of your expected output.

 

[susanbl]

To simplify, I would like to see:

HrsTable.Trade HrsTable.Prj HrsTable.Hrs RateTable.Rate
Apprentice 528 10 20.00
Apprentice 529 20 21.00

Rather than:

HrsTable.Trade HrsTable.Prj HrsTable.Hrs RateTable.Rate
Apprentice 528 10 20.00
Apprentice 528 10 21.00
Apprentice 529 20 20.00
Apprentice 529 20 21.00

Because there are two rates for Apprentice, one for project 529 and another one for everything else, I am getting two rows. If there is a special rate for project 528 I want the query to use it, otherwise use the ALL rate. If it weren't for the 'ALL' label, I would link the tables on the Project Number field and the problem would be solved but...Hopefully this helps. Thanks

Susan

 

[cmmrfrds]

That is a good example and I understand the problem, but I don't see an easy solution that can be done in a couple of minutes. It can be done in SQL but may take a little time to put together. I recommend posting on the SQL Server Forum and referencing this thread. The people there work with sql server syntax on a routine basis. Forum is Microsoft SQL Server Programming.

 

[susanbl]

Thanks again - I will repost as suggested.