locked
SQL query to compare records RRS feed

  • Question

  • User-809753952 posted

    Please help with as SQL query. See my requirement below.

    I have a table with three columns. EmpNo, TaskCode & WorkDate.

    I need to create data for  4th column using the other 3 columns.

    See the output table:

    EmpNo TaskCode WorkDate Column4
    546 1 080118 0
    546 1 160118 0
    546 1 190118 0
    546 1 200118 1
    546 1 220318 0
    547 2 140318 0
    547 2 150318 1
    547 2 160318 1
    547 2 120418 0
    547 2 200418 0

    Here the records are first sorted after EmpNo, TaskCode and WorkDate.

    The current record is always compared to the next record. If the EmpNo, TaskCode & WorkDate are different from the next record values, then ColumnC = 0.

    If the EmpNo & TaskCode  are same as the next record, but the WorkDate  is the previous day , then ColumnC = 1.

    Tuesday, May 8, 2018 1:48 PM

Answers

  • User347430248 posted

    Hi mnmhemaj,

    Try to use another query without LAG function using join to get same result.

    Data in a Table looks like below.

    Query:

    WITH CTE AS (
    SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY p.Emp_no),
    p.Emp_no,p.Task_code,p.WorkDate
    FROM e_data1 p
    )
    
    SELECT
    CTE.Emp_no,
    CTE.Task_code,
    CTE.WorkDate,
    
    case prev.WorkDate 
    when DATEADD(day, -1,CTE.WorkDate)  then 1
    else 0
    end
    
    FROM CTE
    LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
    
    

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 10, 2018 1:22 AM

All replies

  • User347430248 posted

    Hi mnmhemaj,

    You had asked,"The current record is always compared to the next record. If the EmpNo, TaskCode & WorkDate are different from the next record values, then ColumnC = 0.If the EmpNo & TaskCode  are same as the next record, but the WorkDate  is the previous day , then ColumnC = 1."

    I don't know which datatype you had used for 'Workdate' field.

    Here, In this example I am using date data type for 'Workdate' field and as a demo I just compare 'Workdate' field.

    Further you can modify the query to match all the fields.

    Data in a table looks like below.

    Query:

    SELECT
    p.Emp_no,p.Task_code,p.WorkDate,
    
    case DATEADD(day, -1,p.WorkDate)
    when lag(p.WorkDate) OVER (ORDER BY p.Emp_no) then 1
    else 0
    end
    FROM e_data1 p
    

    Output:

    Reference:

    LAG (Transact-SQL)

    Regards

    Deepak

    Wednesday, May 9, 2018 1:43 AM
  • User-809753952 posted

    Hi Deepak

    Thanks for your reply.

    I can't use LAG function, as I am working with SQL server 2008.

    Wednesday, May 9, 2018 8:22 AM
  • User347430248 posted

    Hi mnmhemaj,

    Try to use another query without LAG function using join to get same result.

    Data in a Table looks like below.

    Query:

    WITH CTE AS (
    SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY p.Emp_no),
    p.Emp_no,p.Task_code,p.WorkDate
    FROM e_data1 p
    )
    
    SELECT
    CTE.Emp_no,
    CTE.Task_code,
    CTE.WorkDate,
    
    case prev.WorkDate 
    when DATEADD(day, -1,CTE.WorkDate)  then 1
    else 0
    end
    
    FROM CTE
    LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
    
    

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 10, 2018 1:22 AM