summarizing my data correctly 1

[dbmsguy]

Consider the following tables which reside on a sql 7 db
date servername dbname dbid groupid groupname fileid filesize maxfilesize growth name
--------------------------- ------------------ ----------------------------------- ----------- ----------- -------------------- ----------- -------------------- -------------------- ----------- --------------------------------------------
2001-07-25 14:51:00.000 EUS9808058 abcd 7 1 PRIMARY 1 4.00 .00 10 abcd_Data
2001-07-25 14:51:00.000 EUS9808058 abcd 7 NULL log 2 6.24 .00 10 abcd_Log
2001-07-25 14:51:00.000 EUS9808058 abcd 7 1 PRIMARY 3 6.00 .00 10 abcd2
2001-07-25 14:51:00.000 EUS9808058 abcd 7 2 secondary 4 5.00 .00 10 abcd1
2001-07-25 14:51:00.000 EUS9808058 testsplit 5 1 PRIMARY 1 70.00 .00 10 testsplit_Data
2001-07-25 14:51:00.000 EUS9808058 testsplit 5 NULL log 2 30.75 .00 10 testsplit_Log
2001-07-25 14:51:00.000 EUS9808058 testsplit 5 1 PRIMARY 3 60.00 .00 10 testsplit_data1

(7 row(s) affected)

date servername dbname dbid groupid reserved used
--------------------------- ------------------ ----------------------------------- ----------- ----------- -------------- --------------
2001-07-25 14:51:00.000 EUS9808058 abcd 7 1 6.80 5.63
2001-07-25 14:51:00.000 EUS9808058 abcd 7 2 3.64 3.57
2001-07-25 14:51:00.000 EUS9808058 testsplit 5 1 16.86 14.39

(3 row(s) affected)

I am trying to create a sql stmt that will summarize filesize (table1) with a summary of reserved (table2) for each database for a given date. so, it is grouped by date, dbname and servername. as well, I want to include a percent used....

at any rate, the problem is that since the groupid does not match up, the summaries get a little screwed up (there is more than on group id on table1, but only 1 groupid in table 2). ultimately, i have nothing to join by so that i can do summaries properly.


my solution to this point is the following sql, which works but will only work for one db at a time which means i need a cursor. I am trying to build a sql that will summarize then entire table for all dbs for a given date.

select date,servername,dbname,
(select sum(reserved) from dbusage where dbname='abcd'and date=(select max(date) from dbusage)),
sum(filesize)
from dbstats a where date=(select max(date) from dbstats)
and groupid > 0 and dbname='abcd'
group by date,servername,dbname

ideas? Paul

http://www.dbmsguy.com

 

[tlbroadbent]

How about trying something like this?
[tt]
/* First find the max date from each table.
This step isn't necessary but it simplifies
and makes the following query more efficient. */
declare @mud datetime, @msd datetime
select @mud=max(date) from dbusage
select @msd=max(date) from dbstats

/* Summarize file & reserve sizes in a subqueries.
Join the subqueries on dbname. */

Select a.*, b.ResSum
From

(Select date, servername, dbname, sum(filesize) As FileSize
From dbstats
Where date=@msd
And isnull(groupid,0) > 0
Group By date, servername, dbname) As a

Inner Join

(Select dbname, sum(reserved) As ResSum
From dbusage
Where date=@mud
Group By dbname) As b

On a.dbname=b.dbname[/tt] Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[dbmsguy]

Very elegant solution.... Works quite nicely.

Thankyou. Paul

http://www.dbmsguy.com