Trying to get data into one view

  • 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.)

  • This was removed by the editor as SPAM

  • 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.

  • This was removed by the editor as SPAM

  • 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![/I]

    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

  • 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![/I]

    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

  • Yes when there are 0 logins, then it should return 0

  • 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![/I]

    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

  • 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

  • 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![/I]

    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

  • 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.

  • Yes, yours is working great now. thanks a lot

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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