question

tombiernacki avatar image
tombiernacki asked

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 .
stored-proceduresdistinct
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is [FileGrowth] a setting or the amount of growth?
0 Likes 0 ·
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
0 Likes 0 ·
tombiernacki avatar image
tombiernacki answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Beandon10 avatar image
Beandon10 answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.