x

stored procedure select data if change in column

I have a stored procedure that does a select statement from my table and displays data about the database growth.The stored procedure is part of a scheduled job that runs every night. For example it provides filename, filembsize,filegrowth drivename, datetime, filembused ect... What I am trying to accomplish is to select the data so it displays only the changed FileMbSize column. Basically so it shows a distinct Filembsize. Below is my stored procedure and also I will show what it displays now. Store procedure is...

 select [FileLogicalName]
       ,[Filename]
       ,[FileMBSize]
       ,[FileGrowth]
       ,[FileMBGrowth]
       ,[DriveName]
       ,[DriveMBEmpty]
       ,[FileMBUsed]
       ,[FileMBEmpty]
       ,[FilePercentEmpty]
       ,[DBName]
       ,[DateTime], 
       isnull(rn.ReleaseNumber,'') as [Release] 
 from CRCUtilities..TrackDatabaseGrowthTB tdg
 left outer join VisionMeta_3..Releases rn
 on  CAST(tdg.DateTime as date) = CAST(rn.ReleaseDate as date)
 where tdg.DBName = @DBName and tdg.Filename like '%.mdf' 
 order by tdg.DateTime desc
 end

it displays....

  FileLogicalName Filename FileMBSize FileGrowth FileMBGrowth DriveName DriveMBEmpty FileMBUsed FileMBEmpty FilePercentEmpty DBName DateTime Release
     LTC_Data D:\Data\test.mdf 194742 10 % 214216     D    45839 176379 18363 9 test 2013-02-20 02:00:00                     
     LTC_Data D:\Data\test.mdf 194742 10 % 214216     D    48332 176105 18637 10 test 2013-02-19 02:00:00                     
     LTC_Data D:\Data\test.mdf 194742 10 % 214216     D    45924 175669 19073 10 test 2013-02-18 02:00:00                     
     LTC_Data D:\Data\test.mdf 194742 10 % 214216     D    45926 175540 19202 10 test 2013-02-17 02:00:00                     
     LTC_Data D:\Data\test.mdf 177038 10 % 194741     D    66027 176108 930 1 test 2013-02-16 02:00:00                     
     LTC_Data D:\Data\test.mdf 177038 10 % 194741     D    63038 175834 1204 1 test 2013-02-15 02:00:00                     
     LTC_Data D:\Data\test.mdf 177038 10 % 194741     D    63036 175416 1622 1 test 2013-02-14 02:00:00                     
     LTC_Data D:\Data\test.mdf 177038 10 % 194741     D    63035 175017 2021 1 test 2013-02-13 02
 
 
   

so What I am trying to accomplish here is select * but display all columns but distinct FileMBsize column .

more ▼

asked Mar 25, 2013 at 03:44 PM in Default

avatar image

tombiernacki
338 20 22 27

Is [FileGrowth] a setting or the amount of growth?

Mar 25, 2013 at 08:03 PM Jeff O

Ok I came up with the solution and that is doing a partition over... select a.FileLogicalName, a.Filename, a.FileMBSize, a.FileGrowth, a.FileMBGrowth, a.DriveName, a.DriveMBEmpty, a.FileMBEmpty, a.FilePercentEmpty, a.DBName, a.DateTime, a.ReleaseNumber from ( SELECT ROW_NUMBER() OVER(PARTITION BY FileMBSize ORDER BY FileMBSize DESC) AS RowNumber, tdg.*,rn.ReleaseNumber from CRCUtilities..TrackDatabaseGrowthTB tdg left outer join VisionMeta_3..Releases rn on CAST(tdg.DateTime as date) = CAST(rn.ReleaseDate as date) where tdg.DBName = 'HMXafo' and tdg.Filename like '%.mdf' ) a where a.RowNumber = 1 order by a.DateTime desc

Mar 25, 2013 at 09:03 PM tombiernacki
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
 select a.FileLogicalName, a.Filename, a.FileMBSize, a.FileGrowth, a.FileMBGrowth, a.DriveName, a.DriveMBEmpty,
 a.FileMBEmpty, a.FilePercentEmpty, a.DBName, a.DateTime, a.ReleaseNumber from
 (
 SELECT ROW_NUMBER() OVER(PARTITION BY FileMBSize ORDER BY FileMBSize DESC) AS RowNumber,
 tdg.*,rn.ReleaseNumber
 from CRCUtilities..TrackDatabaseGrowthTB tdg
 left outer join VisionMeta_3..Releases rn
 on  CAST(tdg.DateTime as date) = CAST(rn.ReleaseDate as date)
 where tdg.DBName = 'HMXafo' and tdg.Filename like '%.mdf' 
 ) a
 where a.RowNumber = 1
 order by a.DateTime desc
more ▼

answered Mar 25, 2013 at 09:02 PM

avatar image

tombiernacki
338 20 22 27

(comments are locked)
10|1200 characters needed characters left

If I am understanding you correctly, you want 1 row per distinct FileMBSize value, but you also want all of the associated data with that row. The problem is that there are many values for the other columns per FileMBSize value, so you would need to decide which value to show.

I would guess you could solve this by grouping by the FileMBSize column (and probably the other fixed value columns), and use either MAX or MIN to the the values for the other columns.

more ▼

answered Mar 25, 2013 at 04:10 PM

avatar image

Beandon10
136 2 4

Thanks for the response, I came up with a solution that actually works by doing a partition over...

select a.FileLogicalName, a.Filename, a.FileMBSize, a.FileGrowth, a.FileMBGrowth, a.DriveName, a.DriveMBEmpty, a.FileMBEmpty, a.FilePercentEmpty, a.DBName, a.DateTime, a.ReleaseNumber from ( SELECT ROW_NUMBER() OVER(PARTITION BY FileMBSize ORDER BY FileMBSize DESC) AS RowNumber, tdg.*,rn.ReleaseNumber from CRCUtilities..TrackDatabaseGrowthTB tdg left outer join VisionMeta_3..Releases rn on CAST(tdg.DateTime as date) = CAST(rn.ReleaseDate as date) where tdg.DBName = 'HMXafo' and tdg.Filename like '%.mdf' ) a where a.RowNumber = 1 order by a.DateTime desc

Mar 25, 2013 at 09:03 PM tombiernacki
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x479
x24

asked: Mar 25, 2013 at 03:44 PM

Seen: 1039 times

Last Updated: Mar 26, 2013 at 03:58 PM

Copyright 2018 Redgate Software. Privacy Policy