SQL Query Library

The DirectScale platform includes a SQL Manager that allows you to query the database and export data to create custom reports.

Below are some example SQL queries other customers have used with a brief description of the outcome when it runs. If you have SQL queries that have been helpful for you, leave a comment.

Date Snippets

First day of last month

DATEADD (mm, DATEDIFF (mm, 0, GETDATE ()) - 1, 0)

First day of this month

DATEADD(month, DATEDIFF(month, 0, GetDate()), 0)

For the last 3 hours

AND o.OrderDate > DATEADD(hour, -3, getDate())

For the last month

AND o.OrderDate > DATEADD(month, -1, getDate())

For the last 30 days

AND o.OrderDate > DATEADD(day, 30, getDate())

For the last year

AND o.OrderDate > DATEADD(year, -1, getDate())

How many Associates of each type have placed orders in the last [month, year, day, etc.]?

SELECT d.AssociateType AS AssociateTypeID,
t.AssociateType,
COUNT(D.AssociateType) AS AssociateCount
FROM CRM_Distributors D
JOIN CRM_AssociateTypes t ON t.recordnumber = D.AssociateType
WHERE D.recordnumber IN
(select distinct distributorID FROM ORD_Order o WHERE o.OrderDate > '2019-09-10') -- **NOTE**: OR DATEADD(month, -1, getDate())--
GROUP BY D.AssociateType, t.AssociateType

How many Associates have signed up in each state (or country) in the last month?

SELECT
a.[State], -- **NOTE**: OR a.CountryCode for count by country--.
COUNT(d.recordnumber) AssociateCount
FROM CRM_Distributors d
JOIN Address a ON a.recordnumber = d.AddressID
WHERE d.Void = 0
AND d.SignupDate > DATEADD(month, -1, getDate())
GROUP BY a.[State] -- OR a.CountryCode
ORDER BY AssociateCount DESC

Note: You can replace the DATEADD portion with a specific date if you’d instead specify. In this case, it will just go one month back from today, because of the getDate() function.

Which Associates bought a particular SKU in the last month?

Start with a list of all the SKUs in the system, for reference:

SELECT DISTINCT sku FROM INV_Inventory

Now, here’s the query that joins the right tables to figure out the order, order line items, and some necessary distributor information:

SELECT d.FirstName, d.LastName,
d.EmailAddress, d.BackofficeID,
o.OrderDate, o.recordnumber AS OrderNum,
od.SKU, od.Qty
FROM ORD_OrderDetail od
JOIN ORD_Order o ON o.recordnumber = od.OrderNumber
JOIN CRM_Distributors d ON d.recordnumber = o.DistributorID
WHERE od.SKU = 'YOUR_SKU_HERE'
AND o.OrderDate > DATEADD(month, -1, getDate())

How can I check for duplicate accounts?

To audit accounts, this query looks at Enroller Upline IDs and then compares first and last names. It also indicates whether the Associate created the order to help identify which account to keep.

WITH CTE_Duplicates (LastName, FirstName, EnrollCount, UplineID) AS
(SELECT d.lastname, d.firstname, count(d.recordnumber) AS EnrollCount, et.UplineID FROM crm_distributors d
JOIN crm_enrolltree et ON et.DistributorID = d.recordnumber
WHERE d.signupdate > '2020-02-29'
AND d.void = 0
GROUP BY et.UplineID, d.lastname, d.FirstName
HAVING count(d.recordnumber) > 1),
CTE_TodaysOrders (OrderNumber, DistributorID) AS
(
SELECT
o.recordnumber, o.DistributorID
FROM ord_order o
WHERE o.CommissionDate > '2020-02-29'
)
SELECT --d.recordnumber AS DistID,
d.firstname, d.lastname, et.uplineid, d.signupdate, d.recordnumber, ord.OrderNumber
FROM crm_enrolltree et
JOIN CTE_Duplicates dup ON dup.UplineID = et.UplineID
JOIN CRM_distributors d ON d.recordnumber = et.DistributorID
LEFT JOIN CTE_TodaysOrders ord ON ord.DistributorID = d.recordnumber
JOIN CRM_EnrollTree etu ON etu.uplineID = d.recordnumber
WHERE d.firstname = dup.firstname AND d.lastname = dup.lastname
AND d.SignupDate > '2020-02-29'
ORDER BY et.UplineID, d.firstname, d.lastname

