Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-12
14
Medium Priority
?
514 Views
Last Modified: 2012-05-10
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
Comment
Question by:stevejebson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 11

Expert Comment

by:jasonduan
ID: 34121434
use LEFT JOIN instead:

...
LEFT JOIN callout c
         ON j.id = c.jobid
LEFT JOIN jobline jl
...
0
 
LVL 1

Author Comment

by:stevejebson
ID: 34121562
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34125047
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
Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

 
LVL 1

Author Comment

by:stevejebson
ID: 34136575
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34137301
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
 
LVL 1

Author Comment

by:stevejebson
ID: 34137580
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
 
LVL 1

Author Comment

by:stevejebson
ID: 34137593
oh, complains about invlaid syntax near ','
0
 
LVL 1

Author Comment

by:stevejebson
ID: 34137620
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
 
LVL 1

Author Comment

by:stevejebson
ID: 34137722
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34138395
The where clause is eliminating weeks that do not have results.

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

-G
0
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34138431
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
 
LVL 10

Accepted Solution

by:
GDG_DBA earned 2000 total points
ID: 34139886
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34149156
How are you doing with this?
-G
0
 
LVL 1

Author Closing Comment

by:stevejebson
ID: 34153044
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question