Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help with summing multiple rows in columns

Author  Topic 

tllrmr
Starting Member

16 Posts

Posted - 2014-07-09 : 13:51:37
Hi - SQL novice here and I am kind of over my head. I am working with a MS SQL database associated with SCCM 2007. SCCM collects software product usage data, and I am tasked with generating a report that will return results between two user-chosen date ranges. I set up prompts for month, year, endmonth, endyear. They would enter 2, 2014, 4, 2014 for example. Timekey would equal 201402, endtimekey = 201404. The tables I am concerned about look like this:

v_MonthlyUsageSummary

TimeKey ResourceID FileID UserID UsageCount TSUsageCount UsageTime LastUsage
201402 108343 2533622 40038 122 0 255027 2014-02-28 13:29:39.000
201402 108340 2533622 40665 2 0 12 2014-02-27 15:58:13.000
201402 108932 2533622 31708 0 10 21670 2014-02-14 20:41:35.000
201403 124035 5040065 42145 15 0 175629 2014-03-31 19:37:37.000
201403 124036 5040065 37579 46 0 773711 2014-04-01 00:00:00.000

v_SummarizationInterval

TimeKey IntervalStart
201310 2013-10-01 00:00:00.000
201311 2013-11-01 00:00:00.000
201312 2013-12-01 00:00:00.000
201401 2014-01-01 00:00:00.000
201402 2014-02-01 00:00:00.000
201403 2014-03-01 00:00:00.000
201404 2014-04-01 00:00:00.000
201405 2014-05-01 00:00:00.000
201406 2014-06-01 00:00:00.000
201407 2014-07-01 00:00:00.000


The problem is I only know how to grab one timekey, but I need to combine multiple timekeys to sum up the usage counts for each workstation.

Here is the full query. If you need examples of more tables, let me know. Thanks in advance for any help you can send my way!


declare @TimeKey int
declare @months float
declare @endTimeKey int
set @TimeKey=100*@Year+@Month
set @endTimeKey=100*@endYear+@EndMonth

select @months=DATEDIFF(d,@timekey,@endTimeKey)

from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @endTimeKey

if IsNULL(@months,0) > 0

select
sys.Netbios_Name0, mf.productname, MAX(mf.meteredfileversion) as 'Product Version', sys.User_Name0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.topconsoleuser0, USR.displayName0 as 'User Display Name', MAX(v_RA_User_UserOUName.User_OU_Name0) as 'User OU', USR.unique_user_name0 as 'FQ User Name', USR.telephonenumber0 as 'Telephone', USR2.telephonenumber0 as 'Telephone TCU', USR.mail0 as 'E-Mail', USR2.mail0 as 'E-Mail TCU',
DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
SUM(UsageCount) + SUM(TSUsageCount) as C021,
ROUND((SUM(UsageCount) + SUM(TSUsageCount))/(@months*30),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/(@months*30),2) as C025
from v_R_System sys
join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP TCU on TCU.resourceID = sys.resourceID
full join v_R_User USR on sys.user_name0 = USR.user_name0
join v_R_User USR2 on TCU.TopConsoleUser0 = USR2.Unique_User_Name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
left join v_RA_System_SMSInstalledSites inst on sys.ResourceID=inst.ResourceID
left outer join v_RA_User_UserOUName on v_RA_User_UserOUName.ResourceID = USR.ResourceID
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = sys.ResourceID
where mf.ProductName = 'Adobe Acrobat'
and mus.TimeKey = @endTimeKey
group by sys.Netbios_Name0, sys.User_Name0, USR.displayName0, USR.unique_user_name0,
USR.telephonenumber0, USR2.telephonenumber0, USR.mail0, USR2.mail0, mf.productname, V_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.topconsoleuser0
having SUM(UsageCount) + SUM(TSUsageCount) > 0
order by sys.Netbios_Name0


