I am not a great T-SQL developer, so I frequently struggle with some of the more advanced queries.  I spent some time building the query below, so I want to post it for future reference and the hope it may help someone else.

Say you have a dependant table for lookup values of status.  The status code in the primary table may be 1, 2, 3, etc., so the lookup table will define them as Open, Closed, Pending, whatever.  Now say for reporting you want to know how many Open or Closed items you have in the table.  The SQL query below will return each Status name with a count of items using the codes in the primary table:

SELECT OS.ObjectStateID, OS.Code, OS.Name,

    (SELECT COUNT(RS.ProductRequestID)

       FROM ProductRequestState RS

      WHERE RS.IsCurrent = 1 AND RS.ObjectStateID = OS.ObjectStateID

    )

FROM ObjectState OS

Where OS.IsActive = 1

The output will look something like this:

  OID Code              Name                              Count  
----- ----------------- --------------------------------- -----   
    1 WaitSubmit        Created                             154 
    3 Rejected          Rejected                           4785 
    4 WaitTSTechRvw     Waiting TS Tech Review               68 
    6 WaitGroupApprv    Waiting Cost/Labor/TS Tech Apprv    228 
    8 WaitExecApprv     Waiting Manager/Executive Apprv       0 
   13 Complete          Complete                          14433 
   14 WaitCostApprv     Waiting Cost Accounting Apprv         0 
   15 WaitLaborApprv    Waiting Labor and Supply Apprv        0 
   16 WaitTSTechApprv   Waiting TS Technologist Apprv         0