Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
imlaug
Frequent Visitor

SQL - removing data

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'

8 REPLIES 8
v-lili6-msft
Community Support
Community Support

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

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
Not applicable

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.

 

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
Not applicable

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.

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
Not applicable

;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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.