• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Summing up a converted column into its own column

I have this stored procedure:


CREATE PROCEDURE dbo.imprptceridian
@serial_number varchar(10),
@pay_period varchar(10)
AS

SELECT    
A.serial_number AS 'Employee ID',
B.last_name AS 'Employee Last Name',
B.first_name AS 'Employee First Name',
CONVERT(VARCHAR(10),CONVERT(datetime,A.pay_period),101) AS 'Pay Period',
A.detail_date AS 'Pay Date',
A.sequence AS 'Pay Date Sequence',
A.timecode_id AS 'Pay Code',
A.ld_code3 AS 'Project',
A.hours AS 'Hours Non-Formatted',
SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Formatted'
FROM
(SELECT * FROM Ceridian.dbo.employee
WHERE audit_timestamp >= ALL (SELECT audit_timestamp FROM Ceridian.dbo.employee TMP WHERE serial_number = Ceridian.dbo.employee.serial_number)) AS B
INNER JOIN Ceridian.dbo.timecard_raw AS A
ON B.serial_number = A.serial_number
WHERE
A.serial_number = @serial_number AND
A.pay_period = @pay_period  
GROUP BY A.serial_number, B.last_name,B.first_name,A.pay_period, A.detail_date,A.sequence, A.timecode_id,
A.ld_code3,A.hours

I would like to sum up the converted column:

SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Formatted'

by the timecode_id

0
PROJHOPE
Asked:
PROJHOPE
1 Solution
 
LowfatspreadCommented:
CREATE PROCEDURE dbo.imprptceridian
@serial_number varchar(10),
@pay_period varchar(10)
AS

SELECT    
A.serial_number AS 'Employee ID',
B.last_name AS 'Employee Last Name',
B.first_name AS 'Employee First Name',
CONVERT(VARCHAR(10),CONVERT(datetime,A.pay_period),101) AS 'Pay Period',
A.detail_date AS 'Pay Date',
A.sequence AS 'Pay Date Sequence',
A.timecode_id AS 'Pay Code',
A.ld_code3 AS 'Project',
A.hours AS 'Hours Non-Formatted',
SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Formatted'
into #temp
FROM
(SELECT * FROM Ceridian.dbo.employee
WHERE audit_timestamp >= ALL (SELECT audit_timestamp FROM Ceridian.dbo.employee TMP WHERE serial_number = Ceridian.dbo.employee.serial_number)) AS B
INNER JOIN Ceridian.dbo.timecard_raw AS A
ON B.serial_number = A.serial_number
WHERE
A.serial_number = @serial_number AND
A.pay_period = @pay_period  
GROUP BY A.serial_number, B.last_name,B.first_name,A.pay_period, A.detail_date,A.sequence, A.timecode_id,
A.ld_code3,A.hours

select * from #temp

select  timecode_id,
sum([Hours Formatted]) as [Total Hours]
 from #temp
group by  timecode_id
order by 1

return
go
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now