Sql Server 2008 Query

Hi,

I have a table into which i store daily stats, all i need to do is to output the data in a report
where the first column will be the date and the second column will be the 'num_of_bytes_read' and the third
will be num_of_bytes_written etc... but only shown for that day, so i need to take value off from the previous day for that physical_name
The current table figures are cumulative so include everyday upto the day the table was populated
please help
thanks
H


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
) ON [PRIMARY]
LVL 1
hraja77Asked:
Who is Participating?

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

x
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
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
I'm confused about what you want to report - you mention that the results should be cumulative, but then in your results you don't show it added up cumulatively. And for the IO values, how are you getting those - are they AVG, SUM, or what?

If you just want to add up what happens every day, then maybe you want something like this:

SELECT CONVERT(VARCHAR(10), [Timestamp], 120) as WhatDate,
       physical_name,
       SUM(num_of_bytes_read) as TotalBytesRead,
       SUM(num_of_bytes_written) as TotalBytesWritten,
       AVG(io_stall_write_ms) as AverageWriteWaitMS,
       SUM(io_stall) as UserIOWaits
  FROM [iostats]
GROUP BY CONVERT(VARCHAR(10), [Timestamp], 120) as WhatDate,
       physical_name

Open in new window

0
SharathData EngineerCommented:
Post some sample data with expected result.
0
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.

hraja77Author Commented:
soory the raw dta in the table is cumulative - i wnat it broken into a day by day

thanks in advance
0
hraja77Author Commented:
just to clarify the results should not be cumulative but only show the values for that day - so currently in the base table it will should number of reads for day one as '150' then for day 2 will always be 150 or more - if day 2 was 170 then my report should show 150 for day one then only 20 for day 2 etc for each column

thanks
H
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
Will there be a row in the table for every single day, or multiple records/day (or days missing)? I started to write a query that would get what you want, but I need to know the answer to that question before I can make sure it's done right.
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
Just to clarify, I know that every physical name is tracked in there, but I'm wondering what the data for each physical_name looks like - rows only for some days, one row for each day, or lots of rows for each day.
0
hraja77Author Commented:
for every day we would have one row of data for each physical_name;
so every datafile would have a value for each day i.e seven rows for a weekly report

thanks ryan for looking at this
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
Ah - that's actually the easiest one you could have picked and just involves a simple self-join :) Try something like this:

SELECT CONVERT(VARCHAR(10), n.[Timestamp], 120) as WhatDate,
       n.[physical_name],
       (n.[num_of_bytes_read] - o.[num_of_bytes_read) as [num_of_bytes_read],
       (n.[num_of_bytes_written] - o.[num_of_bytes_written]) as [num_of_bytes_written],
       (n.[io_stall_write_ms] - o.[io_stall_write_ms]) as [io_stall_write_ms],
       (n.[io_stall] - o.[io_stall]) as [io_stall]
  FROM iostats n
  JOIN iostats o
    ON n.physical_name = o.physical_name
   AND CONVERT(VARCHAR(10), n.[Timestamp], 120) = CONVERT(VARCHAR(10), DATEADD(dd, 1, o.[Timestamp]), 120)

Open in new window


This will join the table to itself, comparing rows with the same row from the day before and displaying the difference in the values between the two days. Let me know if you have any issues with it, or if it doesn't display the data you're expecting.
0

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
hraja77Author Commented:
thanks ryan i think you're almost and may have saved my bacon - i cannot test this now till the morning and will let you know then thanks alot;
i think i confused everyone as i only needed the difference like you said - thanks alot - really appeciate it
0
hraja77Author Commented:
i get this errors when i run the sql any odeas ?

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.


thanks
H
0
hraja77Author Commented:
whoops that seems to work just a ] missing - i will carry on testing now

thanks again ryan
0
hraja77Author Commented:
perfect just one last question and i will close this

how can i change your query to show the sum for each column per day

thanks
H
0
hraja77Author Commented:
thanks
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
You said it was one row/physical_name/day, so that should show the sum of activity for each physical_name. If you're looking for the sum of activity by day, regardless of the physical_name, then just remove that column from the select portion, wrap the stats in SUM(), and then add a group by containing only the first element of the select (the DATETIME portion I use).

Glad it worked for you!
0
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.