December 27, 2011 at 2:20 pm
Greetings,
Since I started a stats class I'm thinking of a bunch of crazy stuff to do with my data...however, my imagination far exceeds my capabilities. Please review the description below and provide pointers/direction....thanks in adavance.
I have created a simple script that will show me how many hosts have been on each of the past 30 days. The view I'm pulling from removes duplicates and shows me only the data from the last scantime.
Using this data I can create a line chart with 30 data points (1 for each of the last 30 days) along with the number of hosts scanned on each day. Since scans occur constantly the data is front loaded so most of the hosts have been scanned in the last X days.
I would like to add an ogive (running total) to my chart to show what the cumulative scan count is for each of the last 30 days. So, the count from DAy 30 - Scan Count from day 30....scan count for day 29 = DAY 30 count + DAY 29 Count)....etc.
How can I add a 'ogive' column to the output of the SQL below?
Table:
HOST SCANTIME
Computer1 12/1/2011
Computer2 12/2/2011
Computer3 12/3/2011
etc...
SQL:
SELECT (DATEPART(dayofyear,getdate())-(DATEPART(dayofyear,SCANTIME)) AS [# of DAYS AGO], COUNT(*) AS CNT FROM
(SELECT DISTINCT HOST, SCANTIME FROM vwSCANVIEW) ve
GROUP BY DATEPART(dayofyear,SCANTIME)
ORDER BY [# of DAYS AGO]
December 27, 2011 at 2:41 pm
There's an article on running totals in SQL Server at: http://www.sqlservercentral.com/articles/T-SQL/68467/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 1:42 pm
Thank you for the information and the link to the article. I found that I am able to do what I needed within the analytics toolset that I have at my disposal. I will, however, save the article for review at a later time..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply