• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

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

0
stevejebson
Asked:
stevejebson
  • 7
  • 6
1 Solution
 
jasonduanCommented:
use LEFT JOIN instead:

...
LEFT JOIN callout c
         ON j.id = c.jobid
LEFT JOIN jobline jl
...
0
 
stevejebsonAuthor Commented:
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
0
 
GDG_DBACommented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
stevejebsonAuthor Commented:
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
0
 
GDG_DBACommented:
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

0
 
stevejebsonAuthor Commented:
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
0
 
stevejebsonAuthor Commented:
oh, complains about invlaid syntax near ','
0
 
stevejebsonAuthor Commented:
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

0
 
stevejebsonAuthor Commented:
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
0
 
GDG_DBACommented:
The where clause is eliminating weeks that do not have results.

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

-G
0
 
GDG_DBACommented:
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

0
 
GDG_DBACommented:
Here's the same thing using a temp table.  (Sometimes table variables have given me trouble in SSRS).

Try this and let me know.

-G
if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
   and o.id = object_id(N'tempdb..#weeks')
)
DROP TABLE #weeks;

create table #weeks (week int)

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

0
 
GDG_DBACommented:
How are you doing with this?
-G
0
 
stevejebsonAuthor Commented:
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now