August 29, 2017 at 11:37 pm
sir ,
i am stuck in one problem . USING LAG() AND LEAD() FUNCTIONS
trying to filter the data as per the requirement .
Attaching the table script with data .
Link for downloading the table script.
https://uploadfiles.io/19aqz
Trying this Query
";With test1 as
(
Select EmployeeCode,AttendanceDate,
)
Select Distinct temp.EmployeeCode,temp.
CASE when (DATEDIFF(MINUTE,ISNULL(Lag(
--CASE when DATEDIFF(MINUTE,Lag(t1.
CASE when DATEDIFF(MINUTE,t1.
--CASE when DATEDIFF(MINUTE,Lead(t1.
from [G4SACS].[dbo].[
WHAT i am Trying is From a Single Table on the basis of EmployeeCode and AttendanceDate
i am FilterING IN's and Out's for Particular Employee on particular date
Conditions for IN's and Out's
1. IN>=Previous IN and Previous Out, Default i am using Current IN
2. IN<Out>Previous IN and Previous Out ,Default i am using Current Out
Problem is If i pass the EmployeeCode in the Where Clause then the results are coming fine for that particular EmployeeCode but if i am selecting ALL the records with this condition 1st record for each employeeCode is getting missed except the top one.
PLEASE CHECK AND REPLY IF THERE IS ANY POSSIBLE SOLUTION FROM YOUR SIDE.
August 30, 2017 at 1:31 am
1st of all, please share the DDL along with some sample data. secondly, your upload file is corrupted need to fix that.
Regarding query, there are few things which needed to be adjusted in your query
1. There is a CTE "Test", whats the use of this CTE it is currently irrelevant. Should be removed.
2. You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee.
Please share the sample data, DDL and your required output to give you a proper solution.
August 30, 2017 at 1:45 am
twin.devil - Wednesday, August 30, 2017 1:31 AM1st of all, please share the DDL along with some sample data. secondly, your upload file is corrupted need to fix that.Regarding query, there are few things which needed to be adjusted in your query
1. There is a CTE "Test", whats the use of this CTE it is currently irrelevant. Should be removed.
2. You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee.Please share the sample data, DDL and your required output to give you a proper solution.
trying "You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee. " thanks for the fast reply.
August 30, 2017 at 2:14 am
Formatted to be readable. The test1 CTE is used, I do recommend more meaningful names though
WITH test1
AS (SELECT EmployeeCode,
AttendanceDate,
ROW_NUMBER() OVER (PARTITION BY EmployeeCode, AttendanceDate ORDER BY EmployeeCode) AS rn
FROM [G4SACS].[dbo].[TempAttendancetableProcessed1]
)
SELECT DISTINCT
temp.EmployeeCode,
temp.AttendanceDate,
temp.INs,
temp.OUTs
FROM (SELECT t1.EmployeeCode,
t1.AttendanceDate,
t1.AttendanceDatetimeIN,
t1.AttendanceDatetimeOUT,
CASE WHEN (DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeIN) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeIN)),
t1.AttendanceDatetimeIN) > 0
OR t1.AttendanceDatetimeIN = LAG(t1.AttendanceDatetimeIN) OVER (ORDER BY t1.employeeCode))
AND DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeOUT) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeIN)),
t1.AttendanceDatetimeIN) > 0 THEN t1.AttendanceDatetimeIN
ELSE NULL
END AS INs,
CASE WHEN DATEDIFF(MINUTE, t1.AttendanceDatetimeIN, t1.AttendanceDatetimeOUT) > 0
AND DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeIN) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeOUT)),
t1.AttendanceDatetimeOUT) > 0
AND DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeOUT) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeOUT)),
t1.AttendanceDatetimeOUT) > 0 THEN t1.AttendanceDatetimeOUT
ELSE NULL
END AS OUTs
FROM [G4SACS].[dbo].[TempAttendancetableProcessed1] t1
JOIN test1 ON t1.EmployeeCode = test1.EmployeeCode
AND t1.AttendanceDate = test1.AttendanceDate
WHERE t1.EmployeeCode = 11
) AS temp
WHERE temp.INs IS NOT NULL;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2017 at 2:48 am
GilaMonster - Wednesday, August 30, 2017 2:14 AMFormatted to be readable. The test1 CTE is used, I do recommend more meaningful names though
WITH test1
AS (SELECT EmployeeCode,
AttendanceDate,
ROW_NUMBER() OVER (PARTITION BY EmployeeCode, AttendanceDate ORDER BY EmployeeCode) AS rn
FROM [G4SACS].[dbo].[TempAttendancetableProcessed1]
)
SELECT DISTINCT
temp.EmployeeCode,
temp.AttendanceDate,
temp.INs,
temp.OUTs
FROM (SELECT t1.EmployeeCode,
t1.AttendanceDate,
t1.AttendanceDatetimeIN,
t1.AttendanceDatetimeOUT,
CASE WHEN (DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeIN) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeIN)),
t1.AttendanceDatetimeIN) > 0
OR t1.AttendanceDatetimeIN = LAG(t1.AttendanceDatetimeIN) OVER (ORDER BY t1.employeeCode))
AND DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeOUT) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeIN)),
t1.AttendanceDatetimeIN) > 0 THEN t1.AttendanceDatetimeIN
ELSE NULL
END AS INs,CASE WHEN DATEDIFF(MINUTE, t1.AttendanceDatetimeIN, t1.AttendanceDatetimeOUT) > 0
AND DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeIN) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeOUT)),
t1.AttendanceDatetimeOUT) > 0
AND DATEDIFF(MINUTE,
ISNULL(LAG(t1.AttendanceDatetimeOUT) OVER (ORDER BY t1.EmployeeCode), DATEADD(MINUTE, -2, t1.AttendanceDatetimeOUT)),
t1.AttendanceDatetimeOUT) > 0 THEN t1.AttendanceDatetimeOUT
ELSE NULL
END AS OUTsFROM [G4SACS].[dbo].[TempAttendancetableProcessed1] t1
JOIN test1 ON t1.EmployeeCode = test1.EmployeeCode
AND t1.AttendanceDate = test1.AttendanceDate
WHERE t1.EmployeeCode = 11
) AS temp
WHERE temp.INs IS NOT NULL;
Thanks sir ,
IS there any editor you used for formatting ? or did it manually ?:rolleyes:
August 30, 2017 at 2:50 am
lokesh.sehgal - Wednesday, August 30, 2017 1:45 AMtwin.devil - Wednesday, August 30, 2017 1:31 AM1st of all, please share the DDL along with some sample data. secondly, your upload file is corrupted need to fix that.Regarding query, there are few things which needed to be adjusted in your query
1. There is a CTE "Test", whats the use of this CTE it is currently irrelevant. Should be removed.
2. You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee.Please share the sample data, DDL and your required output to give you a proper solution.
trying "You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee. " thanks for the fast reply.
and Partition by is giving the exact result as i need without CTE table . thankyou .:Whistling:
August 30, 2017 at 3:12 am
lokesh.sehgal - Wednesday, August 30, 2017 2:50 AMlokesh.sehgal - Wednesday, August 30, 2017 1:45 AMtwin.devil - Wednesday, August 30, 2017 1:31 AM1st of all, please share the DDL along with some sample data. secondly, your upload file is corrupted need to fix that.Regarding query, there are few things which needed to be adjusted in your query
1. There is a CTE "Test", whats the use of this CTE it is currently irrelevant. Should be removed.
2. You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee.Please share the sample data, DDL and your required output to give you a proper solution.
trying "You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee. " thanks for the fast reply.
and Partition by is giving the exact result as i need without CTE table . thankyou .:Whistling:
You're crossing the table to itself on employee and date (and not using rn) - doesn't this produce a multiple of relevant rows in your output?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 30, 2017 at 3:38 am
lokesh.sehgal - Wednesday, August 30, 2017 2:50 AMlokesh.sehgal - Wednesday, August 30, 2017 1:45 AMtwin.devil - Wednesday, August 30, 2017 1:31 AM1st of all, please share the DDL along with some sample data. secondly, your upload file is corrupted need to fix that.Regarding query, there are few things which needed to be adjusted in your query
1. There is a CTE "Test", whats the use of this CTE it is currently irrelevant. Should be removed.
2. You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee.Please share the sample data, DDL and your required output to give you a proper solution.
trying "You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee. " thanks for the fast reply.
and Partition by is giving the exact result as i need without CTE table . thankyou .:Whistling:
Just want to confirm if your issue have been resolved after applying the changes or not?
August 30, 2017 at 3:57 am
twin.devil - Wednesday, August 30, 2017 3:38 AMlokesh.sehgal - Wednesday, August 30, 2017 2:50 AMlokesh.sehgal - Wednesday, August 30, 2017 1:45 AMtwin.devil - Wednesday, August 30, 2017 1:31 AM1st of all, please share the DDL along with some sample data. secondly, your upload file is corrupted need to fix that.Regarding query, there are few things which needed to be adjusted in your query
1. There is a CTE "Test", whats the use of this CTE it is currently irrelevant. Should be removed.
2. You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee.Please share the sample data, DDL and your required output to give you a proper solution.
trying "You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee. " thanks for the fast reply.
and Partition by is giving the exact result as i need without CTE table . thankyou .:Whistling:
Just want to confirm if your issue have been resolved after applying the changes or not?
Resolved bro. thanks AlOT 🙂
August 30, 2017 at 3:59 am
ChrisM@Work - Wednesday, August 30, 2017 3:12 AMlokesh.sehgal - Wednesday, August 30, 2017 2:50 AMlokesh.sehgal - Wednesday, August 30, 2017 1:45 AMtwin.devil - Wednesday, August 30, 2017 1:31 AM1st of all, please share the DDL along with some sample data. secondly, your upload file is corrupted need to fix that.Regarding query, there are few things which needed to be adjusted in your query
1. There is a CTE "Test", whats the use of this CTE it is currently irrelevant. Should be removed.
2. You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee.Please share the sample data, DDL and your required output to give you a proper solution.
trying "You need to use the PARTITION BY in your LAG() function If you want to run for Each Employee. " thanks for the fast reply.
and Partition by is giving the exact result as i need without CTE table . thankyou .:Whistling:
You're crossing the table to itself on employee and date (and not using rn) - doesn't this produce a multiple of relevant rows in your output?
ya they were , but the conditioned i had applied were not showing the multiple records.
Now i have Removed the cte table and just directly running the query on one table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply