Help with programming code. 5

[Jdbenike]

Need help with SQL programming in microsoft sql server managment studio.

 

[Jdbenike]

SELECT DISTINCT
VP_PERSON.HOMELABORLEVELNAME2,
TIMESHEETITEM.WORKEDSHIFTID,
VP_PERSON.HOMELABORLEVELNAME3,
VP_PERSON.HOMELABORLEVELNAME4,
VP_PERSON.PERSONFULLNAME,
VP_TIMESHEETPUNCH.PERSONNUM,
datediff(yy,VP_PERSON.BIRTHDTM,getdate()) AS AGE,
VP_TIMESHEETPUNCH.EVENTDATE,
ISNULL(VP_TIMESHEETPUNCH.INPUNCHDTM, VP_TIMESHEETPUNCH.STARTDTM) AS PUNCH1,
ISNULL(VP_TIMESHEETPUNCH.OUTPUNCHDTM, VP_TIMESHEETPUNCH.ENDDTM) AS PUNCH2,
MINORRULE.MINORRULENM,
VP_TIMESHEETPUNCH.TIMEINSECONDS,
VP_TIMESHEETPUNCH.STARTREASON,
(SELECT SUM(DISTINCT VP_TIMESHEETPUNCH.TIMEINSECONDS)
FROM WFC_PRD.dbo.TIMESHEETITEM,
WFC_PRD.dbo.VP_TIMESHEETPUNCH
WHERE TIMESHEETITEM.WORKEDSHIFTID = TIMESHEETITEM.WORKEDSHIFTID
AND 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')
AND VP_TIMESHEETPUNCH.PERSONNUM = '099136'
group by TIMESHEETITEM.WORKEDSHIFTID) AS A
FROM WFC_PRD.dbo.VP_TIMESHEETPUNCH,
WFC_PRD.dbo.VP_PERSON,
WFC_PRD.dbo.PRSNMINORRULEMM,
WFC_PRD.dbo.MINORRULE,
WFC_PRD.dbo.TIMESHEETITEM
WHERE VP_TIMESHEETPUNCH.PERSONID = VP_PERSON.PERSONID
AND VP_TIMESHEETPUNCH.TIMESHEETITEMID = TIMESHEETITEM.TIMESHEETITEMID
AND datediff(yy,VP_PERSON.BIRTHDTM,getdate()) < 18
AND VP_TIMESHEETPUNCH.PERSONID = PRSNMINORRULEMM.PERSONID
AND PRSNMINORRULEMM.MINORRULEID = MINORRULE.MINORRULEID
AND VP_TIMESHEETPUNCH.TMSHTITEMTYPEID not in (1, 2, 3)
AND VP_TIMESHEETPUNCH.LABORLEVELNAME4 <> ''
AND VP_TIMESHEETPUNCH.PAIDSW = 1
AND (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'
ORDER BY VP_TIMESHEETPUNCH.EVENTDATE , TIMESHEETITEM.WORKEDSHIFTID ASC

 

[Jdbenike]

Now from this I need to do three things. One, I need to get the sum function to work grouped by workedshiftID. So, right now it's just showing a sum for the first set of records all the way down the whole thing if you look at the picture attachment for column A. I need it to show a sum of @Timeinseconds grouped by @workedshiftid.

Then after that I need it not to show results greater then 21600 seconds only, and not the rest.

Then lastly, and the hardest part. Is that I need a calculation of break time between punches.
So, where @workedshiftid is the same represents a person shift through the day. It could be one continous shift with one line of results, or 3 lines with mutiple worked segments with breaks between.

I need to get @punch 1 - previous(@punch2) into some column named break time. I can do it in crystal and reporting services, but then I can't sum off of it. This is the main problem. I need a way to filter the results by the totaltimeinseconds above over 21600.. AND. I need to see if an employee has any break more then 30 minutes. If they do, not include them. If they dont. I need them to show in the results.

Maybe a join would work?
The only ways to group is workedshiftID and eventdate for these lines. That's it. So all the table names are on there that we need.

Help me.
Please.

 

[Jdbenike]

This is what the results look like so far.

 

[Jdbenike]

Just click on it once to zoom it.

 

[gmmastros]

You are hard coding a PERSONNUM in the subquery. Clearly, this is not what you want. Fix this and your sums will be better.

SELECT DISTINCT
            VP_PERSON.HOMELABORLEVELNAME2,
            TIMESHEETITEM.WORKEDSHIFTID,
            VP_PERSON.HOMELABORLEVELNAME3,
            VP_PERSON.HOMELABORLEVELNAME4,
            VP_PERSON.PERSONFULLNAME,
            VP_TIMESHEETPUNCH.PERSONNUM, 
            datediff(yy,VP_PERSON.BIRTHDTM,getdate()) AS AGE, 
            VP_TIMESHEETPUNCH.EVENTDATE, 
            ISNULL(VP_TIMESHEETPUNCH.INPUNCHDTM, VP_TIMESHEETPUNCH.STARTDTM) AS PUNCH1,
            ISNULL(VP_TIMESHEETPUNCH.OUTPUNCHDTM, VP_TIMESHEETPUNCH.ENDDTM) AS PUNCH2,
            MINORRULE.MINORRULENM,
            VP_TIMESHEETPUNCH.TIMEINSECONDS, 
            VP_TIMESHEETPUNCH.STARTREASON,
            (SELECT SUM(DISTINCT VP_TIMESHEETPUNCH.TIMEINSECONDS) 
                  FROM       WFC_PRD.dbo.TIMESHEETITEM,
                              WFC_PRD.dbo.VP_TIMESHEETPUNCH
                  WHERE TIMESHEETITEM.WORKEDSHIFTID = TIMESHEETITEM.WORKEDSHIFTID
                  AND  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')
                  AND  [!]VP_TIMESHEETPUNCH.PERSONNUM = '099136'[/!]
                  group by TIMESHEETITEM.WORKEDSHIFTID) AS A
 FROM   WFC_PRD.dbo.VP_TIMESHEETPUNCH,
            WFC_PRD.dbo.VP_PERSON,
              WFC_PRD.dbo.PRSNMINORRULEMM,
            WFC_PRD.dbo.MINORRULE,
            WFC_PRD.dbo.TIMESHEETITEM
 WHERE  VP_TIMESHEETPUNCH.PERSONID = VP_PERSON.PERSONID
 AND    VP_TIMESHEETPUNCH.TIMESHEETITEMID = TIMESHEETITEM.TIMESHEETITEMID
 AND    datediff(yy,VP_PERSON.BIRTHDTM,getdate()) < 18
 AND      VP_TIMESHEETPUNCH.PERSONID = PRSNMINORRULEMM.PERSONID
 AND      PRSNMINORRULEMM.MINORRULEID = MINORRULE.MINORRULEID
 AND      VP_TIMESHEETPUNCH.TMSHTITEMTYPEID not in (1, 2, 3)
 AND      VP_TIMESHEETPUNCH.LABORLEVELNAME4 <> '' 
 AND      VP_TIMESHEETPUNCH.PAIDSW = 1 
 AND      (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'
 ORDER BY VP_TIMESHEETPUNCH.EVENTDATE , TIMESHEETITEM.WORKEDSHIFTID ASC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think that line should be....

AND VP_TIMESHEETPUNCH.PERSONNUM = WFC_PRD.dbo.VP_PERSON.PERSONNUM

Of course, I could be wrong. I'm fairly certain that you need to have PersonNum in there, and it need to point to another table. You can't just take it out.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jdbenike]

When i take it out I get this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 

[Jdbenike]

The only thing that connects the lines of data is workedshiftid and eventid should be the same.

 

[Jdbenike]

Your right, I shouldn't of had that in there to begin with. I just forgot to space it out.

 

[gmmastros]

Can you show some sample data from TIMESHEETITEM and VP_TIMESHEETPUNCH?

Also... show the calculations that need to be made on these tables. By showing this information, it will help me understand the relationship between these tables, and allow me to help you better.

Currently, you are using a sub-query method to perform your calculations. Instead, I think you would benefit from a derived table solution. By showing sample data from those 2 tables, I should be able to show you how to build your query to get the results you desire.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jdbenike]

