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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Code With Filtering

Status
Not open for further replies.
Jdbenike

Jdbenike

MIS
Sep 11, 2008
74
US
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
 
Sort by date Sort by votes
I'm not going to read through all of your code because it's huge and you didn't use the code tag so it's not formatted and hard to read.

But anyhow, the SIMPLEST solution to code is to wrap it in a subquery and add a where clause. For example:
Code: 
  SELECT * FROM 
  (Your Query Code Here) x
   WHERE Infraction1 <> 'No Infraction' OR Infraction2 <> 'No Infraction' OR Infraction3 <> 'No Infraction' OR Infraction4 <> 'No Infraction'
  ORDER BY Something

This shouldn't kill performance either if your resultset is fairly small.

But another, more time consuming and potentially better way is to combine your CASE statements in the where clause of your original query. Fortunately, your CASE statements aren't that large. For example:

Code: 
WHERE some stuff...
AND ((i.EndDtmHours >= MRD.LATESTENDTM) OR (i.StartDtmHours <= MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt) OR (i.DayTotalMinutes >= MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt) OR (i.TotalWeekHoursMinutes >= MRW.MAXHOURSNUM))
 
Upvote 0 Downvote
[ignore]
Code: 
Put your code here
[/ignore]

Full list here: TGML

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Code: 
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


Any further suggestions? I can't do the select statement outside of the declare statement like that. Maybe I can't use a case statment for this section to filter. Yet, it has to be in the bottom half of the code.
 
Upvote 0 Downvote
I don't think I'm truly clear on what you want.

Please show some sample data and what you want as sample results.

And please start using ANSI Standard joins, those old style joins are a mess to read and will cause you problems when you want to start using outer joins.

River guy is on the right track I suspect. His solution is called a derived table and it is often the best solution to these types of issues.

On a side note, most employers like us to work more than our scheduled number of hours!It wouldn't be an infraction. Your workers must be paid hourly not salaried.

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
Sorry it's not clear.

I figured it out on my own for that part.

Sorry I wasen't using the correct standards that you wanted me to use for my program.

You're side not is irrelevant because this is for catching minor infractions for underage people, but thanks for your opinion.

Next question.

Code: 
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'

This value brings back. " Worked more than 4 hours for one day"

Now with the number value. It won't show decimal places. It's supposed to be 4.5

How would I change that code snippet so that it brought back the decimal place needed?
Thanks
 
Upvote 0 Downvote
If you divide an integer by an integer, you get an integer. So you need to change this:

Code: 
WHEN i.DayTotalMinutes >= MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than '  + CAST(CONVERT(DECIMAL(18,1), MRD.MAXHOURSNUM) / 60 as varchar(5))+ ' hours for one day'
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
332
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
358
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
229
Mike Lewis
Mike Lewis
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
606
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
205
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top