This content has been marked as final. Show 5 replies
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
(select count(*) as sample1
where somedate = #getthedata.dd1# ) sq1,
(select count(*) as sample2
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.
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.
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.
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?
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
(select top 2 datadate
datadate between dateAdd("d", -28, #datevalue#)
and #datevalue#) AS std
#DD1#, #DD2#, #DDIFF#
<cfif getdatedata.DDiff LTE 0>
<cfquery name="getdataDD1" datasource="Dairy">
SELECT Paddock, DryMatter
<cfquery name="getdataDD2" datasource="Dairy">
SELECT Paddock, DryMatter
<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#>