I'm trying to build a Matrix that shows week numbers across the top with a list of Sites that contain Lufts on the left. I want to show how many Callouts/Jobs have been created in each week. I've got that working but if there were no Callouts/Jobs in a week I'd still like to show that week column, similarly if there were no Callouts/Jobs for a particular Site/Lift i would still like to show that blank row.
I think i need to somehow select all Weeknumbers in a date range and Join that in some way to my main query and also include Sites/Lifts with no data in my main query ??
I've been trying all sorts of joins/sub queries but can't get it, can anybody help me please ?
here's the sql for my main query...
DATEPART (week,c.datetimereported) cReportedWeek,
WHEN l.liftshortname is Null THEN ' ** Site Level ** '
FROM site s
LEFT JOIN job j
ON s.id = j.siteid
INNER JOIN callout c
ON j.id = c.jobid
INNER JOIN jobline jl
ON j.id = jl.jobid
LEFT JOIN lift l
ON jl.liftid = l.id
WHERE ( c.datetimereported BETWEEN '01/01/2010' AND '12/12/2010' )
AND ( s.id IN (SELECT id
FROM site s
WHERE ( contractid = 3005 )) )
group by DATEPART (week,c.datetimereported), s.id, l.id, l.liftshortname