Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with programming code. 5

Status
Not open for further replies.
Jdbenike

Jdbenike

MIS
Sep 11, 2008
74
0
0
US
Need help with SQL programming in microsoft sql server managment studio.
 
Sort by date Sort by votes
I posted the wrong query. Sorry.

Code: 
Select  WorkShiftId,
        Sum(DurationSecQty) As WorkTime,
        DateDiff(Second, Min(StartDTM), Max(EndDTM)) - Sum(DurationSecQty) As BreakTime
From    VP_TIMESHEETPUNCH
Where   VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
        AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
        AND VP_TIMESHEETPUNCH.PERSONNUM = '099136'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Now I need each break calculated though for each shift between each line. Not a sum of the breaks for a day.

now there is a timesheetitemid that seperates each item with a number, and outpuncheventid and an inpuncheventid

any suggestions?
 
Upvote 0 Downvote
You are amazing though. Thank you so much with giving me the total time for a shift.

How does a site like this work without membership fee's?
 
Upvote 0 Downvote
It's free. Just show appreciation for help by marking a post "valuable." (see that little link below each post?)
 
Upvote 0 Downvote
George have a star for sticking it out.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 
Upvote 0 Downvote
Sometimes people like me forget such simple small links on a page, so I do thank him.
 
Upvote 0 Downvote
Now I need each break calculated though for each shift between each line. Not a sum of the breaks for a day.
Click to expand...

Well... this gets a lot uglier. If you were using SQL2005, then you could use windowing functions to make this easier. Since you are using SQL2000, I recommend you use a table variable with an identity column. This will allow us to create a sequential number for each row. We will then use this number to link each row with it's 'next' row.

Try this...

Code: 
Declare @Temp 
Table   (RowId Int Identity(1,1), 
        WorkedShiftId Int, 
        StartDTM DateTime, 
        EndDTM DateTime 
        Primary Key (WorkedShiftId, StartDTM))

Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM)
Select WorkedShiftId, StartDTM, EndDTM
From   TimeSheetItem
[!]Order By WorkedShiftID, StartDTM[/!]

Select  A.WorkedShiftId, 
        A.EndDTM As BreakStart, 
        B.StartDTM As BreakEnd, 
        DateDiff(Second, A.EndDTM, B.StartDTM)
From    @Temp As A
        Inner Join @Temp As B
          On A.WorkedShiftId = B.WorkedShiftId
          And [!]A.RowId = B.RowId - 1[/!]
Where   A.EventDTM = '2008-06-01'

This should give you a list of WorkedShiftId's and the corresponding break times (and duration in seconds) for each break. I added a filter so that it would only show you info for a given day.

The trick here is the way I join the temp table back to itself. I'm joining first on WorkedShiftId and then on [!]A.RowId = B.RowId - 1[/!] This only works if you order the data properly when inserting it in to the table variable. The order by is important.

Does this appear to give you the correct data?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
When I run without the ''where a.eventdtm = '2008-06-01'

Msg 515, Level 16, State 2, Line 8
Cannot insert the value NULL into column 'StartDTM', table '@Temp'; column does not allow nulls. INSERT fails.
The statement has been terminated.

When I run this as is.. the error is.

Msg 207, Level 16, State 3, Line 13
Invalid column name 'EventDTM'.



 
Upvote 0 Downvote
Hmmm.....

Try this:

Code: 
Declare @Temp 
Table   (RowId Int Identity(1,1), 
        WorkedShiftId Int, 
        StartDTM DateTime, 
        EndDTM DateTime 
        Primary Key (WorkedShiftId, StartDTM))

Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM)
Select WorkedShiftId, StartDTM, EndDTM
From   TimeSheetItem
Order By WorkedShiftID, StartDTM

Select  A.WorkedShiftId, 
        A.EndDTM As BreakStart, 
        B.StartDTM As BreakEnd, 
        DateDiff(Second, A.EndDTM, B.StartDTM)
From    @Temp As A
        Inner Join @Temp As B
          On A.WorkedShiftId = B.WorkedShiftId
          And A.RowId = B.RowId - 1
