Calculating Group Sum

Question

User-47788187 posted

;WITH Prep AS
(
	SELECT 
		preffered_name as Department ,email_address1 as subdepartment,friday,saturday,government_num as dutyhours,ci.person_num,  ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,DATEname(weekday,dbo.UtcToLocal(co.creation_date)) AS Day,
		CAST(dbo.UtcToLocal(ci.creation_date) AS date) AS in_date, CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time,
		CAST(dbo.UtcToLocal(co.creation_date) AS date) AS out_date, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,
		DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
	FROm dbo.test ci
	OUTER APPLY 
	(
		SELECT TOP 1 creation_date 
		FROM dbo.test t 
		WHERE t.person_num = ci.person_num AND t.creation_date >= ci.creation_date AND t.event_name = 'CLOCK_OUT' 
		ORDER BY t.creation_date
	) co
	WHERE ci.event_name = 'CLOCK_IN' AND  preffered_name='COMMERCIAL'  
)

,CalcPart AS
(
	SELECT 
		department,subdepartment,dutyhours,person_num,  event_date, in_time, out_time, minutes,day,friday,saturday,
		ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
	FROM Prep
)
,tmpResult as
(  
	SELECT 
		subdepartment,--full_name,

		SUM(minutes) / 60.0 AS HoursWorked,

		dutyhours,Day,friday,saturday,
		--SUM(minutes) / 60.0 AS HoursWorked,-----This column is duplicate and could be removed
	
		case 
			when ( [friday] = 1 and [day]  = 'Friday' ) or ( [saturday] = 1 and [day] = 'Saturday' ) then  
				SUM(minutes) / 60.0
		else 
			sum(( [minutes] /60.0)) - [Dutyhours]
		end as OT	
	FROM CalcPart
	GROUP BY subdepartment,dutyhours,day,friday,saturday
)
select
	subdepartment AS SUBDEPARTMENT,
	
	SUM(cast(DutyHours as decimal(10,0))) as DUTYHOURS,
	

	cast(sum(HoursWorked) as int) as HOURSWORKED,
	cast(sum(OT) as int) as OT
from tmpResult
group by subdepartment

incorrect result,
 

Duty hours suppose to be 56 hours where is its shows only 48..

experts any guess for this error?

Answer 1

User-2057865890 posted

Hi Av2020,

For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

The following examples shows using the decimal(10,0) and decimal(10,2) to return summary data in the AdventureWorks2014 database.

USE AdventureWorks2014;  
GO

SELECT Color, 
SUM(cast(ListPrice as decimal(10,0))),SUM(StandardCost)  
FROM Production.Product  
WHERE Color IS NOT NULL   
    AND ListPrice != 0.00   
    AND Name LIKE 'Mountain%'  
GROUP BY Color  
ORDER BY Color;  
GO  

SELECT Color, 
SUM(cast(ListPrice as decimal(10,2))),SUM(StandardCost)  
FROM Production.Product  
WHERE Color IS NOT NULL   
    AND ListPrice != 0.00   
    AND Name LIKE 'Mountain%'  
GROUP BY Color  
ORDER BY Color;  
GO  

Best Regards,

Chris