Get Basic Circulation Totals by Age Range
This came up as a statistical question - How many items were checked out within a certain timeframe by people within certain age ranges? You can modify the TransactionTypeID to suit other, similar questions too. Or fiddle about with the age ranges to get what you need there.
-- Gotta hold that data somewhere
CREATE TABLE #TempCamPatrons
(
PatronID INT
)
/* -------------------------------------------------- */
INSERT INTO #TempCamPatrons
SELECT
DISTINCT pr.PatronID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK) ON td.TransactionID = th.TransactionID
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = td.numValue AND td.TransactionSubTypeID = 6
WHERE -- 6001 = Check out. 6002 = Check in. Adjust these as needed
th.TransactionTypeID IN (6001, 6002)
AND -- Adjust dates as needed
th.TranClientDate BETWEEN '2018-09-30 00:00:00.000' AND '2018-09-30 23:59:59.999'
/* -------------------------------------------------- */
SELECT -- Gotta do some date math
SUM(CASE WHEN pr.Birthdate BETWEEN '20130608' AND '20220608' THEN 1 ELSE 0 END) AS [Age: 0 - 9],
SUM(CASE WHEN pr.Birthdate BETWEEN '20030608' AND '20130607' THEN 1 ELSE 0 END) AS [Age: 10 - 19],
SUM(CASE WHEN pr.Birthdate BETWEEN '19930608' AND '20030607' THEN 1 ELSE 0 END) AS [Age: 20 - 29],
SUM(CASE WHEN pr.Birthdate BETWEEN '19830608' AND '19930607' THEN 1 ELSE 0 END) AS [Age: 30 - 39],
SUM(CASE WHEN pr.Birthdate BETWEEN '19730608' AND '19830607' THEN 1 ELSE 0 END) AS [Age: 40 - 49],
SUM(CASE WHEN pr.Birthdate BETWEEN '19630608' AND '19730607' THEN 1 ELSE 0 END) AS [Age: 50 - 59],
SUM(CASE WHEN pr.Birthdate BETWEEN '19530608' AND '19630607' THEN 1 ELSE 0 END) AS [Age: 60 - 69],
SUM(CASE WHEN pr.Birthdate BETWEEN '19430608' AND '19530607' THEN 1 ELSE 0 END) AS [Age: 70 - 79],
SUM(CASE WHEN pr.Birthdate BETWEEN '19330608' AND '19430607' THEN 1 ELSE 0 END) AS [Age: 80 - 89],
SUM(CASE WHEN pr.Birthdate BETWEEN '19230608' AND '19330607' THEN 1 ELSE 0 END) AS [Age: 90 - 99],
SUM(CASE WHEN pr.Birthdate BETWEEN '19130608' AND '19230607' THEN 1 ELSE 0 END) AS [Age: 100 - 109]
FROM
#TempCamPatrons tempcam
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = tempcam.PatronID
-- Tidy up
DROP TABLE #TempCamPatrons