How can I check for duplicate orders?

This query shows anyone on a given day (using the date filter) who has created more than one order, along with the order total, to make sure it wasn’t intentional.

SELECT ord.distributorid, d.BackofficeID, d.firstname, d.lastname, d.PrimaryPhone, ord.recordnumber AS orderNumber,
ord.CommissionDate AS CommDate, ord.OrderDate AS OrderDate, ot.Total
FROM ord_order ord
JOIN ord_ordertotals ot ON ord.recordnumber = ot.OrderNumber
JOIN crm_distributors d ON ord.distributorid = d.recordnumber
WHERE ord.DistributorID IN
(
SELECT DistributorID
FROM ord_order o
WHERE o.InvoiceDate > '2020-02-29'
GROUP BY o.DistributorID
HAVING count(recordnumber) > 1
)
AND ord.InvoiceDate > '2020-02-29'
ORDER BY ord.distributorid, ord.orderdate
DESC

How can I view the top earners in the past 90 days (sum of commission)?

SELECT d.FirstName, d.LastName, d.BackofficeID, SUM(h.Amount) AS Amount
FROM CRM_CommissionHistory h
LEFT JOIN CRM_Distributors d ON h.AssociateID = d.recordnumber
WHERE h.PostDate >= (GetDate() - 90)
GROUP BY d.BackofficeID, d.LastName, d.FirstName
ORDER BY Amount DESC;

How can I view top earners in the past six months (sum of commission)?

SELECT d.FirstName, d.LastName, d.BackofficeID, SUM(h.Amount) AS Amount
FROM CRM_CommissionHistory h
LEFT JOIN CRM_Distributors d ON h.AssociateID = d.recordnumber
WHERE h.PostDate >= dateadd(mm,-6,getdate())
GROUP BY d.BackofficeID, d.LastName, d.FirstName
ORDER BY Amount DESC;

How can I view Total Sales Report + shipping address information (AutoShip orders included)?

SELECT Distinct d.ordernumber, o.DistributorID, op.ShipToName, o.OrderDate, t.Total, ad.Address1, ad.State, AD.zip
FROM ORD_OrderDetail d
JOIN Ord_Order o ON d.Ordernumber = o.recordnumber
JOIN ORD_Ordertotals t ON o.recordnumber = t.OrderNumber
JOIN Address a ON o.AddressID = a.recordnumber
LEFT JOIN ORD_OrderPackages p ON a.recordnumber = p.AddressID
JOIN address ad ON o.AddressID = ad.recordnumber
LEFT JOIN Ord_OrderPackages op ON o.AddressID = op.AddressID
WHERE o.InvoiceDate >= '10/14/2019' AND o.InvoiceDate <= '10/15/2019' AND o.CommissionDate IS NOT NULL;

How can I view Active Members without AutoShips?

SELECT Distinct d.recordnumber AS DistID, d.BackofficeID, d.FirstName, d.LastName, t.AssociateType, s.StatusName, d.EmailAddress, d.TextNumber, d.PrimaryPhone
FROM CRM_Distributors d
LEFT JOIN CRM_AutoShip a ON d.recordnumber = a.AssociateID
JOIN CRM_AssociateTypes t ON d.AssociateType = t.recordnumber
LEFT JOIN CRM_AssociateStatuses s ON s.recordnumber = d.StatusID
JOIN CRM_EnrollTree et ON et.distributorID = d.recordnumber
WHERE d.StatusID IN (0, 1, 6) AND a.AssociateID IS NULL;
Shipping Totals FROM Order Date

How much am I getting from shipping costs applied to the customer?

SELECT SUM(Shipping) AS ShippingTotal FROM ORD_OrderTotals t
JOIN ORD_Order o ON t.OrderNumber = o.recordnumber
WHERE o.OrderDate >= '10/01/2019';

