Last post May 10, 2018 01:22 AM by Deepak Panchal
May 08, 2018 01:48 PM|mnmhemaj|LINK
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:
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.
May 09, 2018 01:43 AM|Deepak Panchal|LINK
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.
case DATEADD(day, -1,p.WorkDate)
when lag(p.WorkDate) OVER (ORDER BY p.Emp_no) then 1
FROM e_data1 p
May 09, 2018 08:22 AM|mnmhemaj|LINK
Thanks for your reply.
I can't use LAG function, as I am working with SQL server 2008.
May 10, 2018 01:22 AM|Deepak Panchal|LINK
Try to use another query without LAG function using join to get same result.
Data in a Table looks like below.
WITH CTE AS (
rownum = ROW_NUMBER() OVER (ORDER BY p.Emp_no),
FROM e_data1 p
when DATEADD(day, -1,CTE.WorkDate) then 1
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1