5 Replies Latest reply on Dec 29, 2006 10:09 AM by muellertj

    compare and sum

    muellertj Level 1
      I need to compare individual records from a large database based on date and sample_number. If the sample from date1 (DD1) is smaller than the sample from date2 (DD2), I need to subtract the samples and divide by the number of days difference(DDIFF). If it is bigger or doesn't exist I want to skip it. Then I need to SUM all of the products and divide by the total number of products.

      I first locate the two dates to compare from a summary table, my query looks like this (THIS WORKS):
      <cfquery name="getdatedata" datasource="TEST">
      SELECT MAX(std.DataDate) as DD1,
      MIN(std.DataDate) as DD2,
      DateDiff(d, MIN(std.DataDate), MAX(std.DataDate)) AS DDiff
      FROM
      (select top 2 datadate
      FROM dairy.dbo.summarydata
      WHERE FarmId='#form.locationid#'
      AND
      datadate between dateAdd("d", -28, #datevalue#)
      and #datevalue#) AS std
      </cfquery>
        • 1. Re: compare and sum
          Dan Bracuk Level 5
          Well aren't you ambitious? There are more than one ways to accomplish this. You can make one trip to the db, or make two trips and use Q of Q. You should try both, the 2nd might actually run faster.

          For the 1st way, the syntax will be something like this:

          select (sample1 - sample2) / #getthedata.ddif# as num1
          from
          (select count(*) as sample1
          from yourtables
          where somedate = #getthedata.dd1# ) sq1,
          (select count(*) as sample2
          from yourtables
          where somedate = #getthedata.dd2# ) sq2

          where sample1 > sample2

          The part about summing the products and dividing by the number of products is too vague. You have to specify which products.
          • 2. Re: compare and sum
            MikerRoo Level 1
            This question is not clear. For example, the posted code will always have DD2 <= DD1.

            Attach or link to a nice sample of the starting data.
            Then attach or link to what you want the final output to be for that data.
            • 3. compare and sum
              BKBK Adobe Community Professional & MVP
              Your problem needs some clarification. It might not be well-defined.

              If the database table has just one column for sample_number, one for sample_measure and one for date, then there will be a problem of size. To see this, suppose we pick an arbitrary sample and date. There would be a very large number of rows whose sample_measure is larger. You would have to do the so-called Cartesian product, whereby you compare r rows with r-1 rows. The product r(r-1) increases more rapidly than the number of rows r. That is why working with Cartesian products on large tables is usually not an option.

              However, it could be that what you have is the other, better, model. The table has a column for sample_number, a column for sample_measure at date1 and a column for sample_measure at date2. Your SQL logic will then be something like Dan's suggestion, likely including an if-statement. The trouble with this approach is that you'll be into proprietary code. The SQL you write for MS SQL might no longer be valid when database server changes to MySQL.

              My suggestion is therefore to keep the SQL simple and standard. Then, do the comparisons and any min-max business in CFML.



              • 4. compare and sum
                muellertj Level 1
                The data base loooks like this: DataDate - Sample_number - Sample_Data
                There may be more than 75 Sample_numbers to match and subtract the Sample_data on.

                I need to match up the sample_number for DD1 and DD2 (ex. sample1 for DD1 needs to match up with sample1 for DD2) then subtract their Sample_Data values if sample_data for DD1 is larger than Sample_data for DD2. If the subtrtaction takes place then I need to divide the output by the DDIFF. This will give me a new value Sample_Diff.

                I then need to add all of the Sample_Diff's together and divide by the number of sample_diff's that were calculated to get an average of Sample_diff's.

                Does that help clarify things?
                • 5. Re: compare and sum
                  muellertj Level 1
                  I did get this working on my own. Here is the code if anyone wants to know.

                  <cfquery name="getdatedata" datasource="dairy">
                  SELECT MAX(std.DataDate) as DD1,
                  MIN(std.DataDate) as DD2,
                  DateDiff(d, MIN(std.DataDate), MAX(std.DataDate)) AS DDiff
                  FROM
                  (select top 2 datadate
                  FROM dairy.dbo.summarydata
                  WHERE FarmId='#form.farmid#'
                  AND
                  datadate between dateAdd("d", -28, #datevalue#)
                  and #datevalue#) AS std
                  </cfquery>
                  <cfoutput query="getdatedata">
                  #DD1#, #DD2#, #DDIFF#
                  </cfoutput><br />

                  <cfif getdatedata.DDiff LTE 0>
                  <cfset estgrowth="-">
                  <cfelse>

                  <cfquery name="getdataDD1" datasource="Dairy">
                  SELECT Paddock, DryMatter
                  FROM dairy.dbo.paddockdata
                  WHERE FarmID='#form.farmID#'
                  AND DataDate='#getdatedata.DD1#'
                  </cfquery>

                  <cfquery name="getdataDD2" datasource="Dairy">
                  SELECT Paddock, DryMatter
                  FROM dairy.dbo.paddockdata
                  WHERE FarmID='#form.farmID#'
                  AND DataDate='#getdatedata.DD2#'
                  </cfquery>

                  <cfset DD1numsamps="#getdataDD1.recordcount#">
                  <cfset DD2numsamps="#getdataDD2.recordcount#">
                  <cfset estgrowth=0>
                  <cfloop index="D1pad" From="1" to="#getdataDD1.recordcount#">
                  <cfloop index="D2pad" from="1" to="#getdataDD2.recordcount#">
                  <cfif #getdataDD1.drymatter[D1pad]# GT #getdataDD2.drymatter[D2pad]# AND #getdataDD1.paddock[D1pad]# EQ #getdataDD2.paddock[D2pad]#>
                  <cfset padavg[D1pad] = (#getdataDD1.drymatter[D1pad]#-#getdataDD2.drymatter[D2pad]#)/#getdatedata.DDIFF#>
                  <cfset estgrowth=#estgrowth#+#padavg[D1pad]#>
                  </cfif>
                  </cfloop>
                  </cfloop>
                  </cfif>