screenshot on view

  • I had a table like this:

    Name StartTime EndTime

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

    abc 10:00 14:00

    bcd 10:05 12:32

    cde 13:10 14:08

    dfe 11:00 11:08

    We planning to create the view using sql commend to view the total clients in every 30 mins.

    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

    May I know how to create this view....Thanks for your help....

  • absolutely reeks of a homework assignment.

    If you can group by half hours, then you're golden. Try something like getting the number of half hours since midnight, and then grouping on that.

  • You need a table of numbers, I have an explanation of this concept on

    http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum (only read down to the header "An inline function".)

    Once you have this table, you can translate the numbers to half-hours with the dateadd function. Then you join against your table:

    ON halfhours.halfhour BETWEEN StartTime AND EndTime

    And then do a SELECT halfhour, COUNT(*) GROUP BY halfhour.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog (9/21/2013)


    You need a table of numbers, I have an explanation of this concept on

    http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum (only read down to the header "An inline function".)

    Once you have this table, you can translate the numbers to half-hours with the dateadd function. Then you join against your table:

    ON halfhours.halfhour BETWEEN StartTime AND EndTime

    And then do a SELECT halfhour, COUNT(*) GROUP BY halfhour.

    Thanks for your help, but I still have problems,

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

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

    Thanks a lot

  • pietlinden (9/21/2013)


    absolutely reeks of a homework assignment.

    If you can group by half hours, then you're golden. Try something like getting the number of half hours since midnight, and then grouping on that.

    If you say yes, fine....I only do the view for internal to check the system logs.

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

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • 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

  • I repeat what I said in my previous post:

    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.

    Your posts are very confusing. The expected output is presented as a time slot and a count, and then you start talking about hostnames reapparing. Yeah, those two jobs may have the same host, but they are not overlapping, so what does that matter? And even if they were overlapping, why would that matter?

    Your screenshot tells me that you have a datetime column, and that you may be be interesting in the dates as well. Then again, I don't know. For all I care, you may want to lump all jobs that ran at 12:30, no matter the day, into a single slot.

    And what is the output supposed to mean? The jobs that were running at that point in time? (So that a job that ran from 12:01 to 12:29 would not be counted?)

    All this boils down to: if you don't know what you want, nor will I know. And if you know what you want, you need to tell me.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog (9/22/2013)


    I repeat what I said in my previous post:

    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.

    Your posts are very confusing. The expected output is presented as a time slot and a count, and then you start talking about hostnames reapparing. Yeah, those two jobs may have the same host, but they are not overlapping, so what does that matter? And even if they were overlapping, why would that matter?

    Your screenshot tells me that you have a datetime column, and that you may be be interesting in the dates as well. Then again, I don't know. For all I care, you may want to lump all jobs that ran at 12:30, no matter the day, into a single slot.

    And what is the output supposed to mean? The jobs that were running at that point in time? (So that a job that ran from 12:01 to 12:29 would not be counted?)

    All this boils down to: if you don't know what you want, nor will I know. And if you know what you want, you need to tell me.

    sorry about, I only know the simple english....

    this is backup report of our company

    "hostname" is the server name

    start time is start backup time

    but some server will have file level and DB level backup at same day...

    so will have more then one entry in a day

    we want to know how many backup jobs in process at every 30 mins. (from 8p.m. to 8a.m.)

    to do the vertex buffer.

  • Again:

    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.

    I will be travelling tomorrow, so if you post something to work from, I hope that someone else can step in.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog (9/22/2013)


    Again:

    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.

    I will be travelling tomorrow, so if you post something to work from, I hope that someone else can step in.

    Enjoy your trip, thanks for your help

  • Erland Sommarskog (9/22/2013)


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

    )

    I know you can probably do such rCTEs in your sleep 😉 but that's an rCTE that counts and, even for small numbers of rows, it's unnecessarily resource intensive. Please see the following article for why you might want to avoid such a thing.

    [font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font][/url]

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • cs 29850 (9/22/2013)


    Erland Sommarskog (9/22/2013)


    I repeat what I said in my previous post:

    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.

    Your posts are very confusing. The expected output is presented as a time slot and a count, and then you start talking about hostnames reapparing. Yeah, those two jobs may have the same host, but they are not overlapping, so what does that matter? And even if they were overlapping, why would that matter?

    Your screenshot tells me that you have a datetime column, and that you may be be interesting in the dates as well. Then again, I don't know. For all I care, you may want to lump all jobs that ran at 12:30, no matter the day, into a single slot.

    And what is the output supposed to mean? The jobs that were running at that point in time? (So that a job that ran from 12:01 to 12:29 would not be counted?)

    All this boils down to: if you don't know what you want, nor will I know. And if you know what you want, you need to tell me.

    sorry about, I only know the simple english....

    this is backup report of our company

    "hostname" is the server name

    start time is start backup time

    but some server will have file level and DB level backup at same day...

    so will have more then one entry in a day

    we want to know how many backup jobs in process at every 30 mins. (from 8p.m. to 8a.m.)

    to do the vertex buffer.

    @cs 29850,

    Please see the first link in "Helpful Links" in my signature line below to see what Erland is talking about so we can help you better. Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for your're help.

    I found the solution of today record only

    anyone can teach me who to show every day records,

    select Period.[Time], count(FileBackup.Hostname)

    from dbo.FileBackup, dbo.Period

    where (CONVERT(varchar(19), FileBackup.DateKey, 120)

    > CONVERT(varchar(10), GETDATE() - 1, 120) + SPACE(1) + '09:59:00')

    AND (Period.[Time] between CONVERT(VARCHAR(12) , FileBackup.StartTime,114) AND CONVERT(VARCHAR(12) , FileBackup.EndTime,114))

    group by Period.[Time]

    order by Period.[Time]

  • cs 29850 (9/23/2013)


    Thanks for your're help.

    I found the solution of today record only

    anyone can teach me who to show every day records,

    select Period.[Time], count(FileBackup.Hostname)

    from dbo.FileBackup, dbo.Period

    where (CONVERT(varchar(19), FileBackup.DateKey, 120)

    > CONVERT(varchar(10), GETDATE() - 1, 120) + SPACE(1) + '09:59:00')

    AND (Period.[Time] between CONVERT(VARCHAR(12) , FileBackup.StartTime,114) AND CONVERT(VARCHAR(12) , FileBackup.EndTime,114))

    group by Period.[Time]

    order by Period.[Time]

    today I check the results, 1000 records,....only found out 442......Oh my god....

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply