Solved

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

Posted on 2010-11-12
14
491 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
  • 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now