Suggestion for WHERE condition in SQL Server -

here's edited code: code more close actual code running. gives me records except cancelled records. want include cancelled records not null fields in prod columns.

select  appid,     prod1,  prod2,  prod3,  prod4,  prod5,  prod6,  prod7,  prod8, case when len(prod1) > 0 or    len(prod2) > 0 or        len(prod3) > 0 or        len(prod4) > 0 or        len(prod5) > 0 or        len(prod6) > 0 or        len(prod7) > 0 or        len(prod8) > 0 1 else 0 end nbrincomplete      (case when left(productshortname, 2) in ('ct', 'gr') 1 else 0 end = 0) ,          (ctracks.ctrack null) , (appinitiateddate >= @startdate) , (appinitiateddate < @enddate) ,         (status <> n'cancelled' or     status null)  or        (case when left(productshortname, 2) in ('ct', 'gr') 1 else 0 end = 0) ,         (ctracks.ctrack null) , (status <> n'cancelled' or         status null) ,     (case when           len(prod1) > 0 or              len(prod1) > 0 or                      len(prod1) > 0 or                      len(prod1) > 0 or                      len(prod1) > 0 or                      len(prod1) > 0 or                      len(prod1) > 0 or                      len(prod1) > 0                       1 else 0 end = 1) order appid 

based on sample of desired output, want this:

select appid, status, prod1,prod2,prod3,prod4,prod5,prod6,prod7,prod8   status  coalesce(prod1, prod2, prod3, prod4, prod5, prod6, prod7, prod8) not null    or  status <> 'cancelled'  

that assumes reasonable db schema, such values of prod1,etc columns null, , not empty strings. if empty strings, should fix schema.

---

having seen comment prod[n] types are, indeed, nvarchar, re-iterate: you should fix db schema. have broken. in addition making query more complicated needs be, near-impossible accurately compare dates find things like, example, prod[n] specific row came first (i recommend pulling product fields out separate table, key table plus product number , date columns).

but in near term, , not knowing yet whether empty values in report null or empty strings, can this:

select    appid, status, prod1,prod2,prod3,prod4,prod5,prod6,prod7,prod8      status     status <> 'cancelled' or             len(coalesce(prod1,''))>0 or            len(coalesce(prod2,''))>0 or            len(coalesce(prod3,''))>0 or            len(coalesce(prod4,''))>0 or            len(coalesce(prod5,''))>0 or            len(coalesce(prod6,''))>0 or            len(coalesce(prod7,''))>0 or            len(coalesce(prod8,''))>0   order   appid 

i provide because have needs data now, again: your next task should fix schema use date types.