DataMajor Scripts available

All the scripts below with an update date of April 2003 or earlier will already be on your machines. They are included here for reference purposes and to allow them to imported into MSQuery/Crystal Reports is required.

The scripts have been grouped by subject matter. These also match up with the Index entries.

Practice Statistics Practice Growth Financial Figures
Average Transaction Value Branch Specific Client Related
Marketting Who has been seen Animal Related
Recalls Analysis Codes User Queries
Update the System Tidy Up Laboratory Work
Stock / Drugs Clinical Queries Scripts and Medication

Practice Statistics (Index)

Name Updated  Details
stats.sql 22-09-2002 How many Clients and Animals are there on the system.
newcli.sql 25-04-2003 Table to Show number of new clients and Animals per Year and Month.
newcli1.sql 26-08-2002 Display a 'dot' graph of the number of new CLIENTS registering over the past three years.
species.sql 26-08-2002 Breakdown by Species of all 'Alive' animals. Gives count and Percentage.
sex.sql 23-09-2002 Breakdown of animal base by sex (just Dogs and Cats), summarise if entire or not. Dead animals will be ignored.
breed.sql 26-08-2002 Look at a specific species giving a breakdown by breed, dead animals are ignored. You will be prompted for the required Species.
othbreed.sql 23-09-2002 This script will ignore Farm, Canine, Feline and Equine cards and breakdown all other species. Dead animals will be ignored and the report will be sorted by breed.
branch.sql 11-04-2003 Various figures by Record Type. Count by SA, FC and EQ records, Client type registered by branch, Total alive/dead animals per site.
linden.sql 11-04-2003 Will give number of clients, broken down by record type, seen between two dates where clinical work has been added.
noshow.sql 24-04-2003 Report on clients who cancelled appointments, failed to keep their appointments or arrived without an appointment. Covers the last 12 months and counts animals.
postcode.sql 11-04-2003 Looks at the 1st part of the Postcode and give a breakdown of the number of CLIENTS in that area. Clients with no animals (all dead) will be ignored.
post1.sql 26-08-2002 Show the Percentage of your client base by postcode area.
anover5.sql 19-04-2003 Who as more than 5 animals?

Growth and Lost Clients (Index)

Name Updated  Details
lost1.sql 15-05-2003 This will list any clients that spent money in the past but have not spent anything in the last full year (+ current year). Bad Debtors, moved away and clients with no animals will be ignored.
lost2.sql 15-05-2003 Look back at the year ending 18 months ago, which clients were in your 'top 200' but have not been back in the last year. This uses the clinical records. Moved Away, Dead and records marked Bad Debt will be ignored.
growth.sql 28-04-2003 Look at number of new animals and number of deaths over last 5 years. Will use the 'Moved Away' Status codes (if set)

Old Queries (Index)

Name Updated  Details
k9breed.sql 26-08-2002 Breakdown of Canine breeds, dead animals are ignored.
febreed.sql 24-08-2002 Breakdown of Feline breeds, dead animals are ignored.
eqbreed.sql 24-08-2002 Breakdown of Equine breeds, dead animals are ignored.

Financial Figures (Index)

Name Updated  Details
income.sql 26-08-2002 Practice income by Month and Year
btf.sql 24-09-2002 Look at how much it costs to treat different species. This will scan the work entered for the LAST 12 months and generate some averages. Will list all animals where more that 2000.00 has been generated.
inbysp.sql 12-04-2003 Income by Species (Canine, Feline, Equine and Others) broken down by Month for the last 12 months.
ibyuser.sql 21-05-2003 Breakdown the work done over a period by analysis code and the user that carried it out. Summarise by User.
ubygroup.sql 7-03-2003 Breakdown the work done over a period by analysis group and the user that carried it out.
fdsplit.sql 26-08-2002 Show the 'spilt' between Fees and Drugs - Clinical lines where BOTH fees and Drugs have been entered. Usually this will be dispensing fees. Report will be broken down by Analysis code, Month and Year.
cc.sql 26-08-2002 Surgery Breakdown for animals SEEN in the last three years. Clients and Animals broken down by surgery they are registered at. Dead animals and clients with no 'live' animals are ignored.
baddebt.sql 25-04-2003 List all clients marked as a Bad Debt. If you leave the balance question blank you will get all Clients otherwise will limit to those with an outstanding balance greater than that specified.
vatbook.sql 10-10-2002 This is the same as the Premvet Historic Vatbook. Enter a RANGE of dates, 10 days, 1 month, 3 months. Note: For end date enter the START of the next month, it will subtract 1 day.
credit.sql 10-10-2002 Lists all animals, the user and the transaction details were a credit has been entered in the last XX days.
spend.sql 14-12-2002 List the clients who have spent the most in the last XX Days. Report can be limited to just the top xx clients.
year.sql 31-12-2002 Top spenders (clients) over the last 12 month period. Shows spend, number of visits and average spend per visit.
class1.sql 16-10-2002 Income in the last 30 days logged against a classification. e.g. petaid Practice Plan etc.

