March 24, 2017 at 3:33 am
.
March 24, 2017 at 4:10 am
sqlnewbie17 - Friday, March 24, 2017 3:33 AMThis is my current view.
ServerName DBName Total Disk Space LastInserted Rank1
ServerA DbA 12671 11/21/2016 40
ServerA DbA 14784 3/12/2017 1
ServerB DbB 366 11/21/2016 24
ServerB DbB 466 3/12/2017 1
ServerC DbC 142 11/21/2016 14
ServerC DbC 432 3/12/2017 1
I would like to see the data in this form.
ServerName DBName OldDt NewDt Diff in Dates Initial Size Final Size Space growth
Server DbA 11/21/2016 3/12/2017 81 12671 14784 2077
Server DbB 11/21/2016 3/12/2017 81 366 466 100
Server DbC 11/21/2016 3/12/2017 81 142 432 290Thanks
See if it helps, also, it could have been more helpful if u had provided schema with data script for this.
USE tempdb;
CREATE TABLE TEST
(
ServerName VARCHAR(20),
DBName VARCHAR(20),
TotalDiskSpace INT,
LastInserted DATE,
Rank1 TINYINT
);
INSERT INTO TEST(ServerName ,DBName ,TotalDiskSpace ,LastInserted, Rank1)
SELECT
'3M-ENC-DB','COR_3M_NMH_LIVE', 12671 ,'11/21/2016', 40
UNION ALL
SELECT
'3M-ENC-DB' ,'COR_3M_NMH_LIVE' ,14784 ,'3/12/2017', 1
UNION ALL
SELECT
'3M-ENC-DB', 'COR_3M_NMH_TEST', 366 ,'11/21/2016', 24
UNION ALL
SELECT
'3M-ENC-DB', 'COR_3M_NMH_TEST', 466 ,'3/12/2017', 1
UNION ALL
SELECT
'3M-ENC-DB' ,'DBA_Services' ,142 ,'11/21/2016' ,14
UNION ALL
SELECT
'3M-ENC-DB' ,'DBA_Services', 432 ,'3/12/2017', 1;
;WITH CTE AS
(SELECT
ServerName,
DBName,
LastInserted AS OldDt,
LAST_VALUE(LastInserted) OVER (PARTITION BY ServerName, DBName ORDER BY ServerName, DBName) AS NewDt,
TotalDiskSpace AS IntialSize,
LAST_VALUE(TotalDiskSpace) OVER (PARTITION BY ServerName, DBName ORDER BY ServerName, DBName) AS FinalSize,
Rank1
FROM TEST
)
SELECT
ServerName,
DBName,
OldDt,
NewDt,
DATEDIFF(DAY,OldDt,NewDt) AS DiffInDates,
IntialSize,
FinalSize,
(FinalSize-IntialSize) AS SpaceGrowth
FROM CTE
WHERE Rank1<>1;
March 24, 2017 at 4:18 am
These are beginning to look like we're just doing your job for you. This is the third post you've made with very similar requirements.
What have you tried so far? Why are you not supplying DDL and consumable sample data, like you've been asked in every topic? I would suggest doing this first, as it firstly really helps us help you.
Also, we're not paid to help you, or paid to do your job; you are.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 24, 2017 at 11:13 am
sqlnewbie17 - Friday, March 24, 2017 3:33 AMThis is my current view.
ServerName DBName Total Disk Space LastInserted Rank1
ServerA DbA 12671 11/21/2016 40
ServerA DbA 14784 3/12/2017 1
ServerB DbB 366 11/21/2016 24
ServerB DbB 466 3/12/2017 1
ServerC DbC 142 11/21/2016 14
ServerC DbC 432 3/12/2017 1
I would like to see the data in this form.
ServerName DBName OldDt NewDt Diff in Dates Initial Size Final Size Space growth
Server DbA 11/21/2016 3/12/2017 81 12671 14784 2077
Server DbB 11/21/2016 3/12/2017 81 366 466 100
Server DbC 11/21/2016 3/12/2017 81 142 432 290Thanks
No function in 2008
March 24, 2017 at 1:03 pm
Mr. Kapsicum - Friday, March 24, 2017 4:10 AMsqlnewbie17 - Friday, March 24, 2017 3:33 AMThis is my current view.
ServerName DBName Total Disk Space LastInserted Rank1
ServerA DbA 12671 11/21/2016 40
ServerA DbA 14784 3/12/2017 1
ServerB DbB 366 11/21/2016 24
ServerB DbB 466 3/12/2017 1
ServerC DbC 142 11/21/2016 14
ServerC DbC 432 3/12/2017 1
I would like to see the data in this form.
ServerName DBName OldDt NewDt Diff in Dates Initial Size Final Size Space growth
Server DbA 11/21/2016 3/12/2017 81 12671 14784 2077
Server DbB 11/21/2016 3/12/2017 81 366 466 100
Server DbC 11/21/2016 3/12/2017 81 142 432 290Thanks
No function in 2008
I can neither see your "view" nor the underlying data....suggest you read this article and post back accordingly please...
EDIT> seems the OP post has been deleted.....???
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 24, 2017 at 1:36 pm
Does this help? It is based on what I could find in other posts since your original post has been edited so that nothing is there.
/*
ServerName DBName Total Disk Space LastInserted Rank1
ServerA DbA 12671 11/21/2016 40
ServerA DbA 14784 3/12/2017 1
ServerB DbB 366 11/21/2016 24
ServerB DbB 466 3/12/2017 1
ServerC DbC 142 11/21/2016 14
ServerC DbC 432 3/12/2017 1
*/
declare @TestTable table (
ServerName sysname,
DBName sysname,
TotalDiskSpace int, -- may want BIGINT
LastInserted date,
Rank1 int
);
insert into @TestTable
values
('ServerA','DbA',12671,'2016/11/21',40)
,('ServerA','DbA',14784,'2017/03/12',1)
,('ServerB','DbB',366,'2016/11/21',24)
,('ServerB','DbB',466,'2017/03/12',1)
,('ServerC','DbC',142,'2016/11/21',14)
,('ServerC','DbC',432,'2017/03/12',1);
select * from @TestTable;
/*
Assumptions, so I don't make an ass of myself
1) There will only be two entries for any given
ServerName DBName pair. This allows for more
than one database on a server.
2) The Rank1 column is actually unneeded as it
does not appear in the expected results.
3) As this posted in a SQL2008 forum, you running
SQL Server 2008 or 2008 R2.
4) All code is provided as is with no warranty
implied or expressed and with no promise that
it will work in your environment without any
modification that must be done by you.
*/
with BaseData as (
select
rn = row_number() over (partition by tt.ServerName, tt.DBName order by tt.LastInserted)
, tt.ServerName
, tt.DBName
, tt.TotalDiskSpace
, tt.LastInserted
from
@TestTable tt
), PivotData as (
select
bd.ServerName
, bd.DBName
, max(case rn when 1 then bd.LastInserted end) OldDt
, max(case rn when 2 then bd.LastInserted end) NewDt
, max(case rn when 1 then bd.TotalDiskSpace end) InitialSize
, max(case rn when 2 then bd.TotalDiskSpace end) FinalSize
from
BaseData bd
group by
bd.ServerName
, bd.DBName
)
select
pd.ServerName
, pd.DBName
, pd.OldDt
, pd.NewDt
, pd.InitialSize
, pd.FinalSize
, SpaceGrowth = pd.FinalSize - pd.InitialSize
from
PivotData pd
order by
pd.ServerName
, pd.DBName
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply