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 Sunday, September 22, 2013 7:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
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
Post #1497255
Posted Sunday, September 22, 2013 11:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 35,769, Visits: 32,438
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497265
Posted Sunday, September 22, 2013 11:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 35,769, Visits: 32,438
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497266
Posted Monday, September 23, 2013 2:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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



  Post Attachments 
Untitled.jpg (6 views, 475.84 KB)
Post #1497291
Posted Tuesday, September 24, 2013 5:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
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....
Post #1497767
Posted Tuesday, September 24, 2013 5:49 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: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
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
Post #1497780
Posted Tuesday, September 24, 2013 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 3:03 AM
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
Post #1497783
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse