I'm probably just not seeing it clear but..

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)

I'm probably just not seeing it clear but..

Author

Topic

paul27uk
Starting Member

9 Posts

Posted - 2011-09-30 : 10:30:52

I have an issue at work. I need to basically be able to see a history of say employment for a person.

Each person can have multiple jobs that can run concurrently (part time for example). They could start and end at different times though.

Job1 |-------|
Job2 |--------|
Job3 [---------]

Question 1:

I want to be able to pass in the person ID and have it return the Earliest Job Start and how long that person stayed in work before a break. That should be (based on above) the Start of Job one and the end of job 2

Can it be done? If someone could help that would be great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 10:57:10

do you've a table where you're logging person details and their jobs?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-30 : 10:59:05

Post an actual sample of data and the desired output based on that sample.

PLEASE make life easier for us providing the sample in a format that we can simply execute in a query window.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

paul27uk
Starting Member

9 Posts

Posted - 2011-10-03 : 05:59:35

Like this?



IF OBJECT_ID('tempdb..#Jobs') IS NOT NULL DROP TABLE #Jobs

CREATE TABLE  #Jobs
(
	ClientProvisionFK	INT
	,ActionPlanFK		INT
	,ClaimFK			INT
	,JobStart			DATETIME
	,JobALD				DATETIME
)

INSERT INTO #Jobs (ClientProvisionFK,ActionPlanFK,ClaimFK,JobStart,JobALD)
VALUES
(1,9,1,'03-Oct-2011','06-oct-2011')
,(1,9,2,'07-oct-2011','26-oct-2011')
,(1,9,3,'09-oct-2011','27-oct-2011')
,(1,9,4,'31-oct-2011',NULL)

SELECT
		DENSE_RANK() OVER (PARTITION BY ClientProvisionFK ORDER BY JobStart ASC, CASE WHEN JobALD IS NULL THEN 1 ELSE 0 END ASC, JobALD ASC,ClaimFK ASC)AS [Ordering]
		,J.ClientProvisionFK
		,J.JobStart
		,J.JobALD
FROM
		#Jobs J
ORDER BY
		1

In the above example I would want the records ordered 1,2 & 3 to be merged and the output would be return Start Date 03/10/2011 and ALD 27/10/2011, plus a count of the number of records merged and the days in a job.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-03 : 10:52:28

>>Like this?
Almost - but you didn't provide your desired output. Should row: [ordering]=4 be returned as a separate row?
Is this what you're looking for?


select ClientProvisionFK
       ,min(JobStart) JobStart
       ,MAX(JobALD) JobALD
       ,COUNT(*) [mergecount]
from   #Jobs
where  jobStart is not null
and    JobALD is not null
group by ClientProvisionFK

OUTPUT:
ClientProvisionFK JobStart                JobALD                  mergecount
----------------- ----------------------- ----------------------- -----------
1                 2011-10-03 00:00:00.000 2011-10-27 00:00:00.000 3

Be One with the Optimizer
TG

paul27uk
Starting Member

9 Posts

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

Hi, thanks for the reply.

The 2nd/subsequent rows would all need evaluating and returning.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-03 : 11:52:42

You're welcome.
Again - please provide the expected/desired results based on your sample data. (like what I have in my "OUTPUT" - not a description of what the results should look like

) If you need more sample data to illustrate your requirements then provide that as well.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources