Getting count of Total Present And Absent In Day Wise sql query

shah Momin

SELECT 
    Day = DATENAME(DW,dutydatetime) -- Wrong function previously used this will get the name
    , Date = CONVERT(VARCHAR, dutydatetime,103) -- this returns in format dd/mm/yyyy other codes are available
    ,COUNT(CASE WHEN [type]='Absent' then 1 end ) as  [Absent]
	,COUNT (CASE WHEN [type]='Present' then 1 end ) as  [Present]  
FROM
    tblattendancelog
GROUP BY 
    DATENAME(DW,dutydatetime)
    ,CONVERT(VARCHAR, dutydatetime,103)
	 ORDER BY 1,2

i Will Try Daily Base Report For " Get Daily of Day Wise Report

Get Month of Day Wise Report

Get Year Of Day Wise Report

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
Can you explain your requirement properly?
If you want to show day, month and year in one query then try this
SELECT
    [Day] = DATENAME(DW, dutydatetime)
    ,[Month] = DATENAME(MONTH, dutydatetime)
    ,[Year] = DATENAME(YEAR, dutydatetime)
    ,[Date] = CONVERT(VARCHAR, dutydatetime,103)
    ,[Absent] = COUNT(CASE WHEN [type]='Absent' THEN 1 END)
    ,[Present] = COUNT(CASE WHEN [type]='Present' THEN 1 END)
FROM
    tblattendancelog
GROUP BY
    DATENAME(DW,dutydatetime)
    ,CONVERT(VARCHAR, dutydatetime,103)
    ,DATENAME(MONTH, dutydatetime)
    ,DATENAME(YEAR, dutydatetime)
ORDER BY
     CONVERT(VARCHAR, dutydatetime,103)
shah Momin4y ago
I Wan Try Add Day
Amit Mohanty4y ago
Check this
-- For month wise
SELECT
    [Month] = DATENAME(MONTH,dutydatetime)
    ,[Absent] = COUNT(CASE WHEN [type]='Absent' then 1 end )
    ,[Present] = COUNT (CASE WHEN [type]='Present' then 1 end )
FROM
    tblattendancelog
GROUP BY
    DATENAME(MONTH,dutydatetime)

-- year wise
SELECT
    [Year] = DATENAME(YEAR,dutydatetime)
    ,[Absent] = COUNT(CASE WHEN [type]='Absent' then 1 end )
    ,[Present] = COUNT (CASE WHEN [type]='Present' then 1 end )
FROM
    tblattendancelog
GROUP BY
    DATENAME(YEAR,dutydatetime)