~[tlist_sql;
SELECT
TO_CHAR(~[wc:meritstore/meritSqlRangeStart],'MM/DD/YYYY') AS rangestart,
TO_CHAR(~[wc:meritstore/meritSqlRangeEnd],'MM/DD/YYYY') AS rangeend
FROM dual]
~[tlist_sql;
SELECT
CASE WHEN NVL(p.inrange,0) + NVL(r.inrange,0) > 0 THEN '' ELSE 'noActivityRow' END AS srowclass,
s.dcid AS studentdcid,
s.student_number AS student_number,
s.lastfirst AS sname,
NVL(p.earned,0) AS earned,
NVL(r.spent,0) AS spent,
NVL(p.balearned,0) - NVL(r.balspent,0) AS balance
FROM students s
LEFT JOIN (
SELECT
studentsdcid,
SUM(CASE WHEN date_recorded BETWEEN ~[wc:meritstore/meritSqlRangeStart] AND ~[wc:meritstore/meritSqlRangeEnd] THEN points ELSE 0 END) AS earned,
SUM(CASE WHEN date_recorded BETWEEN ~[wc:meritstore/meritSqlRangeStart] AND ~[wc:meritstore/meritSqlRangeEnd] THEN 1 ELSE 0 END) AS inrange,
SUM(CASE WHEN yearid = ~(curyearid) OR '~[wc:meritstore/meritPrefCarryOver]' = '1' THEN points ELSE 0 END) AS balearned
FROM u_nt_merit_points
GROUP BY studentsdcid
) p ON p.studentsdcid = s.dcid
LEFT JOIN (
SELECT
studentsdcid,
SUM(CASE WHEN redeemed_date BETWEEN ~[wc:meritstore/meritSqlRangeStart] AND ~[wc:meritstore/meritSqlRangeEnd] THEN points_spent ELSE 0 END) AS spent,
SUM(CASE WHEN redeemed_date BETWEEN ~[wc:meritstore/meritSqlRangeStart] AND ~[wc:meritstore/meritSqlRangeEnd] THEN 1 ELSE 0 END) AS inrange,
SUM(CASE WHEN yearid = ~(curyearid) OR '~[wc:meritstore/meritPrefCarryOver]' = '1' THEN points_spent ELSE 0 END) AS balspent
FROM u_nt_merit_redemption
GROUP BY studentsdcid
) r ON r.studentsdcid = s.dcid
WHERE s.schoolid = ~(curschoolid)
AND s.enroll_status = 0
ORDER BY s.lastfirst]
~[tlist_sql;
SELECT
TO_CHAR(~[wc:meritstore/meritSqlRangeStart],'MM/DD/YYYY') AS rangestart,
TO_CHAR(~[wc:meritstore/meritSqlRangeEnd],'MM/DD/YYYY') AS rangeend
FROM dual]
~[tlist_sql;
SELECT
i.name AS iname,
NVL((SELECT SUM(quantity) FROM u_nt_merit_redemption WHERE item_id = i.id
AND redeemed_date BETWEEN ~[wc:meritstore/meritSqlRangeStart] AND ~[wc:meritstore/meritSqlRangeEnd]),0) AS qtyredeemed,
NVL((SELECT SUM(points_spent) FROM u_nt_merit_redemption WHERE item_id = i.id
AND redeemed_date BETWEEN ~[wc:meritstore/meritSqlRangeStart] AND ~[wc:meritstore/meritSqlRangeEnd]),0) AS pointsspent
FROM u_nt_merit_item i
WHERE i.schoolid = ~(curschoolid)
ORDER BY qtyredeemed DESC, i.name]
~[tlist_sql;
SELECT
TO_CHAR(MIN(firstday),'YYYY-MM-DD') AS yrstart,
TO_CHAR(MAX(lastday),'YYYY-MM-DD') AS yrend
FROM terms
WHERE schoolid = ~(curschoolid)
AND yearid = ~(curyearid)]
[/tlist_sql]
~[tlist_sql;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') AS todaystr
FROM dual]
[/tlist_sql]