Help with SQL Server Syntax 1

[susanbl]

I originally posted my problem on the Access ADP forum but it was suggested that I repost it here. Please refer to Thread 958-916853 - any help you can give me is appreciated.

Thanks

 

[SQLSister]

YOur link isn;t working, could you report the question here?

Questions about posting. See faq183-874

 

[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 # and is linked to a HoursTable which also includes the trade, project #, and the number of hours worked. They are joined on the Trade field. Because there are approximately 30 trades and 1000 projects, only the exceptions are represented by the actual project # in the Rates table and everything else is labeled “ALL”. For example:

Trade Rate Project
Apprentice 20.00 ALL
Apprentice 21.00 529

I am trying to write a statement (CASE WHEN perhaps?) that picks up the exception rate if there is one for a project but, if not, picks up the ALL rate resulting in, for example:

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

which is what I am getting now.

Thanks

Susan

 

[SQLSister]

Not at all sure if this will work, but it's a place to start.

Select HrsTable.Trade, HrsTable.Prj, HrsTable.Hrs, 
case RateTable.Project when null then a.Rate else RateTable.Rate end as Rate
from HrsTAble left join RateTable on HrsTAble.Proj = RateTable.Project
cross Join (select Rate from RateTable where project = 'All') a

Questions about posting. See faq183-874

 

[SQLBill]

By the way, when you want to post a FAQ or THREAD as a link...don't put a space between the word and the first number.

For example: FAQ183-874 not FAQ 183-874.

-SQLBill

 

[susanbl]

Thanks SQLSister,

This codes works (I am getting two lines not four) but it didn't pick up the rate for the ALL project - left it blank?

And thanks for the tip SQLBill - my first time posting!

 

[SQLSister]

Might want to add, a.Rate to the select list and just see what value it has. If it has value and the case makes it blank, we have a problem with the case. If doesn;t ever have a value we have a problem with the cross join.

Questions about posting. See faq183-874

 

[susanbl]

I added the a.Rate to the beginning of the select list and it returned the same value the exception rate. Then added it to the end of the select list and it brought back a null value again. In both cases, the view created a new column0? Sorry, I am not much help - this is all so new to me.

 

[SQLSister]

Could you show me your actual query? Sometimes it's the little things that trip us up.

Questions about posting. See faq183-874

 

[susanbl]

I was thinking along the same lines so changed the CASE WHEN
Statement

from:

CASE RateTable.Project WHEN NULL THEN a.Rate...

to:

CASE WHEN RateTable.Project IS NULL THEN a.Rate...

and it works!! A big thank you!