query assistance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query assistance

Author

Topic

timgru
Starting Member

2 Posts

Posted - 2011-03-10 : 23:29:41

Scenario: There is a job that is initiated at random times, with random runtimes, and the job can have multiple threads running at once. When the job is initiated it creates a row in the Test table and inserts the StartTime, when the job finishes it updates the same row with the EndTime.

Problem: Write a query that will tell you anytime the job was not running over the previous six months.

Table = Test
Column = StartTime (DateTime)
Column = EndTime (DateTime)

Any suggestions on how to write a query, or set of queries to solve this is greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-11 : 00:28:54

what have you tried ?

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 04:13:12

This is a complex problem...I've been thinking about it for some time now and I'm not sure I even know where to start

Is this an actual business requirement or or is it some kind of school assignment or something?

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 04:34:31

Tim;: can you confirm that this is a relevant implementation of your problem (no solution yet). The @PeriodStart and @PeriodEnd variables would reflect your requirement of "last 6 months":

DECLARE 
	@PeriodStart datetime = '2011-01-01 00:00:00',
	@PeriodEnd datetime = '2011-01-03 00:00:00'

DECLARE @table table (
	ID int NOT NULL IDENTITY (1, 1),
	StartTime datetime,
	EndTime datetime
	)
	
INSERT INTO @table (StartTime, EndTime)
VALUES	('2011-01-01 00:00:00', '2011-01-02 00:00:00'),
	('2011-01-02 04:00:00', '2011-01-02 12:00:00'),
	('2011-01-02 06:00:00', '2011-01-02 12:00:00'),
	('2011-01-02 18:00:00', '2011-01-02 23:00:00')

--> Expected output:
NotRunningFrom		NotRunningTo
2011-01-02 00:00:00.000	2011-01-02 04:00:00.000
2011-01-02 12:00:00.000	2011-01-02 18:00:00.000
2011-01-02 23:00:00.000	2011-01-03 00:00:00.000

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 05:06:10

Hmm...is this it? Now where is Peso when you need him??


;WITH cte AS (
	SELECT RowNum = ROW_NUMBER() OVER (ORDER BY StartTime), * 
	FROM (
		SELECT ID = -1, Action = 'Start', StartTime = @PeriodStart
		UNION ALL 
		SELECT ID, 'Start', StartTime FROM @table WHERE StartTime >= @PeriodStart AND EndTime <= @PeriodEnd
		UNION ALL 
		SELECT ID, 'End', EndTime FROM @table WHERE StartTime >= @PeriodStart AND EndTime <= @PeriodEnd
		UNION ALL
		SELECT -1, 'Start', @PeriodEnd
		) AS a
	)
SELECT NotRunningFrom = a.StartTime, NotRunningTo = b.StartTime
FROM cte a
	INNER JOIN cte b
		ON a.RowNum + 1 = b.RowNum
WHERE a.Action = 'End'
	AND b.Action = 'Start'
	AND a.StartTime < b.StartTime

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

timgru
Starting Member

2 Posts

Posted - 2011-03-11 : 17:00:32

Thanks! This was a problem presented to me in an interview...

Your example seems right on, and I got a lot out of it as it just caused me to dive into CTEs.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-12 : 07:38:45

Since timgru is done with his interview, it is only of academic interest, but very interesting problem!

While I can think of 3 different ways to do this using a while loop, I can't think of a way to do it in a set-based query. I can almost taste it using a recursive CTE, but not clear how to relate the recursive part of the CTE to the anchor part. Must be that my mind is polluted by C# and procedural programming, I am not able to think in sets.

Lumbago, in the code that you posted, for cases where there are overlapping pieces that work together to cover a range, does it seem like it is not picking up the overlapping pieces? Test case I was trying is this

DECLARE 
   @PeriodStart datetime = '2011-01-01 00:00:00',
   @PeriodEnd datetime = '2011-01-13 00:00:00'

INSERT INTO @table (StartTime, EndTime) VALUES   
   ('2011-01-01 00:00:00', '2011-01-05 00:00:00'),
   ('2011-01-03 00:00:00', '2011-01-10 00:00:00'),
   ('2011-01-07 00:00:00', '2011-01-13 00:00:00')

