September 5, 2012 at 6:22 am
Hello,
I have already posted this before, but gave too less info.
I am trying to get a total of unique usernames per day out of a table with the activity is Logon. So it is only one table.
But the way I do it, does not give me one view with all info, but the each day seperatly.
I tried to use UNION or other statement but they don't fly.
I cannot get my logic into the logic of SQL 🙂
The answers I got from the other posted messages were helping but just need to have the final step,
And also if I can get the date back as 8/1 (for first of august) instead of only 1 and the table header as August it would also be great. But mainly I want to have only one view with 31 rows displaying each day the total amount of unique logon.
Attached a document of the query, result, table definition and a part of the raw data. (in total the data is about 200K rows.)
September 5, 2012 at 7:23 am
This was removed by the editor as SPAM
September 5, 2012 at 8:02 am
unfortunately not
Msg 8120, Level 16, State 1, Line 6
Column 'Miview.Start' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
That is always the error I get when I try also something like what you proposed.
September 5, 2012 at 8:30 am
This was removed by the editor as SPAM
September 6, 2012 at 10:12 pm
If you have 0 logins on a day, do you want to show that date (with 0 unique logins) row?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 6, 2012 at 10:29 pm
Here is another way to return m/d format (mm/dd without leading 0s):
SELECT STUFF(REPLACE('/'+CONVERT(VARCHAR(5), YourDate, 101), '/0', '/'), 1, 1, '')
On the GROUP BY, I would use something like this to handle crossing year end boundary:
GROUP BY CONVERT(VARCHAR(10), YourDate, 101)
But on ORDER BY, just use the truncated date:
ORDER BY DATEADD(d, DATEDIFF(d, 0, YourDate), 0)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 7, 2012 at 2:22 am
Yes when there are 0 logins, then it should return 0
September 7, 2012 at 2:54 am
In that case, this should work nicely for you:
DECLARE @Miview TABLE
(Username VARCHAR(20), Start DATETIME, Activity VARCHAR(20))
INSERT INTO @Miview
SELECT 'Tom', '2012-09-07 12:00', 'Logon'
UNION ALL SELECT 'Fred', '2012-09-07 12:01', 'Logon'
UNION ALL SELECT 'Fred', '2012-09-07 12:02', 'Logon'
UNION ALL SELECT 'Tom', '2012-09-05 12:00', 'Logon'
UNION ALL SELECT 'Fred', '2012-09-05 12:01', 'Logon'
UNION ALL SELECT 'Sam', '2012-09-05 12:02', 'Logon'
UNION ALL SELECT 'Bob', '2012-09-05 12:03', 'Logon'
DECLARE @Start DATE = '2012-09-04'
,@End DATE = '2012-09-08'
,@Days INT
;WITH Tally (n) AS (
SELECT TOP(1+DATEDIFF(day, @Start, @End))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns)
SELECT [Date]=STUFF(
REPLACE('/'+CONVERT(VARCHAR(5), Start, 101), '/0', '/')
, 1, 1, '')
,UniqueLogins=COUNT(DISTINCT Username)
FROM (
SELECT UserName, Start=DATEADD(d, DATEDIFF(d, 0, Start), 0), Activity
FROM @Miview
WHERE Activity = 'Logon' AND Start >= @Start AND Start < DATEADD(day, 1, @End)
UNION ALL
SELECT NULL, DATEADD(day, n, @Start), 'Logon'
FROM Tally) a
GROUP BY Start
ORDER BY Start
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 7, 2012 at 2:56 am
Still not working with the newest queries I got, same error withe the group by clause (even twice this time)
Maybe this is more difficult then expected. because if I use this table and put it into an excel sheet and use pivot table then I can also get the date back, but again only per one date. Not all in once.
Example off the results I want (this is done manually now, each row is calculated manually), but want to get it automated
DateUnique logon
1-Jul0
2-Jul0
3-Jul0
4-Jul0
5-Jul0
6-Jul152
7-Jul40
8-Jul37
9-Jul217
10-Jul182
11-Jul159
12-Jul130
13-Jul135
14-Jul6
15-Jul16
16-Jul117
17-Jul118
18-Jul100
19-Jul98
20-Jul78
21-Jul6
22-Jul10
23-Jul85
24-Jul91
25-Jul75
26-Jul75
27-Jul59
28-Jul6
29-Jul7
30-Jul61
31-Jul75
September 7, 2012 at 3:10 am
peter2501 (9/7/2012)
Still not working with the newest queries I got, same error withe the group by clause (even twice this time)
Have you tried mine?
Note that I just made a quick edit to better handle the end date in the range.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 7, 2012 at 3:22 am
Yes, it works.
Thanks, now I can dig into the script what it really does so I can also try to understand.
Thanks very much.
September 7, 2012 at 3:29 am
Yes, yours is working great now. thanks a lot
September 7, 2012 at 3:45 am
peter2501 (9/7/2012)
Yes, it works.Thanks, now I can dig into the script what it really does so I can also try to understand.
Thanks very much.
You're welcome.
A brief explanation:
The Tally table is used to generate a set of "dummy" logins for each date in the range (the NULL Username ensures these aren't counted). In the query out of your table, I've truncated each login time to the day (without time stamp), so I can use this for GROUP and ORDER BY. The range of records starts at @Start but ends before @End + 1 day.
Hope that helps.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 13, 2012 at 7:32 am
just to be clear.
What you want is a pivot table with months across the top (columns), days down the side (31 rows, include rows with no logins) and #logins as values in the intersect
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply