SQL Help

[cbrunny]

I'm trying to figure out how to do something that is probably relatively easy. I'm not an expert but I'm halfway good with SQL.

In english:

Columns:
Factor 1 (easily defined),
Factor 2 (easily defined),
Factor 3 (easily defined),
Factor 4:
Value A: if the count of Value B (for Factor 4) within Factors 1 and 2 or the count of Value C (for Factor 4) within Factors 1 and 2 is less than 5, Value A = 1
Value B: if Value A = 1, null else 2
Value C: if Value A = 1, null else 3

This calculation would logically loop back on itself which is probably why I can't figure out how to do it. Does anyone have any suggestions? This would be a summary of record (individual people) level data.

 

[nakedfrog]

Hm, you could try finagling a CASE WHEN THEN ELSE END in there, or write functions that accept the values and return what you're looking for, and call the functions in your select statement.

 

[cbrunny]

Solved. It took a rather long SQL code, but I've got the output coming the way I want it. Now I have to figure out the easiest way to rebuild this in Cognos 10! That'll be fun.

 

[seamadan]

If you don't mind, post your code or pseodocode, so we can see the solution. 🙂

 

[BOOGY_DOG]

Looks like you'd need a mess of functions in there.. it'd be a lot more clean if you could use PLSQL or TSQL.