Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Subtract last three characters!

    Hello everyone. I have a query where I pull all item_codes that start with a 7. I need all the item_codes that start with 7, but I need to subtract the last 3 characters from only the item_codes that are not 71860kit or 71851nggun. How would I be able to accomplish this. What I have below subtracts the last 3 characters from all item_codes including 71860kit and 71851nggun. I want these two to stay in tact when I select them in the query.

    This is the Query that I have now!

    select datepart(year, date_cust_invoice)as INV_YEAR,
    datepart(month, date_cust_invoice)as INV_MONTH,
    item_code=left(item_code, len(item_code)-3)
    , sum(QTY_SALES)as QTY_SALES_TOTALS, sum(SALES_VAL)as SALES_VAL_TOTALS
    from opcsahf
    where datepart(year, date_cust_invoice) >= '2003'
    and substring(item_code, 1, 1) = '7'
    group by datepart(year, date_cust_invoice), datepart(month, date_cust_invoice),
    item_code
    order by inv_year, inv_month, item_code



    All help is appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    item_code = CASE WHEN item_code IN ('71860kit', '71851nggun') THEN item_code ELSE Left(item_code, Len(item_code) - 3) END

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •