Link to home
Start Free TrialLog in
Avatar of jdbenike
jdbenike

asked on

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.
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

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)

Open in new window

Avatar of jdbenike
jdbenike

ASKER

This is in reporting services
where do i put that?
Can I do something in the field itself in the report under it
Avatar of Zberteoc
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)

Open in new window

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)

Open in new window

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

untitled.bmp
You would put that SQL Function in your select list the SQL Server returns.  Are you using a view, stored procedure or adhoc query?
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

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

Open in new window

Your query should then be:


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

Open in new window

The formula should work. What do you have in that column if you simply output the column without the formula?
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

Open in new window


(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

Open in new window

same result with the last recommendation.
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) 

Open in new window

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?

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)

Open in new window

It might be better to do it the sql way, either way that works 1st would be great.
Yes, I was assuming you would have created the function that I put the code in for that I was referencing in the SQL.

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
http://www.tek-tips.com/viewthread.cfm?qid=1501124
is free, and they respond a lot quicker..
thanks for ur effort.
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
Thank u for ur effort.
Good day.
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.
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.
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.
>>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.