--month prompt (endmonth prompt uses same code)
begin
if (@__filterwildcard = '')
select distinct DATEPART(month,IntervalStart) as Month, DATEPART(year,IntervalStart) as Year from v_SummarizationInterval order by DATEPART(year,IntervalStart), DATEPART(month,IntervalStart)
else
select distinct DATEPART(month,IntervalStart) as Month, DATEPART(year,IntervalStart) as Year from v_SummarizationInterval
WHERE DATEPART(month,IntervalStart) like @__filterwildcard
order by DATEPART(year,IntervalStart), DATEPART(month,IntervalStart)
end

--year prompt (endyear prompt uses same code)
begin
if (@__filterwildcard = '')
select distinct DATEPART(year,IntervalStart) as Year from v_SummarizationInterval order by DATEPART(year,IntervalStart)
else
select distinct DATEPART(year,IntervalStart) as Year from v_SummarizationInterval
WHERE DATEPART(year,IntervalStart) like @__filterwildcard
order by DATEPART(year,IntervalStart)
end

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-10 : 07:42:25
Please post your sample tables as CREATE TABLE followed by INSERT INTO. Also, please post the output you want to see from the sample data you provide.
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-10 : 10:50:04
OK I think I have recreated the tables involved with enough date for the example. Input from a report user prompt returns a month/year combo. The start might be 201311 and the end 201401. For the period between these ranges I need to add up the usagecount and TSusagecount for all instances of Adobe Acrobat. I believe thhe meteredFileID identifies the workstation it is used on, and I would join monthlyusagesummary with meteredfiles. I could email the schema if that would be helpful.

Thank you.

CREATE TABLE dbo.v_MonthlyUsageSummary (TimeKey int, ResourceID int, FileID int, MeteredUserID int, UsageCount int,
TSUsageCount int, UsageTime int, LastUsage datetime)

INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 131163, 1824054, 36197, 1, 0, 25, '2013-12-05 21:39:32.710')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 133850, 2745083, 37311, 5, 0, 13392000, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 133850, 2748182, 37311, 1, 0, 2678400, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 142350, 2748182, 36233, 1, 0, 2678400, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 84374, 533779, 39750, 1, 1, 1906415, '2014-01-28 22:03:59.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 84374, 533779, 40578, 0, 1, 304, '2014-01-28 21:21:18.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 97071, 533779, 37828, 0, 1, 1422064, '2014-02-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 107343, 5029641, 36500, 19, 0, 451187, '2014-02-27 21:40:52.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 107345, 5029641, 38476, 17, 0, 890014, '2014-02-28 04:35:12.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 107345, 5029641, 41001, 1, 0, 2630, '2014-02-24 14:01:35.000')


CREATE TABLE dbo.v_SummarizationInterval (TimeKey int, IntervalStart datetime)


