Avatar of Steve Jebson
Steve JebsonFlag for United Kingdom of Great Britain and Northern Ireland asked on

Matrix columns (week numbers) and rows with no data but want to display ?

Hi,

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


 
SELECT 
	s.id sId,
    l.id lId,
	count(c.ID) cCount,
    DATEPART (week,c.datetimereported) cReportedWeek,
	CASE
	 WHEN l.liftshortname is Null THEN ' ** Site Level ** '
	 ELSE l.liftshortname
	END lLift

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

Open in new window

SSRS

Avatar of undefined
Last Comment
Steve Jebson

8/22/2022 - Mon
jasonduan

use LEFT JOIN instead:

...
LEFT JOIN callout c
         ON j.id = c.jobid
LEFT JOIN jobline jl
...
ASKER
Steve Jebson

doesn't work, Lifts belong to Sites and a Callout/Job/Jobline is either at Site or Lift level. I want to count the Callout/Job/Jobline's that have been created against a Site/Lift but it's not returning me Site/Lifts that have a count of 0
G Godwin

Create a permanent or a temp table with the week numbers (1, 2, 3, ... 52).

Outer join this table in your query so that all the weeks are represented (right outer or left outer depending on placement in query).

Say you call the table 'WeekNumbers', and the week number column 'WeekNumber'; add WeekNumbers.WeekNumber to the select statement.  You will need this because if there are no calls (c.datetimereported) for the week the cReportedWeek will be null.

Let me know if you have questions from there.
-G
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Steve Jebson

Hi GDG,

Thanks for the pointer on the weeks, I've managed to get what i'm looking for in 2 seperate queries, just not sure how to join them !

here's the 1st query that gets me  the weeks for each Lift i require... (Obviously just limiting the weeks to 4 at the moment)

which returns rows of (SiteID, LiftID, Week) i.e.
2994, 4409, 30
2994, 4409, 31
2994, 4409, 32
2994, 4409, 33
2994, 4418, 30
2994, 4418, 31
2994, 4418, 32
2994, 4418, 33
2994, 4419, 30
2994, 4419, 31
2994, 4419, 32
2994, 4419, 33
 
with  Weeks (week) AS 
(
 SELECT 30 AS week    UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 
)
select  s.id, s.addressline1, l.id, l.liftshortname, week 
from site s  
inner join lift l on l.siteid= s.id 
cross join weeks 


WHERE s.id=2994

Open in new window


and here's the query that gets me the count of Callouts for each Lift by Week

which returns rows of (SiteID, LiftID, Week, Count) i.e.
2994,NULL,33,1
2994,4409,30,1
2994,4409,32,1
 
SELECT 
	   s.id sId,
       s.addressline1 sAddress, l.id as lID,
       CASE
         WHEN l.liftshortname is Null THEN 'At Site Level'
         ELSE l.liftshortname
       END lLift,
 DATEPART (week,c.datetimereported) cReportedWeek, count (c.id) as cCount

FROM   site s
       right OUTER JOIN job j
         ON s.id = j.siteid
       LEFT OUTER JOIN callout c
         ON j.id = c.jobid
       INNER JOIN jobline jl
         ON j.id = jl.jobid
       LEFT OUTER JOIN lift l
         ON jl.liftid = l.id
WHERE  ( c.datetimereported BETWEEN '01/01/2010' AND '12/12/2010' )
       AND (j.LookupJobStatusID <> 7)
       AND ( s.id IN (SELECT id
                         FROM   site s
                         WHERE  ( s.id = 2994 )) )

group by DATEPART (week,c.datetimereported), s.id, s.addressline1, l.id, l.liftshortname

Open in new window


there's a gotcha in that a Callout might not be linked to the Lift, so I suppose i need it to join on the SiteID ??

Sorry to be a pain, i'm going round and round trying to get them together. so i can end up with  rows like  (SiteID, LiftID, Week, Count) i.e.
2994,NULL,33,1
2994, 4409, 30,1
2994, 4409, 31,0
2994, 4409, 32,1
2994, 4409, 33,0
2994, 4418, 30,0
2994, 4418, 31,0
2994, 4418, 32,0
2994, 4418, 33,0
2994, 4419, 30,0
2994, 4419, 31,0
2994, 4419, 32,0
2994, 4419, 33,0
G Godwin

Try this instead.

I created a temp table, populated it, and then used it in your original query.

Let me know if you have questions.

-G
declare @weeks table (week int)
insert into @weeks (week) values 
(1), (2) , (3) , (4) , (5) , (6) , (7) , (8) , (9) 
, (10) , (11) , (12) , (13) , (14) , (15) , (16) , (17) , (18) , (19) 
, (20) , (21) , (22) , (23), (24) , (25) , (26) , (27) , (28) , (29) 
, (30) , (31) , (32) , (33), (34) , (35) , (36) , (37) , (38) , (39) 
, (40) , (41) , (42) , (43), (44) , (45) , (46) , (47) , (48) , (49) 
, (50) , (51) , (52) 

-- select * from @weeks


SELECT 
	s.id sId,
    l.id lId,
	count(c.ID) cCount,
--    DATEPART (week,c.datetimereported) cReportedWeek,
	weeks.week 'cReportedWeek',
	CASE
	 WHEN l.liftshortname is Null THEN ' ** Site Level ** '
	 ELSE l.liftshortname
	END lLift

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
       right outer join @weeks weeks on weeks.week = DATEPART(week,c.datetimereported)
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)
weeks.week, s.id, l.id, l.liftshortname

Open in new window

ASKER
Steve Jebson

didn't realise you could use temp tables in srss, however it doesn;t seem to like this, i've tried all combinations for the values but the only one that works is when i put a single values (1) so i can;t seem to get more valeus in ?? I'm using 2005 if that makes any difference ??

declare @weeks table (week int)
insert into @weeks (week) values (1),(2)

select * from @weeks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Steve Jebson

oh, complains about invlaid syntax near ','
ASKER
Steve Jebson

looks like i need to code it so...
declare @weeks table (weeknum int)
insert into @weeks (weeknum)  values (1)
insert into @weeks (weeknum)  values (2)
insert into @weeks (weeknum)  values (3)
insert into @weeks (weeknum)  values (4)
select * from @weeks

Open in new window

ASKER
Steve Jebson

nope, still only returning a small set of data, is it because of the COUNT ? i.e. it appears i'm only getting rows back where the count > 0
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
G Godwin

The where clause is eliminating weeks that do not have results.

Add
"or c.datetimereported is null"
to your where clause.  

-G
G Godwin

To be specific, replace your where clause with this one...
WHERE  (( c.datetimereported BETWEEN '01/01/2010' AND '12/12/2010' )
		or c.datetimereported is null)
       AND (j.LookupJobStatusID <> 7)
       AND ( s.id IN (SELECT id
                         FROM   site s
                         WHERE  ( s.id = 2994 )) )

Open in new window

ASKER CERTIFIED SOLUTION
G Godwin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
G Godwin

How are you doing with this?
-G
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Steve Jebson

Thanks GDG, took some tweaking, ended up with a couple of temp tables and took me most of yesterday cleaning it up and reformatting. Looking good now though. Thanks again for your help, definately learnt a few things here.

Steve