CODEWITHSUNDEEP
sqlsql-servert-sql
Almaji
Almaji

Reputation: 213

get row count of a dynamic column

i have a sql server database and i am using following query to get a list of attendance dates on which any given employee is late. 

SELECT MIN(att.Date) 
  from Attendance att
  where att.Date between '12/01/2011 00:00:00' and '12/31/2011 00:00:00' 
  and att.EmpID = 4700 
  GROUP BY EmpID, CONVERT(VARCHAR, att.Date , 111) 
  having min(CONVERT(VARCHAR, att.Date , 8)) > '09:00:00'

the query returns a list of dates with no column name. i need is count the dates of this list. how can this be done with this query?

Upvotes: 2

Views: 188

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7969

Try this:

WITH CTE AS (
    SELECT MIN(att.Date) [minDate]
    FROM Attendance att
    WHERE att.Date between '12/01/2011 00:00:00'
    AND '12/31/2011 00:00:00'
    AND att.EmpID = 4700
    GROUP BY EmpID, CONVERT(VARCHAR, att.Date , 111)
    HAVING MIN(CONVERT(VARCHAR, att.Date , 8)) > '09:00:00')

SELECT COUNT(minDate) FROM CTE

Alt without using CTE:

SELECT COUNT(minDate) FROM (
    SELECT MIN(att.Date) [minDate]
    FROM Attendance att
    WHERE att.Date between '12/01/2011 00:00:00'
    AND '12/31/2011 00:00:00'
    AND att.EmpID = 4700
    GROUP BY EmpID, CONVERT(VARCHAR, att.Date , 111)
    HAVING MIN(CONVERT(VARCHAR, att.Date , 8)) > '09:00:00') AS tbl

Upvotes: 3

Related Questions