Solved

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

Posted on 2010-11-12
14
489 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

20 Experts available now in Live!

Get 1:1 Help Now