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,
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 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-02' and applydate <= '2008-11-08' and a.paycodename = '00 TOTAL 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 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-02' and applydate <= '2008-11-08' and a.paycodename = '00 TOTAL 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,
a.startdtm,
a.enddtm,
(datepart(hh, a.startdtm) * 60) + datepart(n, a.startdtm) as StartDtmHours,
(datepart(hh, a.enddtm) * 60) + datepart(n, a.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
where a.personnum = b.personnum
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-02'
and e.schoolcalendarid = h.schoolcalendarid
and i.schoolcalendardt = DateAdd(day, 1, '2008-11-02')
and e.schoolcalendarid = i.schoolcalendarid
and j.schoolcalendardt = DateAdd(day, 2, '2008-11-02')
and e.schoolcalendarid = j.schoolcalendarid
and k.schoolcalendardt = DateAdd(day, 3, '2008-11-02')
and e.schoolcalendarid = k.schoolcalendarid
and l.schoolcalendardt = DateAdd(day, 4, '2008-11-02')
and e.schoolcalendarid = l.schoolcalendarid
and m.schoolcalendardt = DateAdd(day, 5, '2008-11-02')
and e.schoolcalendarid = m.schoolcalendarid
and n.schoolcalendardt = DateAdd(day, 6, '2008-11-02')
and e.schoolcalendarid = n.schoolcalendarid
and a.paycodename not like '00 %'
and a.paycodename not like '24 %'
and a.applydate >= '2008-11-02'
and a.applydate <='2008-11-08'
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,
a.startdtm,
a.enddtm
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, 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.EndDtmHours >= MRD.LATESTENDTM THEN 'Worked passed ' + CAST(MRD.LATESTENDTM / 60 as varchar(5))+ ':00 at night'
ELSE 'No Infraction'
END AS Infraction,
CASE WHEN i.StartDtmHours <= MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before ' + CAST(MRD.EARLIESTSTARTTM /60 as varchar(5))+ ':00 in the morning'
ELSE 'No Infraction'
END AS Infraction1,
CASE WHEN i.DayTotalMinutes >= MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(MRD.MAXHOURSNUM / 60 as varchar(5))+ ' hours for one day'
ELSE 'No Infraction'
END AS Infraction2,
CASE WHEN i.TotalWeekHoursMinutes >= MRW.MAXHOURSNUM THEN 'Worked more than ' + CAST(MRW.MAXHOURSNUM / 60 as varchar(5))+ ' total week hours'
ELSE 'No Infraction'
END AS Infraction3
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
-----------------------------------------------------
Above is the code.
The part I need help with is ->
CASE WHEN i.EndDtmHours >= MRD.LATESTENDTM THEN 'Worked passed ' + CAST(MRD.LATESTENDTM / 60 as varchar(5))+ ':00 at night'
ELSE 'No Infraction'
END AS Infraction,
CASE WHEN i.StartDtmHours <= MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before ' + CAST(MRD.EARLIESTSTARTTM /60 as varchar(5))+ ':00 in the morning'
ELSE 'No Infraction'
END AS Infraction1,
CASE WHEN i.DayTotalMinutes >= MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(MRD.MAXHOURSNUM / 60 as varchar(5))+ ' hours for one day'
ELSE 'No Infraction'
END AS Infraction2,
CASE WHEN i.TotalWeekHoursMinutes >= MRW.MAXHOURSNUM THEN 'Worked more than ' + CAST(MRW.MAXHOURSNUM / 60 as varchar(5))+ ' total week hours'
ELSE 'No Infraction'
END AS Infraction3
-----------------------------------------------------
Iam trying to filter so that if Infraction, Infraction1, Infraction2, and Infraction3 fields = 'No Infraction' Then don't show the record. Only if all 4 at the same time = 'No Infraction'. Or a way to put those 4 case statements into one infraction. Yet, the problem with this is that one person could have two of those infractions hit, and would only flag the first one and not hit the 2nd.
Thanks in advance for any help. If you reply and have any questions ill be sure to reply quickly. It's important. Also, expect many stars for help. =P
( 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,
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 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-02' and applydate <= '2008-11-08' and a.paycodename = '00 TOTAL 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 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-02' and applydate <= '2008-11-08' and a.paycodename = '00 TOTAL 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,
a.startdtm,
a.enddtm,
(datepart(hh, a.startdtm) * 60) + datepart(n, a.startdtm) as StartDtmHours,
(datepart(hh, a.enddtm) * 60) + datepart(n, a.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
where a.personnum = b.personnum
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-02'
and e.schoolcalendarid = h.schoolcalendarid
and i.schoolcalendardt = DateAdd(day, 1, '2008-11-02')
and e.schoolcalendarid = i.schoolcalendarid
and j.schoolcalendardt = DateAdd(day, 2, '2008-11-02')
and e.schoolcalendarid = j.schoolcalendarid
and k.schoolcalendardt = DateAdd(day, 3, '2008-11-02')
and e.schoolcalendarid = k.schoolcalendarid
and l.schoolcalendardt = DateAdd(day, 4, '2008-11-02')
and e.schoolcalendarid = l.schoolcalendarid
and m.schoolcalendardt = DateAdd(day, 5, '2008-11-02')
and e.schoolcalendarid = m.schoolcalendarid
and n.schoolcalendardt = DateAdd(day, 6, '2008-11-02')
and e.schoolcalendarid = n.schoolcalendarid
and a.paycodename not like '00 %'
and a.paycodename not like '24 %'
and a.applydate >= '2008-11-02'
and a.applydate <='2008-11-08'
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,
a.startdtm,
a.enddtm
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, 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.EndDtmHours >= MRD.LATESTENDTM THEN 'Worked passed ' + CAST(MRD.LATESTENDTM / 60 as varchar(5))+ ':00 at night'
ELSE 'No Infraction'
END AS Infraction,
CASE WHEN i.StartDtmHours <= MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before ' + CAST(MRD.EARLIESTSTARTTM /60 as varchar(5))+ ':00 in the morning'
ELSE 'No Infraction'
END AS Infraction1,
CASE WHEN i.DayTotalMinutes >= MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(MRD.MAXHOURSNUM / 60 as varchar(5))+ ' hours for one day'
ELSE 'No Infraction'
END AS Infraction2,
CASE WHEN i.TotalWeekHoursMinutes >= MRW.MAXHOURSNUM THEN 'Worked more than ' + CAST(MRW.MAXHOURSNUM / 60 as varchar(5))+ ' total week hours'
ELSE 'No Infraction'
END AS Infraction3
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
-----------------------------------------------------
Above is the code.
The part I need help with is ->
CASE WHEN i.EndDtmHours >= MRD.LATESTENDTM THEN 'Worked passed ' + CAST(MRD.LATESTENDTM / 60 as varchar(5))+ ':00 at night'
ELSE 'No Infraction'
END AS Infraction,
CASE WHEN i.StartDtmHours <= MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before ' + CAST(MRD.EARLIESTSTARTTM /60 as varchar(5))+ ':00 in the morning'
ELSE 'No Infraction'
END AS Infraction1,
CASE WHEN i.DayTotalMinutes >= MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than ' + CAST(MRD.MAXHOURSNUM / 60 as varchar(5))+ ' hours for one day'
ELSE 'No Infraction'
END AS Infraction2,
CASE WHEN i.TotalWeekHoursMinutes >= MRW.MAXHOURSNUM THEN 'Worked more than ' + CAST(MRW.MAXHOURSNUM / 60 as varchar(5))+ ' total week hours'
ELSE 'No Infraction'
END AS Infraction3
-----------------------------------------------------
Iam trying to filter so that if Infraction, Infraction1, Infraction2, and Infraction3 fields = 'No Infraction' Then don't show the record. Only if all 4 at the same time = 'No Infraction'. Or a way to put those 4 case statements into one infraction. Yet, the problem with this is that one person could have two of those infractions hit, and would only flag the first one and not hit the 2nd.
Thanks in advance for any help. If you reply and have any questions ill be sure to reply quickly. It's important. Also, expect many stars for help. =P