Selecting second to last record
-
I have applicants on my database who have a list of temporary jobs they have been assigned to.
Using SELECT min(tempbookings.start_date) I can find when they first start and using SELECT max(tempbookings.end_date) I can find when they last worked.
My problem is that I need to identify when someone is starting a job four weeks after they finished the previous job. I need to be able to select the max(tempbookings.start_date) and compare it to the tempbookings.end_date for the job before the last one (max(tempbookings.end_date) -1!!!
Any ideas guys?
-
Try this:
select (max(tempbookings.end_date) where tempbookings.end_date Not In (select (max(tempbookings.end_date))
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Hi! Helen, Jim...
Jim's solution will work (once rewrited in a correct way) but if you have more tha a few applicants, would be very expensive...(see below)
try this... it would work with a better performance...
SELECT
a.idusr, a.st_date, MAX(b.end_date)
FROM
your_table_name AS a
INNER JOIN your_table_name AS b
ON a.st_date > b.end_date
GROUP BY a.idusr, a.st_date
HAVING datediff(dd,MAX(b.end_date),a.st_date) > 10 -- your time out in days
To test this I created a temp table, with a IDUsr key field. You must replace this with your own mandatory field for your applicants...(NOTE THAT THE TABLE (whatever its name is) MAKES AN INNER JOIN WITH ITSELF)
Explanation about performance: the select on the where statement would be evaluated for every record in the table, so, if you have 1 million records, the (select max(...)) statement would execute a million times. Replacing it by an inner join would match same, but executing just once.
Nicolas Donadio
SW Developer
-
Just for tracing purposes... !!!
-
I have been struggling with the replies recieved so far and getting nowhere. Maybe I should explain better.
Table name Tempbookings
Fields, app_id, start_date, End_date
For each app_id I need to find the max(start_date) no problems there
I also need to find the second to last end_date for each app_id, and that is where I am struggling.
My results set needs to include app_id, max(start_date) and 'second to last' end_date.
Any more bright ideas out there?
-
Temp table solution:
select app_id,current_start=max(start_date) into #a
from tempbookings
group by app_id
select a.app_id,current_start,last_start = max(start_date) into #b
from #a a, tempbookings b
where a.app_id = b.app_id
and start_date < current_start
group by a.app_id
select * from #b where datedif(week,last_start,current_start) = 4
-
Interpretation:
CREATE TABLE dbo.Tempbookings
( app_id int, start_date datetime, End_date datetime)
INSERT INTO dbo.Tempbookings VALUES(1,'3/1/2005','3/7/2005')
INSERT INTO dbo.Tempbookings VALUES(1,'4/1/2005','4/7/2005')
INSERT INTO dbo.Tempbookings VALUES(1,'5/1/2005','5/7/2005')
INSERT INTO dbo.Tempbookings VALUES(1,'6/1/2005','6/7/2005')
INSERT INTO dbo.Tempbookings VALUES(1,'7/1/2005','7/7/2005')
INSERT INTO dbo.Tempbookings VALUES(1,'8/1/2005','8/7/2005')
INSERT INTO dbo.Tempbookings VALUES(1,'9/1/2005','9/7/2005')
INSERT INTO dbo.Tempbookings VALUES(2,'3/1/2005','3/7/2005')
INSERT INTO dbo.Tempbookings VALUES(2,'4/1/2005','4/7/2005')
INSERT INTO dbo.Tempbookings VALUES(2,'5/1/2005','5/7/2005')
INSERT INTO dbo.Tempbookings VALUES(2,'6/1/2005','6/7/2005')
A little ugly as a single query and not without some restrictions, but it does give the required results:
SELECT ms.app_id, ms.start_date, me.end_date
FROM ( SELECT app_id, max(start_date) AS start_date
FROM tempbookings
GROUP BY app_id
) AS ms
INNER JOIN ( SELECT te.app_id, max(te.end_date) AS end_date
FROM tempbookings te
, ( SELECT m1s.app_id, t.end_date
FROM ( SELECT m1.app_id, max(m1.start_date) AS start_date
FROM tempbookings m1
GROUP BY m1.app_id
) AS m1s
INNER JOIN dbo.Tempbookings t
ON t.app_id = m1s.app_id
AND t.start_date = m1s.start_date
) AS me
WHERE te.app_id = me.app_id
AND te.end_date < me.end_date
GROUP BY te.app_id
) AS me
ON me.app_id = ms.app_id
Results:
app_id start_date end_date
1 9/1/2005 8/7/2005
2 6/1/2005 5/7/2005
Beware of results when:
- one app_id can have multiple records with the same start_date and/or end_date
- one app_id has only one record
- very large quantites of data are queried - look at the explain
-
It might be just me, but I'm not sure what you're trying to do. Can you provide the information mentioned here?
http://www.aspfaq.com/etiquette.asp?id=5006
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry Frank next time I shall try and follow the etiquette.
I adapted rttompkins query by creating a view from his first query and then combining this with the tempbookings table and I have now got what I want.
Thanks, as ever for everyone's help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply