Custom Reports - Using T SQL to view data

Introduction

The Custom Reports has a quick and easy way to select what you want to filter out and print. However there are times where you want more than what is available and this is were custom T-SQL comes in handy.

The Report Builder has always had a way to make your own SQL statement, it just was not in plain view. In version 6.0 we made that an option from the start. Here are some sample T-SQL statements that we make to find other data, just in case you might find use for them.

Just copy and Paste the SQL Statements into the SQL Editor to see the results

Rounds Fired for 2017

If you use the maintenance ability by logging the rounds that you fired, you can use this to gather how many rounds you fired. This one is for 2017

SQL Statement

SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2017') and cdate(OpDate) < cdate('1/1/2018')

Rounds Fired for 2016

If you use the maintenance ability by logging the rounds that you fired, you can use this to gather how many rounds you fired.

This one is for 2016

SQL Statement

SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2016') and cdate(OpDate) < cdate('1/1/2017')

Rounds Fired from 2012-2018

If you use the maintenance ability by logging the rounds that you fired, you can use this to gather how many rounds you fired. This one shows the past 5 years of rounds fired per year.

SQL Statement

SELECT sum(cInt(RndFired)) as 2018,(SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2017') and cdate(OpDate) < cdate('1/1/2018')) as 2017,
(SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2016') and cdate(OpDate) < cdate('1/1/2017')) as 2016,
`(SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2015') and cdate(OpDate) < cdate('1/1/2016')) as 2015,`
`(SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2014') and cdate(OpDate) < cdate('1/1/2015')) as 2014,`
`(SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2013') and cdate(OpDate) < cdate('1/1/2014')) as 2013,`
`(SELECT sum(cInt(RndFired)) as total FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2012') and cdate(OpDate) < cdate('1/1/2013')) as 2012 `
`FROM Maintance_Details where cdate(OpDate)  > cdate('1/1/2018') and cdate(OpDate) < cdate('1/1/2019')

List Firearms Missing Picture

List the full name of the firearm(s) that are missing a picture

SQL Statement

select g.FullName from Gun_Collection g where not exists  (select * from Gun_Collection_Pictures p where p.cid=g.id)

Simple Firearm List by Origin

List the Full Name Caliber and Place of Origin of the firearm collection.

SQL Statement

SELECT FullName as [Full Name] ,Caliber ,gn.country as [Place of Origin]  from qryGunCollectionDetails g inner join Gun_Nationality gn on gn.id = g.NatID