locked
Sql Query To Count Present and Absent Days RRS feed

  • Question

  • User-698989805 posted

    Hello everyone! I have two tables: attendance and details table. The attendance table contain datetime and userID column that means from the datetime I have to count the present and absent days of a user. Unfortunately I've lost the earlier query that was written. I would expect some suggestions to make it done again.

    Note: I could count the present days of the user but when there is no record exist for the user on any specific date, just stuck on it.

    Sunday, March 13, 2016 5:34 AM

Answers

  • User-219423983 posted

    Hi TechView,

    You could have a look at below complete code.  As you have get the “present” count, you could just use “total” day count minus the “present” count to get the “absent” count.

    declare @PunchRecords table(empid int, PunchDate datetime)
    
    insert into @PunchRecords values (1, '2016-03-02'),(1,'2016-03-03')
    								,(1,'2016-03-04'),(1,'2016-03-05')
    								,(2,'2016-03-02'),(2,'2016-03-03')
    								,(3,'2016-03-04'),(3,'2016-03-05')
    declare @empDetails table (empid int, empname varchar(20))
    insert into @empDetails values (1, 'AT-2015'),(2, 'AT-2016')
    
    
    SELECT DISTINCT 
    (SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    FROM @PunchRecords k WHERE k.empid = 2) present,
    (
    	(select datediff(day,'2016-03-02','2016-03-05')) 
    	- 
    	(SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    		FROM @PunchRecords k WHERE k.empid = 2)
    )
    as absent
    FROM @empDetails m 
    CROSS JOIN @PunchRecords l 
    LEFT JOIN @PunchRecords k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 2 AND l.Punchdate BETWEEN '2016-03-02' AND '2016-03-05'
    

    The result is below.

    present absent
    2 1

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2016 9:13 AM
  • User-219423983 posted

    Hi TechView,

    Currently I get the above result and this is not the actual result as the database has the attendance up to '2016-03-05'. So if I search with the date range '2016-03-02' to '2016-03-10', it will just give the following output:

    As the latest record data in your record is “2016-03-05”, why you request the record to “2016-03-10”? 

    Is there any way to check the DATEDIFF(day, '2016-03-02', '2016-03-05') with the table to make sure the datetime is saved into it?? It is getting little bit weird. Thanks again for your time and I would be looking forward for your suggestion.

    The “2016-03-05” could be a parameter and you could set any value according to your need. But, I suggested that you’d better first get the latest record date and then compare with the incoming parameter. If the parameter is larger than the latest record date, please set the incoming parameter equals latest record date.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 18, 2016 3:28 AM
  • User77042963 posted

    Use a calendar table to make this type of question a lot easy. Here I use Weibo's sample tables to show you a solution with a on the fly date ranges.

    Here it is:

    declare @PunchRecords table(empid int, PunchDate datetime)
    
    insert into @PunchRecords values (1, '2016-03-02'),(1,'2016-03-03')
    								,(1,'2016-03-04'),(1,'2016-03-05')
    								,(2,'2016-03-02'),(2,'2016-03-03')
    								,(3,'2016-03-04'),(3,'2016-03-05')
    declare @empDetails table (empid int, empname varchar(20))
    insert into @empDetails values (1, 'AT-2015'),(2, 'AT-2016')
    
    
    
    
    ---Search parameters
    declare @minDate date ='2016-03-02' 
    declare @maxDate date = '2016-03-10' 
    
    
    
    
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    ,dateRange as (
    Select Dateadd(day,n-1,@minDate) dt from Nums
    Where Dateadd(day,n-1,@minDate)<= (select max(PunchDate) from @PunchRecords)
    --Use the most recent date from @PunchRecords table
    
    )
    
    ,cteEmpDates as (
    Select dt,empid,empname 
    from dateRange , @empDetails )
    
    Select m.empid,m.empname , SUM(Case When k.empid is not null then 1 else 0 end) present,
    SUM(Case When k.empid is  null then 1 else 0 end) absent
    from cteEmpDates m 
    LEFT JOIN @PunchRecords k ON k.empid = m.empid and m.dt=k.PunchDate
    Group by m.empid,m.empname 
    Order by m.empid 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 18, 2016 2:37 PM

