SQL to tidy up an order report

This question is not answered

Hi folks

I'm trying to create an order report in CRM that for on the selected date, it shows each product on a new line and the total quantity ordered for the day for all customers.

I have managed to get each product on a new line, but the quantity is still showing a separate line for each individual order.

I want to show:

Product A total  - 7

Product B total - 6

But instead I have the line totals as well as the grand total.

Is there any way of getting rid of the unrequired detail?

Also, I would like to limit the report to a selection of product families but cannot get the sql syntax right to do so (novice!)

Any help would be greatly appreciated.

Thanks!

All Replies
  • Can you post your SQL query you are currently using.

    Sage CRM specialist and all round fan! Please feel free to follow me on twitter @dannycrm

  • Hi Daniel, it's about as basic as they come!

    Emma

    CREATE VIEW vSummaryOrderReport

    AS

    select * from orders o

    inner join orderitems oi on o.Orde_OrderQuoteID=oi.OrIt_orderquoteid and OrIt_Deleted is null

    where o.orde_deleted is null

  • The only way I've managed it in the past is to hard code into the source view the timeframe (e.g. a 6 month period, or a 3 month period)

    Created using the following SQL (just need to add into the WHERE the hard coded date filter like  orde_opened > GETDATE()-30 to show the information for a rolling 30 days. 

    SELECT prod_name, SUM(TQ.TQ)
    FROM Orders
    OUTER APPLY
        (SELECT orit_productid, SUM(orit_quantity) AS TQ
        FROM OrderItems
        WHERE OrIt_orderquoteid = Orde_OrderQuoteID AND orit_deleted IS NULL AND OrIt_LineType = 'i'
        GROUP BY OrIt_productid) AS TQ
    LEFT OUTER JOIN NewProduct ON prod_productid = TQ.orit_productid
    WHERE Orde_Deleted IS NULL AND prod_name IS NOT NULL
    GROUP BY Prod_name

      CRM Consultant

    Pinnacle Computing Ltd

  • Thanks Matthew

    I tried that but am obviously doing something wrong as I get an SQL error message.

    Here is what I've copied in:

    CREATE VIEW vSummaryOrderReport

    AS

    SELECT prod_name, SUM(TQ.TQ)

    FROM Orders

    OUTER APPLY

       (SELECT orit_productid, SUM(orit_quantity) AS TQ

       FROM OrderItems

       WHERE OrIt_orderquoteid = Orde_OrderQuoteID AND orit_deleted IS NULL AND OrIt_LineType = 'i'

       GROUP BY OrIt_productid) AS TQ

    LEFT OUTER JOIN NewProduct ON prod_productid = TQ.orit_productid

    WHERE Orde_Deleted IS NULL AND prod_name IS NOT NULL AND orde_opened > GETDATE()-30

    GROUP BY Prod_name

    E

  • Hi E.

    What's in the CRM SQL Log? It should tell you what the error is. Let me know and I'll adjust the script accordingly.

      CRM Consultant

    Pinnacle Computing Ltd

  • This is the last entry -

    Feb 11 2019 10:57:05.455 5860 10324 1 execsql,time,sql,errormsg 62 CREATE VIEW vSummaryOrderReport

    AS

    SELECT prod_name, SUM(TQ.TQ)

    FROM Orders

    OUTER APPLY

       (SELECT orit_productid, SUM(orit_quantity) AS TQ

       FROM OrderItems

       WHERE OrIt_orderquoteid = Orde_OrderQuoteID AND orit_deleted IS NULL AND OrIt_LineType = 'i'

       GROUP BY OrIt_productid) AS TQ

    LEFT OUTER JOIN NewProduct ON prod_productid = TQ.orit_productid

    WHERE Orde_Deleted IS NULL AND prod_name IS NOT NULL AND orde_opened > GETDATE()-30

    GROUP BY Prod_name

    Create View or Function failed because no column name was specified for column 2

  • My Mistake, change the first part

    SELECT prod_name, SUM(TQ.TQ)

    to this

    SELECT prod_name, SUM(TQ.TQ) AS Total_Quantity

      CRM Consultant

    Pinnacle Computing Ltd

  • Thank you - that worked a dream!

    Really appreciate it.