Average Transaction (Index)

Name Updated  Details
atf.sql 7-04-2003 Global Average Transaction Value report. This will include all staff but will exclude the System user 'PVS'.
atu.sql 7-04-2003 Average Transaction Value per User per Month. The System User 'PVS' will be ignored.
atua.sql 27-04-2003 ATV Per User were specific Analysis Codes were Used. (Ex VAT) The System user 'PVS' will be ignored.
fdratio.sql 21-05-2003 Show the ratio of Fees to Drugs on a per user basis, over the last 12 months. The user must have done more than 20 transactions in the period, retired users will be excluded.

Branch Reports (Index)

Name Updated  Details
bincome.sql 12-09-2002 Practice income by Month and Year broken down by site the work was done
batf.sql 12-09-2002 Global Average Transaction Value report. This will include all staff, broken down by Site
batu.sql 12-09-2002 Average Transaction Value per User per Month. The System User 'PVS' will be ignored. Breakdown by Site.
binbysp.sql 12-09-2002 Income by Species (Canine, Feline, Equine and Others) broken down by Month for the last 12 months and by Site the work was done.
bana.sql 20-01-2003 Breakdown by Analysis code by Branch the Work was done. Use the Pivot Table option in Excel to alter presentation.

Client Related Queries (Index)

Name Updated  Details
reg.sql 26-08-2002 Number of new ANIMALS by Month broken down by how referred to you.
hreg.sql 26-08-2002 Total Number of animals and how referred.
phone.sql 26-08-2002 Client telephone list for all client types.
farmp.sql 23-09-2002 Farm Client Telephone List
farml.sql 21-05-2003 List of Farm clients including Map Ref., Herd Ref and Mileage
email.sql 26-08-2002 Client E-Mail Address List
novat.sql 28-08-2002 Clients with a 'W' in the 4th Discount field indicating that drugs are supplied Ex VAT or Classification 1. Dead animals will be ignored.
drugcost.sql 26-08-2002 Clients with a 'V' in the 4th Discount field indicating that drugs are supplied at Cost. Dead animals will be ignored.
class.sql 26-08-2002 Which clients have a 'Classification' against them.
disc.sql 19-04-2003 Who is set up to receive a 'Time of Sale' Discount or will be offered an 'Early Settlement' Discount.
newcli2.sql 25-04-2003 List of new Clients/Animals Registered since a specific date.
visit.sql 26-08-2002 Clients visited the practice the most and the amount they have spent. This will limit the results to the top 200.
locate.sql 25-04-2003 Search for all clients with xxxx in address line. Dead animals are INCLUDED.

Marketing Field (Index)

Name Updated  Details
mtag.sql 22-09-2002 List all clients marked with a marketing tag. This will include clients with NO animals
mtagp.sql 22-09-2002 List all clients marked with a Platinum marketing tag. Clients with no animals will be ignored
mtagg.sql 22-09-2002 List all clients marked with a Gold marketing tag. Will ignore clients with no animals
mtags.sql 22-09-2002 List all clients marked with a Silver marketing tag. Clients with no animals will be ignored.
mtagb.sql 22-09-2002 List all clients marked with a Bronze marketing tag. Will ignore clients with no animals.
mdead.sql 22-09-2002 This will set the marketing tag to NONE for all dead animals that are currently tagged. Premvet and DataMajor will be updated, this will Cross update to remote sites.

Clients seen (Index)

Name Updated  Details
koshep1.sql 25-04-2003 Will give names and addresses of clients seen in last 3 years by a specific user.
kowood1.sql 25-04-2003 Name and addresses of clients seen in between two dates.

Animal Related Queries (Index)

Name Updated  Details
age.sql 11-09-2002 List of all animals that are 20 or older on the system, you will be asked the species, and when they were last seen by the practice.
birth.sql 26-08-2002 Birthday Card List for the over 7's
insured.sql 20-10-2002 Insured Animals by Insurance Scheme.
inscount.sql 26-08-2002 Table showing breakdown of insurance schemes
idchip.sql 26-08-2002 List of all 'Chipped' animals, listed in Chip Number order.
pplan.sql 28-09-2002 Table showing breakdown of Classifications used
iddup.sql 26-08-2002 Check for duplicate ID chips being entered.
post2.sql 23-09-2002 Breakdown of animal base by Postcode area. Dead animals are ignored. Looks at Canine, Feline, Lagomorphs and Equine Records
abyin.sql 26-08-2002 Show a list of Canine/Feline records, whether they are insured or not by age.
mileage.sql 28-08-2002 Mileage: Lists all records where a mileage is recorded.
locked.sql 13-11-2002 List all animals that have had an Administrative lock applied.
status.sql 25-04-2003 Search the Management screen for specific status codes that have been seen in the last XXX months; includes Client Name and Address details.

