Prev: To hide database schema
Next: Date Range - One Date Provided
From: Mark on 25 Feb 2010 08:31
I have no idea how to do this, but hopefully someone out there can help.
Running SMS 2003 and I have the two reports below that I would like to
combine with a change.

I would like to be able to see the username and time of day usage for each
user hit, not just the last usage along with everything in that first
report.
I would also like to just pull all the data for all games and all dates
instead of having to specify. We aren't that big that I need to cut this
list down.

Any ideas? Thanks a bunch for any help!

Mark


"Users that have run a specific metered software program"

declare @TimeKey int
declare @days float

set @TimeKey=100*@Year+(a)Month

select @days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))
from v_SummarizationInterval where TimeKey=(a)TimeKey

if IsNull(@days,0) > 0
select mu.FullName,
DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
SUM(UsageCount) + SUM(TSUsageCount) as C021,
ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2) as C022,
ROUND(SUM(UsageTime)/60.0,2) as C023,
ROUND(SUM(UsageTime)/60.0/(SUM(UsageCount) + SUM(TSUsageCount)),2) as
C024,
ROUND(SUM(UsageTime)/60.0/@days,2) as C025
from v_MeteredUser mu
join v_MonthlyUsageSummary mus on mu.MeteredUserID=mus.MeteredUserID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
where mf.ProductName = @RuleName
and mus.TimeKey = @TimeKey
group by mu.FullName
having SUM(UsageCount) + SUM(TSUsageCount) > 0
order by mu.FullName

*************

"Time of day usage summary for a specific metered software program"

set nocount on
declare @hour int
create table #hours (Hour int)
set @hour=0
while @hour < 24
begin
insert into #hours(Hour) values(@hour)
set @hour = @hour + 1
end

create table #avgusage
(
hour int,
dow int,
ucount int
)

insert into #avgusage(hour,dow,ucount)
select DATEPART(hour,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart)),
DATEPART(dw,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart)),
AVG(IsNULL(UCount,0))
from v_FileUsageSummaryIntervals fusi
left join
(
select fus.IntervalStart, SUM(fus.DistinctUserCount) as UCount
from v_FileUsageSummary fus
join v_MeteredFiles mf on fus.FileID=mf.MeteredFileID
where fus.IntervalStart >= DATEADD(day,-90,GetDate())
and mf.SecurityKey = LEFT(@RuleName,8)
and fus.IntervalWidth=60
group by fus.IntervalStart
) as USums on fusi.IntervalStart=USums.IntervalStart
where fusi.IntervalWidth=60 and fusi.IntervalStart >=
DATEADD(day,-90,GetDate())
group by DATEPART(hour,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart)),
DATEPART(dw,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart))
select
hrs.Hour,
IsNULL((select ucount from #avgusage where dow=1 and hour=hrs.Hour),0) as
C042,
IsNULL((select ucount from #avgusage where dow=2 and hour=hrs.Hour),0) as
C043,
IsNULL((select ucount from #avgusage where dow=3 and hour=hrs.Hour),0) as
C044,
IsNULL((select ucount from #avgusage where dow=4 and hour=hrs.Hour),0) as
C045,
IsNULL((select ucount from #avgusage where dow=5 and hour=hrs.Hour),0) as
C046,
IsNULL((select ucount from #avgusage where dow=6 and hour=hrs.Hour),0) as
C047,
IsNULL((select ucount from #avgusage where dow=7 and hour=hrs.Hour),0) as
C048
from #hours hrs
order by hrs.Hour


drop table #avgusage
drop table #hours


 | 
Pages: 1
Prev: To hide database schema
Next: Date Range - One Date Provided