SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL to combine rows based on dates and other common factors


SQL to combine rows based on dates and other common factors

Author
Message
SQLStalker
SQLStalker
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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


mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4352 Visits: 7865
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);




  • 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

  • LinksUp
    LinksUp
    Ten Centuries
    Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

    Group: General Forum Members
    Points: 1049 Visits: 4457
    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
    Steven Willis
    SSC Eights!
    SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

    Group: General Forum Members
    Points: 871 Visits: 1721


    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
    SQLStalker
    Valued Member
    Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

    Group: General Forum Members
    Points: 55 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


    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search