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.

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.

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

9 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
  • 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
    0
  • 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
    0
  • 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
    0
  • 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
    0
  • 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
    0
  • 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

Please sign in to leave a comment.