Solved

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

Posted on 2008-09-30
30
400 Views
Last Modified: 2013-11-27
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
Comment
Question by:jdbenike
  • 15
  • 8
  • 6
  • +1
30 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
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)

Open in new window

0
 

Author Comment

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

Author Comment

by:jdbenike
ID: 22606775
Can I do something in the field itself in the report under it
0
 
LVL 26

Expert Comment

by:Zberteoc
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)

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
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)

Open in new window

0
 

Author Comment

by:jdbenike
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

by:BrandonGalderisi
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

by:jdbenike
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

by:jdbenike
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

by:BrandonGalderisi
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

by:jdbenike
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

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22607883
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

0
 
LVL 26

Expert Comment

by:Zberteoc
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

by:BrandonGalderisi
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

Open in new window

0
 

Author Comment

by:jdbenike
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

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:jdbenike
ID: 22607975
same result with the last recommendation.
0
 

Author Comment

by:jdbenike
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) 

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
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

by:jdbenike
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)

Open in new window

0
 

Author Comment

by:jdbenike
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

by:BrandonGalderisi
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

by:jdbenike
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
http://www.tek-tips.com/viewthread.cfm?qid=1501124
is free, and they respond a lot quicker..
thanks for ur effort.
0
 
LVL 26

Accepted Solution

by:
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)

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
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

by:jdbenike
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

by:jdbenike
ID: 22608401
Thank u for ur effort.
Good day.
0
 
LVL 26

Expert Comment

by:Zberteoc
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

by:BrandonGalderisi
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

by:jdbenike
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

by:Anthony Perkins
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now