SQL Query Library

Learning Experience Team

November 04, 2019 20:14

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.

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

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.

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.

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'

Comments

10 comments

DS Russell K
February 25, 2020 04:35

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
Harrison
August 16, 2021 21:00

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
Harrison
September 01, 2021 19:07

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

Harrison

December 03, 2021 20:59 (Edited April 13, 2022 22:25)

How can I see all of my Shipping Methods at once?

select
SM.Recordnumber as ShipMethodID,
SM.Void,
SM.MethodName,
R.Name,
CASE
WHEN SM.Shippingtype = 1 then 'FixedCost'
WHEN SM.Shippingtype = 2 then 'PerPound'
WHEN SM.Shippingtype = 3 then 'Table'
WHEN SM.Shippingtype = 4 then 'LogisticsProvider'
ELSE 'Unknown'
END as ShippingType,
W.WarehouseName
from ORD_ShippingMethods SM
JOIN INV_Warehouse W on W.recordnumber = SM.warehouseID
JOIN ORD_ShippingMethods_Regions SMR on SMR.ShippingMethodID = SM.recordnumber
JOIN Regions R on SMR.RegionID = R.recordnumber
Order by SM.recordnumber

Harrison

December 10, 2021 19:13

Auditing Associate Types and their Status - what do the numbers look like?
If you want to exclude voided accounts from your results, remove the "--" from the WHERE line. As is, this will report all accounts regardless of void status in CRM_Distributors.

SELECT 
   A.AssociateType, 
   S.StatusName, 
   count(d.recordnumber)as [Count]
FROM CRM_Distributors d
JOIN CRM_Associatetypes A on A.recordnumber = D.associatetype
JOIN CRM_AssociateStatuses S on S.recordnumber = D.StatusID
--WHERE d.void = 0 
GROUP BY S.StatusName, A.associatetype

Harrison

January 24, 2022 20:10

How do I view the order history of a specific Sponsor's Downline?

Select 
Concat (D.Firstname,' ',D.Lastname) as SponsorName,
O.Name as CustomerName,
O.recordnumber as OrderNumber,
O.OrderDate,
OD.SKU,
OD.Amount,
O.Email
FROM Ord_Order O
JOIN Ord_OrderDetail OD on OD.Ordernumber = O.recordnumber
JOIN CRM_EnrollTree E on O.DistributorID = E.DistributorID
JOIN CRM_Distributors D on D.recordnumber = E.UplineID
WHERE D.BackofficeID = '15F92' -- Change this to pull the Sponsor you're inquiring for, currently set to Sam Space
AND O.OrderDate >= '1/1/2022' 
AND O.VOID = 0 -- As written, this will exclude cancelled orders. To include cancelled orders, change this to a 1

Harrison

May 06, 2022 19:04 (Edited July 18, 2022 22:51)

How can I audit Tree Movements that have been executed by Corporate Users?
NOTE: If you process a lot of movements, consider narrowing the date range.

select  ST.Date, ST.[user] as CorporateAgent, ST.AssociateID as AffectedUser_DS_ID, D.BackofficeID as AffectedUser_BackofficeID, ST.subject as MovementDetail
from CRM_SupportTickets ST 
JOIN CRM_Distributors D on D.recordnumber = ST.associateID
where ST.subject like '%associate placement%'  
AND ST.date > '7/15/2022' -- edit the start date, results will be inclusive of this date
AND ST.date < '7/17/2022' -- edit the end date, results will be exclusive of this date
AND ST.[user] != ''
Order By ST.Date DESC

Harrison

June 03, 2022 16:45 (Edited June 03, 2022 17:11)

Autoship Inventory Planning
How many (qty) of which items (SKU) do I need to be prepared to ship per date?
Report includes "today" and future Next Process Dates through the next 365 days, ordered by greatest QTY SKU per date.

SELECT AI.ItemID, I.SKU, A.NextProcessDate, count(AI.recordnumber) as QTY 
FROM CRM_Autoshipitems AI 
JOIN INV_Inventory I on I.recordnumber = AI.itemID
JOIN CRM_Autoship A on A.recordnumber = AI.autoshipID
WHERE A.NextProcessDate > DATEADD(day, -1, CAST(GETDATE() AS date))
AND A.NextProcessDate < DATEADD(day, 366, CAST(GETDATE() AS date))
GROUP BY A.NextProcessDate, AI.itemID, I.SKU
ORDER BY A.NextProcessDate, QTY DESC

Anika
June 22, 2022 20:28

Query to see specific users IP addresses (last two lines should be edited based on what you need):

select concat (d.FirstName,' ', d.LastName), u.username, ul.IPAddress, ul.logintimestamp
from userloginhistory ul
join users u on u.BackOfficeId = ul.UserId
join crm_distributors d on d.recordnumber = ul.UserID
where OriginTypeID in (2,3)
and ul.logintimestamp >= '06/01/2022'
and d.BackofficeID = '117'

0

Harrison Smith

December 22, 2022 00:04

Top Enroller Query - modified version of the CorpAdmin report. This version only counts enrollments with a valid order within the allotted Date Range.

select 
    D.Firstname, 
    D.Lastname, 
    D.BackofficeID, 
    CASE
        When S.Highestrank = 30 Then 'Rank 1' --update these with your rankIDs and Names
        When S.Highestrank = 40 Then 'Rank 2' --update these with your rankIDs and Names
        Else 'SomeOtherRank'--filler, you should update this too
        End HighestRank,
    A.State, 
    D.SignupDate, 
    D.PrimaryPhone as Phone,
    D.TextNumber,
    D.emailaddress,
    Count (Case WHEN D2.associatetype = 1 THEN 1 Else Null End) as Dist_Enrolled_W_Order,
    Count (Case WHEN D2.associatetype = 2 THEN 1 Else Null End) as RC_Enrolled_W_Order,
    Count (Case WHEN D2.associatetype = 3 THEN 1 Else Null End) as PC_Enrolled_W_Order --if you have additional Associatetypes, copy/paste these COUNTs and update the associatetypeID
    
From CRM_Distributors D
JOIN CRM_EnrollTree E on E.uplineID = D.recordnumber
Join Address A on A.recordnumber = D.addressID
JOIN CRM_Distributors D2 on D2.recordnumber = E.distributorID
JOIN CRM_Stats S on S.associateID = D.recordnumber
Where D2.recordnumber in (
    select o.distributorID 
    from Ord_Order o 
    join crm_distributors D on d.recordnumber = o.distributorID 
    where o.commissiondate >= '12/1/2022' 
    and o.commissiondate < '1/1/2023' --only looks for valid orders in this period
)
and D2.signupdate >= '12/1/2022' 
and D2.signupdate < '1/1/2023' --only looks for valid signups from this period with a valid order from the period designated above
and d2.void = 0
and s.periodkey in (
    select top 1 periodkey 
    from CRM_Stats order by last_modified desc --pulls highest rank from current period
)
Group By D.BackofficeID, D.firstname, d.lastname, a.state, d.signupdate, d.primaryphone, d.emailaddress, s.highestrank, D.textnumber

Please sign in to leave a comment.