Recall Related Queries (Index)

Name Updated  Details
zerocall.sql 26-08-2002 List of 'Alive' animals that have a zero interval e.g. they will not be sent recalls.
recsplit.sql 26-08-2002 Number of 'Missed' recalls - Count of when the recall was last done showing the number of non-shows by month/year. This will also show the number of animals you should see for the current runs.
recdue.sql 25-04-2003 Number of animals due to come in - uses Next Recall Due date.
recmth.sql 25-04-2003 Recalls for Specific Species and Specific Month Enter the month.year the recall will be due it. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline. Dead animals are ignored.
recgen.sql 25-04-2003 List animals seen since a specific date but their vaccinations are not up-to-date. Enter the cutoff date for last seen. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline. Enter status codes to exclude. At least one code must be entered for this report to work, dead animals are already excluded.
recage.sql 7-11-2002 Look for all animals over a certain age, for any species (you select) where they have not been back for a vaccination. e.g. OverDue Recalls
cvacc.sql 8-05-2003 Compare Management Record Vaccination Date with Clinical Records
kocass.sql 25-04-2003 List animals who have had a vaccination since a specific date Enter the date for last seen, the species to search for. Enter amount client should have spent more than in last year. Dead Animals are ignored.
kocass1.sql 25-04-2003 List animals who have had a recall since a specific date. Enter recall number, enter the date for last seen, enter species to search for, enter amount client should have spent more than in last year. Dead Animals are ignored.
kopkg.sql 25-04-2003 List animals who have had a vaccination since a specific date Enter the date for last seen and the minimum age of animal. Dead Animals are ignored.
kopks.sql 25-04-2003 Animals over 8 years who have had a vaccination. Prompts for surgery number
korec.sql 25-04-2003 List Rabbits who have had a vaccination since a specific date. Enter the date for last seen. Dead Animals are ignored.

Analysis Code Queries (Index)

Name Updated  Details
anaalpha.sql 26-08-2002 Analysis code list in Alphabetical Order
ananum.sql 26-08-2002 Analysis Code list showing group in numeric order.
group.sql 26-08-2002 Show the current grouping used for DataMajor group queries. These are the itemised billing categories you current have.
anabgrp.sql 26-08-2002 Which group do the Analysis codes fall into.
anangrp.sql 26-08-2002 List all analysis codes that are not currently allocated to a group.

User Code Queries (Index)

