February 3, 2014 at 12:54 pm
Hello!
I use counter queries to monitor activity in my document management systems, and they work great for normal activity monitoring.
Here's an example:
select status, count(status) as counter from _obj_2 group by status
That will return a number of records for each of an alphabetical set of statuses in the 'status' column.
Now I'm trying to drill down to be more specific and also filter the returns to show only the last 7 days of activity.
There's a column with timestamp data in the table I'm working with now, but I don't care if I filter the week by that or system data.
I've been chasing down a PIVOT based solution, but my results don't mirror the results that I get in a SQL Fiddler test environment, even though the basics seem to match.
Instead of getting a single counter of the number of entries in the column that match my criteria during the last week, in my case the pivot query seems to deliver a return for each time the criteria was met.
My first attempt was to add qualifiers to my original counter, but on the second part of the qualifying statements where I specify the date range I get an error saying that my column name doesn't exist when it does.
Here are the two angles that I've come up with so far:
Attempt #1-
select * from eventlog
(
select oldvalue, count(oldvalue) as counter from eventlog group by oldvalue
)
T where oldvalue = 'P'
and eventtime >= DATEADD(day,-7, GETDATE()))
Attempt #2-
SELECT
[P] AS Pending
FROM
(SELECT * FROM EventLog WHERE eventtime >= DATEADD(day,-7, GETDATE()))
AS T
PIVOT
(
COUNT (oldvalue) FOR oldvalue IN ([P])
)
AS P;
Here's a sample table for testing:
create table eventlog
([oldvalue] varchar 32, [eventtime] varchar 100)
select 'P', '2014-02-01 12:24:52.083'
union
select 'E', '2014-02-02 12:24:52.083'
union
select 'P', '2014-01-31 12:24:52.083'
union
select 'P', '2014-01-16 12:24:52.083'
The correct result would be a counter showing the value of 2, since I am looking for the number of P entries within the last week.
I appreciate you taking a few minutes to check out my situation, and please let me know if I've made any mistakes in submitting my issue.
Thanks!
February 3, 2014 at 1:59 pm
Like this?
select COUNT(*)
from eventlog
T where oldvalue = 'P'
and eventtime >= DATEADD(day,-7, GETDATE())
You would do yourself a world of good to change your datatype of eventtime to datetime instead varchar.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2014 at 2:08 pm
Ah, and so it turns out that in my live table it IS datetime.
My bad, and I'm working hard to learn the art and craft of creating well thought out forum posts.
Everything database oriented is an adventure, and that's why I enjoy working in them so much.
Still after a few years of daily exposure I find that I'm still just at the beginning of the journey!
Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply