February 23, 2010 at 5:22 am
Hii guys,
i have table which stores dbsize on daily basis
structure for table is,
CREATE TABLE [dbo].[db_size](
[dbno] [int] NULL,
[dbdate] [datetime] NULL,
[dbname] [nvarchar](50) NULL,
[datasize] [float] NULL,
[logsize] [float] NULL,
[actual] [float] NULL
) ON [PRIMARY]
i want to build a query which gives o\p as
record for max(dbdate) & min (dbDate) for each database
e.g.
O\p
dbdate dbname datasize_MB
12/3/2009 9:55DB_ONLINE_SURVEILLANCE 53000
2/23/2010 8:00DB_ONLINE_SURVEILLANCE 60000
12/3/2009 9:55Omnet_App1 12000
2/23/2010 8:00Omnet_App1 12000
12/3/2009 9:55Omnet_App15 6502
2/23/2010 8:00Omnet_App15 8556
12/3/2009 9:55omnetapp1 2193
2/23/2010 8:00omnetapp1 4393
12/3/2009 9:55Omnet_App2 43198
2/23/2010 8:00Omnet_App2 67641
12/3/2009 9:55Omnet_App3 13106
2/23/2010 8:00Omnet_App3 15534
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 23, 2010 at 7:37 am
sanketahir1985 (2/23/2010)
record for max(dbdate) & min (dbDate) for each database
can you elaborate min and max ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 23, 2010 at 9:34 am
SELECT dbname
,MaxDate
,MinDate
FROM (SELECT dbname
,MAX(dbDate) AS MaxDate
FROM dbsize
GROUP BY dbname) AS tbl1
INNER JOIN (SELECT dbname
,MIN(dbDate) AS MinDate
FROM dbsize
GROUP BY dbname) AS tbl2
ON tbl1.dbname = tbl2.dbname
February 23, 2010 at 8:57 pm
hiii dan
i want O\P in foll. format
dbdate dbname datasize_MB
====== ======== ===========
<MaxDate> DB1 Size
<MinDate> DB1 Size
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 23, 2010 at 10:49 pm
Like this:
SELECT *
FROM (
SELECT MAX(dbdate) AS dbdate
, dbname
, datasize_MB
FROM db_size
UNION ALL
SELECT MIN(dbdate) AS dbdate
, dbname
, datasize_MB
FROM db_size
) a
ORDER BY dbname, dbdate desc
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 11:34 pm
hiii Rbarry
got error
Msg 8120, Level 16, State 1, Line 1
Column 'db_size.dbname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 24, 2010 at 12:31 am
The error clearly says what is missing in the query, a GROUP BY clause.
SELECTdbname, datasize, 'Min Date' AS DateAggregate, MIN(dbdate) AS dbdate
FROMdb_size
GROUP BY dbname, datasize
UNION ALL
SELECTdbname, datasize, 'Max Date' AS DateAggregate, MAX(dbdate) AS dbdate
FROMdb_size
GROUP BY dbname, datasize
ORDER BY dbname, dbdate desc
--Ramesh
February 24, 2010 at 1:03 am
hiii ramesh
query provided by u is not fulfilling the requirement ,
just read above posts
& suggest soln
Thanx in advance
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 24, 2010 at 1:52 am
select a.dbdate, a.dbname, a.datasize
from db_size a inner join
(
select dbname,min(dbdate)Midt,max(dbdate)Madt
from db_size
group by dbname
)b
on a.dbname = b.dbname and
a.dbdate = b.Midt
union all
select a.dbdate, a.dbname, a.datasize
from db_size a inner join
(
select dbname,min(dbdate)Midt,max(dbdate)Madt
from db_size
group by dbname
)b
on a.dbname = b.dbname and
a.dbdate = b.Madt
order by 2
February 24, 2010 at 1:54 am
my guess is you are not getting
Db_size corectly as i think ramesh's query will return same size for both max and min.right ?
can u provide some sample data ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 24, 2010 at 2:36 am
See if this works..
; WITH cteDBDetails AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate DESC ) RDesc,
ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate ASC ) RAsc,
dbdate, dbname, datasize_MB
FROM db_size
)
SELECTdbdate, dbname, datasize_MB
FROMcteDBDetails
WHERERDesc = 1 OR RAsc = 1
ORDER BY dbname
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 24, 2010 at 3:17 am
Hii arun
Thanx a lotttttt
query worked for me, great job
only the problem is in o\p min & max date changes its sequence alternatively for each database.
for e.g.
O\p is
dbdate dbname datasize_MB
====== ======== ===========
<MaxDate> DB1 Size
<MinDate> DB1 Size
<MinDate> DB2 Size
<MaxDate> DB2 Size
<MaxDate> DB3 Size
<MinDate> DB3 Size
but its ok i will manage that
thanx once again!!!!!!!!
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 26, 2010 at 1:31 am
Kingston Dhasian (2/24/2010)
See if this works..
; WITH cteDBDetails AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate DESC ) RDesc,
ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate ASC ) RAsc,
dbdate, dbname, datasize_MB
FROM db_size
)
SELECTdbdate, dbname, datasize_MB
FROMcteDBDetails
WHERERDesc = 1 OR RAsc = 1
ORDER BY dbname
Sorting can be achieved just by adding Rdesc, Rasc in the orderby
Following Kingston code
; WITH cteDBDetails AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate DESC ) RDesc,
ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate ASC ) RAsc,
dbdate, dbname, datasize_MB
FROM db_size
)
SELECTdbdate, dbname, datasize_MB
FROMcteDBDetails
WHERERDesc = 1 OR RAsc = 1
ORDER BY dbname,Rdesc,Rasc
--Divya
February 26, 2010 at 9:01 pm
sanketahir1985 (2/23/2010)
hiii dani want O\P in foll. format
dbdate dbname datasize_MB
====== ======== ===========
<MaxDate> DB1 Size
<MinDate> DB1 Size
I've got to ask.... WHY?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply