SQL to combine rows based on dates and other common factors

Question

Post reply

SQL to combine rows based on dates and other common factors

SQLStalker

Mr or Mrs. 500

Points: 557
More actions
July 3, 2013 at 11:40 am

#279944

Hi All,

We have a database for employees, jobs and work allocation. I am trying to find an efficient way to combine rows based on date and work load but excluding vacation allocations.

Here is an example of the current data set:

ID Employee Job StartDate EndDate Workload

1 John Doe HSBC 01/01/2013 31/12/2013 100

2 John Doe Vacation 17/06/2013 21/06/2013 100

3 John Doe HSBC 01/01/2014 31/12/2014 100

4 John Doe Vacation 19/08/2013 23/08/2013 100

5 John Doe Barclays 01/01/2014 31/01/2014 50

6 John Doe Barclays 01/01/2015 31/01/2015 50

7 John Doe Santander 06/01/2014 25/01/2014 50

8 John Doe Santander 05/03/2014 17/07/2014 80

9 John Doe Santander 05/03/2014 17/07/2014 50

10 John Doe Vacation 13/01/2014 17/01/2014 100

The final result should be as follows (bold indicates the changed rows excluding header). Note that the IDs are not important at this point and also the work allocation to the job 'Santander' are separate rows as one is at 50% workload and the other is 80. The rows should only be combined if the employee, job and workload is the same. 'Vacations' are not combined at all.

ID Employee Job StartDate EndDate Workload

1 John Doe HSBC 01/01/2013 31/12/2014 100

2 John Doe Vacation 17/06/2013 21/06/2013 100

4 John Doe Vacation 19/08/2013 23/08/2013 100

5 John Doe Barclays 01/01/2014 31/01/2015 50

7 John Doe Santander 06/01/2014 17/07/2014 50

8 John Doe Santander 05/03/2014 17/07/2014 80

10 John Doe Vacation 13/01/2014 17/01/2014 100

Here is the DDL which another forumite kindly provided for an earlier query. Any suggestions would be greatly appreciated.

set dateformat dmy
if OBJECT_ID('tempdb..#Something') is not null drop table #Something create table #Something ( ID int, Employee varchar(20), Job varchar(20), StartDate datetime, EndDate datetime, Workload int ) insert #Something select * from (Values (1, 'John Doe', 'HSBC', '01/01/2013', '31/12/2013', 100) ,(2, 'John Doe', 'Vacation', '17/06/2013', '21/06/2013', 100) ,(3, 'John Doe', 'HSBC', '01/01/2014', '31/12/2014', 100) ,(4, 'John Doe', 'Vacation', '19/08/2013', '23/08/2013', 100) ,(5, 'John Doe', 'Barclays', '01/01/2014', '31/01/2014', 50) ,(6, 'John Doe', 'Barclays', '01/01/2015', '31/01/2015', 50) ,(7, 'John Doe', 'Santander', '06/01/2014', '25/01/2014', 50) ,(8, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 80) ,(9, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 50) ,(10, 'John Doe', 'Vacation', '13/01/2014', '17/01/2014', 100) ) x(a,b,c,d,e,f) select *
from #Something
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

How about this



select min(ID) ID,

	Employee,

	Job,

	min(StartDate),

	max(EndDate),

	Workload

from #Something

where Job<>'Vacation'

group by 	Employee,

	Job,

	Workload

union all

select ID,

	Employee,

	Job,

	StartDate,

	EndDate,

	Workload

from #Something

where Job='Vacation'

order by ID

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

Here is another possible solution using cte's. While it makes seeing what the query does a bit easier, they are usually not as efficient as a solution without one!



;with cte as

(

   select ID,

     ROW_NUMBER() OVER(PARTITION by employee, job, [workload] order by ID) rowNum

     from #Something

     where Job <> 'Vacation'

),

cte1 as

(

  select ID,

     ROW_NUMBER() OVER(Partition by ID order by ID) rowNum

     from #Something

     where Job = 'Vacation'

)

select s.ID, s.Employee, s.job, s.StartDate, s.EndDate, s.workload

from #Something s

left outer join cte c on c.id = s.ID

left outer join cte1 n on n.ID = s.ID

where  n.RowNum > 0 or c.RowNum = 1

order by s.ID

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3



IF OBJECT_ID('tempdb..#Something') is not null

	DROP TABLE #Something

CREATE TABLE #Something

(

	ID int,

	Employee varchar(20),

	Job varchar(20),

	StartDate datetime,

	EndDate datetime,

	Workload int

)

INSERT #Something

SELECT *

FROM (VALUES

(1, 'John Doe', 'HSBC', '01/01/2013', '31/12/2013', 100)

,(2, 'John Doe', 'Vacation', '17/06/2013', '21/06/2013', 100)

,(3, 'John Doe', 'HSBC', '01/01/2014', '31/12/2014', 100)

,(4, 'John Doe', 'Vacation', '19/08/2013', '23/08/2013', 100)

,(5, 'John Doe', 'Barclays', '01/01/2014', '31/01/2014', 50)

,(6, 'John Doe', 'Barclays', '01/01/2015', '31/01/2015', 50)

,(7, 'John Doe', 'Santander', '06/01/2014', '25/01/2014', 50)

,(8, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 80)

,(9, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 50)

,(10, 'John Doe', 'Vacation', '13/01/2014', '17/01/2014', 100)

) x(a,b,c,d,e,f)



