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]
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]
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:
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
Post some sample data with expected result.
ASKER
soory the raw dta in the table is cumulative - i wnat it broken into a day by day
thanks in advance
thanks in advance
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
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
i think i confused everyone as i only needed the difference like you said - thanks alot - really appeciate it
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
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
thanks
H
ASKER
whoops that seems to work just a ] missing - i will carry on testing now
thanks again ryan
thanks again ryan
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
how can i change your query to show the sum for each column per day
thanks
H
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!
Glad it worked for you!
ASKER
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\Autopar
04/04/2011 N:\Autopart\VTData\Autopar
03/04/2011 N:\Autopart\VTData\Autopar
02/04/2011 N:\Autopart\VTData\Autopar