Select statement results

  • Hi,

    I am trying to get result when only C drive is less than 10% and other drives less than 20% but exclude server vc5 for e,g,h,i drives - "SERVER='VC5' and drive not in ('e','g','h','i')".

     

    drop table tbl_test
    go

    create table tbl_test
    (Server varchar(200),DRIVE varchar(200),FREE_SPACE_IN_MB int,
    used_SPACE_IN_MBint, Total_SPACE_IN_MBint, Precentage_free int
    )

    insert into tbl_test values ('VC1','C',89899,2007248,2097147,4)
    insert into tbl_test values ('VC1','E',736379,836355,1572734,47)
    insert into tbl_test values ('VC1','D',15100,87298,102398,15)
    insert into tbl_test values ('VC2','C',15100,87298,102398,15)
    insert into tbl_test values ('VC2','E',15100,87298,102398,15)

    insert into tbl_test values ('VC5','C',15100,87298,102398,15)
    insert into tbl_test values ('VC5','E',15100,87298,102398,15)
    insert into tbl_test values ('VC5','G',15100,87298,102398,15)
    insert into tbl_test values ('VC5','H',15100,87298,102398,15)
    insert into tbl_test values ('VC5','I',15100,87298,102398,15)
    insert into tbl_test values ('VC5','J',819571,1228426,2047997,40)
    insert into tbl_test values ('VC5','K',15100,87298,102398,15)

    -- Server -'VC2','E' is not coming in the list

    -- Other than C drive
    select * from tbl_test
    where (
    drive ='c' and Precentage_free<=10) or ( (Precentage_free<20) and
    (SERVER='VC5' and drive not in ('e','g','h','i'))
    )
  • It seems there are 3 compound conditions: 1)  where (drive=c and percentage free<10), or 2) where (drive!=c and percentage free<20), and 3) where not (server=vc5 and drive in (...)).  Maybe like this

    select *
    from #tbl_test
    where (drive ='c' and Precentage_free<=10)
    or (drive !='c' and Precentage_free<20)
    and not (server='VC5' and drive in ('e','g','h','i'));

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thank you. Working perfect.

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply