Prefilter SQL SERVER for power pivot

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi all.

 

I hope I can explain this well. I have a retail mgmt system written in SQL SERVER. I’m trying to connect to the database and select just a small period of sales. From what I’ve read online this will speed up my excel file.

 

I’m using Power query to select certain tables and fields. I’ve read something about folding data. But given the data i’m selecting i see limitations.

 

Below is the query statement I would like to use. But the last line “Having…” limiting the date range wont work.

 

Any thoughts?

SELECT tblsale.sale_link,
tblsale.loc_code AS [LOC#],
tblsale.date AS [TRANS DATE],
tblsale.time,
tblsale.trans_no AS [TRANS#],
tblsale.customer_code AS [CUST#],
tblsalecommission.salesperson,
tblsaleitem.sku_no AS [SKU#],
tblsaleitem.description,
Min(tblsalediscount.disc_code) AS [DISCOUNT CODE],
tblsaleitem.qty AS [UNITS SOLD],
Sum(tblsaleitem.ext_cost) AS COST,
[unit_ext_price] – [unit_ext_best_price] AS DISCOUNT,
tblsaleitem.unit_ext_price,
tblsaleitem.unit_ext_best_price AS [NET RETAIL],
tblsaleitem.line_no,
tblsale.trans_type,
tblsale.void_code,
tblsale.suspended,
tblsaleitem.affect_inv,
tblsaleitem.affect_total,
tblsale.note AS [SALE NOTE],
tblsaleitem.note AS [SALE ITEM NOTE]
FROM tblsalediscount
RIGHT JOIN (tblsale
INNER JOIN (tblsaleitem
INNER JOIN tblsalecommission
ON ( tblsaleitem.line_no =
tblsalecommission.line_no )
AND ( tblsaleitem.sale_link =
tblsalecommission.sale_link ))
ON tblsale.sale_link = tblsaleitem.sale_link)
ON ( tblsalediscount.line_no = tblsaleitem.line_no )
AND ( tblsalediscount.sale_link = tblsaleitem.sale_link )
GROUP BY tblsale.sale_link,
tblsale.loc_code,
tblsale.date,
tblsale.time,
tblsale.trans_no,
tblsale.customer_code,
tblsalecommission.salesperson,
tblsaleitem.sku_no,
tblsaleitem.description,
tblsaleitem.qty,
[unit_ext_price] – [unit_ext_best_price],
tblsaleitem.unit_ext_price,
tblsaleitem.unit_ext_best_price,
tblsaleitem.line_no,
tblsale.trans_type,
tblsale.void_code,
tblsale.suspended,
tblsaleitem.affect_inv,
tblsaleitem.affect_total,
tblsale.note,
tblsaleitem.note
HAVING (( ( tblsale.date ) ># 1 / 31 / 2015 # ));

Also anything anyone can suggest to increase the speed of our system would be most helpful.

I have a new Xeon system with 32gb ddr4, i7, using ssd. Running office 2016. Are there any tips tricks to help. I was thinking of buying a newer system with more cores. Not sure if it would help.

 

Thanks for reading

 

[Author]

Posts