Query , Help !

[billum]

I have a table which stores jobs.
Jobs has columns:
jbid
submitdate
enddate

dates stored as:

submitdate
2004-07-27 16:08:19.757
---
---

enddate
2004-07-27 16:10:19.757
--
2004-07-27 10:18:19.757

Some Jobs overlap.I need to display the jobs that are concurrently running when a particular date and time is given( some jobs may start at 10:20am and may end at 12:00pm on the same day,others for eg may start at 10:20 am and end at 10:00am the next day)

Can someone would help me with a query for this

Thanks
Bill

 

[SQLBill]

Maybe something like:

SELECT jbid
FROM jobtable
WHERE enddate >= GETDATE()
  AND submitdate <= GETDATE()

or maybe using the BETWEEN in the WHERE might be better:

WHERE GETDATE() BETWEEN submitdate AND enddate

-SQLBill

Posting advice: FAQ481-4875

 

[dbomrrsm]

If the jobs table is updated as and when jobs start and completes the submitdate and jbid would be populated for a job when it starts whilst the enddate would not be populated

SELECT jbid
FROM JOBSTABLE
WHERE submitdate IS NOT NULL
AND enddate IS NULL

DBomrrsm

 

[billum]

Thanks for the posts

I need to get a list as to which had already completed
Sorry for the miscommunication.
Like say where at, on july 25 12pm which were all concurrently running?

Thanks
Bill

 

[SQLBill]

Create a procedure like this:

CREATE PROCEDURE usp_jobchecker
  @mydate VARCHAR(19)
 AS
SELECT jbid
FROM jobtable
WHERE @mydate BETWEEN submitdate AND enddate

Then run it with EXEC usp_jobchecker '2004-07-25 12:00:00'

-SQLBill

 

[maswien]

The SQL for getting all the concurrently running jobs:

select distinct j1.* from jobs j1, jobs j2
where ( j1.submit_date between j2.submit_date and j2.end_date
or j1.end_date between j2.submit_date and j2.end_date )
and j1.job_id <> j2.job_id

 

[ESquared]

That's a bizarre query, mjia. I don't think he wanted a list of all jobs that have ever concurrently run!

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[billum]

Thank you very much for the posts.
If I want to get the jobs concurrently run between a given
time say I give 2 inputs date1 and date2 .Will adding the date condition to mjia's query server the purpose?

Thanks
Bill

 

[ESquared]

SQLBill's stuff will work fine for you. You just have to replace the GetDate() things with the correct times for the range you want.

-------------------------------------
A sacrifice is harder when no one knows you've made it.