Fees By Year and Week
Back to Query Directory
We want to see a breakdown of fees charged by year and week.
Here we use some interesting functions, such as DATEPART, which returns a specified portion of a date value, as well as more commonly used aggregate functions like SUM, MIN and MAX. We also use the CAST function to return the result of the AVG function as a number with two decimal places. We also add a little spice by throwing in the ROLLUP function to give us totals and subtotals. Where you see NULL in the results, interpret it as meaning "ALL". Therefore a NULL in the YEAR column should be interpretted as the total line for all the years.
SELECT
DATEPART(yy,trandate) AS "YEAR",
DATEPART(wk,trandate) AS "WEEK_Number",
MIN(trandate) AS "FROM",
MAX(trandate) AS "THROUGH",
CAST (AVG(amount) AS DECIMAL(12,2)) AS "AVERAGE FEE",
SUM(amount) AS "TOTAL FEES CHARGED"
FROM
journal
WHERE
trandate BETWEEN '2000-01-01' AND '2003-12-31'
AND licnum = 101
AND trantype = 'S'
GROUP BY
ROLLUP("YEAR","WEEK_Number" )
ORDER BY
"YEAR","WEEK_Number"
DATEPART(yy,trandate) AS "YEAR",
DATEPART(wk,trandate) AS "WEEK_Number",
MIN(trandate) AS "FROM",
MAX(trandate) AS "THROUGH",
CAST (AVG(amount) AS DECIMAL(12,2)) AS "AVERAGE FEE",
SUM(amount) AS "TOTAL FEES CHARGED"
FROM
journal
WHERE
trandate BETWEEN '2000-01-01' AND '2003-12-31'
AND licnum = 101
AND trantype = 'S'
GROUP BY
ROLLUP("YEAR","WEEK_Number" )
ORDER BY
"YEAR","WEEK_Number"
CategoryQueries
