DECLARE @ITbl TABLE
( laborlevelname3 VARCHAR(50),
laborlevelname4 VARCHAR(50),
personfullname VARCHAR(64),
personnum VARCHAR(15),
Age INT,
applydate DATETIME,
personid INT,
TotalSecondsDay INT,
ShiftTotal VARCHAR(5),
DayTotal VARCHAR(5),
TotalWeekHours VARCHAR(5),
ShiftTotalMinutes VARCHAR(5),
DayTotalMinutes DECIMAL(8,2),
TotalWeekHoursMinutes DECIMAL(8,2),
minorrulenm VARCHAR(50),
minorruleid INT,
schoolcalendarid INT,
schoolcalendardt DATETIME,
NextSchoolCalendardt DATETIME,
CurrSCtypid TINYINT,
NextSCtypid TINYINT,
MinorDayTypeID TINYINT,
MinorWeekTypeID TINYINT,
nightstartminnum INT,
nightendminnum INT,
schclndrdaytypid1 INT,
schclndrdaytypid2 INT,
schclndrdaytypid3 INT,
schclndrdaytypid4 INT,
schclndrdaytypid5 INT,
schclndrdaytypid6 INT,
schclndrdaytypid7 INT,
StartDtm DATETIME,
EndDtm DATETIME,
InPunchDTM DATETIME,
OutPunchDtm DATETIME,
StartDtmHours DECIMAL(8,2),
EndDtmHours DECIMAL(8,2)
)
INSERT INTO @ITbl
select distinct
a.laborlevelname3,
a.laborlevelname4,
a.personfullname,
a.personnum,
(CONVERT(INT, CONVERT(VARCHAR(8), GetDate(), 112)) - CONVERT(INT, CONVERT(VARCHAR(8), b.BIRTHDTM, 112))) /10000 As Age,
a.applydate,
b.personid,
sum(a.timeinseconds) AS TotalSecondsDay,
Convert(VarChar(5), DateAdd(Minute, sum(a.timeinseconds)/60, 0), 108) As ShiftTotal,
Convert(VarChar(5), DateAdd(Minute, (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate = f.schoolcalendardt and a.paycodename ='00 TOTAL Worked Hours' group by a.personnum)/60,0),108) As DayTotal,
Convert(VarChar(5), DateAdd(Minute, (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate >= '2008-11-30' and applydate <='2008-12-06' and a.paycodename = '00 TOTAL Worked Hours' group by personnum)/60,0),108) As TotalWeekHours,
Convert(VarChar(5), sum(a.timeinseconds) / 60) As ShiftTotalMinutes,
Convert(VarChar(5), (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate = f.schoolcalendardt and a.paycodename = '00 TOTAL Worked Hours' group by a.personnum) / 60) As DayTotalMinutes,
Convert(Varchar(5), (select sum(a.timeinseconds) from vp_totals a where a.personnum = b.personnum and a.applydate >='2008-11-30' and applydate <='2008-12-06' and a.paycodename = '00 TOTAL Worked Hours' group by personnum) / 60) As TotalWeekHoursMinutes,
d.minorrulenm,
d.minorruleid,
f.schoolcalendarid,
f.schoolcalendardt,
DateAdd(day, 1, f.schoolcalendardt) as NextSchoolcalendardt,
f.schclndrdaytypid as fsch,
g.schclndrdaytypid as gsch,
CASE WHEN f.schclndrdaytypid = 0 AND g.schclndrdaytypid = 0 THEN 3
WHEN f.schclndrdaytypid = 0 AND g.schclndrdaytypid = 1 THEN 2
WHEN f.schclndrdaytypid = 1 AND g.schclndrdaytypid = 0 THEN 1
WHEN f.schclndrdaytypid = 1 AND g.schclndrdaytypid = 1 THEN 0
WHEN f.schclndrdaytypid = 2 AND g.schclndrdaytypid = 2 THEN 4
END AS MinorDayTypeID,
CASE WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 2 THEN 0
WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid > 10 THEN 1
WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 7 THEN 2
WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 3 THEN 3
WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 4 THEN 4
WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 5 THEN 5
WHEN h.schclndrdaytypid + i.schclndrdaytypid + j.schclndrdaytypid + k.schclndrdaytypid + l.schclndrdaytypid + m.schclndrdaytypid + n.schclndrdaytypid = 6 THEN 6
END AS MinorWeekTypeID,
d.nightstartminnum,
d.nightendminnum,
h.schclndrdaytypid as hsch,
i.schclndrdaytypid as isch,
j.schclndrdaytypid as jsch,
k.schclndrdaytypid as ksch,
l.schclndrdaytypid as lsch,
m.schclndrdaytypid as msch,
n.schclndrdaytypid as nsch,
ISNULL(o.inpunchdtm, o.STARTDTM),
ISNULL(o.outpunchdtm, o.ENDDTM),
o.inpunchdtm,
o.outpunchdtm,
--(datepart(hh, o.inpunchdtm) * 60) + datepart(n, o.inpunchdtm) as StartDtmHours,
--(datepart(hh, o.outpunchdtm) * 60) + datepart(n, o.outpunchdtm) as EndDtmHours
ISNULL((datepart(hh, o.inpunchdtm) * 60) + datepart(n, o.inpunchdtm), (datepart(hh, o.startdtm) * 60) + datepart(n, o.startdtm)) as StartDtmHours,
ISNULL((datepart(hh, o.outpunchdtm) * 60) + datepart(n, o.outpunchdtm),(datepart(hh, o.enddtm) * 60) + datepart(n, o.enddtm)) as EndDtmHours
from vp_totals a,
vp_person b,
PRSNMINORRULEMM c,
minorrule d,
PRSNSCHCLNDRMM e,
SCHOOLCLNDRDAY f,
SCHOOLCLNDRDAY g,
SCHOOLCLNDRDAY h,
SCHOOLCLNDRDAY i,
SCHOOLCLNDRDAY j,
SCHOOLCLNDRDAY k,
SCHOOLCLNDRDAY l,
SCHOOLCLNDRDAY m,
SCHOOLCLNDRDAY n,
vp_timesheetpunch o
where a.personnum = b.personnum
and a.timesheetitemid = o.timesheetitemid
and b.personid = c.personid
and c.minorruleid = d.minorruleid
and b.personid = e.personid
and e.schoolcalendarid = f.schoolcalendarid
and f.schoolcalendardt = a.applydate
and e.schoolcalendarid = g.schoolcalendarid
and g.schoolcalendardt = DateAdd(day, 1, f.schoolcalendardt)
and h.schoolcalendardt = '2008-11-30'
and e.schoolcalendarid = h.schoolcalendarid
and i.schoolcalendardt = DateAdd(day, 1,'2008-11-30')
and e.schoolcalendarid = i.schoolcalendarid
and j.schoolcalendardt = DateAdd(day, 2, '2008-11-30')
and e.schoolcalendarid = j.schoolcalendarid
and k.schoolcalendardt = DateAdd(day, 3, '2008-11-30')
and e.schoolcalendarid = k.schoolcalendarid
and l.schoolcalendardt = DateAdd(day, 4, '2008-11-30')
and e.schoolcalendarid = l.schoolcalendarid
and m.schoolcalendardt = DateAdd(day, 5, '2008-11-30')
and e.schoolcalendarid = m.schoolcalendarid
and n.schoolcalendardt = DateAdd(day, 6,'2008-11-30')
and e.schoolcalendarid = n.schoolcalendarid
and a.paycodename not like '00 %'
and a.paycodename not like '24 %'
and a.paycodename not like '04 %'
and a.paycodename not like '08 %'
and a.paycodename not like '61 %'
and a.paycodename not like '55 %'
and a.paycodename not like '03 %'
and a.paycodename not like '22 %'
and a.paycodename not like '17 %'
and a.paycodename not like '41 %'
and a.paycodename not like '08 %'
and a.paycodename not like '18 %'
and a.paycodename not like '06 %'
and a.paycodename not like '11 %'
and a.paycodename not like '12 %'
and a.paycodename not like '13 %'
and a.paycodename not like '14 %'
and a.paycodename not like '56 %'
and a.paycodename not like '57 %'
and a.paycodename not like '58 %'
and a.paycodename not like '62 %'
and a.paycodename not like '63 %'
and a.paycodename not like '94 %'
and a.applydate >= '2008-11-30'
and a.applydate <='2008-12-06'
and (CONVERT(INT, CONVERT(VARCHAR(8), GetDate(), 112)) - CONVERT(INT, CONVERT(VARCHAR(8), b.BIRTHDTM, 112))) /10000 < 18
group by a.personfullname,
b.PERSONNUM,
a.laborlevelname3,
a.laborlevelname4,
a.personnum,
b.birthdtm,
a.applydate,
d.minorrulenm,
d.minorruleid,
b.personid,
f.schoolcalendarid,
f.schoolcalendardt,
f.schclndrdaytypid,
d.nightstartminnum,
d.nightendminnum,
g.SCHCLNDRDAYTYPID,
h.schclndrdaytypid,
i.schclndrdaytypid,
j.schclndrdaytypid,
k.schclndrdaytypid,
l.schclndrdaytypid,
m.schclndrdaytypid,
n.schclndrdaytypid,
o.startdtm,
o.enddtm,
o.inpunchdtm,
o.outpunchdtm
SELECT laborlevelname3, laborlevelname4, personfullname, personnum, Age, applydate,personid, TotalSecondsDay, ShiftTotal, DayTotal, TotalWeekHours,
ShiftTotalMinutes, DayTotalMinutes, TotalWeekHoursMinutes, minorrulenm, i.minorruleid, schoolcalendarid, schoolcalendardt, NextSchoolCalendardt,
CurrSCtypid, NextSCtypid, MinorDayTypeID, MinorWeekTypeID, nightstartminnum, nightendminnum, schclndrdaytypid1, schclndrdaytypid2,
schclndrdaytypid3, schclndrdaytypid4, schclndrdaytypid5, schclndrdaytypid6,schclndrdaytypid7,startdtm, enddtm, inpunchdtm, outpunchdtm, StartDtmHours,EndDtmHours,
MRD.MINORRULEID, MRD.MINORRLDAYTYPEID, MRD.MINORAGENUM, MRD.MAXHOURSNUM, MRD.EARLIESTSTARTTM, MRD.LATESTENDTM, MRD.SAFETYMARGINNUM, MRW.MINORRULEID,
MRW.MINORRLWEEKTYPID, MRW.MAXHOURSNUM, MRW.MAXDAYSNUM, MRW.MAXCONSECDAYSNUM, MRW.MAXNIGHTSNUM, MRW.MAXCONSECNIGHTSNUM,
CASE WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM AND i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt AND i.StartDtmHours < MRD.EARLIESTSTARTTM AND i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) + ' and ' + 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.StartDtmHours < MRD.EARLIESTSTARTTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) + ' and ' + 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt and i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) + ' and ' + 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day'
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM and i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' + ' and ' + 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt and i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.EndDtmHours > MRD.LATESTENDTM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day' + ' and ' + 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
WHEN i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt and i.EndDtmHours > MRD.LATESTENDTM THEN 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108) + ' and ' + 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours'
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day'
WHEN i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before ' + Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)
WHEN i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked passed ' + Convert(VarChar(5), DateAdd(Minute, MRD.LATESTENDTM, 0), 108)
ELSE 'No Infraction'
END AS Infraction
FROM @ITbl i, MinorRuleDay MRD, MinorRuleWeek MRW
WHERE
i.Age = MRD.MinorAgeNum AND
i.MinorRuleId = MRD.MinorRuleID AND
i.MinorDayTypeID = MRD.MinorRLDayTypeID AND
i.Age = MRW.MinorAgeNum AND
i.MinorRuleId = MRW.MinorRuleID AND
i.MinorWeekTypeID = MRW.MinorRLWeekTypID
order by i.personfullname, i.startdtm