Where   A.StartDTM >= '2008-06-01'
        A.StartDTM < '2008-06-02'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Same errors. Now with this original code.

--------------------------------------------
Select TIMESHEETITEM.WorkedShiftId,
Sum(DurationSecsQty) As WorkTime,
DateDiff(Second, Min(VP_TIMESHEETPUNCH.StartDTM), Max(VP_TIMESHEETPUNCH.EndDTM)) - Sum(TIMESHEETITEM.DurationSecsQty) as breaktime
From WFC_PRD.dbo.TIMESHEETITEM,
WFC_PRD.dbo.VP_TIMESHEETPUNCH
Where VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
-- AND VP_TIMESHEETPUNCH.PERSONNUM = '099136'
-- AND TIMESHEETITEM.WORKEDSHIFTID = '38802469'
AND VP_TIMESHEETPUNCH.TIMESHEETITEMID = TIMESHEETITEM.TIMESHEETITEMID
group by TIMESHEETITEM.WorkedShiftId, VP_TIMESHEETPUNCH.EVENTDATE
---------------------------------------

The two filters I need for this report is any minor who works over a 6 hour shift, and has no consecutive at least 30 minute break.

Now, there is a VP_TIMESHEETPUNCH.STARTREASON
the only 4 start reasons that can occur for these minors are
'newSHIFT'
'break:MEG LU30 Max2H E3 L3 EX35'
'break:MEG BRK 15Min PD'
'schedTransfer'

newshift is there first in punch, LU30 is the 30 minute break, 15min is the 15 minute break and schedtransfer is not important. now, behind these break rules the 30 minute rule occurs anytime after a person stays on break pass 24 minutes. So that's why I can't just filter by these rules. I can't change the code behind them either.

So basically i need someone who works more then 6 hours.. AND if 'break:MEG LU30 Max2H E3 L3 EX35' occurs and is more then 30 minutes then don't show them.

I don't know if that helps.

 
Upvote 0 Downvote
Changed it 2
-----------------------------------------
Declare @Temp
Table (RowId Int Identity(1,1) Primary Key,
WorkedShiftId Int,
StartDTM DateTime,
EndDTM DateTime)
-- Primary Key (WorkedShiftId, StartDTM))

Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM)
Select WorkedShiftId, StartDTM, EndDTM
From TimeSheetItem
where workedshiftid is not null
Order By WorkedShiftID, StartDTM

Select A.rowid,
B.rowid,
A.WorkedShiftId,
A.EndDTM As BreakStart,
B.StartDTM As BreakEnd,
DateDiff(Second, A.EndDTM, B.StartDTM)
From @Temp As A
Inner Join @Temp As B
On A.WorkedShiftId = B.WorkedShiftId
And A.RowId = B.RowId - 1
Where A.StartDTM >= '2008-06-01'
and A.StartDTM < '2008-06-02'
--------------------------------------

Got results with this. Thank you so far.
 
Upvote 0 Downvote
Is there a way to get

Select WorkShiftId,
Sum(DurationSecQty) As WorkTime,
DateDiff(Second, Min(StartDTM), Max(EndDTM)) - Sum(DurationSecQty) As BreakTime
From VP_TIMESHEETPUNCH
Where VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
AND VP_TIMESHEETPUNCH.PERSONNUM = '099136'

The sum part of this formula
""" Sum(DurationSecQty) As WorkTime,"""
Into our new formula? Or do I need to use a 'union' statement or something?
 
Upvote 0 Downvote
Declare @Temp
Table (RowId Int Identity(1,1) Primary Key,
WorkedShiftId Int,
PersonFullName Text,
StartDTM DateTime,
EndDTM DateTime)
-- Primary Key (WorkedShiftId, StartDTM))

Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM,PersonFullName)
Select a.WorkedShiftId,
a.StartDTM,
a.EndDTM,
b.PersonFullName
From TimeSheetItem a,
Vp_person b
where a.workedshiftid is not null
AND b.personid = a.employeeid
Order By a.WorkedShiftID, a.StartDtm

