barnettjacob
IS-IT--Management
Hi, I've got the following query that I use in PowerPivot to bring through a chunk of the product table from our SQL Server Database. It worked brilliantly until I decided to throw a load of other stuff in there in terms of pulling in sales and goods receipt data from other tables - from a PowerPivot point of view this is hugely beneficial however its now taking an eternity for the thing to run. If anybody has any ideas as to what I could do the improve performance that would be appreciated!
Regards
Jacob
Regards
Jacob
SQL:
select
Distinct(p.ProductCode) as 'Productcode',
Description,
websiteshortdescription as 'Description 2',
(case
when p.analysis1code = 'baby' or p.analysis1code = 'home' or p.analysis1code = 'MC H&T' then 'H&T'
when p.analysis1code = 'mcclothing' or p.analysis1code = 'fashion' then 'Apparel'
when p.analysis1code = 'elc' or p.analysis1code = 'giftware' then 'Toy and Gift' else 'other' end) as 'Main Division',
(select d.description from eee..analysis2 d where d.analysis2code=p.analysis2code) as Department,
(select e.description from eee..analysis3 e where e.analysis3code=p.analysis3code) as Class,
(select f.description from eee..analysis4 f where f.analysis4code=p.analysis4code) as 'Sub Class',
p.Analysis6Code,
p.Analysis7Code as Concession,
p.Analysis5Code as Story,
p.analysis8code as Origin,
(select g.name from eee..supplier g where g.suppliercode=p.supplier1) as Supplier,
c.BuyPrice1 as Cost,
p.StyleCode as Style,
p.ColourCode as Colour,
p.SizeCode as Size,
Purchasepacksize as 'Pack Size',
p.SeasonCode as 'Season',
pr.RetailPrice as 'Retail Price',
pr.fullprice as 'Full Price',
p.extrainfo6 as 'Compliance Status',
p.extrainfo7 as 'Price Status',
p.extrainfo15 as 'Order Type',
(select g.description from eee..brand g where g.brandcode=p.brandcode) as Brand,
(SELECT CONVERT(VARCHAR(10), (select min(sm.date) from eee..stockmovementtbl sm where sm.description = 'goods receipt' and sm.productcode = p.productcode), 103)) as 'First Receipt',
(SELECT CONVERT(VARCHAR(10), (select max(sm.date) from eee..stockmovementtbl sm where sm.description = 'goods receipt' and sm.productcode = p.productcode), 103)) as 'Last Receipt',
(SELECT CONVERT(VARCHAR(10), (select min(s.saledate) from eee..vwsaleline_gp s where s.productcode = p.productcode), 103)) as 'First Sale',
(SELECT CONVERT(VARCHAR(10), (select max(s.saledate) from eee..vwsaleline_gp s where s.productcode = p.productcode), 103)) as 'Last Sale',
(select sum(coalesce(s.fcextendednetamount-s.fcextendedtaxamount,0)) from eee..vwsaleline_gp s where s.productcode = p.productcode and s.saledate >= '07/04/12') as 'YTD Sales',
(select sum(coalesce(s.grossprofitalternateGP,0)) from eee..vwsaleline_gp s where s.productcode = p.productcode and s.saledate >= '07/04/12') as 'YTD GP$',
(select sum(coalesce(s.quantity,0)) from eee..vwsaleline_gp s where s.productcode = p.productcode and s.saledate >= '07/04/12') as 'YTD Units'
from eee..product p
left join eee..Cost c on c.ProductCode = p.ProductCode and c.BranchCode = '99'
left join eee..price pr on pr.ProductCode = p.ProductCode and pr.BranchCode = '99'
where (Purge = 0 or (select sum(s.fcextendednetamount-s.fcextendedtaxamount) from eee..vwsaleline_gp s where s.productcode = p.productcode and s.saledate >= '06/27/10') <> 0)