Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trying to get data into one view Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 6:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 8:45 AM
Points: 14, Visits: 35
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.)


  Post Attachments 
table-discription.pdf (13 views, 289.58 KB)
Post #1354473
Posted Wednesday, September 5, 2012 7:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 3,856, Visits: 4,996
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”
Post #1354518
Posted Wednesday, September 5, 2012 8:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 8:45 AM
Points: 14, Visits: 35
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.
Post #1354553
Posted Wednesday, September 5, 2012 8:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 3,856, Visits: 4,996
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”
Post #1354591
Posted Thursday, September 6, 2012 10:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1355715
Posted Thursday, September 6, 2012 10:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1355721
Posted Friday, September 7, 2012 2:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 8:45 AM
Points: 14, Visits: 35
Yes when there are 0 logins, then it should return 0
Post #1355814
Posted Friday, September 7, 2012 2:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1355829
Posted Friday, September 7, 2012 2:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 8:45 AM
Points: 14, Visits: 35
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
Post #1355833
Posted Friday, September 7, 2012 3:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1355840
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse