# Convert seconds to Hh:mm so that it's almost like date time

Field
=Fields!TOTALTIME.Value
In reporting services comes back as total seconds for a day.
If I divide it like this.
=Fields!TOTALTIME.Value / 60 / 60
I would get results like 7.50, and 7.88.
How do I convert the field to show hour and minutes like on a clock?  So everytime it hits 60 minutes its the next hour.
So 7.50 I think would be like 7 hour and 30 minutes.
###### Who is Participating?

Commented:
try
``````=right("0"+Str((Fields!TOTALTIME.Value/60)/60 - (Fields!TOTALTIME.Value/60)/60/24*24),2)+":"+
right("0"+Str((Fields!TOTALTIME.Value/60)-(Fields!TOTALTIME.Value/60)/60*60),2)+":"+
right("0"+Str(Fields!TOTALTIME.Value-(Fields!TOTALTIME.Value/60)*60),2)
``````
0

Commented:
Here you go...
``````create function dbo.fn_Seconds_to_HoursMinutes(@YourSeconds int)
returns char(5)
as
begin
return (select right('0'+cast((@YourSeconds/60 /*now it's in minutes*/) / 60 as varchar(2)),2) + ':' +
right('0'+cast((@YourSeconds/60 /*now it's in minutes*/) % 60 as varchar(2)),2))
end
go
select dbo.fn_Seconds_to_HoursMinutes(120)
select dbo.fn_Seconds_to_HoursMinutes(600)
select dbo.fn_Seconds_to_HoursMinutes(6000)
select dbo.fn_Seconds_to_HoursMinutes(12000)
``````
0

Author Commented:
This is in reporting services
where do i put that?
0

Author Commented:
Can I do something in the field itself in the report under it
0

Commented:
Try the below. If the functions are not the correct one just replace them. You need the conversion to string from integer and you also need to concatenate "0" and get only the right(... , 2) from the expression:
``````right("0"+Cstr((Fields!TOTALTIME.Value/60)/60 - (Fields!TOTALTIME.Value/60)/60/24*24),2)+':'+
right("0"+Cstr((Fields!TOTALTIME.Value/60)-(Fields!TOTALTIME.Value/60)/60*60),2)+':'+
right("0"+Cstr(Fields!TOTALTIME.Value-(Fields!TOTALTIME.Value/60)*60),2)
``````
0

Commented:
one correction:
``````right("0"+Cstr((Fields!TOTALTIME.Value/60)/60 - (Fields!TOTALTIME.Value/60)/60/24*24),2)+":"+
right("0"+Cstr((Fields!TOTALTIME.Value/60)-(Fields!TOTALTIME.Value/60)/60*60),2)+":"+
right("0"+Cstr(Fields!TOTALTIME.Value-(Fields!TOTALTIME.Value/60)*60),2)
``````
0

Author Commented:
All the results come back as
00:00:00 or 16:00:00
That's it. for everything.  All results.

untitled.bmp
0

Commented:
You would put that SQL Function in your select list the SQL Server returns.  Are you using a view, stored procedure or adhoc query?
0

Author Commented:
This picture shows which field in reporting services iam trying to change.
This field.

=Fields!TOTALTIME.Value
This field returns seconds as a value.
How can I convert this field in reporting services to hh:mm
I want to do something in reporting services in the 'expression' code under that part of report.

I dont' want to go back into the actual sql b/c it's complex in just getting that value you in the first place.

untitled.bmp
0

Author Commented:

