SQL Server Performance
  1. vsnreddi New Member

    Hi

    I have table i need to identify one record qty column shld be -ve value and another one is shld be +ve value ?

    One record name shld be starts with EU and another one is anything..it shld be two rows for ACNO only. if is more thatn we can ignore it. thanks

    sample output data:

    ACNo Name QTY
    =====================================
    B0"XYZ" 927.0000
    B0"EU" -4237.000





    SURYA
  2. khtan New Member

    please post your table structure, sample data and result that you want


    KH
  3. Adriaan New Member

    What do you mean by +ve and -ve - minimum and maximum?

    And where does the value on the middle column come from? From the same row as the QTY value?

    SELECT t1.ACNO, t1.Name, t1.QTY
    FROM MyTable t1
    WHERE t1.QTY = (SELECT MIN(t2.QTY) FROM MyTable t2 WHERE t2.ACNO = t1.ACNO)
    UNION
    SELECT t1.ACNO, t1.Name, t1.QTY
    FROM MyTable t1
    WHERE t1.QTY = (SELECT MAX(t2.QTY) FROM MyTable t2 WHERE t2.ACNO = t1.ACNO)

    You might get one row for an ACNO, in case all rows for ACNO have the same QTY value and the same Name.

    You might get more than two rows, in case more than one row have the same minimum/maximum QTY value for the QTY value, but with a different Name. You can suppress that by using MIN(t1.Name) and grouping by ACNO and QTY in the main queries.
  4. ranjitjain New Member

    SELECT acno from(
    SELECT acno,name,qty from urtable
    where qty<0 and name='EU'
    union all
    SELECT acno,name,qty from urtable
    where qty>0 and name<>'EU')tmp
    group by acno
    having count(*)>1
  5. vsnreddi New Member

    table strcture and sample data

    ACNo Name QTY
    =============
    B0 "XYZ" 927
    B0 "EU" -4237

    ZX "ABC" 100
    ZX "DEN" 200
    ZX "EU" 300

    OF "AC" 100
    OF "DG" 200
    OF "DF 250
    OF "EU" -250

    DZ "AC" -150
    DX "EU" -100

    OP "AX" 200
    OP "EU 100

    expected output:
    --------------
    ACNo Name QTY
    =============
    B0 "XYZ" 927
    B0 "EU" -4237



    SURYA
  6. vsnreddi New Member

    The Rules are:

    01. Only two records should be consider
    02. one record shld be start with EU
    03. one record shld contain -ve value(ex: -1000) and another one shld be +ve value( 200).

    ignore:

    01. two records are +ve values or -ve values
    02. more than two records


    SURYA
  7. khtan New Member

    Rule 1, 3. Have not figured out Rule 2 yet.


    declare @table table
    (
    ACNo varchar(10),
    Name varchar(10),
    QTYint
    )

    insert into @table
    select'B0', 'XYZ', 927union all
    select'B0', 'EU', -4237union all

    select'ZX', 'ABC', 100union all
    select'ZX', 'DEN', 200union all
    select'ZX', 'EU', 300union all

    select'OF', 'AC', 100union all
    select'OF', 'DG', 200union all
    select'OF', 'DF', 250union all
    select'OF', 'EU', -250union all

    select'DZ', 'AC', -150union all
    select'DX', 'EU', -100union all

    select'OP', 'AX', 200union all
    select'OP', 'EU', 100

    selectt.*
    from@table t
    inner join
    (-- 2 records
    selectACNo
    from@table t
    group by ACNo
    having count(*) = 2
    ) a
    ont.ACNo= a.ACNo
    whereexists (select * from @table x where x.ACNo = t.ACNo and QTY > 0)-- positive
    andexists (select * from @table x where x.ACNo = t.ACNo and QTY < 0)-- negative



    KH
  8. khtan New Member

    here it is. Rule 1, 2, 3 there.



    declare @table table
    (
    ACNo varchar(10),
    Name varchar(10),
    QTYint
    )

    insert into @table
    select'B0', 'XYZ', 927union all
    select'B0', 'EU', -4237union all

    select'ZX', 'ABC', 100union all
    select'ZX', 'DEN', 200union all
    select'ZX', 'EU', 300union all

    select'OF', 'AC', 100union all
    select'OF', 'DG', 200union all
    select'OF', 'DF', 250union all
    select'OF', 'EU', -250union all

    select'DX', 'AC', -150union all
    select'DX', 'EF', 100union all

    select'OP', 'AX', 200union all
    select'OP', 'EU', 100

    selectt.*
    from@table t
    inner join
    (-- 2 records
    selectACNo
    from@table t
    group by ACNo
    having count(*) = 2
    ) a
    ont.ACNo= a.ACNo
    whereexists (select * from @table x where x.ACNo = t.ACNo and QTY > 0)-- positive
    andexists (select * from @table x where x.ACNo = t.ACNo and QTY < 0)-- negative
    andexists (select * from @table x where x.ACNo = t.ACNo and Name like 'EU%')-- EU



    KH
  9. vsnreddi New Member

    thanks KH.

    SURYA
  10. rao New Member


    Please look at the below very simple

    select AcNO,Name, QTY from #Table t where t.QTy = (Select top 1 Qty from #table t1 order by QTY desc)
    union
    select AcNO,Name, QTY from #Table t where t.QTy = (Select top 1 Qty from #table t1 order by QTY asc)

Share This Page