SELECT

    ROW_NUMBER() OVER (ORDER BY StartDate, EndDate) AS ID,

    r.*

FROM

    (

    SELECT DISTINCT

        Employee,

        Job,

        MIN(StartDate) OVER (PARTITION BY Employee, Job, Workload) AS StartDate,

        MIN(EndDate) OVER (PARTITION BY Employee, Job, Workload) AS EndDate,

        Workload

    FROM

        #Something

    WHERE

        Job <> 'vacation'

    ) r





ID	Employee	Job		StartDate			EndDate				Workload

1	John Doe	HSBC		2013-01-01 00:00:00.000		2013-12-31 00:00:00.000		100

2	John Doe	Barclays	2014-01-01 00:00:00.000		2014-01-31 00:00:00.000		50

3	John Doe	Santander	2014-01-06 00:00:00.000		2014-01-25 00:00:00.000		50

4	John Doe	Santander	2014-03-05 00:00:00.000		2014-07-17 00:00:00.000		80

SQLStalker Mr or Mrs. 500 Points: 557 More actions · Answer 4

Thank you all for your responses.

mister.magoo, your query appears to be giving the correct results.

Another question. Is there a way of merging rows only if they are separated by vacation? So the criteria is the rows must have the same employee, same job, same workload and separated by vacation.

The current data set is:

ID Employee Job StartDate EndDate Workload

1 John Doe HSBC 2013-08-01 2013-08-14 100

2 John Doe Vacation 2013-08-15 2013-08-20 100

3 John Doe HSBC 2013-08-21 2013-08-31 100

4 John Doe Vacation 2013-09-20 2013-09-26 100

5 John Doe HSBC 2013-09-27 2013-09-30 100

6 John Doe Barclays 2013-10-01 2013-10-10 50

7 John Doe Vacation 2013-10-11 2013-10-17 100

8 John Doe Barclays 2013-10-18 2013-10-24 50

9 John Doe Barclays 2013-10-26 2013-10-31 50

10 John Doe Santander 2013-11-01 2013-11-07 50

11 John Doe Vacation 2013-11-08 2013-11-15 100

12 John Doe Santander 2013-11-16 2013-11-24 50

13 John Doe Santander 2013-11-25 2013-11-30 80

The expected results (rows merged are in bold):

ID Employee Job StartDate EndDate Workload

1 John Doe HSBC 2013-08-01 2013-08-31 100

2 John Doe Vacation 2013-08-15 2013-08-20 100

4 John Doe Vacation 2013-09-20 2013-09-26 100

5 John Doe HSBC 2013-09-27 2013-09-30 100

6 John Doe Barclays 2013-10-01 2013-10-24 50

7 John Doe Vacation 2013-10-11 2013-10-17 100

9 John Doe Barclays 2013-10-26 2013-10-31 50

10 John Doe Santander 2013-11-01 2013-11-24 50

11 John Doe Vacation 2013-11-08 2013-11-15 100

13 John Doe Santander 2013-11-25 2013-11-30 80

Please find below the DDL. Any help is appreciated.

set dateformat dmy



if OBJECT_ID('tempdb..#Something') is not null

	drop table #Something

create table #Something

(

	ID int,

	Employee varchar(20),

	Job varchar(20),

	StartDate datetime,

	EndDate datetime,

	[Workload] int

)

insert #Something

select *

from (Values

(1, 'John Doe', 'HSBC', '01/08/2013', '14/08/2013', 100)

,(2, 'John Doe', 'Vacation', '15/08/2013', '20/08/2013', 100)

,(3, 'John Doe', 'HSBC', '21/08/2013', '31/08/2013', 100)

,(4, 'John Doe', 'Vacation', '20/09/2013', '26/09/2013', 100)

,(5, 'John Doe', 'HSBC', '27/09/2013', '30/09/2013', 100)

,(6, 'John Doe', 'Barclays', '01/10/2013', '10/10/2013', 50)

,(7, 'John Doe', 'Vacation', '11/10/2013', '17/10/2013', 100)

,(8, 'John Doe', 'Barclays', '18/10/2013', '24/10/2013', 50)

,(9, 'John Doe', 'Barclays', '26/10/2013', '31/10/2013', 50)

,(10, 'John Doe', 'Santander', '01/11/2013', '07/11/2013', 50)

,(11, 'John Doe', 'Vacation', '08/11/2013', '15/11/2013', 100)

,(12, 'John Doe', 'Santander', '16/11/2013', '24/11/2013', 50)

,(13, 'John Doe', 'Santander', '25/11/2013', '30/11/2013', 80)

) x(a,b,c,d,e,f)

select *

from #Something

order by StartDate