Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Unanswered: Merge rows for datetime

    I have this table

    Code:
    user          function         datetime
    -------      -----------        -----------
    111                  1               2011-2-15 07:01:00.000
    111                  2               2011-2-15 15:01:00.000
    111                  1               2011-2-17 06:01:00.000
    111                  2               2011-2-17 14:01:00.000
    222                  1               2011-2-15 08:01:00.000
    222                  2               2011-2-15 12:01:00.000
    333                  1               2011-2-15 09:01:00.000
    333                  2               2011-2-15 18:01:00.000
    and i want it to be like this

    Code:
    user             datetime1                                datetime2
    -------         -------------                            --------------
    111           2011-2-15 07:01:00.000                2011-2-15 15:01:00.000
    111           2011-2-17 06:01:00.000                2011-2-17 14:01:00.000
    222           2011-2-15 08:01:00.000                2011-2-15 12:01:00.000         
    333           2011-2-15 09:01:00.000                2011-2-15 18:01:00.000
    using the function and user as basis for date combination.

    I hope you can help me....
    Thanks in advance!

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Something like this? I used left outer join just in case there is a user having function "1" but not 2". If these should not be included, change it to an inner join.
    Code:
    with NumberedRows as (
    	select
    		USER,
    		function,
    		datetime,
    		ROW_NUMBER() over (partition by user, function order by datetime asc) as row_no
    	from
    		YourTable
    )
    select
    	USER,
    	n1.datetime as datetime1,
    	n2.datetime as datetime2
    from
    	NumberedRows n1
    		left outer join
    	NumberedRows n2 on
    			n1.user = n2.user and
    			n1.row_no = n2.row_no and
    			n1.function = 1 and
    			n2.function = 2
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    WITH CTE AS
    (SELECT usr, 
    	funct, 
    	DaTime, 
    	ROW_NUMBER() OVER (PArtition by usr ORDER BY DaTime ASC) as RowNum
    FROM #DaTable
    )
    SELECT T1.usr, T1.DaTime as datetime1, T2.DaTime as datetime2
    FROM CTE as T1
    	INNER JOIN CTE as T2 on
    		T1.usr = T2.usr AND
    		T1.funct = T2.funct - 1 AND
    		T1.RowNum = T2.RowNum - 1
    This code will work correct when your data is correct (for any user: function only values 1 or 2, always a 2 present when there is a 1, always a 1 present when there is a 2, the datetime of any 2 is always larger then the datetime of a 1, ...). You should run some scripts first to verify that.

    About the names you used for the columns; do yourself a favour, don't use reserved words (user, function, datetime) for names of columns, tables, sp's, ... When you're really really really out of inspiration, append an underscore at the end, like "USER_"
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    "Great minds think alike, and so do we"

    Roac, sorry, I didn't saw you had already submitted a reply. When I opened this post, no one else had submitted a reply yet.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Roac, I ran your code. It doesn't give the result rvr707 was looking for. I think the LEFT OUTER JOIN creates extra records with Dateatime2 NULL.

    Code:
    DROP TABLE #DaTable
    CREATE TABLE #DaTable(
    	usr	INT NOT NULL,
    	funct	INT	NOT NULL
    		 CONSTRAINT cc_funct_ CHECK(funct in (1, 2)),
    	DaTime	datetime	NOT NULL
    )
    
    INSERT INTO #DaTable VALUES
    (111, 1, '2011-2-15 07:01:00.000'),
    (111, 2, '2011-2-15 15:01:00.000'),
    (111, 1, '2011-2-17 06:01:00.000'),
    (111, 2, '2011-2-17 14:01:00.000'),
    (222, 1, '2011-2-15 08:01:00.000'),
    (222, 2, '2011-2-15 12:01:00.000'),
    (333, 1, '2011-2-15 09:01:00.000'),
    (333, 2, '2011-2-15 18:01:00.000')
    
    SELECT * from #DaTable
    GO
    
    WITH CTE AS
    (SELECT usr, 
    	funct, 
    	DaTime, 
    	ROW_NUMBER() OVER (PArtition by usr ORDER BY DaTime ASC) as RowNum
    FROM #DaTable
    )
    --SELECT * FROM CTE
    SELECT T1.usr, T1.DaTime, T2.DaTime
    FROM CTE as T1
    	INNER JOIN CTE as T2 on
    		T1.usr = T2.usr AND
    		T1.funct = T2.funct - 1 AND
    		T1.RowNum = T2.RowNum - 1
    GO
    
    with NumberedRows as (
    	select
    		USR,
    		funct,
    		datime,
    		ROW_NUMBER() over (partition by usr, funct order by datime asc) as row_no
    	from
    		#DaTable
    )
    select
    	n1.usr,
    	n1.datime as datetime1,
    	n2.datime as datetime2
    from
    	NumberedRows n1
    		left outer join
    	NumberedRows n2 on
    			n1.usr = n2.usr and
    			n1.row_no = n2.row_no and
    			n1.funct = 1 and
    			n2.funct = 2
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2009
    Posts
    90
    THANKS FOR THAT! WIM AND roac!
    WHAT IF I WANT TO ADD ANOTHER FUNCTION?
    SAY 1,2 AND 3.
    WHAT WILL I DO?

  7. #7
    Join Date
    Sep 2009
    Posts
    90
    Yes it should be inner join

  8. #8
    Join Date
    Sep 2009
    Posts
    90
    THANKS FOR THAT! WIM AND roac!
    WHAT IF I WANT TO ADD ANOTHER FUNCTION?
    SAY 1,2 AND 3. BUT IT CAN ONLY BE A PAIR, SAY 1 AND 2 THEN 1 AND 3.
    WHAT WILL I DO?

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Take our solutions as a start and build on top of it.

    Show us that you are willing to invest time and effort in it. Post it on this forum when you're struck and there will be plenty of people here willing to help you further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Sep 2009
    Posts
    90
    ok Wim! Thanks a lot!

  11. #11
    Join Date
    Sep 2009
    Posts
    90
    I'm trying out this code for the case of multiple values on function column. not only 1 and 2 but also can be 1 and 4, or 1 and 3. But there something wrong with the value of 1 and 4 or 1 and 3 evertytime i execute it. The datetime 2 is right but datetime1 is wrong.

    Code:
    with NumberedRows as (
    	select
    		USER,
    		function,
    		datetime,
    		ROW_NUMBER() over (partition by user, function order by datetime asc) as row_no
    	from
    		YourTable
    )
    select
    	USER,
    	n1.datetime as datetime1,
    	n2.datetime as datetime2
    from
    	NumberedRows n1
    		left outer join
    	NumberedRows n2 on
    			n1.user = n2.user and
    			n1.row_no = n2.row_no and
    			((n1.function = 1 and
    			n2.function = 2)  OR
                             (n1.function = 1 and
    			n2.function = 4))
    Please help Me...
    Thanks!

  12. #12
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Something like this?

    Code:
    with NumberedRows as (
    	select
    		USER,
    		function,
    		datetime,
    		ROW_NUMBER() over (partition by user, case when function > 1 then 2 else 1 end order by datetime asc) as row_no
    	from
    		YourTable
    )
    select
    	USER,
    	n1.datetime as datetime1,
    	n2.datetime as datetime2
    from
    	NumberedRows n1
    		left outer join
    	NumberedRows n2 on
    			n1.user = n2.user and
    			n1.row_no = n2.row_no and
    			n1.function = 1 and
    			n2.function in (2,3,4)
    Last edited by roac; 07-06-11 at 03:17. Reason: Needed to add a case statement to the row_number function
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  13. #13
    Join Date
    Sep 2009
    Posts
    90
    same result. the combination 1 and 2 has no problem.. but when i make it 1 and 4, the data becomes incorrect.

    This is is actually an employee time in time out where function 1 is time in and function 2 is time out but there are cases when there are combination of 1 and 4 in case the employee stays overnight.


    Quote Originally Posted by roac View Post
    Something like this?

    Code:
    with NumberedRows as (
    	select
    		USER,
    		function,
    		datetime,
    		ROW_NUMBER() over (partition by user, case when function > 1 then 2 else 1 end order by datetime asc) as row_no
    	from
    		YourTable
    )
    select
    	USER,
    	n1.datetime as datetime1,
    	n2.datetime as datetime2
    from
    	NumberedRows n1
    		left outer join
    	NumberedRows n2 on
    			n1.user = n2.user and
    			n1.row_no = n2.row_no and
    			n1.function = 1 and
    			n2.function in (2,3,4)

  14. #14
    Join Date
    Sep 2009
    Posts
    90
    Sorry I forgot the function on partition by.
    Yes its now working. But when the date value on function4 is next day
    it doesn't show up. like for eample

    Code:
    user     function                datetime
    ----    ---------               --------
    111          1                  2011-2-15 07:01:00.000
    111          4                  2011-2-16 06:01:00.000
    Quote Originally Posted by roac View Post
    Something like this?

    Code:
    with NumberedRows as (
    	select
    		USER,
    		function,
    		datetime,
    		ROW_NUMBER() over (partition by user, case when function > 1 then 2 else 1 end order by datetime asc) as row_no
    	from
    		YourTable
    )
    select
    	USER,
    	n1.datetime as datetime1,
    	n2.datetime as datetime2
    from
    	NumberedRows n1
    		left outer join
    	NumberedRows n2 on
    			n1.user = n2.user and
    			n1.row_no = n2.row_no and
    			n1.function = 1 and
    			n2.function in (2,3,4)

  15. #15
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Had a bug in the code, n1.function = 1 should be in the where clause, not in the join condition. Check out the following code, still full of restricted words

    Code:
    use tempdb;
    go
    
    create table YourTable (
    	[user] int,
    	[function] int,
    	[datetime] datetime
    );
    
    insert into YourTable values (111,1,GETDATE()-1);
    insert into YourTable values (111,4,GETDATE());
    insert into YourTable values (111,1,GETDATE()+1);
    insert into YourTable values (111,2,GETDATE()+2);
    
    with NumberedRows as (
    	select
    		[USER],
    		[function],
    		[datetime],
    		ROW_NUMBER() over (partition by user, case when [function] > 1 then 2 else 1 end order by datetime asc) as row_no
    	from
    		YourTable
    )
    select
    	n1.[user],
    	n1.[datetime] as [datetime1],
    	n2.[datetime] as [datetime2]
    from
    	NumberedRows n1
    		left join
    	NumberedRows n2 on
    			n1.[user] = n2.[user] and
    			n1.row_no = n2.row_no and
    			n2.[function] in (2,3,4)
    where
    	n1.[function] = 1
    			
    drop table YourTable
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •