Hi - hopefully someone her can help me straighten out this query. I have two tables from which I'm 'retrieving' records to add to a third table.
Table 1 has three columns ([TheDate], [A], [B], [C]) and contains approximately 20 records per minute.
Table 2 has two columns ([TheDate]. [D], [E]) and contains approximately 1 record per minute.
Table 3 (#TempRes) is a table with six columns ([TheDate], [A], [B], [C], [D], [E]) containing all the records in tables 1 and 2.
Below is my SQL command for returning information from #TempRes that groups (and averages) the information from Table 3 by minute - but the problem with my command is that in the 'HAVING' clause, where the COUNT([A]) > 19 text is, I really want something
like 'COUNT([A] where A>0) > 19'. (That is, I only want to include records that have greater than 19 non-zero A values). The function is merely a function to convert the [TheDate] column to always have '00' seconds (ie, the result of the
function on '14:21:43' would be '14:21:00').
Any idea how I can do this, please?
CASE WHEN SUM([B]) IS NULL OR SUM([B]) = 0 THEN 0 ELSE SUM([A] * [B])/SUM([B]) END,
CASE WHEN SUM([B]) >100 THEN 100 ELSE SUM([B]) END,
View Complete Post