As sample data do you want me just to run a select * from it so it shows all the table names and some data it portrays?
Ill assume that

 

[gmmastros]

Yes. But I really only need to see a couple rows from each table. If you can, show related data (for a person, on a day or 2).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jdbenike]

http://i37.tinypic.com/2u61wdw.jpg

Is the timesheetitem one.. i ran just with select * from

and

http://i34.tinypic.com/2kjwc9.jpg

is the vp_timesheetpunch one i ran with

select * from vp_timesheetpunch
where (VP_TIMESHEETPUNCH.EVENTDATE >= '2008-04-01 00:00:00.000'
AND VP_TIMESHEETPUNCH.EVENTDATE < '2008-04-10 00:00:00.000')
AND VP_TIMESHEETPUNCH.PERSONNUM = '095032'

----- if you can't see it b/c of the size save the file off to your desktop and zoom in, it's big enough

 

[Jdbenike]

I didn't do a single person for data. Do you need me to do that for you?

 

[gmmastros]

It looks like your database engine is SQL2000. Is this right? If your database engine is SQL2000, the required code to get this to work will be different than the code for SQL2005.

If you are not sure...

Select @@Version

Post the output here.

Also, how do you identify breaks? From the looks of it, the StartDTM and EndDTM in the TimeSheetItem table correspond to WORK times. So, any gap in work times represents break time?

Example:

[tt]
WorkShiftId Start End
12345 2008-06-01 7:01 [!]2008-06-01 9:39[/!]
12345 [!]2008-06-01 9:57[/!] 2008-06-01 11:47
[/tt]

The difference between 9:57 and 9:39 is the break time, right?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jdbenike]

it's sql 2000

 

[Jdbenike]

Yes, that is correct.

god, your going to be a live saver for my job.

Some guy created this in crystal reports and it never even worked a few years back and now I got it thrown at me and ive been on it for at least a month and got no where and receiving a ton of pressure for it

 

[Jdbenike]

The main issue is not being able to use sum or max on aggregrate functions that use 'previous' in crystal or reporting services. So it's been like.. the impossible challenge for me.

 

[gmmastros]

To get the Break Time, we should be able to get the Min(StartTime) and Max(EndTime). The difference would be the total of WorkTime + BreakTime. Since we can simply SUM the seconds to get the total work time, we should be able to do this in a single query. Let's see....

Run this...

Select  WorkShiftId,
        Sum(DurationSecQty),
        Min(StartDTM),
        Max(EndDTM),
        DateDiff(Second, Min(StartDTM), Max(EndDTM)) - Sum(DurationSecQty) As BreakTime
From    @TimeSheetItem
Group By WorkShiftID

If I'm not mistaken, this should accurately calculate the BreakTime for you. If there are multiple breaks throughout the shift, this value should be the sum of the break time (not an individual break).

You should check this query to make sure it is working correctly. If it does, then I can show you how to make this become a derived table that you can use to link to your total query.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom