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

Question

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

Amit Mohanty · Answer

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)

Amit Mohanty · Answer

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)

shah Momin · Answer

I Wan Try Add Day