SQL - removing data

[imlaug]

Hi all,

I have this SQL shown below. Only problem is that there is a problem with 2 of the days with 2 specific style.

Is there a way to remove style numbers 12170151 and 12138940 for the days 23-12-2019 and 24-12-2019?

SELECT TTL_CALC_COST,
STOCK_AGE,
COLLECTION_NAME,
COLLECTION_TERM,
STOCK_DATE,
STOCK_COUNTRY_NAME,
QUANTITY_TYPE,
WAREHOUSE_NAME,
SUPPLY,
ASSRTMNT_TYPE_NAME,
QUANTITY,
TTL_WHS_EUR,
BRAND_NAME,
STYLES_ID,
SALES_TYPE,
COUNTRY_CMP,
STYLE_CATEGORY_NAME,
STYLE_SUBCATEGORY_NAME,
STYLE_TYPE,
STYLE_NUMBER,
STYLE_NAME,
COLLECTIONS_ID,
ASSORTMENTS_ID,
ASSORTMENT_NAME,
CUSTOMER_GROUPS_ID,
GROUP_NAME,
SUBDEPARTMENT_ID
FROM DISCOVER.DISC_STOCK
Where SUBDEPARTMENT_ID = '21'

[v-lili6-msft]

hi @imlaug 

Add a conditioanal as below:
SELECT TTL_CALC_COST,
STOCK_AGE,
COLLECTION_NAME,
COLLECTION_TERM,
STOCK_DATE,
STOCK_COUNTRY_NAME,
QUANTITY_TYPE,
WAREHOUSE_NAME,
SUPPLY,
ASSRTMNT_TYPE_NAME,
QUANTITY,
TTL_WHS_EUR,
BRAND_NAME,
STYLES_ID,
SALES_TYPE,
COUNTRY_CMP,
STYLE_CATEGORY_NAME,
STYLE_SUBCATEGORY_NAME,
STYLE_TYPE,
STYLE_NUMBER,
STYLE_NAME,
COLLECTIONS_ID,
ASSORTMENTS_ID,
ASSORTMENT_NAME,
CUSTOMER_GROUPS_ID,
GROUP_NAME,
SUBDEPARTMENT_ID
FROM DISCOVER.DISC_STOCK
Where (SUBDEPARTMENT_ID = '21') AND  (STOCK_DATE <> '23-12-2019' OR (STYLE_NUMBER<>12170151 AND STYLE_NUMBER<>12138940))
 AND (STOCK_DATE <> '23-12-2019' OR (STYLE_NUMBER<> 12170151 AND STYLE_NUMBER<>12138940))
 
Regards,
Lin

[Anonymous]

Create measure 

New measure=if(max(Table[datecolumn]) in {"23-12-2019","24-12-2019"} && table[style Number] in {12170151,12138940},1,-1)

then add this measure to visual level filter and set it to 1.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[imlaug]

Hi Pravin Wattamwar,

Thank you for your response, however the struggle isnt to create a measure or an extra column, but to do it via the SQL.

The issue is that i would prefer to have it removed via the SQL, as the SQL is used multiple places.

By doing that i can avoid having to create a measure in every report.

[Anonymous]

So you need SQL of this?

simply update where clause 

where style_number not in (Value1,value2)

and date not in ("value1","value2")

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[imlaug]

But wouldnt that remove the 2 days completely?

I only need to remove the figures for those 2 styles on those 2 days. i still want all other data from those 2 days.

Does it make sense?

[Anonymous]

then simply create cte

;with cte as(

Select 

Case when style_number in (value1,value2) and date in (value1,value2) then null else style_number end as [style number]

from table

)

select * from cte where [style number] is not null

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[imlaug]

It seems more like a solution that i am looking for, however, i am still a rookie at SQLs, so i dont really know how to put it in. Can you show me how to put it in, in relation to the SQL i posted? 🙂

And thank you!

[Anonymous]

;with cte as(SELECT TTL_CALC_COST,
STOCK_AGE,
COLLECTION_NAME,
COLLECTION_TERM,
STOCK_DATE,
STOCK_COUNTRY_NAME,
QUANTITY_TYPE,
WAREHOUSE_NAME,
SUPPLY,
ASSRTMNT_TYPE_NAME,
QUANTITY,
TTL_WHS_EUR,
BRAND_NAME,
STYLES_ID,
SALES_TYPE,
COUNTRY_CMP,
STYLE_CATEGORY_NAME,
STYLE_SUBCATEGORY_NAME,
STYLE_TYPE,
case when STYLE_NUMBER in (val1,val2) && Date in ("val1","val2") then null else STYLE_NUMBER end as [STYLE_NUM] ,
STYLE_NAME,
COLLECTIONS_ID,
ASSORTMENTS_ID,
ASSORTMENT_NAME,
CUSTOMER_GROUPS_ID,
GROUP_NAME,
SUBDEPARTMENT_ID
FROM DISCOVER.DISC_STOCK
Where SUBDEPARTMENT_ID = '21')

select * from CTE where  [style_num] is not null