Skip to main content
C
Cozmo2
Inspiring
September 23, 2013
Question

Reporting: If statement and Sum statement

  • September 23, 2013
  • 1 reply
  • 1136 views

I have an instructors schedule that shows each time a course is taught for example:

Course   Credits   Day

AAA123       3       Mon.

                             Wed.

The Credit only shows the first time (Print When Condition: calc.counter IS 1). I need to total the credits for each course when I use the Sum(credits) function it totals for each line (the above ezample would show a total of 6), I only want to sum if calc.counter EQ 1

This topic has been closed for replies.

1 reply

EddieLotter
EddieLotter
Inspiring
September 23, 2013

Please show your SQL query and also how you are calculating "calc.counter".

C
Cozmo2Author
Inspiring
September 23, 2013

My query looks like this:

<cfquery name="CFReportDataQuery" datasource="#session.ds#">

SELECT crssect."sch_yr", crssect."session", crssect."section", crssect."sp_taken", crssect."cancel", crssect."lead_fac",

crssect."sid", name."soc_sec", name."last_name", name."first_name", name."mi", name."suffix", course."course",

course."crs_txt", course."credits", course."crs_id", practicl."room_cod", practicl."start_hr", practicl."start_min",

practicl."start_ampm", practicl."end_hr", practicl."end_min", practicl."end_ampm", semester."sm_desc", sysvar."title",

dayweek."day_rid", dayweek."day_cod"

FROM

    { oj (((((((((("crssect" crssect INNER JOIN "course" course ON

        crssect."crs_id" = course."crs_id")

     INNER JOIN "rpt_sem" rpt_sem ON

        crssect."semester" = rpt_sem."semester")

     INNER JOIN "rpt_schy" rpt_schy ON

        crssect."sch_yr" = rpt_schy."sch_yr")

     INNER JOIN "name" name ON

        crssect."lead_fac" = name."soc_sec")

     INNER JOIN "semester" semester ON

        crssect."semester" = semester."semester")

     LEFT OUTER JOIN "practicl" practicl ON

        crssect."sid" = practicl."sid")

     INNER JOIN "sysvar" sysvar ON

        course."crs_id" <> sysvar."title")

     INNER JOIN "rpt_dept" rpt_dept ON

        name."dept_cod" = rpt_dept."dept_cod")

     INNER JOIN "rpt_div" rpt_div ON

        name."div_cod" = rpt_div."div_cod")

     INNER JOIN "rpt_camp" rpt_camp ON

        name."camp_cod" = rpt_camp."camp_cod")

     LEFT OUTER JOIN "dayweek" dayweek ON

        practicl."day_cod" = dayweek."day_cod"}

WHERE

    crssect."cancel" = 0 AND

    rpt_sem."token" = '#session.token#' AND

    rpt_schy."token" = '#session.token#' AND

    crssect."lead_fac" LIKE '#client.idnum#' AND

    rpt_div."token" = '#session.token#' AND

    rpt_dept."token" = '#session.token#' AND

    rpt_camp."token" = '#session.token#'

    UNION

SELECT crssect."sch_yr", crssect."session", crssect."section", crssect."sp_taken", crssect."cancel", crssect."lead_fac",

crssect."sid", name."soc_sec", name."last_name", name."first_name", name."mi", name."suffix", course."course",

course."crs_txt", course."credits", course."crs_id", practicl."room_cod", practicl."start_hr", practicl."start_min",

practicl."start_ampm", practicl."end_hr", practicl."end_min", practicl."end_ampm", semester."sm_desc",

sysvar."title", dayweek."day_rid", dayweek."day_cod"

FROM

    { oj (((((((((((("crssect" crssect INNER JOIN "course" course ON

        crssect."crs_id" = course."crs_id")

     INNER JOIN "rpt_sem" rpt_sem ON

        crssect."semester" = rpt_sem."semester")

     INNER JOIN "rpt_session" rpt_session ON

        crssect."session" = rpt_session."session_code")

     INNER JOIN "rpt_schy" rpt_schy ON

        crssect."sch_yr" = rpt_schy."sch_yr")

     INNER JOIN."practssn" practssn ON

        crssect."sid" = practssn."sid")

     INNER JOIN "name" name ON

        practssn."ssn" = name."soc_sec")

     INNER JOIN "semester" semester ON

        crssect."semester" = semester."semester")

     LEFT OUTER JOIN "practicl" practicl ON

        crssect."sid" = practicl."sid")

     INNER JOIN "sysvar" sysvar ON

        course."crs_id" <> sysvar."title")

     INNER JOIN "rpt_dept" rpt_dept ON

        name."dept_cod" = rpt_dept."dept_cod")

     INNER JOIN "rpt_div" rpt_div ON

        name."div_cod" = rpt_div."div_cod")

     INNER JOIN "rpt_camp" rpt_camp ON

        name."camp_cod" = rpt_camp."camp_cod")

     LEFT OUTER JOIN "dayweek" dayweek ON

        practicl."day_cod" = dayweek."day_cod"}

WHERE

    crssect."cancel" = 0 AND

    rpt_sem."token" = '#session.token#' AND

    rpt_session."token" = '#session.token#' AND

    rpt_schy."token" = '#session.token#' AND

    practssn."ssn" LIKE '#client.idnum#' AND

    rpt_div."token" = '#session.token#' AND

    rpt_dept."token" = '#session.token#' AND

    rpt_camp."token" = '#session.token#'

ORDER BY name."last_name", name."first_name", name."soc_sec", crssect."session",course."course", crssect."section", dayweek."day_rid", practicl."start_ampm", practicl."start_hr"

  </cfquery>

calc.counter - I count the number of course found and zero the counter when the soc_sec changes

EddieLotter
EddieLotter
Inspiring
September 23, 2013

The quickest solution I can think of, off the top of my head, would be to have another calc field that stores the value of course.credits when course.course changes.

When course.course changes, you can use the new calc field to print the total and then reset the new calc field.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices