Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to get data into one view


Trying to get data into one view

Author
Message
peter2501
peter2501
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 63
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.)
Attachments
table-discription.pdf (13 views, 289.00 KB)
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5802 Visits: 7137
Does this help:
Declare @Start DATETIME2,
@End DATETIME2 = DATEADD(ms,-3,CONVERT(DATETIME2, CONVERT(DATE, DATEADD(DD,-DATEPART(DD, GETDATE()-1),GETDATE()))))
SELECT @Start = CONVERT(DATETIME2, CONVERT(DATE, DATEADD(DD, -DATEPART(DD, @End)+1,@End)))
SELECT @Start, @End

Select CONVERT(VARCHAR(4),DATEPART(MM, start))+'/'+CONVERT(VARCHAR(4), DATEPART(DD, start)) AS [Date],
COUNT (DISTINCT Username) AS uniquelogon
from Miview
Where Activity = 'Logon'
and Start BETWEEN @Start AND @End
GROUP BY CONVERT(VARCHAR(4),DATEPART(MM, start))+'/'+CONVERT(VARCHAR(4), DATEPART(DD, start))



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
peter2501
peter2501
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 63
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.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5802 Visits: 7137
Sorry, didn't copy the GROUP BY part of the statement.
I have edited my above post to include it.
Please check if that will work.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4277 Visits: 6431
If you have 0 logins on a day, do you want to show that date (with 0 unique logins) row?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4277 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
peter2501
peter2501
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 63
Yes when there are 0 logins, then it should return 0
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4277 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
peter2501
peter2501
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 63
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

Date Unique logon
1-Jul 0
2-Jul 0
3-Jul 0
4-Jul 0
5-Jul 0
6-Jul 152
7-Jul 40
8-Jul 37
9-Jul 217
10-Jul 182
11-Jul 159
12-Jul 130
13-Jul 135
14-Jul 6
15-Jul 16
16-Jul 117
17-Jul 118
18-Jul 100
19-Jul 98
20-Jul 78
21-Jul 6
22-Jul 10
23-Jul 85
24-Jul 91
25-Jul 75
26-Jul 75
27-Jul 59
28-Jul 6
29-Jul 7
30-Jul 61
31-Jul 75
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4277 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search