• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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]
0
hraja77
Asked:
hraja77
  • 9
  • 5
1 Solution
 
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 McCauleyData and 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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 McCauleyData and 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 McCauleyData and 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 McCauleyData and 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
 
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 McCauleyData and 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now