SQL Query Library



  • Avatar
    DS Russell K

    Ellison created a good post with queries showing order revenue totals and order count totals:



    Comment actions Permalink
  • Avatar
    Learning Experience Team

    Thanks DS Russell K for these additions! We have updated the article to include them.

    Comment actions Permalink
  • Avatar

    Total Sales Report for a given month by invoice date

    DISTINCT O.recordnumber as OrderNumber, O.LocalInvoiceNumber, O.InvoiceDate,
    D.FirstName, D.LastName, O.OrderDate, OT.SubTotal, OT.Shipping, OT.Tax, OT.DiscountTotal, OT.Total, A.CountryCode
    FROM ORD_Order O
    JOIN CRM_Distributors D on O.DistributorID = D.recordnumber
    JOIN ORD_OrderTotals OT on OT.OrderNumber = O.recordnumber
    LEFT JOIN Address A on O.AddressID = A.recordnumber
    WHERE O.Void = '0'
    AND O.InvoiceDate >= '11/01/2019'
    AND O.InvoiceDate < '12/01/2019'
    ORDER BY O.InvoiceDate DESC
    Comment actions Permalink
  • Avatar
    DS Russell K (Edited )

    Question: "How do I get current stat values for associates?"

    The challenge here is to get the key for the statistic that you care about. If you're looking in the Corp Admin (Disco) tool, you'll only see the friendly name of the statistic.The technical name is found in the comp plan program, in the Public API Call "Customers -> Get DataPoint Categories", or by running this query (Note: this takes a minute if you've got a lot of associates):

    SELECT DISTINCT OptionID FROM CRM_Stats_StatValues

    With those values, you can build out this query. It's a tricky way I found on StackOverflow to pivot the table quickly: 

    WITH CurrentStats AS
    Min(Case stat When 'PVKPI' Then [value] End) PV, -- PV (* Your stat name will vary)
    Min(Case stat When 'Qualified' Then [value] End) Qualified, -- Qualified Count (* Your stat name will vary)
    Min(Case stat When 'Enroll' Then [value] End) EnrollCount -- Enrollment Count (* Your stat name will vary)
    FROM CRM_Stats_StatValues v
    v.IsOld = 0 -- The DISCO system marks stats "IsOld=1" whenever a new stat run happens.
    GROUP BY AssociateID
    SELECT TOP 1000 D.FirstName, D.LastName, PV, Qualified, CV
    FROM CurrentStats stat
    JOIN CRM_Distributors D on d.recordnumber = stat.AssociateID
    WHERE stat.PV IS NOT NULL -- This is optional if you want to filter on one of the stats

    Getting that data for past commission periods is just as easy. You'll just need to add "v.ComPeriodID = X", where X is the COM PERIOD ID of the period you want to fetch, selecting from CRM_CommissionAssociateValues_COV. NOTE: If you want the standard commission values, you'd do a simpler query against CRM_CommissionAssociateValues, which has many values per row.


    Comment actions Permalink
  • Avatar
    Ashley Brandis

    Query to be added for Coupons Used


    SELECT O.name, T.OrderNumber, C.DateUsed, C.Amount, P.Code

    FROM ORD_CouponUsage C

    JOIN ORD_Promotions P on C.CouponID = P.recordnumber

    JOIN ORD_OrderTotals T on C.OrderTotalID = T.recordnumber

    JOIN ORD_Order O ON T.OrderNumber = O.recordnumber
    Comment actions Permalink

Please sign in to leave a comment.