Sachin.Nand

2937 Posts

Posted - 2011-03-12 : 10:11:31

Well couldn't think of anything better then this


DECLARE 
	@PeriodStart datetime = '2011-01-01 00:00:00',
	@PeriodEnd datetime = '2011-01-03 00:00:00'

DECLARE @table table (
	ID int NOT NULL IDENTITY (1, 1),
	StartTime datetime,
	EndTime datetime
	)
	
INSERT INTO @table (StartTime, EndTime)
VALUES('2011-01-01 00:00:00', '2011-01-02 00:00:00'),
	('2011-01-02 04:00:00', '2011-01-02 12:00:00'),
	('2011-01-02 06:00:00', '2011-01-02 12:00:00'),
	('2011-01-02 18:00:00', '2011-01-02 23:00:00')
	
;WITH cte AS
  (SELECT *,id-1 idsac
   FROM @table ) ,
     ctesac AS
  ( SELECT *,id-ROW_NUMBER()over(PARTITION BY endtime ORDER BY id)rid
   FROM
     ( SELECT t1.id,t1.EndTime,T.StartTime
      FROM cte t1 OUTER apply
        (SELECT top 1 StartTime
         FROM cte t2
         WHERE t1.ID=t2.idsac
         ORDER BY EndTime DESC)T )T )
SELECT 
MIN(EndTime)NotRunningFrom,
COALESCE(MAX(StartTime),@PeriodEnd)NotRunningTo
FROM ctesac
GROUP BY rID

PBUH

Sachin.Nand

2937 Posts

Posted - 2011-03-12 : 10:19:18

quote:

Originally posted by sunitabeck

DECLARE 
   @PeriodStart datetime = '2011-01-01 00:00:00',
   @PeriodEnd datetime = '2011-01-13 00:00:00'

INSERT INTO @table (StartTime, EndTime) VALUES   
   ('2011-01-01 00:00:00', '2011-01-05 00:00:00'),
   ('2011-01-03 00:00:00', '2011-01-10 00:00:00'),
   ('2011-01-07 00:00:00', '2011-01-13 00:00:00')

The sample data you posted has problem.Check the first two records.The first record states that endtime is 2011-01-05 but the start time on the second record has a value less that the end time in the first record.

So these two records have to be differentiated using a candidate key
and cannot considered as a part of the same process while calculating.

PBUH

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-12 : 12:46:59

Sachin, from timgru's original posting it seemed to me that allowing for the type of overlapping time periods was a requirement.

The picture that came to mind was a 24 hour trauma center where at least one attending physician must be present at all times. So if Dr. Smith's schedule is supposed to end at 8:00AM and Dr. Jones is scheduled to start at the same time:

1. If Dr. Smith takes of at 7:45 so he can get to the golf course by tee time and Dr. Jones gets to the hospital only at 8:00, that is not ok, we want to list that break.

2. If the doctors pass each other in the hallway as one is going out and the other is coming in at precisely 8:00, that is acceptable.

3. If Dr. Smith stayed on until 8:15 to talk to a patient, and Dr. Jones came in early at 7:45, that is a good thing.

Sachin.Nand

2937 Posts

Posted - 2011-03-12 : 13:56:23

Please read the OP's requirement.He is asking for job not running for period of time and not times which are overlapping.

quote:
Problem: Write a query that will tell you anytime the job was not running over the previous six months.

So if I understand it correctly then there wont be a break time between the jobs if they are overlapping each other.

So in your case there wont be a break time to calculate(which is OP's requirement)because the times are overlapping each other as both Dr.Smith and Dr.Jones or either one of them is always present at any given point of time.

OP wants to find the time when neither Dr.Jones not Dr.Smith is present.

PBUH

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-13 : 08:53:18

Sunitas explanation is correct but the corresponding sample data doesn't have any gaps, so according to OPs requirements it doesn't comply. I'm on my iPhone at the moment so I'm unable to test but I hope sunitas sample data will yield no records using my query :)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources