Fees By Year and Week

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
  sos.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"