Professional Web Applications Themes

Help with select query - Microsoft SQL / MS SQL Server

Hello all, Need ur help. create table contract_submission (id char(10) , contract_line int, condition1 int, condition2 int, condition3 int, condition4 int) id contract_line con1 con2 con3 con4 guid1 1 1 2 3 4 guid1 2 1 2 0 0 guid1 3 1 2 3 0 guid1 4 1 2 7 9 guid1 5 1 2 4 6 guid2 1 1 2 0 0 guid2 2 1 2 1 2 guid2 3 1 2 4 6 guid2 4 1 2 4 6 Id and contract_line forms the key. Derived value is will be 1 for all the rows where (con1<=4 and ...

  1. #1

    Default Help with select query

    Hello all,
    Need ur help.
    create table contract_submission
    (id char(10) ,
    contract_line int,
    condition1 int,
    condition2 int,
    condition3 int,
    condition4 int)

    id contract_line con1 con2 con3 con4
    guid1 1 1 2 3 4
    guid1 2 1 2 0 0
    guid1 3 1 2 3 0
    guid1 4 1 2 7 9
    guid1 5 1 2 4 6
    guid2 1 1 2 0 0
    guid2 2 1 2 1 2
    guid2 3 1 2 4 6
    guid2 4 1 2 4 6


    Id and contract_line forms the key.

    Derived value is will be 1 for all the rows where
    (con1<=4 and con2 <=4 and con3 <=4 and con4 <=4 )
    for a particular id. other rows ( con1>4 or con2 >4 or
    con3 >4 or con4 >4)
    will take the value in the increment order 2,3, etc for
    that particular id.

    I want the output to be

    derived_value id contract_line

    1 guid1 1
    1 guid1 2
    1 guid1 3
    2 guid1 4
    3 guid1 5
    1 guid2 1
    1 guid2 2
    2 guid2 3
    3 guid2 4


    I need derived_value.

    Any immediate help is highly appreciated.


    ankita Guest

  2. #2

    Default help with select query

    Hello all,
    I need some help.
    create table contract_submission
    (id char(10) ,
    contract_line int,
    condition1 int,
    condition2 int,
    condition3 int,
    condition4 int)

    id contract_line con1 con2 con3 con4
    guid1 1 1 2 3 4
    guid1 2 1 2 0 0
    guid1 3 1 2 3 0
    guid1 4 1 2 7 9
    guid1 5 1 2 4 6
    guid2 1 1 2 0 0
    guid2 2 1 2 1 2
    guid2 3 1 2 4 6
    guid2 4 1 2 4 6


    Id and contract_line forms the key.

    Derived value is will be 1 for all the rows where
    (con1<=4 and con2 <=4 and con3 <=4 and con4 <=4 )
    for a particular id. other rows ( con1>4 or con2 >4 or
    con3 >4 or con4 >4)
    will take the value in the increment order 2,3, etc for
    that particular id.

    I want the output to be

    derived_value id contract_line

    1 guid1 1
    1 guid1 2
    1 guid1 3
    2 guid1 4
    3 guid1 5
    1 guid2 1
    1 guid2 2
    2 guid2 3
    3 guid2 4


    I need derived_value.

    Any immediate help is highly appreciated.


    ankita Guest

  3. #3

    Default Re: help with select query

    Please check if this is what you want.

    create table contract_submission
    (id char(10) ,
    contract_line int,
    condition1 int,
    condition2 int,
    condition3 int,
    condition4 int)

    INSERT contract_submission Values('guid1', 1, 1, 2, 3, 4)
    INSERT contract_submission Values('guid1', 2, 1, 2, 0, 0)
    INSERT contract_submission Values('guid1', 3, 1, 2, 3 ,0)
    INSERT contract_submission Values('guid1', 4, 1, 2, 7 ,9)
    INSERT contract_submission Values('guid1', 5, 1, 2 ,4 ,6)
    INSERT contract_submission Values('guid2', 1, 1, 2 ,0 ,0)
    INSERT contract_submission Values('guid2', 2, 1 ,2 ,1 ,2)
    INSERT contract_submission Values('guid2', 3, 1, 2 ,4 ,6)
    INSERT contract_submission Values('guid2', 4, 1, 2 ,4, 6)



    select derived_value = Case when (condition1<=4 and condition2 <=4 and
    condition3 <=4 and condition4 <=4 ) then 1
    when (condition1>4 or condition2>4 or condition3>4 or condition4>4 )
    then
    ( SELECT COUNT(*) FROM contract_submission b
    WHERE (condition1>4 or condition2>4 or condition3>4 or condition4>4)
    and a.id =b.id and a. contract_line > b.contract_line)+2
    else null end
    , id
    , contract_line
    from contract_submission a



    HTH,
    Srinivas Sampangi



    "ankita" <com> wrote in message
    news:098f01c365fc$24626ce0$gbl... 


    sampangi Guest

  4. #4

    Default help with select query

    Hi Sri, Thanks a lot...
     
    ankita Guest

  5. #5

    Default help with select query

    Hi ,
    Please help with this query.


    create table demo
    (demoid int, ----------- primary key
    id uniqueidentifier,
    assignednumber int,
    name varchar(50))

    data in this table is as follows:

    8067 3C0C67E9-C418-4B1E-AF85-1660BF329344 0 N1
    8070 3C0C67E9-C418-4B1E-AF85-1660BF329344 1 N2
    8071 3C0C67E9-C418-4B1E-AF85-1660BF329344 3 N2
    8050 3C0C67E9-C418-4B1E-AF85-1660BF329388 0 C1
    8051 3C0C67E9-C418-4B1E-AF85-1660BF329388 1 C2
    8052 3C0C67E9-C418-4B1E-AF85-1660BF329388 2 C3


    create table tran
    (demoid int, --demoid + type makes the primary key
    type char(3),
    sub_id char(10))

    data in this table is as follows:
    8067 P4 A44
    8070 X4 P12
    8071 X4 A70
    8050 P4 A12
    8051 X4 B34
    8052 X4 V80

    create table detail
    (id uniqueidentifier, --id + assigned_no forms the key.
    assigned_no int)

    data in this table is as follows:

    3C0C67E9-C418-4B1E-AF85-1660BF329344 1
    3C0C67E9-C418-4B1E-AF85-1660BF329344 2
    3C0C67E9-C418-4B1E-AF85-1660BF329344 3
    3C0C67E9-C418-4B1E-AF85-1660BF329344 4
    3C0C67E9-C418-4B1E-AF85-1660BF329388 1
    3C0C67E9-C418-4B1E-AF85-1660BF329388 2
    3C0C67E9-C418-4B1E-AF85-1660BF329388 3
    3C0C67E9-C418-4B1E-AF85-1660BF329388 4




    I want the following output:


    splitnbr id name sub_id assigned_no
    1 3C0C67E9-C418-4B1E-AF85-1660BF329344 N1 A44
    0
    1 3C0C67E9-C418-4B1E-AF85-1660BF329344 N1 A44
    2
    1 3C0C67E9-C418-4B1E-AF85-1660BF329344 N1 A44
    4
    2 3C0C67E9-C418-4B1E-AF85-1660BF329344 N2 P12
    1
    3 3C0C67E9-C418-4B1E-AF85-1660BF329344 N2 A70
    3

    1 3C0C67E9-C418-4B1E-AF85-1660BF329388 C1 A12
    0
    1 3C0C67E9-C418-4B1E-AF85-1660BF329388 C1 A12
    3
    1 3C0C67E9-C418-4B1E-AF85-1660BF329388 C1 A12
    4
    2 3C0C67E9-C418-4B1E-AF85-1660BF329388 C2 B34
    1
    3 3C0C67E9-C418-4B1E-AF85-1660BF329388 C3 V80
    2


    basically in first table (demo) first row with
    assigned_number = 0 is the master row.
    In the 3rd table assigned no. where assigned no. does not
    exist in first table are part of the master row.
    that's why name is same for assigned no. (0,2,4). and
    (0,3,4)

    Splitnbr is a derived field which gets reset for every new
    id, and within id it remains 1 for master and their
    corresponding rows and incrments for the others.

    Pls help.



    priya Guest

Similar Threads

  1. help with a SELECT query
    By halex2000 in forum MySQL
    Replies: 2
    Last Post: July 30th, 08:34 PM
  2. Query of Query to select a title first letter
    By Conti in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 18th, 11:42 PM
  3. select query help
    By Abul Hasan Lakhani in forum MySQL
    Replies: 8
    Last Post: October 15th, 07:04 PM
  4. Using < and > in a select query
    By Grynch in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 18th, 03:55 AM
  5. Select query
    By Meher in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: August 21st, 03:06 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