September 20, 2013 at 9:08 pm
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....
September 21, 2013 at 2:11 pm
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.
September 21, 2013 at 4:07 pm
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]
September 21, 2013 at 11:14 pm
Erland Sommarskog (9/21/2013)
You need a table of numbers, I have an explanation of this concept onhttp://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
September 21, 2013 at 11:17 pm
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.
September 22, 2013 at 3:05 am
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]
September 22, 2013 at 3:31 am
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
September 22, 2013 at 4:29 am
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]
September 22, 2013 at 4:42 am
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.
September 22, 2013 at 12:41 pm
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]
September 22, 2013 at 7:45 pm
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
September 22, 2013 at 11:36 pm
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
Change is inevitable... Change for the better is not.
September 22, 2013 at 11:39 pm
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
Change is inevitable... Change for the better is not.
September 23, 2013 at 2:29 am
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]
September 24, 2013 at 5:02 am
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