Professional Web Applications Themes

help with SQL statement - Microsoft SQL / MS SQL Server

I wonder if someone can help me with this SELECT statement. My table has columns subm_no, portion and premium. Portion can be either P or P1. Occasionally you'll have rows with the same subm_no but different portions: subm_no portion premium 39792 P 1000 39792 P1 4000 In such a situation, if the P1 row has premium > 0, the SELECT statement should display ONLY the row with portion = 'P', but for its premium it should display the premium of the row with portion = P1: 39792 P 4000 If there is no P1 row, just show the P row. ...

  1. #1

    Default help with SQL statement

    I wonder if someone can help me with this SELECT statement. My table has
    columns subm_no, portion and premium. Portion can be either P or P1.
    Occasionally you'll have rows with the same subm_no but different
    portions:

    subm_no portion premium
    39792 P 1000
    39792 P1 4000

    In such a situation, if the P1 row has premium > 0, the SELECT statement
    should display ONLY the row with portion = 'P', but for its premium it
    should display the premium of the row with portion = P1:

    39792 P 4000

    If there is no P1 row, just show the P row. If the P1 row has premium =
    0, show both rows with their own premiums.

    Any suggestions? Thanks much.

    -- Scratchin' my hair out
    Rick Guest

  2. #2

    Default Re: help with SQL statement

    Rick,

    Please include DDL and sample data with future questions like this .

    create table #t1
    (
    subm_no int,
    portion varchar(2),
    premium int
    )

    insert into #t1 values(39792,'P',1000)
    insert into #t1 values(39792,'P1',4000)
    insert into #t1 values(39793,'P',5000)
    insert into #t1 values(39794,'P',2000)
    insert into #t1 values(39794,'P1',0)

    select subm_no, 'P' as portion, max(premium) as premium from #t1 group by
    subm_no
    Union
    select subm_no, portion, premium from #t1 where portion='P1' and premium=0

    Drop Table #T1

    HTH
    Praveen Maddali,
    MCSD, MCDBA


    "Rick C." <com> wrote in message
    news:microsoft.com... 


    praveen Guest

  3. #3

    Default Re: help with SQL statement

    Rick,

    Please ignore my previous reply, as the query doesn't handle the cases where
    both 'P' and 'P1' exists and have premium >0.


    Given below is the modified query.

    select subm_no, portion, premium from #t1 where (portion='P1' and premium=0)
    Union All
    select subm_no, 'P' as portion,
    coalesce(max(case when portion='P1' then premium end),max(case when
    portion='P' then premium end)) as Premium
    from #t1
    where not (portion='P1' and premium=0)
    group by subm_no

    Sorry for the confusion. Once again it will be very useful , if you can
    provide us the DDL , sample data (with all possible combinations) and
    required result.

    Hope this helps,

    Praveen Maddali


    "praveen" <stph.net> wrote in message
    news:phx.gbl... [/ref]
    has 
    >
    >[/ref]


    praveen Guest

Similar Threads

  1. Need Help With a SQL Statement
    By mpc in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 25th, 08:46 PM
  2. Help with an IF THEN statement
    By oppels in forum Dreamweaver AppDev
    Replies: 4
    Last Post: May 16th, 12:10 PM
  3. if statement with url
    By John in forum PHP Development
    Replies: 5
    Last Post: September 11th, 03:13 AM
  4. Can i say OR in an if/then statement
    By crispy in forum ASP
    Replies: 6
    Last Post: July 9th, 07:27 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139