All replies

  • User1633621018 posted

    Hi TechView,

    If i am understanding your query correct then you are counting nulls as absent and dates with present day for users. if that is the case, then below is the solution

    SELECT 
    	userid,
    	SUM(CASE WHEN MyDate IS NOT NULL THEN 1 ELSE 0 END) [Present],
    	SUM(CASE WHEN MyDate IS NULL THEN 1 ELSE 0 END) [Absent] 
    FROM [table]
    GROUP BY UserId

    image

    Sunday, March 13, 2016 11:51 AM
  • User-698989805 posted
    Thanks PawanPal. You got it almost done. I hope, it needs to be modified a little. Let me give you an example below with the table scheme:

    empid - Punchdate
    1 - 2016-03-02
    1 - 2016-03-03
    1 - 2016-03-04
    1 - 2016-03-05
    2 - 2016-03-02
    2 - 2016-03-03

    See in the above sample, empid 1 has attendance of 2016-03-02 to 2016-03-05 and empid has attendance of 2 days only. If I search the attendance of empid 1 from 2016-03-02 to 2016-03-05, then it will return all the data as present along with date. Similarly, if I search for empid 2 with the date range, it will return only 2 days as present and others as absent. It was done using Temp_Table earlier (One of our colleague did. Right now he is away for a while). I would be looking forward for an idea or solution. Thanks again.
    Sunday, March 13, 2016 4:20 PM
  • User1633621018 posted

    Hi,

    Why would you need temp table for that, If i am getting your problem? Just add where clause and it would work 

    SELECT 
    	userid,
    	SUM(CASE WHEN MyDate IS NOT NULL THEN 1 ELSE 0 END) [Present],
    	SUM(CASE WHEN MyDate IS NULL THEN 1 ELSE 0 END) [Absent] 
    FROM [table]
    WHERE ID = 2 AND DATE BETWEEN @startdate AND @enddate
    GROUP BY UserId

    Sunday, March 13, 2016 6:46 PM
  • User-698989805 posted

    Thanks again PawanPal. But the query will not work. Let me share a scenario and it will make everything clear. I am sharing two images that was supposed to be shared earlier. 1st one is the data stored and the 2nd is the required output: (Kindly check the images)

    http://h28i.imgup.net/Demo523d.png

    http://f80i.imgup.net/output9495.png - Required Output

    We use finger-print attendance device and it saves the data with datetime I mean the data for any specific date with no additional column like 'P' or 'A'. If any user is absent on the date '2016-03-04', that means there is totally no data stored for the user. Suppose, the user or employee ID 2 is present from '2016-03-02' to '2016-03-03', then it will be saved with datetime. So, if I search the attendance of the employee from '2016-03-02' to '2016-03-05', then it will return present as well the absent status and total present/absent days within the date range. I've provided the required output in the 2nd image. If possible, let me know if it could be done and how with Sql query though it seems little bit tough?? Thanks for your time and I appreciate it.

    Monday, March 14, 2016 3:36 PM
  • User-219423983 posted

    Hi TechView,

    I have created a demo as below code and you could have a look. You could modify it as the stored procedure and set the “@dt1”,”@dt2” and “@queryEmpId” as the parameters.

    declare @PunchRecords table(empid int, PunchDate datetime)
    
    insert into @PunchRecords values (1, '2016-03-02'),(1,'2016-03-03')
    								,(1,'2016-03-04'),(1,'2016-03-05')
    								,(2,'2016-03-02'),(2,'2016-03-03')
    								,(3,'2016-03-04'),(3,'2016-03-05')
    select * from @PunchRecords
    
    
    
    declare @dt1 datetime = '2016-03-02'
    declare @dt2 datetime = '2016-03-05'
    declare @queryEmpId int =2
    
    declare @result table (empid int, PunchDate datetime, status varchar(2))
    
    while @dt1 <=@dt2
    begin
    	declare @PunDate datetime
    	select @PunDate = PunchDate
    	from @PunchRecords where empid = @queryEmpId and PunchDate = @dt1
    
    	if @PunDate is null
    		begin
    			insert into @result values (@queryEmpId, @dt1, 'A')
    		end
    	else
    		begin
    			insert into @result values (@queryEmpId, @dt1, 'P')
    		end
    	
    	set @dt1 = DATEADD(DAY,1,@dt1)
    	set @PunDate = null
    end 
    
    select * from @result
    

    The result is below.

    empid	PunchDate	status
    2	2016-03-02 00:00:00.000	P
    2	2016-03-03 00:00:00.000	P
    2	2016-03-04 00:00:00.000	A
    2	2016-03-05 00:00:00.000	A
    

    Best Regards,

    Weibo Zhang

    Tuesday, March 15, 2016 5:35 AM
  • User-698989805 posted

    Hello Weibo Zhang! Thanks for the solution. I've solved the problem almost similar to yours. Here is the following that I've done:

    SELECT DISTINCT m.empid, m.empname, COALESCE(k.Punchdate, l.Punchdate) AS Date, 
    (CASE WHEN k.Punchdate IS NULL THEN 'A'
    ELSE 'P' 
    END) AS attendance
    FROM empDetails m 
    CROSS JOIN punchDetails l 
    LEFT JOIN punchDetails k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 3 AND l.Punchdate BETWEEN '2016-03-15' AND '2016-03-18'

    The above query gives the exact output that you've provided. Just one more thing is required. I want to get the total present and absent days of a specific user and the output should be like the below within the date range  from 2016-03-15 to 2016-03-18:

    empid - empname - present - absent
      2   - AT-2016 -   3     -   2

    What I've tried is to count the present status 'P' and 'A' of the user from the above query. It gives the exact output for the present status but returns nothing for absent status. Here is the query I tried:

    SELECT DISTINCT 
    (SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    FROM punchDetails k WHERE k.empid = 2) present,
    (SELECT COUNT(CASE WHEN k.Punchdate IS NULL THEN 'A' END) 
    FROM punchDetails k WHERE k.empid = 2) absent
    FROM empDetails m 
    CROSS JOIN punchDetails l 
    LEFT JOIN punchDetails k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 2 AND l.Punchdate BETWEEN '2016-03-15' AND '2016-03-18'

    And it gives the below output:

    empid - empname - present - absent
      2   - AT-2016 -   3     -   0

     Lastly, I've tried to make the queries into single one using UNION ALL as the following:

    SELECT DISTINCT m.empid, m.empname, COALESCE(k.Punchdate, l.Punchdate) AS Date, 
    (CASE WHEN k.Punchdate IS NULL THEN 'A'
    ELSE 'P' 
    END) AS attendance
    FROM empDetails m 
    CROSS JOIN punchDetails l 
    LEFT JOIN punchDetails k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 2 AND l.Punchdate BETWEEN '2016-03-15' AND '2016-03-20'
    
    UNION ALL
    
    SELECT DISTINCT 
    (SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    FROM punchDetails k WHERE k.empid = 2) present,
    (SELECT COUNT(CASE WHEN k.Punchdate IS NULL THEN 'A' END) 
    FROM punchDetails k WHERE k.empid = 2) absent
    FROM empDetails m 
    CROSS JOIN punchDetails l 
    LEFT JOIN punchDetails k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 2 AND l.Punchdate BETWEEN '2016-03-15' AND '2016-03-20'

    I just want to show total present/absent days showing the individual attendance of the user at the end in the same query. Let me know if it could be done and right now I got this message 'All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists'. Thanks again for your time.

    Tuesday, March 15, 2016 2:45 PM
  • User-219423983 posted

    Hi TechView,

    In your code, the first part returns four columns, “empid”,”empname”,”Date” and “attendance”. But, your second part just returns two columns, “present” and “absent”. According to the following link you could know that there are two rules for combining the result sets of two queries by using UNION.

    • The number and the order of the      columns must be the same in all queries.
    • The data types must be compatible.

    So, when you union the two parts, it would happen errors.

    https://msdn.microsoft.com/en-us/library/ms180026.aspx

    Best Regards,

    Weibo Zhang

    Wednesday, March 16, 2016 2:33 AM
  • User-698989805 posted

    Hello Weibo Zhang! I got it. I knew about it but just confirming if it could be solved in any way. By the way, the following query does not get me the expected output:

    SELECT DISTINCT 
    (SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    FROM punchDetails k WHERE k.empid = 2) present,
    (SELECT COUNT(CASE WHEN k.Punchdate IS NULL THEN 'A' END) 
    FROM punchDetails k WHERE k.empid = 2) absent
    FROM empDetails m 
    CROSS JOIN punchDetails l 
    LEFT JOIN punchDetails k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 2 AND l.Punchdate BETWEEN '2016-03-15' AND '2016-03-20'

    What I get is the following:

    empid - empname - present - absent
      2   - AT-2016 -   3     -   0

    But I am trying to get it done as the following:

    empid - empname - present - absent
      2   - AT-2016 -   3     -   2

    As the employee ID 2 is absent for two days within the date range, it should return the 2nd sample output and the table schema is the same that I provided earlier in the image links. Could you please check it out as it is returning only the present status numbers?? Thanks again.  

    Wednesday, March 16, 2016 4:20 AM
  • User-219423983 posted

    Hi TechView,

    You could have a look at below complete code.  As you have get the “present” count, you could just use “total” day count minus the “present” count to get the “absent” count.

    declare @PunchRecords table(empid int, PunchDate datetime)
    
    insert into @PunchRecords values (1, '2016-03-02'),(1,'2016-03-03')
    								,(1,'2016-03-04'),(1,'2016-03-05')
    								,(2,'2016-03-02'),(2,'2016-03-03')
    								,(3,'2016-03-04'),(3,'2016-03-05')
    declare @empDetails table (empid int, empname varchar(20))
    insert into @empDetails values (1, 'AT-2015'),(2, 'AT-2016')
    
    
    SELECT DISTINCT 
    (SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    FROM @PunchRecords k WHERE k.empid = 2) present,
    (
    	(select datediff(day,'2016-03-02','2016-03-05')) 
    	- 
    	(SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    		FROM @PunchRecords k WHERE k.empid = 2)
    )
    as absent
    FROM @empDetails m 
    CROSS JOIN @PunchRecords l 
    LEFT JOIN @PunchRecords k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 2 AND l.Punchdate BETWEEN '2016-03-02' AND '2016-03-05'
    

    The result is below.

    present absent
    2 1

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2016 9:13 AM
  • User-698989805 posted

    Hello Weibo Zhang! Thanks for your effort. It is working but it needs one more to be fixed. See this 'DATEDIFF(day, '2016-03-02', '2016-03-05')' as the dates are in the database (we can say this is the last stored datetime). What about if we write 'DATEDIFF(day, '2016-03-02', '2016-03-10')' (datetime is saved up to '2016-03-05')?? I can manage this one for the individual report of a user but when it comes to total I mean totaling the present/absent days, the query only should count up to '2016-03-05'. Let me give you an example: Date range '2016-03-02' to '2016-03-05' for a user ID 2

    present absent
    2 1

    The above is fine but if I do this: Date range '2016-03-02' to '2016-03-10' for a user ID 2

    present absent
    2 6

    Currently I get the above result and this is not the actual result as the database has the attendance up to '2016-03-05'. So if I search with the date range '2016-03-02' to '2016-03-10', it will just give the following output:

    present absent
    2 1

    Is there any way to check the DATEDIFF(day, '2016-03-02', '2016-03-05') with the table to make sure the datetime is saved into it?? It is getting little bit weird. Thanks again for your time and I would be looking forward for your suggestion.

    Thursday, March 17, 2016 12:48 PM
  • User-219423983 posted

    Hi TechView,

    Currently I get the above result and this is not the actual result as the database has the attendance up to '2016-03-05'. So if I search with the date range '2016-03-02' to '2016-03-10', it will just give the following output:

    As the latest record data in your record is “2016-03-05”, why you request the record to “2016-03-10”? 

    Is there any way to check the DATEDIFF(day, '2016-03-02', '2016-03-05') with the table to make sure the datetime is saved into it?? It is getting little bit weird. Thanks again for your time and I would be looking forward for your suggestion.

    The “2016-03-05” could be a parameter and you could set any value according to your need. But, I suggested that you’d better first get the latest record date and then compare with the incoming parameter. If the parameter is larger than the latest record date, please set the incoming parameter equals latest record date.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 18, 2016 3:28 AM
  • User-698989805 posted

    Thanks a lot Weibo Zhang. I finally figured it out and just used a condition to check the last inserted date with the following:

    SELECT DISTINCT m.empid, m.empname,
    (SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    FROM punchDetails k WHERE k.empid = 2 AND k.Punchdate >= '2016-03-02' AND k.Punchdate <= '2016-03-10') 
    present,
    
    ((SELECT DATEDIFF(DAY, '2016-03-02', (CASE WHEN
    '2016-03-10' > MAX(p.Punchdate) THEN 
    MAX(p.Punchdate) ELSE '2016-03-10' END) + 1) FROM punchDetails p) - 
    
    (SELECT COUNT(CASE WHEN k.Punchdate IS NOT NULL THEN 'P' END)
    FROM punchDetails k WHERE k.empid = 2 AND k.Punchdate >= '2016-03-02' AND k.Punchdate <= '2016-03-10')) 
    absent
    
    FROM empDetails m 
    CROSS JOIN punchDetails l 
    LEFT JOIN punchDetails k ON k.empid = m.empid
    AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
    WHERE m.empid = 2 AND l.Punchdate >= '2016-03-02' AND l.Punchdate <='2016-03-10'

    Thanks again for your time and effort.

    Friday, March 18, 2016 8:45 AM
  • User77042963 posted

    Use a calendar table to make this type of question a lot easy. Here I use Weibo's sample tables to show you a solution with a on the fly date ranges.

    Here it is:

    declare @PunchRecords table(empid int, PunchDate datetime)
    
    insert into @PunchRecords values (1, '2016-03-02'),(1,'2016-03-03')
    								,(1,'2016-03-04'),(1,'2016-03-05')
    								,(2,'2016-03-02'),(2,'2016-03-03')
    								,(3,'2016-03-04'),(3,'2016-03-05')
    declare @empDetails table (empid int, empname varchar(20))
    insert into @empDetails values (1, 'AT-2015'),(2, 'AT-2016')
    
    
    
    
    ---Search parameters
    declare @minDate date ='2016-03-02' 
    declare @maxDate date = '2016-03-10' 
    
    
    
    
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    ,dateRange as (
    Select Dateadd(day,n-1,@minDate) dt from Nums
    Where Dateadd(day,n-1,@minDate)<= (select max(PunchDate) from @PunchRecords)
    --Use the most recent date from @PunchRecords table
    
    )
    
    ,cteEmpDates as (
    Select dt,empid,empname 
    from dateRange , @empDetails )
    
    Select m.empid,m.empname , SUM(Case When k.empid is not null then 1 else 0 end) present,
    SUM(Case When k.empid is  null then 1 else 0 end) absent
    from cteEmpDates m 
    LEFT JOIN @PunchRecords k ON k.empid = m.empid and m.dt=k.PunchDate
    Group by m.empid,m.empname 
    Order by m.empid 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 18, 2016 2:37 PM