IIF Statement help - SUM OF value greater than or equal to

Question

I am stuck and need advice. This may be something simple, however, I am a beginner here and trying to learn as quickly as possible, so please bear with me. I have a table containing attendance data in "A" / "P" format. I am trying to show in a report how many people within a class have missed three or more times with a date range. For the most part I have the SQL statement doing all of the work with one exception. I can show a sum for how many times someone has missed, but I cannot figure out how to only show students with greater than 3 absences. I am entering the following in an expression on the column I want the data to return in:

=SUM(IIF(Fields!Attendance.Value ="A",1,NOTHING))

If I write the expression like this:

=SUM(IIF(Fields!Attendance.Value ="A",1,NOTHING))>=3

I get TRUE/FALSE returns. That would be ok if I could then figure out how to only shows the TRUE returns.

Any advice is appreciated!

My entire SQL statement - there are two other datasets that get called to pull data into this one.

SELECT     smgp_group.group_id
    , smgp_group.group_name
    , core_v_person_basic.last_name + ', ' + core_v_person_basic.nick_name as Name
    , core_v_person_basic.birth_date AS Birthdate
    , '*' + CONVERT(varchar, core_v_person_basic.person_id) + '*' AS person_id
    , core_v_person_basic.street_address_1
    , core_v_person_basic.city + ',' + core_v_person_basic.state + ' ' + core_v_person_basic.postal_code AS Citystatezip
    , core_v_person_basic.home_phone
    , core_v_person_basic.email
    , core_lookup.lookup_value AS [Group Role]
    , CATD.occurrence_end_time
    , CATD.Attendance as attendance
    , MAX(CATD.DateLastAttended) AS Datelastattended
    , CATD.occurrence_end_time AS [Start Date]
    , CATD.occurrence_end_time AS [End Date]
    , '*$' + CONVERT(varchar, CATD.occurrence_id) + '*' AS 'occurrence_id'
    , core_v_person_basic.cell_phone
FROM smgp_group 
    INNER JOIN cust_v_attendanceroll AS CATD 
    INNER JOIN core_lookup ON CATD.role_luid = core_lookup.lookup_id 
    INNER JOIN core_v_person_basic ON CATD.person_id = core_v_person_basic.person_id ON smgp_group.group_id = CATD.group_id
where CATD.occurrence_end_time between @startdate AND @enddate and smgp_group.group_id IN (@group)
GROUP BY smgp_group.group_id, smgp_group.group_name, core_v_person_basic.last_name,core_v_person_basic.nick_name, core_v_person_basic.birth_date, core_v_person_basic.street_address_1, 
                      core_v_person_basic.city + ',' + core_v_person_basic.state + ' ' + core_v_person_basic.postal_code, core_v_person_basic.home_phone, 
                      core_v_person_basic.email, core_lookup.lookup_value, CATD.occurrence_end_time, CATD.Attendance, CATD.occurrence_id, core_v_person_basic.person_id, 
                      core_v_person_basic.cell_phone
HAVING attendance = 'A' 
ORDER BY attendance

can you post your whole query and explain bit more. Do you just want to show records if someone has missed class more than 3 times? — DevelopmentIsMyPassion, Mar 18 '13 at 18:40
this query is too long. Is it possible to shorten it and only include essential columns. If possible post sql fiddle — DevelopmentIsMyPassion, Mar 18 '13 at 19:04
I may be able to when I get off work. I'm stuck behind the desk right now. — user2183577, Mar 18 '13 at 19:57

score 0 · Answer 1 · answered Mar 18 '13 at 20:12

Want to give this a try?

SideTrack: You might want to check: Both [Start Date] and [End Date] are set to CATD.occurrence_end_time

SELECT      *
FROM        (
        SELECT      smgp_group.group_id
                , smgp_group.group_name
                , core_v_person_basic.last_name + ', ' + core_v_person_basic.nick_name as Name
                , core_v_person_basic.birth_date AS Birthdate
                , '*' + CONVERT(varchar, core_v_person_basic.person_id) + '*' AS person_id
                , core_v_person_basic.street_address_1
                , core_v_person_basic.city + ',' + core_v_person_basic.state + ' ' + core_v_person_basic.postal_code AS Citystatezip
                , core_v_person_basic.home_phone
                , core_v_person_basic.email
                , core_lookup.lookup_value AS [Group Role]
                , CATD.occurrence_end_time
                , MAX(CATD.DateLastAttended) AS Datelastattended
                , CATD.occurrence_end_time AS [Start Date]
                , CATD.occurrence_end_time AS [End Date]
                , '*$' + CONVERT(varchar, CATD.occurrence_id) + '*' AS 'occurrence_id'
                , core_v_person_basic.cell_phone

                , COUNT(*) AS absentCount

        FROM        smgp_group 
                INNER JOIN cust_v_attendanceroll AS CATD 
                INNER JOIN core_lookup ON CATD.role_luid = core_lookup.lookup_id 
                INNER JOIN core_v_person_basic ON CATD.person_id = core_v_person_basic.person_id ON smgp_group.group_id = CATD.group_id
        WHERE       CATD.occurrence_end_time BETWEEN @startdate AND @enddate
                AND smgp_group.group_id IN (@group)
                AND CATD.Attendance = 'A'
        GROUP BY    smgp_group.group_id, smgp_group.group_name, core_v_person_basic.last_name,core_v_person_basic.nick_name, core_v_person_basic.birth_date, core_v_person_basic.street_address_1, 
                core_v_person_basic.city + ',' + core_v_person_basic.state + ' ' + core_v_person_basic.postal_code, core_v_person_basic.home_phone, 
                core_v_person_basic.email, core_lookup.lookup_value, CATD.occurrence_end_time, CATD.occurrence_id, core_v_person_basic.person_id, 
                core_v_person_basic.cell_phone
        ) AS tblSource
WHERE       absentCount>=3

the query won't run. I get a blank screen when I try to preview the report. I removed all of my experssions and added them back to be sure I wasn't missing anything. — user2183577, Mar 18 '13 at 21:06
what do you mean blank screen? are you running this on your SQL server or? It should at least give you some output. — jlee88my, Mar 19 '13 at 04:48
A blank screen from the report in Visual Studio and when run directly on SQL. The columns populate but data is not returned. — user2183577, Mar 19 '13 at 14:54
if the columns populate meaning that the sql is ok, no data... is it possible that you don't have anyone with absentCount>=3 within the startdate, enddate & group? — jlee88my, Mar 20 '13 at 11:25

IIF Statement help - SUM OF value greater than or equal to

1 Answers1