Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL to combine rows based on dates and other common factors Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 6, 2013 9:53 AM
Points: 9, Visits: 78
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

Post #1470177
Posted Wednesday, July 3, 2013 6:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 1,805, Visits: 5,870
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1470275
    Posted Thursday, July 4, 2013 11:21 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Yesterday @ 2:44 PM
    Points: 380, Visits: 1,654
    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/
    Post #1470487
    Posted Thursday, July 4, 2013 2:06 PM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Sunday, September 29, 2013 1:24 AM
    Points: 429, Visits: 1,721

    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

    Post #1470504
    Posted Thursday, July 18, 2013 3:59 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Sunday, October 6, 2013 9:53 AM
    Points: 9, Visits: 78
    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

    Post #1474957
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse