• Erland Sommarskog (9/22/2013)


    cs 29850 (9/21/2013)


    1.where can create inline function? In view? or where?

    Not sure why you would create an inline function at all. The reference I gave you is from a larger article, which deals with topic not relevant to yours.

    OK, so that is true, you don't need a numbers table, but you can use the function that I present further down in the same article.

    The answer is that you would create function or table in your database.

    Then again, if all you have is half hours for 24 hours, with no regards to date, all you need is something that generates the 48 half hours for you, and that could be done in the view with a recursive CTE:

    WITH halfhours AS (

    SELECT cast('00:00' AS time(0)) AS halfhour

    UNION ALL

    SELECT dateadd(minute, 30, halfhour)

    FROM halfhours

    WHERE halfhour < cast('23:30' AS time(0))

    )

    2. If I have duplicate name in the table, may I group by name, halfhour ?

    Sorry, I don't understand. Your first post seemed to indicate that you wanted to show number of connections per half hour, not number of connections per halfhour and user.

    Generally, when you ask this question like this, it helps if you post:

    1) CREATE TABLE statements for your table(s).

    2) INSERT statements with sample data, enough to cover all relevant cases.

    3) The desired result given the sample.

    The looser and vaguer your present your problem, the less likely that you will get an answer you can actually use.

    Sorry about that, I can't present my problem very clear.

    I attached my table at the below, for your refer. I hope may be better.....

    any suggestion for me create 2 views to find the total of every half hour...?

    1. by time only....

    Time Clients

    --------------------------------

    10:00 1

    10:30 2

    11:00 3

    11:30 2

    12:00 2

    12:30 2

    13:00 1

    13:30 2

    14:00 2

    14:30 0

    2. group by day and "hostname"

    some "hostname" will appear few times in a day.

    JobID 29261 & 29504 is same hostname some the startime is 22:02 - 02:04