We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

Last post Nov 04, 2016 02:27 AM by Chris Zhao

• av2020

Member

9 Points

72 Posts

### Calculating Group Sum

```;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?

• Chris Zhao

All-Star

17652 Points

3510 Posts

### Re: Calculating Group Sum

Nov 04, 2016 02:27 AM|Chris Zhao|LINK

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