Select --A.rowid,
--B.rowid,
A.PersonFullName,
A.WorkedShiftId,
A.EndDTM As BreakStart,
B.StartDTM As BreakEnd,
DateDiff(Second, A.EndDTM, B.StartDTM)
From @Temp As A
Inner Join @Temp As B
On A.WorkedShiftId = B.WorkedShiftId
And A.RowId = B.RowId - 1
Where A.StartDTM >= '2008-06-01'
and A.StartDTM < '2008-06-02'



When I run this..

This is the results for one person


These are the person's 'actual punches'



As you can see it's only pulling one 'real punch time'.
There are only three breaks that person took, but it's not calculating them correctly. Or getting the right times. Any further suggestions?

hope clicking giving you a star 20 times works.
 
Upvote 0 Downvote
It depends. How do you want that data to appear?

With the temp table, there will be multiple rows (one for each break). The other query returns the total break time (sum for each break). If you want a separate row for the total, then use a union query. If you want the sum row duplicated for each of the individual breaks, then I would recommend a derived table approach.

Something like this...

Code: 
Declare @Temp 
Table   (RowId Int Identity(1,1) Primary Key, 
        WorkedShiftId Int, 
        StartDTM DateTime, 
        EndDTM DateTime)
--        Primary Key (WorkedShiftId, StartDTM))

Insert Into @Temp(WorkedShiftId, StartDTM, EndDTM)
Select WorkedShiftId, StartDTM, EndDTM
From   TimeSheetItem
where  workedshiftid is not null
Order By WorkedShiftID, StartDTM

Select  A.rowid,
        B.rowid,
        A.WorkedShiftId, 
        A.EndDTM As BreakStart, 
        B.StartDTM As BreakEnd, 
        DateDiff(Second, A.EndDTM, B.StartDTM) As BreakDuration,
        TotalAlias.BreakTime As TotalBreakTime
From    @Temp As A
        Inner Join @Temp As B
          On A.WorkedShiftId = B.WorkedShiftId
          And A.RowId = B.RowId - 1
        Inner Join [!]([/!]
          Select  WorkShiftId,
                  Sum(DurationSecQty) As WorkTime,
                  DateDiff(Second, Min(StartDTM), Max(EndDTM)) - Sum(DurationSecQty) As BreakTime
          From    VP_TIMESHEETPUNCH
          Where   VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
                  AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
                  AND VP_TIMESHEETPUNCH.PERSONNUM = '099136'
          [!]) As TotalAlias
          On A.WorkedShiftId = TotalAlias.WorkShiftId[/!]
Where   A.StartDTM >= '2008-06-01'
        and A.StartDTM < '2008-06-02'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Declare @Temp
Table (RowId Int Identity(1,1) Primary Key,
WorkedShiftId Int,
PersonFullName Text,
InPunchDTM DateTime,
OutPunchDTM DateTime)
-- Durationsecsqty Int)
-- Primary Key (WorkedShiftId, StartDTM))

Insert Into @Temp(WorkedShiftId, InPunchDTM, OutPunchDTM, PersonFullName)
Select a.WorkedShiftId,
b.InPunchDTM,
b.OutPunchDTM,
b.PersonFullName
-- a.durationsecsqty
From TimeSheetItem a,
Vp_Timesheetpunch b
where a.workedshiftid is not null
And a.timesheetitemid = b.timesheetitemid
Order By a.WorkedShiftID, b.InPunchDtm

Select --A.rowid,
--B.rowid,
A.PersonFullName,
A.WorkedShiftId,
A.OutPunchDTM As BreakStart,
B.InPunchDTM As BreakEnd,
DateDiff(Second, A.OutPunchDTM, B.InPunchDTM) As BreakTime
-- Sum(A.DurationSecsQty) As totalalias

