Link to home
Start Free TrialLog in
Avatar of hraja77
hraja77

asked on

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]
Avatar of hraja77
hraja77

ASKER

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
Avatar of Ryan McCauley
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

Post some sample data with expected result.
Avatar of hraja77

ASKER

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

thanks in advance
Avatar of hraja77

ASKER

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
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.
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.
Avatar of hraja77

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hraja77

ASKER

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
Avatar of hraja77

ASKER

i get this errors when i run the sql any odeas ?

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


thanks
H
Avatar of hraja77

ASKER

whoops that seems to work just a ] missing - i will carry on testing now

thanks again ryan
Avatar of hraja77

ASKER

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
Avatar of hraja77

ASKER

thanks
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!