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.
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_MonthlyUsageSummaryTimeKey ResourceID FileID UserID UsageCount TSUsageCount UsageTime LastUsage201402 108343 2533622 40038 122 0 255027 2014-02-28 13:29:39.000201402 108340 2533622 40665 2 0 12 2014-02-27 15:58:13.000201402 108932 2533622 31708 0 10 21670 2014-02-14 20:41:35.000201403 124035 5040065 42145 15 0 175629 2014-03-31 19:37:37.000201403 124036 5040065 37579 46 0 773711 2014-04-01 00:00:00.000v_SummarizationIntervalTimeKey IntervalStart201310 2013-10-01 00:00:00.000201311 2013-11-01 00:00:00.000201312 2013-12-01 00:00:00.000201401 2014-01-01 00:00:00.000201402 2014-02-01 00:00:00.000201403 2014-03-01 00:00:00.000201404 2014-04-01 00:00:00.000201405 2014-05-01 00:00:00.000201406 2014-06-01 00:00:00.000201407 2014-07-01 00:00:00.000The 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 intdeclare @months floatdeclare @endTimeKey intset @TimeKey=100*@Year+@Monthset @endTimeKey=100*@endYear+@EndMonthselect @months=DATEDIFF(d,@timekey,@endTimeKey)from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @endTimeKeyif IsNULL(@months,0) > 0select 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 C025from 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 = @endTimeKeygroup 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. |
|
|
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_SummarizationIntervalVALUES(201310, '2013-10-01 00:00:00.000')INSERT INTO dbo.v_SummarizationIntervalVALUES(201311, '2013-11-01 00:00:00.000')INSERT INTO dbo.v_SummarizationIntervalVALUES(201312, '2013-12-01 00:00:00.000')INSERT INTO dbo.v_SummarizationIntervalVALUES(201401, '2014-01-01 00:00:00.000')INSERT INTO dbo.v_SummarizationIntervalVALUES(201402, '2014-02-01 00:00:00.000')CREATE TABLE dbo.v_MeteredFiles (ProductName varchar(255), MeteredFileID int)INSERT INTO dbo.v_MeteredFilesVALUES('Adobe Acrobat', 2748182)INSERT INTO dbo.v_MeteredFilesVALUES('Adobe Acrobat',5029641)INSERT INTO dbo.v_MeteredFilesVALUES('Microsoft Visio', 2986574) |
|
|
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 = @endTimeKeywhat 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. |
|
|
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.aspx2. 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. |
|
|
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:201401201402201403but 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. |
|
|
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 sysjoin v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP TCU on TCU.resourceID = sys.resourceIDfull join v_R_User USR on sys.user_name0 = USR.user_name0join v_R_User USR2 on TCU.TopConsoleUser0 = USR2.Unique_User_Name0join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceIDjoin v_MeteredFiles mf on mus.FileID=mf.MeteredFileIDleft join v_RA_System_SMSInstalledSites inst on sys.ResourceID=inst.ResourceIDleft outer join v_RA_User_UserOUName on v_RA_User_UserOUName.ResourceID = USR.ResourceIDleft join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = sys.ResourceID |
|
|
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_UserVALUES(2124, 'Smith, John', 'jsmith')INSERT INTO dbo.v_R_UserVALUES(2127, 'Smith, Jenny', 'jsmith2')CREATE TABLE v_R_System (ResourceID int, name0 varchar(64), user_name0 varchar(128))INSERT INTO dbo.v_R_SystemVALUES(131163, 'computer1', 'jsmith')INSERT INTO dbo.v_R_SystemVALUES(122850, 'computer2', 'jsmith2') |
|
|
tllrmr
Starting Member
16 Posts |
Posted - 2014-07-18 : 07:10:36
|
anyone? |
|
|
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_UserVALUES(2124, 'Smith, John', 'jsmith')INSERT INTO dbo.v_R_UserVALUES(2127, 'Smith, Jenny', 'jsmith2')CREATE TABLE v_R_System (ResourceID int, name0 varchar(64), user_name0 varchar(128))INSERT INTO dbo.v_R_SystemVALUES(131163, 'computer1', 'jsmith')INSERT INTO dbo.v_R_SystemVALUES(122850, 'computer2', 'jsmith2')
Do you mean you can remove/comment the other joins? If so, can you post the modified query? |
|
|
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. |
|
|
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 intdeclare @months floatdeclare @endTimeKey intset @TimeKey=201312set @endTimeKey=201401declare @__timezoneoffset intset @__timezoneoffset=4select @months=DATEDIFF(d,@timekey,@endTimeKey)from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @endTimeKeyif IsNULL(@months,0) > 0select 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 C025from 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 = @endTimeKeygroup by sys.Netbios_Name0, sys.User_Name0, mf.productname having SUM(UsageCount) + SUM(TSUsageCount) > 0 order by sys.Netbios_Name0 |
|
|
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 intdeclare @months floatdeclare @endTimeKey intset @TimeKey=201312set @endTimeKey=201401declare @__timezoneoffset intset @__timezoneoffset=4select @months=DATEDIFF(d,@timekey,@endTimeKey)from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @endTimeKeyif IsNULL(@months,0) > 0select 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 C025from 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 = @endTimeKeygroup by sys.Netbios_Name0, sys.User_Name0, mf.productname having SUM(UsageCount) + SUM(TSUsageCount) > 0 order by sys.Netbios_Name0 |
|
|
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. |
|
|
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_MAXGROUPAlso, 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? |
|
|
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. |
|
|
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? |
|
|
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 sysfull join v_r_user usron sys.user_name0 = USR.user_name0join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceIDjoin v_MeteredFiles mf on mus.FileID=mf.MeteredFileID and ran it in increasing joins like this:select * from v_R_System sysfull join v_r_user usron sys.user_name0 = USR.user_name0 produced 2 rowsselect * from v_R_System sysfull join v_r_user usron sys.user_name0 = USR.user_name0join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID produced 1 row and select * from v_R_System sysfull join v_r_user usron sys.user_name0 = USR.user_name0join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceIDjoin 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? |
|
|
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') |
|
|
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 C025computer1 Adobe Acrobat 5029641 jsmith 2014-02-28 04:35:16.000 39 0.01 111677.180000 2863.52000000000000000 41.83computer2 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? |
|
|
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 intdeclare @months floatdeclare @endTimeKey intset @TimeKey=201312set @endTimeKey=201402declare @__timezoneoffset intset @__timezoneoffset=4select @months=DATEDIFF(d,@timekey,@endTimeKey)from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey<@endTimeKeyif IsNULL(@months,0) > 0select 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 = @endTimeKeygroup by sys.Name0, sys.User_Name0, mf.productname having SUM(UsageCount) + SUM(TSUsageCount) > 0 order by sys.Name0 |
|
|
Next Page
|
|
|
|
|