compare and sum

[Tonyam]

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>

[Dan_Bracuk]

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.

[MikerRoo]

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.

[BKBK]

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.

[Tonyam]

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?

[Tonyam · Accepted solution]

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>