May 17, 2017 at 12:00 am
My table data.
date column values sample : 2017-05-16 03:14:59.647
Server1 100 1/1/2016
Server2 100 1/1/2016
Server3 100 1/1/2016
Server4 100 1/1/2016
Server5 100 1/1/2016
Server6 100 1/1/2016
Server1 200 2/1/2016
Server2 200 2/1/2016
Server3 200 2/1/2016
Server4 200 2/1/2016
Server5 200 2/1/2016
Server6 200 2/1/2016
Server1 300 3/1/2016
Server2 300 3/1/2016
Server3 300 3/1/2016
Server4 300 3/1/2016
Server5 300 3/1/2016
Server6 300 3/1/2016
Server1 400 4/1/2016
Server2 400 4/1/2016
Server3 400 4/1/2016
Server4 400 4/1/2016
Server5 400 4/1/2016
Server6 400 4/1/2016
These are my Servers and their sizes by the first of every month.I have 40 of them.
I need help with sql which will list all distinct servers and their differences in size from the day it is scheduled to the past 30 days.
My result should have Servername, current size - initial size(30 days back) and current day it is run.
Ex: Server1 200 - 100 = 100 2/1/2016
Server6 200 - 100 = 100 2/1/2016
Thanks
May 17, 2017 at 8:37 pm
sqlnewbie17 - Wednesday, May 17, 2017 12:00 AMMy table data.
date column values sample : 2017-05-16 03:14:59.647
Server1 100 1/1/2016
Server2 100 1/1/2016
Server3 100 1/1/2016
Server4 100 1/1/2016
Server5 100 1/1/2016
Server6 100 1/1/2016
Server1 200 2/1/2016
Server2 200 2/1/2016
Server3 200 2/1/2016
Server4 200 2/1/2016
Server5 200 2/1/2016
Server6 200 2/1/2016
Server1 300 3/1/2016
Server2 300 3/1/2016
Server3 300 3/1/2016
Server4 300 3/1/2016
Server5 300 3/1/2016
Server6 300 3/1/2016
Server1 400 4/1/2016
Server2 400 4/1/2016
Server3 400 4/1/2016
Server4 400 4/1/2016
Server5 400 4/1/2016
Server6 400 4/1/2016These are my Servers and their sizes by the first of every month.I have 40 of them.
I need help with sql which will list all distinct servers and their differences in size from the day it is scheduled to the past 30 days.
My result should have Servername, current size - initial size(30 days back) and current day it is run.
Ex: Server1 200 - 100 = 100 2/1/2016
Server6 200 - 100 = 100 2/1/2016Thanks
The size is run by the first of every month so does that mean any day of the month?
And will there be several collected every month and do you compare to the previous or to the one 30 days less? And what if it's a month with 28 days? Or 31?
And then will there be entries for every day of the month or just one day?
Can you post your DDL and insert statements?
If I were to guess and make a lot of assumptions, it could be you want previous sizes for each server. If you were on SQL Server 2012 or higher, you could use lead/lag.
On SQL Server 2008, you could use a CTE, get row numbers and then self join the CTE. If it's nothing other than previous rows and you just have a row the first of each month for all the servers, you could just do something like this pseudo code:
;WITH CTE AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CollectedDate) RowNum,
ServerName, Size, CollectedDate
FROM Servers
)
SELECT
cte.ServerName,
cte.Size,
prev.Size PreviousSize,
cte.Size - prev.Size as SizeDifference,
cte.CollectedDate
FROM CTE
INNER JOIN CTE Prev
ON prev.rownum = CTE.rownum - 1
AND prev.Servername = CTE.Servername
WHERE cte.CollectedDate = '2/1/2016'
Sue
May 17, 2017 at 9:54 pm
Sue_H - Wednesday, May 17, 2017 8:37 PMsqlnewbie17 - Wednesday, May 17, 2017 12:00 AMMy table data.
date column values sample : 2017-05-16 03:14:59.647
Server1 100 1/1/2016
Server2 100 1/1/2016
Server3 100 1/1/2016
Server4 100 1/1/2016
Server5 100 1/1/2016
Server6 100 1/1/2016
Server1 200 2/1/2016
Server2 200 2/1/2016
Server3 200 2/1/2016
Server4 200 2/1/2016
Server5 200 2/1/2016
Server6 200 2/1/2016
Server1 300 3/1/2016
Server2 300 3/1/2016
Server3 300 3/1/2016
Server4 300 3/1/2016
Server5 300 3/1/2016
Server6 300 3/1/2016
Server1 400 4/1/2016
Server2 400 4/1/2016
Server3 400 4/1/2016
Server4 400 4/1/2016
Server5 400 4/1/2016
Server6 400 4/1/2016These are my Servers and their sizes by the first of every month.I have 40 of them.
I need help with sql which will list all distinct servers and their differences in size from the day it is scheduled to the past 30 days.
My result should have Servername, current size - initial size(30 days back) and current day it is run.
Ex: Server1 200 - 100 = 100 2/1/2016
Server6 200 - 100 = 100 2/1/2016Thanks
The size is run by the first of every month so does that mean any day of the month?
And will there be several collected every month and do you compare to the previous or to the one 30 days less? And what if it's a month with 28 days? Or 31?
And then will there be entries for every day of the month or just one day?
Can you post your DDL and insert statements?If I were to guess and make a lot of assumptions, it could be you want previous sizes for each server. If you were on SQL Server 2012 or higher, you could use lead/lag.
On SQL Server 2008, you could use a CTE, get row numbers and then self join the CTE. If it's nothing other than previous rows and you just have a row the first of each month for all the servers, you could just do something like this pseudo code:
;WITH CTE AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CollectedDate) RowNum,
ServerName, Size, CollectedDate
FROM Servers
)
SELECT
cte.ServerName,
cte.Size,
prev.Size PreviousSize,
cte.Size - prev.Size as SizeDifference,
cte.CollectedDate
FROM CTE
INNER JOIN CTE Prev
ON prev.rownum = CTE.rownum - 1
AND prev.Servername = CTE.Servername
WHERE cte.CollectedDate = '2/1/2016'Sue
Sue, it's nice to see someone who remembers this technique. I still have servers on SQL 2008, so it comes in handy.
May 18, 2017 at 12:07 am
I need server size difference for any 2 dates which are 30 days apart.
The query posted is giving me an empty set.
May 18, 2017 at 8:21 am
sqlnewbie17 - Thursday, May 18, 2017 12:07 AMI need server size difference for any 2 dates which are 30 days apart.
The query posted is giving me an empty set.
And we just don't have enough information as I stated earlier.
The sample data doesn't even have data for any two dates exactly 30 days apart so it would seem the requirements are not clear.
Sue
May 18, 2017 at 8:26 am
Ed Wagner - Wednesday, May 17, 2017 9:54 PMSue, it's nice to see someone who remembers this technique. I still have servers on SQL 2008, so it comes in handy.
You're not alone - there are still so many SQL 2008s around 🙂
Sounds odd but I still think it's fun doing it with CTEs anyway.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply