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
  • Avatar

    Question: How many orders have been placed by which AssociateTypes in this timeframe?
    Includes Autoships.
    Additional statements in "select" statement need to be added to the group by statement.


    Select distinct d.recordnumber, concat(d.firstname,' ',d.lastname) as Name, t.associatetype, 
    COUNT(O.recordnumber) as ordercount
    from Ord_Order O
    Join CRM_Distributors D on D.recordnumber = O.distributorID
    Join CRM_Associatetypes T on T.recordnumber = D.Associatetype
    Where O.void = '0' and o.orderdate >= '6/1/2020' and o.orderdate <='6/15/2020'
    group by d.recordnumber, d.firstname, d.lastname, t.associatetype
    Comment actions Permalink
  • Avatar
    DS Russell K (Edited )

    Question: "How can I get signup counts by Associate without creating KPIs?"

    Here, we'll use a CTE to grab signup counts for each associate type for a specific date range, and then tie them to the Enroller's ID. The resulting list will look like this: 

    Enroller ID | # Distributors Enrolled | # Customers Enrolled | # Preferred Customers Enrolled

    In Disco, each associate type has an ID. You'll see this ID in table/column CRM_Distributors -> AssociateType. Usually, the types are configured like this: 

    1 - Distributor
    2 - Retail Customer
    3 - Preferred Customer

    There is a tool in CorpAdmin, in the advanced settings, where you can rename these and configure more. Check out this Associate Types Configuration doc for more info.

    This fact is the basis for this query. Here's the CTE component you can borrow, with a great "PIVOT" keyword in there, to make this query efficient and cool to work with.

    WITH ThisMonthTotals AS -- Pivot table on Associate Type for this month's sign-ups by Enroller
    SELECT EnrollerID, [1] as Dist, [2] as Retail, [3] as Preferred from (
    recordnumber, AssociateType, EnrollerID
    FROM CRM_EnrollerTree et
    JOIN CRM_Distributors d ON d.recordnumber = et.CustomerId
    WHERE d.SignupDate >= DATEADD(month, DATEDIFF(month, 0, GetDate()), 0) -- The first day of the current month
    AND d.void = 0
    ) t PIVOT(
    FOR AssociateType IN ([1], [2], [3])
    ) as pivot_table

    This provides a CTE table that has EnrollerID, Dist, Retail, and Preferred counts for the provided date range (in this case, that "DATEADD" function is taking it back to the beginning of the current month. It's just as easy to do this, to get the totals for the last three months, from today's date:)

    WHERE d.SignupDate >= DATEADD(month, -3, GetDate())

    Great! Now, we just need to grab the CTE part of the query and add our own other report info to it. Let's add Back Office ID, first and last names:

    [Paste CTE query from above here, it's referenced as ThisMonthTotals below]
    FROM CRM_Distributors d
    JOIN ThisMonthTotals tot ON tot.EnrollerID = d.recordnumber

    Nice work, good to go!

    Comment actions Permalink
  • Avatar
    DS Russell K (Edited )

    Question: "Which Associate personally sponsors the most people?"

    There are ways to get this in Statistics, but sometimes you just want to check stuff out in the tree. "How many folks are in each Distributor's first level?" This is a pretty easy one, and it runs fast. It makes use of the ROW_NUMBER() function, which you'll find useful all over the place. It looks a little more complex because we added some Distributor table data to it.

    Please note, you can switch the Unilevel Tree out for the Enroller Tree by changing CRM_UnilevelTree to CRM_EnrollerTree. This isn't as useful against the Binary tree. And note, too, that it doesn't drill down to give "downline count", though we've got a query that does that if you need it. (It's also very often a statistic you'll find in CRM_Stats_StatValues)

    SELECT UplineID, D.FirstName, D.LastName, D.SignupDate, D.BackOfficeID, MAX(RowNumber) as DownlineCount FROM (
    row_number() OVER (PARTITION BY UplineID ORDER BY UplineID) AS RowNumber
    from crm_unileveltree )
    AS U
    JOIN CRM_Distributors D on U.UplineID = D.recordnumber
    WHERE U.RowNumber > 1
    GROUP BY U.UplineID, D.FirstName, D.LastName, D.SignupDate, D.BackOfficeID
    ORDER BY DownlineCount DESC
    Comment actions Permalink
  • Avatar
    Ashley Brandis (Edited )

    Question " How much money do we collect total in one month from shipping"

    Date can be switched at the bottom after o.OrderDate if you want a specific time range other than one month total.

    SELECT SUM(Shipping) ShippingTotal FROM ORD_OrderTotals t
    JOIN ORD_Order o ON t.OrderNumber = o.recordnumber
    WHERE o.OrderDate > DATEADD(month, -1, getDate())


    Comment actions Permalink

Please sign in to leave a comment.