Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    20

    Unanswered: Inserting missing values

    Hi all,

    i have data like...

    userid startdate in_out
    1 2011-11-20 9.30 1
    1 2011-11-20 10.30 0
    1 2011-11-20 12.30 0
    1 2011-11-20 12.45 1
    1 2011-11-20 6.30 0


    See 1-Entry 0-exit for 2011-11-20 12.30 is exit but entry time of that exit is missing....
    Can i insert same exit value for entry????


    Please replyyyyyyyyyyyyyyyyy its urgent

  2. #2
    Join Date
    Nov 2011
    Posts
    2

    Post generate the missing rows

    Code:
    select T_out.userid, T_out.startdate, 1 -- in line generate from actuals
    from T as T_out
    where in_out = 0 -- exit
       and not exists( select 'x' 
                            from T as T_in
    		        where T_in.userid = T_out.userid
    			    and T_in.startdate = T_out.startdate
    			    and T_in.in_out = 1 )
    UNION ALL
    select *
    from T

  3. #3
    Join Date
    Nov 2011
    Posts
    20
    Thanks for the help...............

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I haven't tested it, but I don't think Ciceto's code is correct. The and T_in.startdate = T_out.startdate part is not correct.

    I have tried something, but it starts to fail when many unmatched in's or out's occur one after another.
    It subtracts 1 second from the Out StartTime, when it inserts an In before an unmatched Out.
    It adds 1 second to the In StartTime, when it inserts an Out after an unmatched In.
    Out.
    Code:
    DROP TABLE #DaTable
    CREATE TABLE #DaTable(
    	userId	Int	NOT NULL,
    	startDate	Datetime	NOT NULL,
    	in_out	int	NOT NULL
    		CONSTRAINT cc_in_out CHECK (in_out BETWEEN 0 AND 1)
    )
    
    INSERT INTO #DaTable(userid, startdate, in_out) VALUES
    (1, '2011-11-20 9:30', 1),
    (1, '2011-11-20 10:30', 0),
    (1, '2011-11-20 12:30', 0),
    (1, '2011-11-20 12:45', 1),
    (1, '2011-11-20 16:30', 0),
    (1, '2011-11-20 17:35', 0),
    (1, '2011-11-20 18:05', 0),
    (1, '2011-11-20 19:30', 1),
    (1, '2011-11-20 20:35', 1),
    (1, '2011-11-20 21:05', 1)
    
    ;WITH CTE AS
    (SELECT #DaTable.userId, 
    	#DaTable.startDate, 
    	#DaTable.in_out,
    	ROW_NUMBER() OVER (ORDER BY #DaTable.userId, #DaTable.startDate, #DaTable.in_out) as RowNum
    FROM #DaTable
    )
    SELECT * 
    from CTE
    
    ;WITH CTE AS
    (SELECT #DaTable.userId, 
    	#DaTable.startDate, 
    	#DaTable.in_out,
    	ROW_NUMBER() OVER (ORDER BY #DaTable.userId, #DaTable.startDate, #DaTable.in_out) as RowNum
    FROM #DaTable
    )
    SELECT T1.userId, 
    	CASE WHEN T1.in_out = 1
    		THEN DATEADD(s, 1, T1.startdate) 
    		ELSE DATEADD(s, -1, T1.startdate)
    	END 
    	AS startdate, 
    	ABS(T1.in_out - 1) as in_out -- generate 0 from 1 or 1 from 0
    from CTE as T1
    	INNER JOIN CTE as T2 ON
    		T1.userId = T2.userId AND
    		T1.RowNum = T2.RowNum + 1 AND
    		T1.in_out = T2.in_out
    UNION ALL
    SELECT userId, 
    	startdate, 
    	in_out
    from CTE
    ORDER BY 1, 2, 3
    Perhaps you can debug it further, or it may give you a clue on how to solve it yourself.

    Now you have two queries that give you a wrong answer. I don't know if my contribution really helped.
    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

Posting Permissions

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