INSERT INTO dbo.v_SummarizationInterval
VALUES
(201310, '2013-10-01 00:00:00.000')
INSERT INTO dbo.v_SummarizationInterval
VALUES
(201311, '2013-11-01 00:00:00.000')
INSERT INTO dbo.v_SummarizationInterval
VALUES
(201312, '2013-12-01 00:00:00.000')
INSERT INTO dbo.v_SummarizationInterval
VALUES
(201401, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_SummarizationInterval
VALUES
(201402, '2014-02-01 00:00:00.000')


CREATE TABLE dbo.v_MeteredFiles (ProductName varchar(255), MeteredFileID int)
INSERT INTO dbo.v_MeteredFiles
VALUES
('Adobe Acrobat', 2748182)
INSERT INTO dbo.v_MeteredFiles
VALUES
('Adobe Acrobat',5029641)
INSERT INTO dbo.v_MeteredFiles
VALUES
('Microsoft Visio', 2986574)


Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-11 : 06:35:37
One more thing - if you note in the table,

where mf.ProductName = 'Adobe Acrobat'
and mus.TimeKey = @endTimeKey

what I was trying to figure out is how to do something like "and mus.timekey = SUM(@timekey + timekeys thru @endtimekey)". It seems like if I could do that it would work.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-11 : 08:46:17
you can add a sum from a subquery, e.g. something like this (untested)


@timekey + (select sum(timekey from dbo.v_MonthlyUsageSummary where timekey <= @endtimekey))


however, that may not be optimal. The risk is that the complexity goes to O(n^2) for that portion, since the subquery may be run for every row in the outer query. You'll need to test it to be sure.

Alternatives include:

1. aggregate functions http://msdn.microsoft.com/en-us/library/ms173454.aspx
2. putting the subquery sample (above) as an additional join table. You'll need to add appropriate keys to it for the join to work. Then, you do the SUM in the main query.
3. Using Cross Apply instead of JOIN for the previous option. Sometimes the result is easier to read.

If possible, I'd go with aggregate functions, since they are optimized nicely and generate simpler query plans.


Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-11 : 10:10:45
I'm getting syntax errors with this subquery and haven't figured out how to modify. And as I think about it, it may not be valid, but I am pretty unsure at this point. Because I am asking it to sum the months and years in the timekey column:

201401
201402
201403

but what I really need is to get the sum of the usage values in the other columns associated with these months. It should make more sense with the tables I created. As I said I am kind of over my head and may not be explaining it correctly.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-11 : 11:27:45
I said that my subquery was untested. Not surprised it doesn't work! But its an idea to work with.

Unfortunately I will need CREATE TABLE and INSERT INTO for the other tables in order to do more:

...
from v_R_System sys
join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP TCU on TCU.resourceID = sys.resourceID
full join v_R_User USR on sys.user_name0 = USR.user_name0
join v_R_User USR2 on TCU.TopConsoleUser0 = USR2.Unique_User_Name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
left join v_RA_System_SMSInstalledSites inst on sys.ResourceID=inst.ResourceID
left outer join v_RA_User_UserOUName on v_RA_User_UserOUName.ResourceID = USR.ResourceID
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = sys.ResourceID
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-11 : 13:54:39
I think these are the only other two relevant tables. The rest I put in for extra information for contacting the user of the workstation.

CREATE TABLE dbo.v_R_User (ResourceID int, displayname0 varchar(128), user_name0 varchar(128))
INSERT INTO dbo.v_R_User
VALUES
(2124, 'Smith, John', 'jsmith')
INSERT INTO dbo.v_R_User
VALUES
(2127, 'Smith, Jenny', 'jsmith2')



CREATE TABLE v_R_System (ResourceID int, name0 varchar(64), user_name0 varchar(128))
INSERT INTO dbo.v_R_System
VALUES
(131163, 'computer1', 'jsmith')
INSERT INTO dbo.v_R_System
VALUES
(122850, 'computer2', 'jsmith2')

Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-18 : 07:10:36
anyone?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-18 : 10:15:10
quote:
Originally posted by tllrmr

I think these are the only other two relevant tables. The rest I put in for extra information for contacting the user of the workstation.

CREATE TABLE dbo.v_R_User (ResourceID int, displayname0 varchar(128), user_name0 varchar(128))
INSERT INTO dbo.v_R_User
VALUES
(2124, 'Smith, John', 'jsmith')
INSERT INTO dbo.v_R_User
VALUES
(2127, 'Smith, Jenny', 'jsmith2')



CREATE TABLE v_R_System (ResourceID int, name0 varchar(64), user_name0 varchar(128))
INSERT INTO dbo.v_R_System
VALUES
(131163, 'computer1', 'jsmith')
INSERT INTO dbo.v_R_System
VALUES
(122850, 'computer2', 'jsmith2')





Do you mean you can remove/comment the other joins? If so, can you post the modified query?
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-18 : 12:16:35
Sorry I am confused. I thought we were working from the tables I re-created. I think using this example to sum the data spanning months is all I need help with. If I can just see how it would be done in this scenario I think I can modify my query to make it work.
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-18 : 12:48:10
OK here's the modified query, removing all the non-relevant columns. I put in vales for prompted variables.

declare @TimeKey int
declare @months float
declare @endTimeKey int
set @TimeKey=201312
set @endTimeKey=201401
declare @__timezoneoffset int
set @__timezoneoffset=4

select @months=DATEDIFF(d,@timekey,@endTimeKey)

from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @endTimeKey

if IsNULL(@months,0) > 0

select
sys.Netbios_Name0, mf.productname, MAX(mf.meteredfileversion) as 'Product Version', sys.User_Name0,
DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
SUM(UsageCount) + SUM(TSUsageCount) as C021,
ROUND((SUM(UsageCount) + SUM(TSUsageCount))/(@months*30),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/(@months*30),2) as C025
from v_R_System sys
join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP TCU on TCU.resourceID = sys.resourceID
full join v_R_User USR on sys.user_name0 = USR.user_name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
where mf.ProductName = 'Adobe Acrobat'
and mus.TimeKey = @endTimeKey
group by sys.Netbios_Name0, sys.User_Name0, mf.productname
having SUM(UsageCount) + SUM(TSUsageCount) > 0
order by sys.Netbios_Name0
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-18 : 12:48:19
OK here's the modified query, removing all the non-relevant columns. I put in vales for prompted variables.

declare @TimeKey int
declare @months float
declare @endTimeKey int
set @TimeKey=201312
set @endTimeKey=201401
declare @__timezoneoffset int
set @__timezoneoffset=4

select @months=DATEDIFF(d,@timekey,@endTimeKey)

from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @endTimeKey

if IsNULL(@months,0) > 0

select
sys.Netbios_Name0, mf.productname, MAX(mf.meteredfileversion) as 'Product Version', sys.User_Name0,
DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
SUM(UsageCount) + SUM(TSUsageCount) as C021,
ROUND((SUM(UsageCount) + SUM(TSUsageCount))/(@months*30),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/(@months*30),2) as C025
from v_R_System sys
join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP TCU on TCU.resourceID = sys.resourceID
full join v_R_User USR on sys.user_name0 = USR.user_name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
where mf.ProductName = 'Adobe Acrobat'
and mus.TimeKey = @endTimeKey
group by sys.Netbios_Name0, sys.User_Name0, mf.productname
having SUM(UsageCount) + SUM(TSUsageCount) > 0
order by sys.Netbios_Name0
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-18 : 13:04:01
In playing with it further what I have to it take all the timekeys in the dbo.v_SummarizationInterval table that are in the range between the start and end timekeys and sum all the usage counts in those rows, so summing the timekeys doesn't get me anything.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-18 : 13:09:38
I;m still missing the table v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP

Also, there is no column called Netbios_Name0 in v_R_System sys (references to sys.Netbios_Name0 are not compiling). Should that be just Name0?
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-18 : 13:23:55
Sorry I forgot to remove join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP TCU on TCU.resourceID = sys.resourceID. Not needed.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-18 : 14:54:28
OK -- now with the sample data, what would a correct query return?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-18 : 15:03:53
I distilled your query down to just the joins:


select * from v_R_System sys
full join v_r_user usr
on sys.user_name0 = USR.user_name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID


and ran it in increasing joins like this:


select * from v_R_System sys
full join v_r_user usr
on sys.user_name0 = USR.user_name0


produced 2 rows


select * from v_R_System sys
full join v_r_user usr
on sys.user_name0 = USR.user_name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID


produced 1 row and


select * from v_R_System sys
full join v_r_user usr
on sys.user_name0 = USR.user_name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID


produced 0 rows (no rows at all)

And, this is before introducing the WHERE clause. Can you set your test data so that the query actually has some data to work with?
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-22 : 07:42:12
I think I have the metered file IDs matching now.

INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 122850, 2745083, 37311, 5, 0, 13392000, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 122850, 2748182, 37311, 1, 0, 2678400, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 122850, 2748182, 36233, 1, 0, 2678400, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 122850, 533779, 39750, 1, 1, 1906415, '2014-01-28 22:03:59.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 122850, 533779, 40578, 0, 1, 304, '2014-01-28 21:21:18.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 122850, 533779, 37828, 0, 1, 1422064, '2014-02-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 122850, 5029641, 36500, 19, 0, 451187, '2014-02-27 21:40:52.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 122850, 5029641, 38476, 17, 0, 890014, '2014-02-28 04:35:12.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 122850, 5029641, 41001, 1, 0, 2630, '2014-02-24 14:01:35.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 131163, 2745083, 37311, 5, 0, 13392000, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 131163, 2748182, 37311, 1, 0, 2678400, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201312, 131163, 2748182, 36233, 1, 0, 2678400, '2014-01-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 131163, 533779, 39750, 1, 1, 1906415, '2014-01-28 22:03:59.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 131163, 533779, 40578, 0, 1, 304, '2014-01-28 21:21:18.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201401, 131163, 533779, 37828, 0, 1, 1422064, '2014-02-01 00:00:00.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 131163, 5029641, 36500, 19, 0, 451187, '2014-02-27 21:40:52.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 131163, 5029641, 38476, 17, 0, 890014, '2014-02-28 04:35:12.000')
INSERT INTO dbo.v_MonthlyUsageSummary
VALUES
(201402, 131163, 5029641, 41001, 1, 0, 2630, '2014-02-24 14:01:35.000')
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-22 : 07:54:31
So, taking this new data and rerunning your query (commenting out the join on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP and commenting out the 'and mus.TimeKey = @endTimeKey' condition in the WHERE clause, I get some output:


name0 productname Product Version User_Name0 LastUsage C021 C022 C023 C024 C025
computer1 Adobe Acrobat 5029641 jsmith 2014-02-28 04:35:16.000 39 0.01 111677.180000 2863.52000000000000000 41.83
computer2 Adobe Acrobat 5029641 jsmith2 2014-02-28 04:35:16.000 39 0.01 111677.180000 2863.52000000000000000 41.83


What results do you want to see?
Go to Top of Page

tllrmr
Starting Member

16 Posts

Posted - 2014-07-22 : 07:57:04
And here's my simplified query that works with these tables now. Sorry for any confusion. Note "and mus.TimeKey = @endTimeKey
" is where I am having the problem. It's wrong. It's only giving me data for the last month, not the sum of months that I need.

declare @TimeKey int
declare @months float
declare @endTimeKey int
set @TimeKey=201312
set @endTimeKey=201402
declare @__timezoneoffset int
set @__timezoneoffset=4

select @months=DATEDIFF(d,@timekey,@endTimeKey)

from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey<@endTimeKey

if IsNULL(@months,0) > 0

select
sys.Name0, mf.productname, sys.User_Name0,
DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as 'LastUsage',
SUM(UsageCount) + SUM(TSUsageCount) as 'Total Usages',
ROUND((SUM(UsageCount) + SUM(TSUsageCount))/(@months*30),2) as 'Average Usages Per Day',
ROUND(SUM(UsageTime)/60.0,2) as 'Total Duration (min)',
ROUND(SUM(UsageTime)/60.0/(SUM(UsageCount) + SUM(TSUsageCount)),2) as 'Average Duration of Use (min)',
ROUND(SUM(UsageTime)/60.0/(@months*30),2) as 'Average Duration Per Day'
from v_R_System sys
full join v_R_User USR on sys.user_name0 = USR.user_name0
join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
where mf.ProductName = 'Adobe Acrobat'
and mus.TimeKey = @endTimeKey
group by sys.Name0, sys.User_Name0, mf.productname
having SUM(UsageCount) + SUM(TSUsageCount) > 0
order by sys.Name0
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -