true
true
StackOverflow Gantt from 10 AM to 6 PM
- Times are UTC
textbox1
0.21in
2.91667in
true
By Supplied Location
textbox3
3
0.375in
0.21in
true
=space(0)
textbox2
true
Date
=iif(countDistinct(Fields!Title.Value) = 0,"",First(Fields!resourceID.Value) & ": " & First(Fields!Title.Value)) & iif(countDistinct(Fields!Title.Value) > 1, "... (" & countDistinct(Fields!Title.Value) & " threads in this time pd)","")
=Fields!resourceColor.Value
2pt
2pt
2pt
2pt
Output
=Fields!MonthName.Value
=Fields!MonthNo.Value
0.21in
true
true
=Fields!MonthName.Value
MonthName
=Fields!Date.Value
=Fields!Date.Value
0.21in
true
=Fields!Day.Value
Date
MonthName
=FormatDateTime(Fields!Date.Value, DateFormat.LongDate)
=Fields!dayColor.Value
2pt
2pt
2pt
2pt
=Fields!StartTime.Value
=Fields!StartTime.Value
0.28458in
true
=Fields!halfHourLabel.Value
halfHourLabel
true
Date
=Fields!dayColor.Value
Bottom
Output
true
Output
Output
=Fields!resourceID.Value
=Fields!resourceNumber.Value
2.54167in
true
=Fields!resourceID.Value
groupID
true
Output
true
true
true
Main
0.21in
0.91458in
2.91667in
1
1.26in
2.91667in
1in
1in
1in
1in
0
SQL
Data Source=.;Initial Catalog=StackOverflow
true
Integrated
2dba3e26-bc1f-4eba-8130-9686e6dc2ff0
DataSource1
declare @HoursBottom as tinyint = 10;
declare @HoursTop as tinyint = 18 ;
declare @DateBottom as date = '1/10/2017';
declare @DateTop as date = '1/13/2017' ;
declare @maxColors as tinyint = 7 ;
declare @minItems as tinyint = 2 ;
declare @nullReplacement as varchar(10) = 'Unknown' ;
---- end parameters
/*
-- here's a @Locations
-- parameter dropdown query
-- for the Resource multi-selection
-- if you want this
SELECT isnull(Location,@nullReplacement) as Value,
isnull(Location,@nullReplacement) + ' (' + cast(count(*) as varchar(10)) + ') ' as Label
FROM Users u
join Comments c
on u.ID = c.UserId
where c.CreationDate between
@DateBottom and @DateTop
group by Location
having count(*) >= @minItems
order by count(*) desc ;
*/
with resourceColors as
(
select
0 as resourceColorNumber, 'PaleVioletRed' as resourceColor
union all
select 1, 'LightSalmon' as resourceColor
union all
select 2, 'Tan' as resourceColor
union all
select 3, 'DarkSeaGreen' as resourceColor
union all
select 4, 'LightSteelBlue' as resourceColor
union all
select 5, 'CornflowerBlue' as resourceColor
union all
select 6, 'Plum' as resourceColor
) ,
resources as
(
select row_number() over (order by count(*) desc ) as resourceNumber,
isnull(Location, @nullReplacement) as resourceID
from Users u
join Posts p
on u.id = p.OwnerUserId
inner join (select comments.ID, PostID
from Comments
join Posts on
Comments.PostId = Posts.ID
where Posts.CreationDate between
@DateBottom and @DateTop
) c
on p.ID = c.PostId
where
p.CreationDate between
@DateBottom and @DateTop
group by isnull(Location, @nullReplacement)
having count(*) >= @minItems
)
,
resourceInfo as
(
select resourceID, resourceColor, resourceNumber
from resources m
join resourceColors rc
on m.resourceNumber % @maxColors = rc.resourceColorNumber
)
select
r.resourceNumber,
r.resourceID,
resourceColor,
p.CreationDate as EventStart,
c.AnsweredDate as EventEnd,
p.Title
into #resourceEvents
from resourceInfo r
inner join Users u
on isnull(Location,@nullReplacement) = r.resourceID
inner join Posts p
on u.Id = p.OwnerUserId
inner join (select PostID, max(comments.CreationDate) as AnsweredDate
from Comments
join Posts on
Comments.PostId = Posts.ID
where Posts.CreationDate between
@DateBottom and @DateTop
group by PostID ) c
on p.ID = c.PostId
where p.CreationDate between
@DateBottom and @DateTop;
-- we'll create a "half hour dimension":
select DatePart(month,d.Date) as MonthNo,DatePart(week,d.Date) as Week, d.Date,
DatePart(Day,d.Date) as Day,
d.IsWeekend,
Hour,
cast(cast(Hour as varchar(2)) + ':' + case when BottomHalfHour = 0 then
'00' else '30' end as DateTime) as StartTime,
cast(cast(Hour as varchar(2)) + ':' + case when BottomHalfHour = 0 then
'29' else '59' end as DateTime) as EndTime,
--cast(cast(Hour as varchar(2)) + ':' + case when BottomHalfHour = 0 then
--'00' else '30' end as Time) as StartTime,
--cast(cast(Hour as varchar(2)) + ':' + case when BottomHalfHour = 0 then
--'29' else '59' end as Time) as EndTime,
case when Hour = 0 and BottomHalfHour = 0 then 'mid nt'
when Hour = 12 and BottomHalfHour = 0 then 'noon'
else cast(case when Hour < 13 then Hour else Hour - 12 end as varchar(2)) +
case when BottomHalfHour = 0 then ':00' else ':30' end +
case when Hour < 12 then ' am' else ' pm' end
end
as halfHourLabel
into #halfHours
from World.dbo.DateDimension d
join
(
select top 24 Day-1 as Hour, 0 as BottomHalfHour from World.dbo.DateDimension
union all
select top 24 Day-1 as Hour, 1 as BottomHalfHour from World.dbo.DateDimension
) h
on 1=1
where d.Date between @DateBottom and @DateTop and
h.Hour between @HoursBottom and @HoursTop
order by 1,2
-- we'll create a subset of events
-- based on the user's choices within this set of events
-- and dates/hours for the presentation.
select h.MonthNo, -- add Week here too if you want
h.Date, h.Day, h.Hour,h.StartTime,
h.halfHourLabel, e.resourceID,
case when h.IsWeekend = 1 then 'Gainsboro' else 'WhiteSmoke' end as dayColor,
DateName(month,h.Date) as MonthName,
isnull( e.Title, 'No Title') as Title, isnull(e.resourceColor,'White') as resourceColor, e.resourceNumber
from #halfHours h
left join #resourceEvents e on
cast(eventStart as date) = h.date and
eventStart <= cast(h.Date as DateTime) + h.StartTime and
eventEnd >= cast(h.Date as DateTime)+ h.EndTime
order by 1,2,3,4,5,isnull(e.Title,'ZZZZZ');
drop table #halfHours ;
drop table #resourceEvents ;
true
MonthNo
System.Int32
Date
System.DateTime
Day
System.Int32
Hour
System.Int32
StartTime
System.DateTime
halfHourLabel
System.String
resourceID
System.String
dayColor
System.String
MonthName
System.String
Title
System.String
resourceColor
System.String
resourceNumber
System.Int64
en-US
true
Inch
0527923a-ecb0-4970-9a25-160607dbde5b