Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2013
    Posts
    3

    Red face Unanswered: Convert Access SQL Query to SQL Server Syntax

    Hi I am trying to convert a code from access Db to sql code can you please help??

    II(Left([dbo_ClaimLosstype].[dscr],4)="Liab","D_Liab","D_Property"),

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT CASE
             WHEN Left(ClaimLosstype.dscr, 4) = 'Liab' THEN
               'D_Liad'
             ELSE
               'D_Property'
           END As column_name
    FROM   ...
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2013
    Posts
    3
    Thank You very Much.....

  4. #4
    Join Date
    Aug 2013
    Posts
    3

    sum by agency

    Hi I am trying to sum the totals by agency and by typedescription and cant seem to do that even with sum (field name), not sure what i am missing, here i have attached a example of data and their field names.

    Agency - 6001-1001 JOHNSON, MALOTT & WILLIAMSON INSURANCE
    TypeDescription - Farmowners
    Paid_Loss - 176433.96

    Agency - 6001-1001 JOHNSON, MALOTT & WILLIAMSON INSURANCE
    Typedescription - Select-O-Matic
    paid_loss - 72961.72 0.00

    so the way i want it now is agency name to appear only once and totals of both the typedescriptions.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you know the full list of typedescriptions in advance, you can use a SELECT with GROUP BY and Sum(CASE ... END) construction, but without knowing the full list that you want to build I can't offer syntax to help with that.

    If you don't know the typedescription values in advance the SELECT with the PIVOT subclause will do a cross-tab, but since the result set will be dynamic your client will need to be able to accommodate a dynamic result set.

    You'll need to give us a bit more insight into your typedecription values if you want a more concrete answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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