Post reply

Select statement results

  • August 18, 2022 at 10:13 am

    #4077853

    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'))
     )

  • August 18, 2022 at 11:49 am

    #4077870

    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

  • August 19, 2022 at 1:52 pm

    #4078376

    Thank you. Working perfect.

  • August 19, 2022 at 4:21 pm

    This was removed by the editor as SPAM

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

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