How to create stats report for each day
hayko98
I need help with my stats report.I have following query .
DECLARE @DateFrom DATETIME;
DECLARE @DateTo DATETIME;
DECLARE @Provider INT;
SET @DateFrom='2009-12-01 00:00:00.0';
SET @DateTo='2009-12-01 23:55:00.0';
SET @Provider=6;
SELECT
SUM(case when AUDIT_CHANGES LIKE '%A%' then 1 else 0 end) AS [A],
SUM(case when AUDIT_CHANGES LIKE '%B1%' then 1 else 0 end) AS [B1],
SUM(case when AUDIT_CHANGES LIKE '%B2%' then 1 else 0 end) AS [B2],
SUM(case when AUDIT_CHANGES LIKE '%B3%' then 1 else 0 end) AS [B3],
SUM(case when AUDIT_CHANGES LIKE '%B4%' then 1 else 0 end) AS [B4],
SUM(case when AUDIT_CHANGES LIKE '%B5%' then 1 else 0 end) AS [B5],
FROM AUDIT_TRAIL WHERE PROVIDER_ID = @Provider
AND
TIME_STAMP BETWEEN @DateFrom AND @DateTo
AND
AUDIT_CHANGES IS NOT NULL
Provider A B1 B2 B3 B4 B5
XXX 0 23 7 10 8 8
This report gives me stats for one day(12.01.09).I need to get results
for from 11.01.09 thru 11.30.09 for each weekday.
I have SQL Server 2005 .
Thank you
Hugo Kornelis
Hi Hayko,
Something like this, I guess:
SELECT DATEADD(day,
DATEDIFF(day, '20000101', TIME_STAMP),
'20000101') AS Day,
PROVIDER_ID AS Provider,
SUM(case when AUDIT_CHANGES LIKE '%A%'
then 1 else 0 end) AS [A],
SUM(case when AUDIT_CHANGES LIKE '%B1%'
then 1 else 0 end) AS [B1],
SUM(case when AUDIT_CHANGES LIKE '%B2%'
then 1 else 0 end) AS [B2],
SUM(case when AUDIT_CHANGES LIKE '%B3%'
then 1 else 0 end) AS [B3],
SUM(case when AUDIT_CHANGES LIKE '%B4%'
then 1 else 0 end) AS [B4],
SUM(case when AUDIT_CHANGES LIKE '%B5%'
then 1 else 0 end) AS [B5]
FROM AUDIT_TRAIL
WHERE TIME_STAMP >= '20091101'
AND TIME_STAMP < '20091201'
AND AUDIT_CHANGES IS NOT NULL
GROUP BY DATEDIFF(day, '20000101', TIME_STAMP), PROVIDER_ID;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
hayko98
>
> - Show quoted text -
Hi Hugo.
After running your query i am getting a lot of duplicate dates:
*
*
*
11/1/2009 1 0 0 0 0 0 0
11/1/2009 1 0 0 0 0 0 0
11/1/2009 1 0 0 0 1 0 0
11/1/2009 1 0 0 0 0 0 0
11/1/2009 1 0 0 0 0 0 0
11/1/2009 1 0 0 0 0 0 0
11/1/2009 1 0 0 0 0 1 0
11/1/2009 1 0 0 0 0 0 0
11/1/2009 1 0 0 0 0 0 0
*
*
*
*
Is there any way to group them by date?Also if you can explain what
does
DATEADD(day,
DATEDIFF(day, '20000101', TIME_STAMP),
'20000101') AS Day
do and why 20000101?
Thank you
Erland Sommarskog
SELECT convert(char(8), TIME_STAMP, 112), AS Day,
PROVIDER_ID AS Provider,
SUM(case when AUDIT_CHANGES LIKE '%A%'
then 1 else 0 end) AS [A],
SUM(case when AUDIT_CHANGES LIKE '%B1%'
then 1 else 0 end) AS [B1],
SUM(case when AUDIT_CHANGES LIKE '%B2%'
then 1 else 0 end) AS [B2],
SUM(case when AUDIT_CHANGES LIKE '%B3%'
then 1 else 0 end) AS [B3],
SUM(case when AUDIT_CHANGES LIKE '%B4%'
then 1 else 0 end) AS [B4],
SUM(case when AUDIT_CHANGES LIKE '%B5%'
then 1 else 0 end) AS [B5]
FROM AUDIT_TRAIL
WHERE TIME_STAMP >= '20091101'
AND TIME_STAMP < '20091201'
AND AUDIT_CHANGES IS NOT NULL
GROUP BY convert(char(8), TIME_STAMP, 112), PROVIDER_ID;
However, this will only list days you actaully had changes. If you want
to list all week days, regardless of whether they have changes or not,
you need a calender table, or at least a table of numbers. See
http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum for how
to create and load one.
SELECT DATEADD(day, Number - 1, @DateFrom) AS Day,
PROVIDER_ID AS Provider,
SUM(case when AUDIT_CHANGES LIKE '%A%'
then 1 else 0 end) AS [A],
SUM(case when AUDIT_CHANGES LIKE '%B1%'
then 1 else 0 end) AS [B1],
SUM(case when AUDIT_CHANGES LIKE '%B2%'
then 1 else 0 end) AS [B2],
SUM(case when AUDIT_CHANGES LIKE '%B3%'
then 1 else 0 end) AS [B3],
SUM(case when AUDIT_CHANGES LIKE '%B4%'
then 1 else 0 end) AS [B4],
SUM(case when AUDIT_CHANGES LIKE '%B5%'
then 1 else 0 end) AS [B5]
FROM Numbers
LEFT JOIN AUDIT_TRAIL ON
TIME_STAMP >= DATEADD(day, Number - 1, @DateFrom) AND
TIME_STAMP < DATEADD(day, Number, @DateFrom) AND
AND AUDIT_CHANGES IS NOT NULL
WHERE Number <= 30
AND datename(dateadd(day, Number - 1, @DateFrom) NOT IN
('Saturday', 'Sunday')
GROUP BY DATEADD(day, Number - 1, @DateFrom), PROVIDER_ID;
Note: the above is totally untested.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Hugo Kornelis
>Hi Hugo.
>After running your query i am getting a lot of duplicate dates:
Hi Hayko,
Did you include the PROVIDER_ID column in the SELECT list?
And did you include the GROUP BY clause? (Note that if you select a part
of the statement in Management Studio, only that part will be executed).
If both questions are answered with "yes", I'll need more information.
And most of all, I'll need a way to reproduce the behaviour on my test
machine. Please see www.aspfaq.com/5006 for an overview of the
information I'll need to debug the problem.
>Also if you can explain what
>does
>DATEADD(day,
> DATEDIFF(day, '20000101', TIME_STAMP),
> '20000101') AS Day
>do and why 20000101?
Well, the "DATEDIFF(day, '20000101', TIME_STAMP)" calculates the number
of day boundaries between the date in the TIME_STAMP column and the
reference date (1/1/2000 in this case). This is used to group stats per
date, since the result will be the same for all stats of the same day,
regardless how late they were. That's why I included this expression in
the GROUP BY.
And "DATEADD(day, (above expression), '200010101') adds that number of
days to the reference date, resulting in the day of the TIME_STAMP but
without the time (or rather, since a datetime always has a time part,
with the time part set to midnight). So this displays the date of the
event, but not the time.
The choice of 20000101 as a reference date is just because I needed a
date. Any other date would have done as well. But do remember that the
same reference date has to be used in the DATEDIFF and the DATEADD!
hayko98
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On Tue, 8 Dec 2009 14:32:09 -0800 (PST), hayko98 wrote:
> >Hi Hugo.
> >After running your query i am getting a lot of duplicate dates:
>
> Hi Hayko,
>
> Did you include the PROVIDER_ID column in the SELECT list?
>
> And did you include the GROUP BY clause? (Note that if you select a part
> of the statement in Management Studio, only that part will be executed).
>
> If both questions are answered with "yes", I'll need more information.
> And most of all, I'll need a way to reproduce the behaviour on my test
> information I'll need to debug the problem.
>
> >Also if you can explain what
> >does
> >DATEADD(day,
> > DATEDIFF(day, '20000101', TIME_STAMP),
> > '20000101') AS Day
> >do and why 20000101?
>
> Well, the "DATEDIFF(day, '20000101', TIME_STAMP)" calculates the number
> of day boundaries between the date in the TIME_STAMP column and the
> reference date (1/1/2000 in this case). This is used to group stats per
> date, since the result will be the same for all stats of the same day,
> regardless how late they were. That's why I included this expression in
> the GROUP BY.
>
> And "DATEADD(day, (above expression), '200010101') adds that number of
> days to the reference date, resulting in the day of the TIME_STAMP but
> without the time (or rather, since a datetime always has a time part,
> with the time part set to midnight). So this displays the date of the
> event, but not the time.
>
> The choice of 20000101 as a reference date is just because I needed a
> date. Any other date would have done as well. But do remember that the
> same reference date has to be used in the DATEDIFF and the DATEADD!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thank you Erland and Hugo feor Your responds.
Erland: I run your first query and i got what i was looking for
(except weekeds part which i'll do it according the attached link).
Hugo: Yes to both of your questions and i could not connect to the
link that you attached (The requested URL /www.aspfaq.com/5006 was not
found on this server. )
Thank you both for your help.
Hugo Kornelis
(snip)
>Hugo: Yes to both of your questions and i could not connect to the
>link that you attached (The requested URL /www.aspfaq.com/5006 was not
>found on this server. )
Strange. I tried it and it did work for me. Though it did redirect me to
another URL for the final page:
http://www.aspfaq.com/etiquette.asp?id=5006
I'm glad your question is now answered, but you might want to keep this
link for future reference.