need help in sql query

Question

Hi All

I have been able to achieve the below sql query in product code(sku level) but now the requirement now is to achieve the same query on store level basis. Sorry please can soemone guide me here

--drop table #ss

select STORE_CODE,STORE_DESCRIPTION
INTO #ss
from DBO.DIM_STORE WITH(NOLOCK)
WHERE STORE_CODE<20 OR STORE_CODE IN(86)
ORDER BY CAST(sTORE_CODE AS INT)

select
[Product_Code],
isnull([UK RS],0)+isnull([Dir RS],0) as [Company RS],
isnull([UK RS],0) as [UK RS],isnull([Dir RS],0) as [Direct RS]
--into #RS
from
(
select
case when len(PRODUCT_CODE) < 6 then right(('000000'+PRODUCT_CODE),6) else PRODUCT_CODE End as product_code,
count(case when len(PRODUCT_CODE) < 6 then right(('000000'+PRODUCT_CODE),6) else PRODUCT_CODE End) as RangedStores,
case when s.store_code <20 then 'UK RS'
when s.store_code in (86) then 'Dir RS'
else 'Other' end as ranging
from dbo.FCT_RANG_STORES rs with (nolock)
join dbo.dim_store s with (nolock) on rs.STORE_KEY = s.STORE_KEY
join #ss ss with(nolock) on s.STORE_CODE = ss.STORE_CODE
where MIN_STOCK_LEVEL > 0
group by product_code,case when s.store_code <200 then 'UK RS'
when s.store_code in (806) then 'Dir RS'
else 'Other' end
) a
pivot (max(RangedStores)
for ranging in ([UK RS],[Dir RS])) as pivottable

The above query is telling me all sku code that are ranged to stores and works perfectly. So this query is perfect on product level but what i need is to find all sku ranged to particular store on store level

Output for the query

Many thanks Farhan Jamil

Answer 1

Hi @Farhan Jamil ,

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.

In addition,

s.store_code in (806)   

Generally we write

s.store_code in (806)   

Regards
Echo