From @Temp As A
Inner Join @Temp As B
On A.WorkedShiftId = B.WorkedShiftId
And A.RowId = B.RowId - 1
Inner Join (
Select
TIMESHEETITEM.WorkedShiftId,
Sum(TIMESHEETITEM.DurationSecsQty) as WorkTime
From WFC_PRD.dbo.TIMESHEETITEM,
WFC_PRD.dbo.VP_TIMESHEETPUNCH
Where VP_TIMESHEETPUNCH.TIMESHEETITEMID = TIMESHEETITEM.TIMESHEETITEMID
AND VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
group by TIMESHEETITEM.WorkedShiftId
) As TotalAlias
On A.WorkedShiftId = TotalAlias.WorkedShiftId
Where A.InPunchDTM >= '2008-06-01'
and A.InPunchDTM < '2008-06-02'




This is the code that brings back the breaktimes coorectly, yet iam still stuck on the sum for each shift part with the inner join. When I run this code it completes with right data but doesen't show the sum for the day shift. Any further suggestions? Do we hafta include this code into the temp table somehow?
 
Upvote 0 Downvote
Inner Join (
Select
TIMESHEETITEM.WorkedShiftId,
Sum(TIMESHEETITEM.DurationSecsQty) as WorkTime
From WFC_PRD.dbo.TIMESHEETITEM,
WFC_PRD.dbo.VP_TIMESHEETPUNCH
Where VP_TIMESHEETPUNCH.TIMESHEETITEMID = TIMESHEETITEM.TIMESHEETITEMID
AND VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
group by TIMESHEETITEM.WorkedShiftId
) As TotalAlias
On A.WorkedShiftId = TotalAlias.WorkedShiftId


---This is the part that won't show in the results as any column.
 
Upvote 0 Downvote
Your derived table is calculating the SUM of DurationSecsQTY, but you don't appear to be using it anywhere.

Try this...

Code: 
Declare @Temp 
Table   (RowId Int Identity(1,1) Primary Key, 
        WorkedShiftId Int,
        PersonFullName Text, 
        InPunchDTM DateTime, 
        OutPunchDTM DateTime)
--        Durationsecsqty Int)
--        Primary Key (WorkedShiftId, StartDTM))

Insert Into @Temp(WorkedShiftId, InPunchDTM, OutPunchDTM, PersonFullName)
Select a.WorkedShiftId,
       b.InPunchDTM,
       b.OutPunchDTM,
       b.PersonFullName
--       a.durationsecsqty
From   TimeSheetItem a,
       Vp_Timesheetpunch b
where  a.workedshiftid is not null
And a.timesheetitemid = b.timesheetitemid
Order By a.WorkedShiftID, b.InPunchDtm

Select  --A.rowid,
        --B.rowid,
        A.PersonFullName,
        A.WorkedShiftId, 
        A.OutPunchDTM As BreakStart,
        B.InPunchDTM As BreakEnd,
        DateDiff(Second, A.OutPunchDTM, B.InPunchDTM) As BreakTime[!],
        TotalAlias.WorkTime[/!]
--        Sum(A.DurationSecsQty) As totalalias

From    @Temp As A
        Inner Join @Temp As B
          On A.WorkedShiftId = B.WorkedShiftId
          And A.RowId = B.RowId - 1
        Inner Join (
        Select  
                TIMESHEETITEM.WorkedShiftId,
                Sum(TIMESHEETITEM.DurationSecsQty) as [blue]WorkTime[/blue]
          From  WFC_PRD.dbo.TIMESHEETITEM,
                WFC_PRD.dbo.VP_TIMESHEETPUNCH
          Where VP_TIMESHEETPUNCH.TIMESHEETITEMID = TIMESHEETITEM.TIMESHEETITEMID
                AND VP_TIMESHEETPUNCH.EVENTDATE >= '2008-06-01 00:00:00.000'
                AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-06-02 00:00:00.000'
                group by TIMESHEETITEM.WorkedShiftId
          ) As [blue]TotalAlias[/blue]  
          On A.WorkedShiftId = TotalAlias.WorkedShiftId
Where   A.InPunchDTM >= '2008-06-01'
        and A.InPunchDTM < '2008-06-02'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Are you fluent with reporting services as well in microsoft visual studio?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
85
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
221
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
257
Chris Miller
Chris Miller
G12Consult
  • Locked
  • Question
Help with some code
Replies
2
Views
64
SaltyTheFrog
SaltyTheFrog
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
164
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top