Home Forums Power Pivot Prefilter SQL SERVER for power pivot

This topic contains 0 replies, has 1 voice, and was last updated by  sjhc1177 3 years, 9 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #8022

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

     

Viewing 1 post (of 1 total)

The forum ‘Power Pivot’ is closed to new topics and replies.