How do I get the Total Sales Report for a given month by invoice date?

SELECT
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

How do I find Associate 1099 Tax Information?

The following is a query that lets you view Associate 1099 Tax information.

DirectScale does not guarantee 100% accuracy, after running this query verify the generated information is correct. You can do this yourself or use an accounting product or service.

Select
Dis.TaxID as SSN,
Dis.backofficeID,
Concat(Dis.FirstName,' ',Dis.LastName) as Name,
Dis.CompanyName,
A.Address1,
A.Address2,
A.City,
A.State,
A.Zip,
A.Countrycode,
Case
When Dis.AssociateType = 1 Then '<Type your Associate Type 1 Name Here>'
When Dis.AssociateType = 2 Then '<Type your Associate Type 2 Name Here>'
When Dis.AssociateType = 3 Then '<Type your Associate Type 3 Name Here>'
Else NULL
End AssociateType,
Sum(CP.Amount) TotalCommissionPaid
from CRM_Distributors Dis
Join Address A On A.Recordnumber = Dis.AddressID
Join CRM_CommissionPaymentBatchPayments CP on cp.AssociateId = Dis.recordnumber
where Year(CP.DatePaid) = 2020 --**Enter Applicable Year Here**
and cp.PaymentStatus = 3
and isnull(Holdings, 0) = 0
Group By Dis.TaxID, Dis.Firstname, Dis.Lastname, Dis.CompanyName,  A.Address1, A.Address2, A.City, A.State, A.Zip, A.Countrycode, Dis.associateType, Dis.backofficeID
Order By Name

If you have more than three Associate Types you can add any extra after the others in the statement using the same format. For Example: When Dis.AssociateType = 4 Then '<Type your Associate Type 4 Name Here>'.

How do I get current stat values for Associates?

The challenge here is to get the statistic key that you care about. If you’re looking in the Corporate Admin, you’ll only see the statistic’s friendly name. You can find the technical term in the compensation 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 found on StackOverflow to pivot the table quickly:

WITH CurrentStats AS
(
SELECT
  AssociateID,
  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
WHERE
  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 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 will do a more straightforward query against CRM_CommissionAssociateValues, which has many values per row.

How do I update Coupon Usage?

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

How many orders have been placed by which AssociateTypes in this timeframe?

This query also includes Autoships. Additional statements in SELECT 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

How can I get sign-up counts by Associate without creating KPIs?

This query uses a CTE to grab sign-up 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 the Corporate Admin, 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 Corporate Admin > Advanced Settings, where you can rename these and configure more.

See Base Associate Types, Associate Types, and Price Groups Explained 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.

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 (
    SELECT
    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(
         COUNT(recordnumber)
     FOR AssociateType IN ([1], [2], [3])
    ) AS pivot_table
)

The preceding provides the CTE table with the EnrollerID, Dist, Retail, and Preferred counts for the provided date range. In this case, that DATEADD function is taking it back to the current month’s beginning. 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())

Now, grab the CTE part of the query and add the Back Office ID first and last names to it:

