Solved

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

Posted on 2008-09-30
417 Views
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.
0
Question by:jdbenike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 15
• 8
• 6
• +1

LVL 39

Expert Comment

ID: 22606747
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 Comment

ID: 22606766
This is in reporting services
where do i put that?
0

Author Comment

ID: 22606775
Can I do something in the field itself in the report under it
0

LVL 27

Expert Comment

ID: 22606965
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

LVL 27

Expert Comment

ID: 22606976
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 Comment

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

untitled.bmp
0

LVL 39

Expert Comment

ID: 22607164
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 Comment

ID: 22607460
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 Comment

ID: 22607851

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

LVL 39

Expert Comment

ID: 22607860
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 Comment

ID: 22607870
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

LVL 39

Expert Comment

ID: 22607883

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

LVL 27

Expert Comment

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

LVL 39

Expert Comment

ID: 22607890
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 Comment

ID: 22607955

(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 Comment

ID: 22607975
same result with the last recommendation.
0

Author Comment

ID: 22607985
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

LVL 27

Expert Comment

ID: 22607994
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 Comment

ID: 22608031
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 Comment

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

LVL 39

Expert Comment

ID: 22608120
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 Comment

ID: 22608179

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

LVL 27

Accepted Solution

Zberteoc earned 500 total points
ID: 22608220
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

LVL 39

Expert Comment

ID: 22608265
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 Comment

ID: 22608396
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 Comment

ID: 22608401
Thank u for ur effort.
Good day.
0

LVL 27

Expert Comment

ID: 22608522
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

LVL 39

Expert Comment

ID: 22608525
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 Comment

ID: 22608581
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

LVL 75

Expert Comment

ID: 22610760
>>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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether youâ€™re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Suggested Courses
Course of the Month5 days, 9 hours left to enroll