Thread: CASE WHEN vs. BETWEEN

Help me out -- are they showing NULL now in the result set? I've got about 3 different query problems working at the same time.

They are showing as NULL, yes. A blank value on the page. NULL in the DB.

See the lines is bold below:

Code:

  Select S.s_id, S.s_name, S.s_symbol, S.s_publisher, S.s_published_date
       , isNull(sum(Case when SD.sd_shares > 0 then SD.sd_shares else 0 end), 0) as sd_shares
       , isNull(AP.sd_price_per_share, 0) as sp_price_per_share
       , isNull(convert(varchar(10), MD.sd_date, 120), 'N/A') as sd_date
    From tblStocks S
    Left Join tblStocks_Detail SD
      On S.s_id = sd.s_id
    Left Join (
          Select s_id, max(sd_date) as sd_date
            From tblStocks_Detail
            Group by s_id
          ) as MD
      On (S.s_id = MD.s_id)
    Left Join (Select sd_id
                    , SUM(sd_shares * sd_price_per_share) / SUM(sd_shares) as sd_price_per_share
                 From tblStocks_Detail
                 Where sd_shares > 0
                 Group By sd_id
                )  as AP
   Group By  S.s_id, S.s_name, S.s_symbol, S.s_publisher, S.s_published_date, AP.sp_price_per_share, MD.sd_date

Note that the convert function will returns dates as YYYY-MM-DD (by using format code 120) to maintain sort order. You can use a different code if sort order is not important.

Wolffy, that works perfect. I have certainly gained a lot of insight into SQL statements. Thanks again for all of your help.

No problem -- glad you got it sorted.