Please bear with me on this, this is quite difficult to explain and I am not sure if I am using the correct terminology. I need to do a rather complicated select. It is effectively a select statement which conditionally decides what field to use to filter the select. If a certain date field is not null, I need to check that the value in that field is within a certain range. Otherwise if that date field is null I need to check another field, int field, on the same table is within a certain range
seudocode: \[code\]If [Date] is not null Get sum (table.value) for rows Date >= dateValue and Date < dateValueElse Get sum (table.value) for rows Int >= intValue and Int < intValue\[/code\]My current attempt:\[code\]SELECT CASE WHEN a.Date IS NOT NULL THEN (SUM(CASE WHEN (a.Date >= cal.Date) THEN ABS(a.Value) ELSE 0 END)) ELSE (SUM(CASE WHEN (b.Days >= 0) THEN ABS(a.Value) ELSE 0 END) END AS 'A'\[/code\]Any ideas? Ask if you need more information. Thanks in advance.
seudocode: \[code\]If [Date] is not null Get sum (table.value) for rows Date >= dateValue and Date < dateValueElse Get sum (table.value) for rows Int >= intValue and Int < intValue\[/code\]My current attempt:\[code\]SELECT CASE WHEN a.Date IS NOT NULL THEN (SUM(CASE WHEN (a.Date >= cal.Date) THEN ABS(a.Value) ELSE 0 END)) ELSE (SUM(CASE WHEN (b.Days >= 0) THEN ABS(a.Value) ELSE 0 END) END AS 'A'\[/code\]Any ideas? Ask if you need more information. Thanks in advance.