CODEWITHSUNDEEP
sqlsql-server
shal
shal

Reputation: 47

Query to Find First and Last Day of Current Month excluding sunday in sql

i need to get the data of monthly report which should exclude Sunday in all the weeks...where am not able to exclude Sunday. Kindly check the below query:

select Name,Actual_Hours,round((Actual_Hours/((DATEDIFF(DAY,'12-01-2016'  ,'12-14-2016')+1)*8.5))*100,0)as percentage 
 from (select Name , SUM(actual_Hours) Actual_Hours  
 from ( select Name,ACTUAL_HOURS=sum(DATEPART(hh,[Time_Taken_in_Min]))+sum(DATEPART(MINUTE,[Time_Taken_in_Min])) /60  
 FROM [HR_Admin].[dbo].[Mst_Daily_Report_Pds]  where date between  '12-01-2016'  and  '12-14-2016' and (DATENAME(weekday,Date)) not in ('sunday') 
 GROUP BY  Name )o group by Name  )a order by Actual_Hours desc

Upvotes: 2

Views: 70

Answers (1)

Techie
Techie

Reputation: 1491

Don't know exactly what error you are getting but I don't see the issue in SQL, but I believe it's because of the use of DATEDIFF in the select query instead of using the actual days.

Check the below query which uses the actual days (already removed Sunday in Where clause) and using less subquery. You can further improve this by taking only the time factor in the innermost query and then rounding off after summing all the hours.

Another suggestion is to avoid using the column name as reserved word like Date as it could create confusion, or using the column in Square brackets like [Date].

select Name
     , sum(Actual_Hours) as Actual_Hours
     , round((sum(Actual_Hours)/COUNT([Day])*8.5)*100,0) as percentage
  from (select Name
             , DATEPART(DAYOFYEAR,[Date]) as [Day]
             , ACTUAL_HOURS=sum(sum(DATEPART(hh,[Time_Taken_in_Min]))+sum(DATEPART(MINUTE,[Time_Taken_in_Min])) /60)
          FROM [HR_Admin].[dbo].[Mst_Daily_Report_Pds] 
         where date between '12-01-2016' and '12-14-2016' 
           and (DATENAME(weekday,[Date])) not in ('Sunday')
         GROUP BY Name, [Date]
     ) a 
 GROUP BY Name
 order by Actual_Hours desc

Upvotes: 1

Related Questions