[Paste CTE query from above here, it's referenced as ThisMonthTotals below]
SELECT
  d.BackOfficeID,
  d.FirstName,
  d.LastName,
  tot.Dist,
  tot.Retail,
  tot.Preferred
FROM CRM_Distributors d
  JOIN ThisMonthTotals tot ON tot.EnrollerID = d.recordnumber

Which Associate personally sponsors the most people?

There are ways to get this in Statistics, but sometimes you want to check stuff out in the tree.

“How many folks are in each Distributor’s first level?”

This query is an easy one, and it runs fast. It makes use of the ROW_NUMBER() function, which you’ll find useful in multiple locations. It looks a little trickier because some Distributor table data has been added to it.

You can switch the Unilevel Tree out for the Enroller Tree by changing CRM_UnilevelTree to CRM_EnrollerTree.

This isn’t used with the Binary tree as it doesn’t give “downline count”.

SELECT UplineID, D.FirstName, D.LastName, D.SignupDate, D.BackOfficeID, MAX(RowNumber) as DownlineCount FROM (
SELECT
    distributorid,
  uplineid,
    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

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

The 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())

Can I view everyone’s Carry Over Volume (Left and Right Leg)?

If you are a Binary Comp Plan, you can use the following query to view everyone in your organization who have Carry Forward Right Leg (CFR) and Carry Forward Left Leg (CFL) volume.

SELECT concat(d.firstname, ' ', d.lastname), d.backofficeID, s.Stat, s.Value, s.PeriodKey
FROM CRM_Stats_StatValues s
JOIN CRM_Distributors d on s.associateID = d.recordnumber
WHERE Stat IN ('CFR','CFL')

Can I view everyone who’ve been inactive for the last 6 months consecutively?

Find people who’ve been inactive for the last 6 months consecutively with no orders or sales whatsoever.

SELECT Recordnumber, BackofficeID, SignupDate, Concat (Firstname,' ',Lastname)
FROM CRM_Distributors
WHERE recordnumber not in (SELECT DistributorID
      FROM ORD_Order
      WHERE (TotalCV != 0) and (TotalQV != 0) and OrderDate >= DATEADD(month, -6, getDate())
                           )
AND Void = 0

Can I view all total shipping charges/costs?

This query will show you all the shipping charges/costs for the specified date range.

SELECT  O.recordnumber as OrderNumber, O.Name, O.InvoiceDate, T.TaxState, T.TaxCounty, A.CountryCode, T.TaxCity, T.TaxRate, OT.Tax, OT.Shipping, OT.SubTotal, OT.Tax + OT.SubTotal + OT.Shipping AS OrderTotal, OT.CurrencyCode
             FROM ORD_Order O
             JOIN ORD_OrderTax T on T.OrderNumber = O.recordnumber
             JOIN Address A on A.recordnumber = O.AddressID
             JOIN ORD_OrderTotals OT on T.OrderNumber = OT.OrderNumber
             WHERE O.InvoiceDate >= '06/01/2021'
             AND O.InvoiceDate < '07/01/2021'
             AND (O.Void = '0' or O.Void IS NULL)
             GROUP BY O.recordnumber, O.Name, O.InvoiceDate, T.TaxState, T.TaxCounty, A.CountryCode, T.TaxCity, T.TaxRate, OT.SubTotal, OT.Tax, OT.Shipping, OT.CurrencyCode
             ORDER BY T.TaxState, T.TaxCounty, T.TaxCity, O.Name

Modify the following snippet with your needed date range:

WHERE O.InvoiceDate >= '06/01/2021'
AND O.InvoiceDate < '07/01/2021'
Was this article helpful?
5 out of 5 found this helpful
Previous Next

Comments

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

    https://help.directscale.com/hc/en-us/community/posts/360038559114-SQL-Query-Order-Revenue-Totals-and-Order-Count-Totals 

     

    0
  • Which distributors have a remaining balance in their Rewards Points ledger?

    Select
         D.Recordnumber AS DS_ID,
         D.BackofficeID,
         SUM (RL.Amount) AS UnusedPoints
    FROM CRM_Distributors D
    JOIN CRM_Rewardpoints R on R.AssociateID = D.recordnumber
    JOIN CRM_Rewardpointledger RL on RL.RewardPointID = R.recordnumber
    GROUP BY D.Recordnumber, D.BackofficeID
    HAVING SUM (RL.Amount) > 0
    ORDER BY UnusedPoints DESC

    1
  • How many standard orders did we process today? Results give a count for each day over last 30 days, excluding canceled orders

    SELECT
         CONVERT(varchar(12),orderdate, 101) as Date,
         COUNT (recordnumber) as [Count]
    FROM ORD_Order
         WHERE OrderDate > DATEADD(day, -30, getDate())
         AND Ordertype = 1
         AND VOID = 0
    GROUP BY CONVERT(varchar(12),orderdate, 101)
    ORDER BY Date DESC

    0

Please sign in to leave a comment.