SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


screenshot on view


screenshot on view

Author
Message
cs 29850
cs 29850
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204328 Visits: 41950
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.
Hidden RBAR: Counting with Recursive CTE's

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204328 Visits: 41950
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
cs 29850
cs 29850
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
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]


Attachments
Untitled.jpg (9 views, 475.00 KB)
cs 29850
cs 29850
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
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....
Erland Sommarskog
Erland Sommarskog
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4938 Visits: 875
Jeff Moden (9/22/2013)
[quote]Erland Sommarskog (9/22/2013)
I know you can probably do such rCTEs in your sleep ;-)


Actually, I had to test it, and that was a good thing, because I had the condition wrong, and kept on looping unitl I hit the default limit of 100 recurssios.

but that's an rCTE that counts and, even for small numbers of rows, it's unnecessarily resource intensive.


Yeah, it is not a bright solution, and I prefer to use a prepared table of numbers. But since the poster seemed to confused and asked about inlined function, I decided to keep it simple.

I see now that he has come up with a query, but as he refuses to give us sample data to work with, it's difficult to help.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
cs 29850
cs 29850
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
I don't know how to use...your're inline and rcte...function

I only know a little of SQL....

Please tell me more.....thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search