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

screenshot on view Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 9:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
Points: 16, Visits: 50
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....
Post #1497096
Posted Saturday, September 21, 2013 2:11 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 847, Visits: 5,461
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.
Post #1497160
Posted Saturday, September 21, 2013 4:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 809, Visits: 727
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1497167
Posted Saturday, September 21, 2013 11:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
Points: 16, Visits: 50
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
Post #1497186
Posted Saturday, September 21, 2013 11:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
Points: 16, Visits: 50
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.
Post #1497187
Posted Sunday, September 22, 2013 3:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 809, Visits: 727
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1497194
Posted Sunday, September 22, 2013 3:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
Points: 16, Visits: 50
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


  Post Attachments 
Untitled.jpg (5 views, 392.46 KB)
Post #1497195
Posted Sunday, September 22, 2013 4:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 809, Visits: 727
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1497196
Posted Sunday, September 22, 2013 4:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
Points: 16, Visits: 50
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.
Post #1497197
Posted Sunday, September 22, 2013 12:41 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 809, Visits: 727
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1497221
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse