Sql Server Syntax


I have a raw table as defined below

CREATE TABLE [dbo].[iostats](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Timestamp] [datetime] NULL,
 [physical_name] [varchar](500) NOT NULL,
 [num_of_bytes_read] [bigint] NOT NULL,
 [num_of_bytes_written] [bigint] NOT NULL,
 [io_stall_write_ms] [bigint] NOT NULL,
 [io_stall] [bigint] NULL

this table shows data taken daily but the raw data show cumulative figures;
i need to break down the data so for each day it only shows stats for that day i.e the number_of_bytes written, number_of_bytes read for that day e.g

so day 1 figures should be ok but day 2 figures should be day 2 - day1 and day 3 figures should be day3 - (day1 +day2) etc - please help
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hraja77Author Commented:
the output would  be something like below

Date       physical_name      num_of_bytes_read      num_of_bytes_written      IO write waits (ms)      User IO waits (ms)
05/04/2011      N:\Autopart\VTData\Autopart_Data.mdf      500      500      10      50
04/04/2011      N:\Autopart\VTData\Autopart_Data.mdf      1000      510      2      200
03/04/2011      N:\Autopart\VTData\Autopart_Data.mdf      1500      650      99      0
02/04/2011      N:\Autopart\VTData\Autopart_Data.mdf      20      600      30      6
Anil GolamariCommented:
Select * from [dbo].[iostats]
where Date =< datetime.now.

Anil GolamariCommented:
select  * from [dbo].[iostats]
where Date <= 'PresentDate'
order by Date desc
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Anil GolamariCommented:
I think I misunderstood your question and posted both above comments thinking that you are trying to all data back to particular date. If you want to get Data only for that day you can filter it by datetime stamp right. I.e what is the amount of bytes etc for a given day.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
try something like this
WITH ctestats ([ID]
      ,rowOrder )
      ,ROW_NUMBER() OVER (ORDER BY [Timestamp] ASC ) AS rowOrder
  FROM [DENEME].[dbo].[iostats])

SELECT C1.ID, C1.Timestamp, C1.physical_name, C1.num_of_bytes_read - ISNULL(C2.num_of_bytes_read, 0) AS num_of_bytes_read, C1.num_of_bytes_written - ISNULL(C2.num_of_bytes_written, 0) AS num_of_bytes_written  
FROM ctestats C1
	LEFT JOIN ctestats C2 ON C1.rowOrder  = C2.rowOrder +1

Open in new window

hraja77Author Commented:
hi i will try this tommorrow as i have just lost connection as the server has gone down for maintenace;
i assume this will give one row for each physical_name per day so for 7 day report each physical_name will appear 7 times and day 1 will be day 1 data, where as day 2 data will be day2-day1 and day3 will be day3-(day1+day2) etc....

hraja77Author Commented:
i think i'm a plonker and confused everyone - i only need to show the difference for eac day so day3 would be day3-day2 and day6 would be day6-day5 not what i said above - sorry and thanks - will test these tommorrow
hraja77Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.