DECLARE @TempC TABLE
( HOMELABORLEVELNAME2 VARCHAR(50),
WORKEDSHIFTID INT,
HOMELABORLEVELNAME3 VARCHAR(50),
HOMELABORLEVELNAME4 VARCHAR(50),
PERSONFULLNAME VARCHAR(64),
PERSONNUM VARCHAR(15),
AGE VARCHAR(14),
EVENTDATE DATETIME,
PUNCH1 DATETIME,
PUNCH2 DATETIME,
MINORRULENM VARCHAR(50),
TIMEINSECONDS INT,
STARTREASON VARCHAR(50)
)
INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
pa.HOMELABORLEVELNAME2,
tsia.WORKEDSHIFTID,
pa.HOMELABORLEVELNAME3,
pa.HOMELABORLEVELNAME4,
pa.PERSONFULLNAME,
tspa.PERSONNUM,
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE,
DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE,
ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
mra.MINORRULENM,
tspa.TIMEINSECONDS,
tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE tspa.PERSONID = pa.PERSONID
AND tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND tspa.PERSONID = pma.PERSONID
AND pma.MINORRULEID = mra.MINORRULEID
AND tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND tspa.LABORLEVELNAME4 <> ''
AND tspa.PAIDSW = 1
AND (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND tspa.EVENTDATE < '2008-06-10 00:00:00.000')
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC
SELECT HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
PUNCH1,
PUNCH2,
AGE,
EVENTDATE,
/*
Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
If this is null, which it will be at end of shift, then use the punch out from this record.
Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
*/
(Case
When DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
Then 1
Else 0
End) As BreakInfraction,
MINORRULENM,
SUM(TIMEINSECONDS) AS WorkTime,
TOTALTIME,
STARTREASON
FROM @TempC a
Inner Join
(SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
On a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
Left Join (
Select A.WorkedShiftId
From @TempC As a
Where DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
) As ShiftsWithBreakInfractions
On a.WorkedShiftId = ShiftsWithBreakInfractions.WorkedShiftId
Where TotalTime > 21600
And ShiftsWithBreakInfractions.WorkedShiftId Is NULL
GROUP BY HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
MINORRULENM,
STARTREASON,
PUNCH1,
PUNCH2,
TOTALTIME
ORDER BY a.WORKEDSHIFTID, PUNCH1

this is the code
where totaltime is what we need
0

Commented:
From SQL it would be as simple as adding

dbo.fn_Seconds_to_HoursMinutes(YourSecondsFieldName)

to the select list in your query.

But I understand.
0

Author Commented:
There is the code above

the row 'TOTALTIME'
is the one i need to modify.

``````DECLARE @TempC TABLE
( HOMELABORLEVELNAME2      VARCHAR(50),
WORKEDSHIFTID            INT,
HOMELABORLEVELNAME3      VARCHAR(50),
HOMELABORLEVELNAME4      VARCHAR(50),
PERSONFULLNAME           VARCHAR(64),
PERSONNUM                VARCHAR(15),
AGE                      VARCHAR(14),
EVENTDATE                DATETIME,
PUNCH1                   DATETIME,
PUNCH2                   DATETIME,
MINORRULENM              VARCHAR(50),
TIMEINSECONDS            INT,
STARTREASON              VARCHAR(50)
)

INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
pa.HOMELABORLEVELNAME2,
tsia.WORKEDSHIFTID,
pa.HOMELABORLEVELNAME3,
pa.HOMELABORLEVELNAME4,
pa.PERSONFULLNAME,
tspa.PERSONNUM,
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE,
DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE,
ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
mra.MINORRULENM,
tspa.TIMEINSECONDS,
tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE  tspa.PERSONID = pa.PERSONID
AND    tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND    datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND    tspa.PERSONID = pma.PERSONID
AND    pma.MINORRULEID = mra.MINORRULEID
AND    tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND    tspa.LABORLEVELNAME4 <> ''
AND    tspa.PAIDSW = 1
AND    (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND    tspa.EVENTDATE <  '2008-06-10 00:00:00.000')
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC

SELECT HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
PUNCH1,
PUNCH2,
AGE,
EVENTDATE,
/*
Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
If this is null, which it will be at end of shift, then use the punch out from this record.
Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
*/
(Case
When DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
Then 1
Else 0
End) As BreakInfraction,
MINORRULENM,
SUM(TIMEINSECONDS) AS WorkTime,
TOTALTIME,
STARTREASON
FROM @TempC a
Inner Join
(SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
On a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
Left Join (
Select A.WorkedShiftId
From   @TempC As a
Where  DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
) As ShiftsWithBreakInfractions
On a.WorkedShiftId = ShiftsWithBreakInfractions.WorkedShiftId
Where TotalTime > 21600
And ShiftsWithBreakInfractions.WorkedShiftId Is NULL
GROUP BY HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
MINORRULENM,
STARTREASON,
PUNCH1,
PUNCH2,
TOTALTIME
ORDER BY a.WORKEDSHIFTID, PUNCH1
``````
0

Commented:

``````DECLARE @TempC TABLE
( HOMELABORLEVELNAME2 VARCHAR(50),
WORKEDSHIFTID INT,
HOMELABORLEVELNAME3 VARCHAR(50),
HOMELABORLEVELNAME4 VARCHAR(50),
PERSONFULLNAME VARCHAR(64),
PERSONNUM VARCHAR(15),
AGE VARCHAR(14),
EVENTDATE DATETIME,
PUNCH1 DATETIME,
PUNCH2 DATETIME,
MINORRULENM VARCHAR(50),
TIMEINSECONDS INT,
STARTREASON VARCHAR(50)
)
INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
pa.HOMELABORLEVELNAME2,
tsia.WORKEDSHIFTID,
pa.HOMELABORLEVELNAME3,
pa.HOMELABORLEVELNAME4,
pa.PERSONFULLNAME,
tspa.PERSONNUM,
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE,
DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE,
ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
mra.MINORRULENM,
tspa.TIMEINSECONDS,
tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE tspa.PERSONID = pa.PERSONID
AND tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND tspa.PERSONID = pma.PERSONID
AND pma.MINORRULEID = mra.MINORRULEID
AND tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND tspa.LABORLEVELNAME4 <> ''
AND tspa.PAIDSW = 1
AND (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND tspa.EVENTDATE < '2008-06-10 00:00:00.000')
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC

SELECT HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
PUNCH1,
PUNCH2,
AGE,
EVENTDATE,
/*
Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
If this is null, which it will be at end of shift, then use the punch out from this record.
Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
*/
(Case
When DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
Then 1
Else 0
End) As BreakInfraction,
MINORRULENM,
SUM(TIMEINSECONDS) AS WorkTime,
TOTALTIME,
dbo.fn_Seconds_to_HoursMinutes(TotalTime),
STARTREASON
FROM @TempC a
Inner Join
(SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
On a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
Left Join (
Select A.WorkedShiftId
From @TempC As a
Where DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
) As ShiftsWithBreakInfractions
On a.WorkedShiftId = ShiftsWithBreakInfractions.WorkedShiftId
Where TotalTime > 21600
And ShiftsWithBreakInfractions.WorkedShiftId Is NULL
GROUP BY HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
MINORRULENM,
STARTREASON,
PUNCH1,
PUNCH2,
TOTALTIME,
dbo.fn_Seconds_to_HoursMinutes(TotalTime)
ORDER BY a.WORKEDSHIFTID, PUNCH1
``````
0

Commented:
The formula should work. What do you have in that column if you simply output the column without the formula?
0

Commented:
Ok... so you don't need both?

Try this:
``````DECLARE @TempC TABLE
( HOMELABORLEVELNAME2 VARCHAR(50),
WORKEDSHIFTID INT,
HOMELABORLEVELNAME3 VARCHAR(50),
HOMELABORLEVELNAME4 VARCHAR(50),
PERSONFULLNAME VARCHAR(64),
PERSONNUM VARCHAR(15),
AGE VARCHAR(14),
EVENTDATE DATETIME,
PUNCH1 DATETIME,
PUNCH2 DATETIME,
MINORRULENM VARCHAR(50),
TIMEINSECONDS INT,
STARTREASON VARCHAR(50)
)
INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
pa.HOMELABORLEVELNAME2,
tsia.WORKEDSHIFTID,
pa.HOMELABORLEVELNAME3,
pa.HOMELABORLEVELNAME4,
pa.PERSONFULLNAME,
tspa.PERSONNUM,
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE,
DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE,
ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
mra.MINORRULENM,
tspa.TIMEINSECONDS,
tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE tspa.PERSONID = pa.PERSONID
AND tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND tspa.PERSONID = pma.PERSONID
AND pma.MINORRULEID = mra.MINORRULEID
AND tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND tspa.LABORLEVELNAME4 <> ''
AND tspa.PAIDSW = 1
AND (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND tspa.EVENTDATE < '2008-06-10 00:00:00.000')
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC

SELECT HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
PUNCH1,
PUNCH2,
AGE,
EVENTDATE,
/*
Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
If this is null, which it will be at end of shift, then use the punch out from this record.
Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
*/
(Case
When DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
Then 1
Else 0
End) As BreakInfraction,
MINORRULENM,
SUM(TIMEINSECONDS) AS WorkTime,
dbo.fn_Seconds_to_HoursMinutes(TotalTime) as TOTALTIME,
STARTREASON
FROM @TempC a
Inner Join
(SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
On a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
Left Join (
Select A.WorkedShiftId
From @TempC As a
Where DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
) As ShiftsWithBreakInfractions
On a.WorkedShiftId = ShiftsWithBreakInfractions.WorkedShiftId
Where TotalTime > 21600
And ShiftsWithBreakInfractions.WorkedShiftId Is NULL
GROUP BY HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
MINORRULENM,
STARTREASON,
PUNCH1,
PUNCH2,
dbo.fn_Seconds_to_HoursMinutes(TotalTime)
ORDER BY a.WORKEDSHIFTID, PUNCH1
``````
0

Author Commented:

(1264 row(s) affected)
Msg 208, Level 16, State 1, Line 49
Invalid object name 'dbo.fn_Seconds_to_HoursMinutes'.
Msg 208, Level 16, State 1, Line 49
Invalid object name 'dbo.fn_Seconds_to_HoursMinutes'.

for the

this is sql server 2000, not 2005 if that matters in sql server management studio

``````DECLARE @TempC TABLE
( HOMELABORLEVELNAME2 VARCHAR(50),
WORKEDSHIFTID INT,
HOMELABORLEVELNAME3 VARCHAR(50),
HOMELABORLEVELNAME4 VARCHAR(50),
PERSONFULLNAME VARCHAR(64),
PERSONNUM VARCHAR(15),
AGE VARCHAR(14),
EVENTDATE DATETIME,
PUNCH1 DATETIME,
PUNCH2 DATETIME,
MINORRULENM VARCHAR(50),
TIMEINSECONDS INT,
STARTREASON VARCHAR(50)
)
INSERT INTO @TempC
SELECT /* DISTINCT */ -- No distinct here as we will aggregate later
pa.HOMELABORLEVELNAME2,
tsia.WORKEDSHIFTID,
pa.HOMELABORLEVELNAME3,
pa.HOMELABORLEVELNAME4,
pa.PERSONFULLNAME,
tspa.PERSONNUM,
datediff(yy,pa.BIRTHDTM,getdate()) AS AGE,
DateAdd(dd, 0, DateDiff(dd, 0, tspa.EVENTDATE)) As EVENTDATE,
ISNULL(tspa.INPUNCHDTM, tspa.STARTDTM) AS PUNCH1,
ISNULL(tspa.OUTPUNCHDTM, tspa.ENDDTM) AS PUNCH2,
mra.MINORRULENM,
tspa.TIMEINSECONDS,
tspa.STARTREASON
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH tspa WITH (NOLOCK),
WFC_PRD.dbo.VP_PERSON pa WITH (NOLOCK),
WFC_PRD.dbo.PRSNMINORRULEMM pma WITH (NOLOCK),
WFC_PRD.dbo.MINORRULE mra WITH (NOLOCK),
WFC_PRD.dbo.TIMESHEETITEM tsia WITH (NOLOCK)
WHERE tspa.PERSONID = pa.PERSONID
AND tspa.TIMESHEETITEMID = tsia.TIMESHEETITEMID
AND datediff(yy,pa.BIRTHDTM,getdate()) < 18
AND tspa.PERSONID = pma.PERSONID
AND pma.MINORRULEID = mra.MINORRULEID
AND tspa.TMSHTITEMTYPEID not in (1, 2, 3)
AND tspa.LABORLEVELNAME4 <> ''
AND tspa.PAIDSW = 1
AND (tspa.EVENTDATE >= '2008-06-01 00:00:00.000'
AND tspa.EVENTDATE < '2008-06-10 00:00:00.000')
-- ORDER BY tspa.EVENTDATE , tsia.WORKEDSHIFTID ASC

SELECT HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
PUNCH1,
PUNCH2,
AGE,
EVENTDATE,
/*
Select the row for the same workshift for same person (if workedshiftid doesn't hanle that, uncomment personnum statement.
If this is null, which it will be at end of shift, then use the punch out from this record.
Take the date difference in seconds and if greater than or equal to 1800 (30 mins) then sum 1 else 0.
*/
(Case
When DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
Then 1
Else 0
End) As BreakInfraction,
MINORRULENM,
SUM(TIMEINSECONDS) AS WorkTime,
TOTALTIME,
dbo.fn_Seconds_to_HoursMinutes(TotalTime),
STARTREASON
FROM @TempC a
Inner Join
(SELECT WORKEDSHIFTID, TOTALTIME=SUM(TIMEINSECONDS) FROM @TempC GROUP BY WORKEDSHIFTID) as ttlTime
On a.WORKEDSHIFTID = ttlTime.WORKEDSHIFTID
Left Join (
Select A.WorkedShiftId
From @TempC As a
Where DateDiff(second, PUNCH2, IsNull
((SELECT MIN(PUNCH1)
FROM @TempC b
WHERE b.WORKEDSHIFTID = a.WORKEDSHIFTID
AND b.EVENTDATE = a.EVENTDATE
AND b.PUNCH1 > a.PUNCH1 /* AND b.PERSONNUM = a.PERSONNUM*/) , PUNCH2)) >= 1800
) As ShiftsWithBreakInfractions
On a.WorkedShiftId = ShiftsWithBreakInfractions.WorkedShiftId
Where TotalTime > 21600
And ShiftsWithBreakInfractions.WorkedShiftId Is NULL
GROUP BY HOMELABORLEVELNAME2,
a.WORKEDSHIFTID,
HOMELABORLEVELNAME3,
HOMELABORLEVELNAME4,
PERSONFULLNAME,
PERSONNUM,
AGE,
EVENTDATE,
MINORRULENM,
STARTREASON,
PUNCH1,
PUNCH2,
TOTALTIME,
dbo.fn_Seconds_to_HoursMinutes(TotalTime)
ORDER BY a.WORKEDSHIFTID, PUNCH1
``````
0

Author Commented:
same result with the last recommendation.
0

Author Commented:
I didn't put this code anywhere yet either.
Dont' know if you were assuming that.

``````create function dbo.fn_Seconds_to_HoursMinutes(@YourSeconds int)
returns char(5)
as
begin
return (select right('0'+cast((@YourSeconds/60 /*now it's in minutes*/) / 60 as varchar(2)),2) + ':' +
right('0'+cast((@YourSeconds/60 /*now it's in minutes*/) % 60 as varchar(2)),2))
end
go
select dbo.fn_Seconds_to_HoursMinutes(120)
select dbo.fn_Seconds_to_HoursMinutes(600)
select dbo.fn_Seconds_to_HoursMinutes(6000)
select dbo.fn_Seconds_to_HoursMinutes(12000)
``````
0

Commented:
jdbenike, leave the SQL alone and use the formula in the reports. Should work. Try to focus on that.

I ask again, what are the values in seconds returned by that column?

0

Author Commented:
With this code.  Maybe Iam putting it in the wrong place.  On the report in reporting services, i right click the field, click expression, I paste this code below in there.
When I run the report I get two values for every result.
I get 00:00:00 or 16:00:00.  Mutiple times.  It doesen't matter what the result is.  The range of seconds will usually be from 21600 as high as 43600.
But that's all I get back when I do it that way on the actual report.

``````=right("0"+Cstr((Fields!TOTALTIME.Value/60)/60 - (Fields!TOTALTIME.Value/60)/60/24*24),2)+":"+
right("0"+Cstr((Fields!TOTALTIME.Value/60)-(Fields!TOTALTIME.Value/60)/60*60),2)+":"+
right("0"+Cstr(Fields!TOTALTIME.Value-(Fields!TOTALTIME.Value/60)*60),2)
``````
0

Author Commented:
It might be better to do it the sql way, either way that works 1st would be great.
0

Commented:
Yes, I was assuming you would have created the function that I put the code in for that I was referencing in the SQL.
0

Author Commented:

Convert(VarChar(5), DateAdd(Minute, TOTALTIME / 60, 0), 108) As HourMin,

worked fine and quick.
so completely different and much more simpler then what you guys recommended.
tek tips
is free, and they respond a lot quicker..
thanks for ur effort.
0

Commented:
I think that your statement "they respond a lot quicker" is unfair.  While it may be true for this case is probably not true overall.  Not saying that the people of EE respond faster, but response on any forum site is going to be based on the availability of people who are experts in the area you are asking a question about.
0

Author Commented:
Well in my experiences, the information they provided that was more correct at looking at the problem as a whole and responding with better information is true.
This isnt my first occurrence on this site.  Before i had up to 48 hours of no response on 2 questions. when the free site repsonded in a matter of minutes.  Remember, I am paying for this service.
So correct, you guys responded quick, just not with useful information yet.  You maybe would of got it though in time.  Its sometimes hard to understand these problems without visualy being there.
0

Author Commented:
Thank u for ur effort.
Good day.
0

Commented:
Ok, jdbenike,

First of all the solution I gave you works as I verified it. Is an elementary mathematical conversion of seconds in hours, minutes and seconds along with some string manipulation. That is what you asked for.

The fact that you were playing on two fronts didn't give you the time maybe to give it more thought. You choose to go with the solution from the other site, that doesn't mean you were not served here.
0

Commented:
First off, I'm sure I'll get in trouble over this but I have to object to this:

"just not with useful information yet"
"You maybe would of got it though in time"

While it may not be the same approach, and one could argue that the other is a better solution, what I provided to you THE FIRST TIME returned the same result.  So don't come here and talk down to me.  I do this to help people out.  I don't get paid.

I always respond as quickly as my time permits.
0

Author Commented:
With the code in place currently and the temp tables, throwing all that code in there would of worked maybe in time with adjusting and re-adjusting the report.  I needed a simple way to do it.  I knew there was a simple way out there either in reporting services or in SQL with a line or more.
It's not talking down to you.  You read it wrong.  Please chill out.  This is like talking to one of my x girlfriends on the rag.  You are mis-reading the comment.  Or reading too far into it.
When I say not useful information yet, it means not useful in the sense that I can't use it with what Iam trying to do with my code.  Not, your information sucks and isn't smart.
When I say You maybe would of got it though in time it means if I would of kept working on it with you, I am willing to bet we would of got it working.  Not, your so dumb it would take you a while but we would finally get it.
Hopefully you dont' get in trouble for it, that's insane.  Obviously, I have optimism on this site b/c I am paying for it when others I get free and quicker data.
Yet, as far as you having a bad day or taking things wrong in text, don't get so worked up in the future.  Gotta realize how you percieve something in a message online maybe the complete opposite way you take it as.
Ok?
Good stuff.  Cheers.  Have a beer for me tonite or whatever kinda drink you like to relax and have a better day.
0

Commented:
>>This is like talking to one of my x girlfriends on the rag. <<
Can you possibly see how this comment is highly offensive and more importantly to you does not serve your cause to any great measure?

I realize you are new here and more than likely will be gone by the end of the month, just realize we are all volunteers.

Please post a message in Community Support to have the reference to the other site removed.  This goes against EE Guidelines, which you explicitly agree to when you sign on to this site.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.