Name Updated  Details
users.sql 11-04-2003 List of Active Users on the System.
retired.sql 11-04-2003 Who has left (been tagged as 'Retired'' in password maintenance)
ug.sql 11-04-2003 Current User Profiles you have and number of users in each.
ubg.sql 11-04-2003 Users broken down by profile they are in and the site they work at.
unowork.sql 21-04-2003 Who has not done any work in the last 5 years - that is they exist in the password database but have never entered any work on the system. These users can be marked as retired.

Update Index

Name Updated  Details
upspec.sql 21-05-2003 Check if a breed contains something and update the Species (Regex)
upspec1.sql 21-05-2003 Change the Species e.g. Rabbit to Lagomorph (Regex)
upbreed.sql 2-05-2003 Change the Breed from X to Y e.g. GSD to German Shepherd Dog (Regex)
uproad.sql 29-03-2003 Scan all the Breeds for any Rodents and update Species
upbird.sql 30-03-2003 Scan all the Breeds for any Birds and update Species
upsex.sql 7-04-2003 Look through records and change NF to FN and NM to MN THIS WILL UPDATE THE DATABASE
upbadd.sql 7-04-2003 Scan all the 'Bad Debt' records and set the Admin Lock to Yes

'Tidy-Up' Index

Name Updated  Details
weirdsex.sql 11-04-2003 List all records where their sex is 'un-common', that is less than fifty on the whole system.
weirdsp.sql 11-04-2003 List all animals with an 'Un-Common' Species, less than 20 in the database.
weirdbr.sql 11-04-2003 List all animals where the count of any breed is less than 20. This will include dead animals (if included)
weirdpc.sql 26-07-2002 Look for records where the postcode has not been entered correctly.
dup.sql 30-11-2002 Report on Clients that have the same Surname AND Address Line 1.
dupb.sql 2-12-2002 Report on CLIENTS that have the same 1st Address Lines
dupa.sql 30-11-2002 Report on ANIMALS that have the same Name, Surname AND Address Line 1.
noanimal.sql 26-08-2002 If all the animals belonging to a client are dead then they will have 'no' animals. This report will list those clients. If you are suppressing dead animals then no results will be returned.

Laboratory Work

Name Updated  Details
lab1.sql 28-08-2002 Lab Results broken down by Species only (All Ages) for last 2 years. Will show the Minimum, Maximun and Average for the actual test. Along with the Minimum 'Lower' and maximum 'Upper'
lab2.sql 28-08-2002 Lab Results broken down by Specific Species and Age for last 2 years. Will show the Minimum, Maximun and Average for the actual test along with the Minimum 'Lower' and maximum 'Upper'
lab3.sql 30-08-2002 Specific Lab Results for Specific Species for last 2 years test and Species are optional
lab4.sql 28-08-2002 Lab Results with abnormal results for last 2 years Test and Species are optional.
lab6.sql 3-05-2003 Look at a specific test over the last 2 years and compute some statistics based on Species and Age.

Stock Related (Index)

Name Updated  Details
pom10.sql 21-05-2003 List top 50 Stock items sold in a three month period BY TIMES USED. Enter your POM anaysis code(s) when asked. The report will list the Number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
pom10a.sql 21-05-2003 List top 50 Stock items sold in a three month period BY INCOME. Enter your POM anaysis code(s) when asked. The report will list the Number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
pom10b.sql 21-05-2003 List top 50 Stock items sold in a three month period BY QUANTITY. Enter your POM anaysis code(s) when asked. The report will list the Number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
fdsplit.sql 26-08-2002 Show the 'spilt' between Fees and Drugs - Clinical lines where BOTH fees and Drugs have been entered. Usually this will be dispensing fees. Report will be broken down by Analysis code, Month and Year.
fdratio.sql 21-05-2003 Show the ratio of Fees to Drugs on a per user basis, over the last 12 months. The user must have done more than 20 transactions in the period, retired users will be excluded.
fdincom.sql 21-05-2003 Show the income over the last year and how much was related to drugs. Brokendown by record type.

Various Pivot Table options (Index)

Name Updated  Details
pivot3.sql 21-04-2003 Analysis code usage: Including Date, Species, Breed, Sex, Insured in a format suitable for 'Pivoting' via Excel.
pivot2.sql 24-08-2002 Analysis Code and Billing Group number including date, Species, Breed, Sex in a format suitable for manipulating in Excel.
pivot1.sql 24-08-2002 Income generated by user and analysis code grouped by Month and Year in a format suitable for exporting to Excel.
pivot4.sql 26-07-2002 Breakdown by Year/Month of income generated by user and service offered.
agp.sql 21-05-2003 Animal, Species, Chipped, Neutered with all the services you offer and has the animal taken them up Yes or No. This is automatically updated by the System on re-build.

Clinical Record Queries

Name Updated  Details
hasanot.sql 26-08-2002 Who has HAD a specific clinical entry BUT not had a followup That is had a Dental BUT not bought toothpaste in the same period. The period should be specified in DAYS e.g. in the last 60 days.
abytime.sql 27-02-2003 Look for a specific range of Analysis codes, between two dates and break the work down by the hour it was done. Summarise by hour and time period. Dead animals will be included.
cnot.sql 20-03-2003 Who has NOT had specific work done in a period, leave 'species' blank to check ALL species. Dead animals will be included.
hasanan.sql 27-04-2003 Who has HAD a specific clinical TEXT entry BUT not had a followup of a Group, that is had 'Adv Dental' but not come in for a dental procedure (group). The period should be specified in DAYS e.g. in the last 60 days.
ttomerge.sql 28-09-2002 Search the clinical screen for work entered in the last XX months and include the Client Name and Address details in the output. Dead animals will be ignored.
notana.sql 20-03-2003 Who has NOT had specific work done in a period, leave 'species' blank to check ALL species. Dead animals will be Ignored. This uses Analysis Code and Age (optional) as a criteria
koki1.sql 25-04-2003 Search the clinical screen for work entered in the last XX months and include the Client Name and Address details in the output. Dead animals will be ignored.
koki2.sql 25-04-2003 Lists the clients who have had x analysis code in y dates. Will list the number of transactions and the financial work generated.

Prescriptions + Scripts (Index)

Name Updated  Details
medbyu.sql 7-03-2003 List all Stock Items where Repeat Scripts are associated with.
med30d.sql 7-03-2003 Who is due to come in in the next 30 days for a repeat. Will ignore the 'As required' records.
medany.sql 7-03-2003 Who is on a 'As Required' Prescription.
medexp.sql 7-03-2003 Records that have expired or where maximum times issues has been reached.
medvet.sql 7-03-2003 Who is Flagged as See Vet?
script60.sql 2-05-2003 List all those Clients who have had a 'Prescription' in the last 60 days.
scriptid.sql 2-05-2003 List all the items that clients have requested 'Prescriptions' for in the last 6 months. Will only 'look' at 1st 35 characters of the item.