Real-Time-Emp-Attendance-Log-Management-SQL-Server-Framework4.8/TextFile1.txt at master · shrikantbdarekar/Real-Time-Emp-Attendance-Log-Management-SQL-Server-Framework4.8 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
SELECT BL1.BMEmployeeId, BL1.PunchTime, BL1.InOut
FROM BiometricLogs BL1
JOIN BiometricLogs BL2
    ON BL1.BMEmployeeId = BL2.BMEmployeeId
    AND BL1.LogId <> BL2.LogId
    AND ABS(DATEDIFF(MINUTE, BL1.PunchTime, BL2.PunchTime)) <= 15
	AND BL1.InOut=''
ORDER BY BL1.BMEmployeeId, BL1.PunchTime;
########################
WITH DuplicateLogs AS (
    SELECT
        BL1.LogId,
        BL1.BMEmployeeId,
        BL1.PunchTime,
        BL1.InOut,
        ROW_NUMBER() OVER (
            PARTITION BY BL1.BMEmployeeId, CONVERT(DATE, BL1.PunchTime) -- Partition by Employee & Date
            ORDER BY
                CASE
                    WHEN FORMAT(BL1.PunchTime, 'tt') = 'AM' THEN BL1.PunchTime -- Keep earliest in AM
                    ELSE BL1.PunchTime DESC -- Keep latest in PM
                END
        ) AS RowNum
    FROM BiometricLog BL1
    JOIN BiometricLog BL2
        ON BL1.BMEmployeeId = BL2.BMEmployeeId
        AND BL1.LogId <> BL2.LogId
        AND ABS(DATEDIFF(MINUTE, BL1.PunchTime, BL2.PunchTime)) <= 15
)
DELETE FROM BiometricLog
WHERE LogId IN (SELECT LogId FROM DuplicateLogs WHERE RowNum > 1);
########################################
WITH DuplicateLogs AS (
    SELECT
        BL1.LogId,
        BL1.BMEmployeeId,
        BL1.PunchTime,
        BL1.InOut,
        ROW_NUMBER() OVER (
            PARTITION BY BL1.BMEmployeeId, CONVERT(DATE, BL1.PunchTime) -- Partition by Employee & Date
            ORDER BY
                CASE
                    WHEN FORMAT(BL1.PunchTime, 'tt') = 'AM' THEN BL1.PunchTime -- Keep earliest in AM
                    ELSE BL1.PunchTime DESC -- Keep latest in PM
                END
        ) AS RowNum
    FROM BiometricLogs BL1
    JOIN BiometricLogs BL2
        ON BL1.BMEmployeeId = BL2.BMEmployeeId
        AND BL1.LogId <> BL2.LogId
        AND ABS(DATEDIFF(MINUTE, BL1.PunchTime, BL2.PunchTime)) <= 5
)
INSERT INTO DeleteLogs (LogId, BMEmployeeId, PunchTime, InOut)
SELECT LogId, BMEmployeeId, PunchTime, InOut FROM DuplicateLogs WHERE RowNum > 1;

DELETE FROM BiometricLogs WHERE LogId IN (SELECT LogId FROM DeleteLogs);
#######################################################


dbX9Y7AB23T4Z2P6W5V
dbX9Y7ABSCS
Password@1234#$

"Data Source=wish.grabweb.in,5422;Initial Catalog=dbX9Y7AB23T4Z2P6W5V;User ID=dbX9Y7ABSCS;Password=Password@1234#$;Trust Server Certificate=True",