Excluding field values in script

[Anonymous]

Hi All

I have a table which contains Status,Operational Date,Cancellation Date.In Status column some sites are operational and some are disconnected,also some sites which have cancellation date in current month are also shown Disconnected.Now I am excluding Disconnected sites in script itself by giving where Status<>'Disconnected',but,I need values from sites whose status is disconnected but cancellation date is of current month.Please help me in solving this issue.Thank you in advance

[Anil_Babu_Samineni]

Would you post the script on wall and meantime few rows to handle it

[Anonymous]

Hi Anil

posted sample data,here I used where Status<>'Disconnected' in script,but,I need values where status is disconnected and cancellation date is of particular month form where value is taken,i.e., row-4 and 5 values have to be read and row-6 can be omitted

[tresesco]

Try like:

Where not (status='Disconnected' and month(today())<>Month([Cancellation Date]));

For Jan comparison try like:

Where not (status='Disconnected' and month(addmonths(today(),-1))<>Month([Cancellation Date]));

[surendraj]

Based on your requirement,Exrtracted only row-4 and 5.

LOAD Region,

     Country,

     City,

     SiteID,

     AccountNumber,

     status,

     [Operational Date],

    [Cancellation Date],

     Model,

     Desc,

     [Local Currency],

     Amount

FROM

(ooxml, embedded labels, table is Sheet1)

Where status ='Disconnected' and Month([Cancellation Date])= month(addmonths(today(),-1));

[Anonymous]

Hi Tresesco

I also have some other filters like where country<>'xyz' then how can I write this where statement,also if I need sites from Nov-2016

[tresesco]

It is all about combining AND, OR, NOT properly. The above logic implementation would give you an idea. For specific and exact solution you have to provide with all the conditions at a time.

[Anonymous]

My conditions are: where Country<>'' and Country<>'xyz' and Status<>'Disconnected' and len(trim([Account Number])) > 0);.I gave like this,now I have to get sites which are disconnected but cancellation date is of particular month(November) ,but, completely disconnected sites shouldn't show as mentioned in above where clause

[Anonymous]

Hi Surendra

Please check all my conditions mentioned in this post(where Country<>'' and Country<>'xyz' and Status<>'Disconnected' and len(trim([Account Number])) > 0)) also what should I have to do if data should be for November-2016

[tresesco]

Try like:

Where  Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

  not (status='Disconnected' and month(addmonths(today(),-1))<>Month([Cancellation Date]));