Custom Reports
To run these reports:
Replace [X] with the number of days you wish to filter.
SELECT clLastName as LastName, clFirstName as FirstName,clPhone as Phone,
clAccountNumber as AccountNumber,DateDiff(d, FollowUp, GetDate()) as DaysOut
FROM tblClients
WHERE DateAdd(d ,[x],FollowUp)>=60 AND FollowUp IS NOT NULL
ORDER BY clLastName,clFirstName,clAccountNumber
Result Example:
Smith | John | 777-777-8888 | 8481 | 30 |
Smith | Mark | 777-555-4499 | 8482 | 14 |
SELECT AmountPaid,Payments.OrderDate,Payments.CompletionDate,PaymentType.TenderType,
Coalesce(Clients.clLastName, ) + ', ' + Coalesce(Clients.clFirstName,
),Payments.OrderID
FROM tblProductOrderTenderType1 as Payments
INNER JOIN tblProductOrders1 as Orders
ON Payments.OrderID=Orders.OrderID
LEFT JOIN tblTenderType as PaymentType
on Payments.TenderType=PaymentType.TenderTYpeID
LEFT JOIN tblClients as Clients
ON Payments.ClientID =Clients.ClientID
WHERE Orders.ActivePO<>0;
Result Example:
300.00 | 2012-03-08 16:14:10.000 | 2012-03-08 16:14:10.000 | Cash | —, Karen, | 65 |
125.00 | 2012-05-15 17:07:09.000 | 2012-05-15 17:07:09.000 | MC | —, Tim, | 90 |
50.00 | 2012-02-16 16:50:13.000 | 2012-02-16 16:50:13.000 | Visa | —, Joe, | 100 |
150.00 | 2012-02-01 14:31:02.000 | 2012-02-01 14:31:02.000 | Visa | —, Joe, | 30 |
75.59 | 2012-04-06 17:05:10.000 | 2012-04-06 17:05:10.000 | Visa | —, Michael | 501 |
50.00 | 2012-03-22 16:48:08.000 | 2012-03-22 16:48:08.000 | Cash | —, Michael | 501 |
50.00 | 2012-05-01 15:22:38.000 | 2012-05-01 15:22:38.000 | Visa | —, Michael | 501 |
100.00 | 2012-03-13 17:45:55.000 | 2012-03-13 17:45:55.000 | Visa | —, Michael | 501 |
SELECT SUM(AmountPaid),PaymentType.TenderType,LEFT(Payments.CompletionDate,11)
FROM tblProductOrderTenderType1 as Payments
INNER JOIN tblProductOrders1 as Orders
ON Payments.OrderID=Orders.OrderID
LEFT JOIN tblTenderType as PaymentType
on Payments.TenderType=PaymentType.TenderTYpeID
LEFT JOIN tblClients as Clients
ON Payments.ClientID =Clients.ClientID
WHERE Orders.ActivePO<>0
GROUP By LEFT(Payments.CompletionDate,11),PaymentType.TenderType;
Result Example:
100.00 | MC | Jun 8 2012 |
125.00 | MC | May 15 2012 |
75.59 | Visa | Apr 6 2012 |
130.00 | Visa | Apr 23 2012 |
150.00 | Visa | Feb 1 2012 |
50.00 | Visa | Feb 16 2012 |
SELECT SUM(AmountPaid),LEFT(Payments.CompletionDate,11)
FROM tblProductOrderTenderType1 as Payments
INNER JOIN tblProductOrders1 as Orders
ON Payments.OrderID=Orders.OrderID
LEFT JOIN tblTenderType as PaymentType
on Payments.TenderType=PaymentType.TenderTYpeID
LEFT JOIN tblClients as Clients
ON Payments.ClientID =Clients.ClientID
WHERE Orders.ActivePO<>0
GROUP By LEFT(Payments.CompletionDate,11),PaymentType.TenderType
Result Example:
10.00 | Jun 6 2012 |
300.00 | Mar 8 2012 |
50.00 | Mar 22 2012 |
125.00 | May 26 2012 |
100.00 | Jun 8 2012 |
125.00 | May 15 2012 |
SELECT tblClients.CompanyName as Vendor,
Details.ProductName,SUM(Quantity),SUM(Quantity*UnitPrice)
FROM tblProductOrderDetails as Details
INNER JOIN tblProducts as Products
ON Products.ProductID=Details.ProductID
INNER JOIN tblClients
ON tblClients.ClientID=Products.SupplierID
WHERE CompletionDate BETWEEN '01/01/2012' AND '01/01/2013'
GROUP BY tblClients.CompanyName ,Details.ProductName
ORDER BY tblClients.CompanyName ,Details.ProductName
SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,
SUM(round(UnitPrice * Quantity - 1) as TotalSales
FROM tblProductOrderDetails
INNER JOIN tblServiceType
ON tblProductOrderDetails.ServiceTypeID=tblServiceType.ServiceTypeID
WHERE CompletionDate Between '01/01/2012' And '01/31/2012'
AND FilterType=0
GROUP BY ServerName,ProductName
ORDER BY ServerName,ProductName
Sales of Products By Employee - Grouped by Employee - Sorted by Product.
SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,
SUM(round(UnitPrice * Quantity - 2) as TotalSales
FROM tblProductOrderDetails
INNER JOIN tblServiceType
ON tblProductOrderDetails.ServiceTypeID=tblServiceType.ServiceTypeID
WHERE CompletionDate Between '01/01/2012' And '01/31/2012'
AND FilterType=0
GROUP BY ServerName,ProductName
ORDER BY ServerName,ProductName
SELECT ClPhone,CellPhone,SettingName as ServiceProvider
FROM tblClients
INNER Join tblSettingsServer
ON tblClients.InsTertiaryNum =tblSettingsServer.SettingName
SELECT OrderID,DOB
FROM tblproductOrders
LEFT JOIN tblclients on
tblclients.clientid=tblProductorders.clientid
WHERE [CompletionDate] BETWEEN '10/01/2015' AND '3/31/2016'
AND (ServiceType='Rec Sale' OR ServiceType='Med Sale' Or ServiceType='Recommendation')
You will want to make sure that your Marijuana products are all departmentalized as “Marijuana” this will ensure the report pulls only Marijuana Item Sales.
SELECT CompletionDate,Products.ProductName,Quantity
FROM [tblProductOrderdetails] as Details
INNER JOIN tblProducts as Products
ON Products.ProductID=Details.ProductID
INNER JOIN tblProducts as Departments
ON Departments.ProductID=Products.DepartmentID
WHERE [CompletionDate] BETWEEN '10/01/2015' AND '3/31/2016'
AND [Products].[ProdModifierType]=1
AND Departments.ProductName='Marijuana'
ORDER BY [Products].[ProductName]
Items must be 'Typed' by Subcategory according to the naming rules of Oregon. This applies to the report, as well as the requirement for Receipts.
Use the Subcategory Column - with the the Update Items Searched feature (as shown in the video) To assign the Subcategory the name of the type of each item, so that it will show on the receipts as dictated by the State
Make sure that this report is pulling the right type of customers, your patients. Change the ClientType in the statement below to include the proper ID from your client type table.
Run this statement from the Pivot Reporting module:
Select * FROM tblClientType
Then Identify which ClientTypeID is the one that is associated with your Patients. We have had people name their patient types different names, so this variable is something you'll need to verify.
Watch this video to get started and understand the following things you'll need to do and navigate. It will show you how to use the item search section to update and modify items like you will need to.
Product Search and Update|
1. Office - (Main toolbar) - Inventory - Product Search 2. Search for the items you wish to update.
Replace the date range where needed.
SELECT [ClAccountNumber],Subcategory.ProductName,UnitName,SUM(Quantity)
FROM [dbo].[tblProductOrderDetails] AS Details
LEFT JOIN [dbo].[tblProducts] AS Products
ON Products.ProductID = Details.ProductID
LEFT JOIN [dbo].[tblProducts] AS Subcategory
ON Subcategory.ProductID = Products.[productsubtypeID]
LEFT JOIN tblClients
ON tblClients.[ClientID]=Details.[ClientID]
WHERE [ClientType]=10
AND [CompletionDate] BETWEEN '06/01/2016' AND '06/30/2016'
GROUP BY [ClAccountNumber],Subcategory.ProductName,UnitName
ORDER BY [ClAccountNumber],Subcategory.ProductName,[UnitName]
Change the date range:
SELECT SUM(LineTotal),
Subcategory.ProductName,UnitName,SUM(Quantity),
Sum(UnitPrice*Quantity) AS NetSales,
SUM(LineTotal) AS TaxTotal,
Sum(UnitPrice*Quantity) + SUM(LineTotal) AS GrossSales
FROM [dbo].[tblProductOrderDetails] AS Details
LEFT JOIN [dbo].[tblProducts] AS Products
ON Products.ProductID = Details.ProductID
LEFT JOIN [dbo].[tblProducts] AS Subcategory
ON Subcategory.ProductID = Products.[productsubtypeID]
WHERE
Details.ServiceType='Rec Sale'
AND [LineTotal]<>0
AND Details.[CompletionDate] BETWEEN '07/24/2016' AND '07/25/2016'
GROUP BY Subcategory.ProductName,UnitName
ORDER BY Subcategory.ProductName,[UnitName]