SQL exercise for hour statistics--including time processing

Monthly statistics, excluding weekend attendance, (work, leave, absence)

--Build table SQL create [dbo]. [Absencehourld]
CREATE TABLE [dbo]. [Absencehourld] (
[ID] [int] NULL,
[Date] [DateTime] Null
[Name] [varchar] () NULL,
[Hours] [INT] Null
) on [PRIMARY]
--Create [dbo]. [Manhour]
CREATE TABLE [dbo]. [Manhour] (
[ID] [int] NULL,
[Date] [DateTime] Null
[Name] [varchar] () NULL,
[Hours] [INT] Null
) on [PRIMARY]
--Build Table SQL
--Statistics of all employees--daily statistics
Select A.name as Name,
CONVERT (varchar (+), A.[date], 111) As time of day,
SUM (A.[hours]) as working time,
Sum (IsNull (b.[hours],0)) as leave time,
8-sum (A.[hours]) +sum (IsNull (b.[hours],0)) as absence time
From Manhour A
Left JOIN Absencehourld b
On A.name=b.name
where DATEPART (weekday, a.[date]) not in (6,7)
GROUP BY CONVERT (varchar (+), A.[date], 111), A.name

--Create a valid number of days to return a month minus weekends--functions
Alter FUNCTION returns the number of days after which the month is reduced (@date varchar (8))
RETURNS INT
As
BEGIN
DECLARE @fistM varchar (8)-the first day of the month
Declare @YY char (4), @MM char (2)-year, month
Set @YY =left (@date, 4)
Set @MM =dateadd (mm,1, @date)-This interception causes the month to start at 0
Set @[email protected][email protected]+ ' 01 '
DECLARE @edate varchar (8), @monthCount int, @weekCount INT-end time of month, days of month, number of days on weekends
-The number of days to take out the month @monthCount
SET @edate =convert (VARCHAR (8), DateAdd (D,-1, @fistM), 112)
SET @monthCount =cast (DatePart (day, @edate) as INT)
--Number of weekends in the month @weekCount
Set @weekCount =0
DECLARE @wdate varchar (8), @temp_time varchar (8), @WM varchar (6)-Week calculation (month) first day, intermediate variable
Set @WM =left (@date, 6)
Set @[email protected]+ ' 01 '
Set @[email protected]
While left (@wdate, 6) =left (@temp_time, 6)
Begin
IF DATEPART (WEEKDAY, @temp_time) =1 OR DATEPART (WEEKDAY, @temp_time) =7--Day of the week
Set @[email protected]+1
Set @temp_time =convert (varchar (8), DateAdd (d,1, @temp_time), 112)
End
return @[email protected]
END

SELECT dbo. Returns the number of days after the month minus the weekend (' 20150105 ')

--Statistics of all employees--monthly attendance data
Select A.name as Name,
Convert (char (4), Year (a.[date)) + ' +convert (char (2), month (A.[date]) + ' month ' as time,
SUM (A.[hours]) as working time,
Sum (IsNull (b.[hours],0)) as leave time,
8*dbo. Returns the number of days after the weekend (CONVERT (varchar), min (a.[date), 112))--Call function to get
-sum (A.[hours]) +sum (IsNull (b.[hours],0)) as absence time
From Manhour A
Left JOIN Absencehourld b
On A.name=b.name
where DATEPART (weekday, a.[date]) not in (6,7)
Group BY month (A.[date]), A.name,year (A.[date])

SQL exercise for hour statistics--including time processing