Tuesday's fuzzy head...

[cheuschober]

Agregation between different values of a multiple key

Okay, so today's fuzzy headed mystery applies to a three field wonder involving a key (combined with date to be pk), a numeric (semester) date, and a value.

Table 1
----------
1 20041 3
1 20042 3
3 20042 4
4 20041 2
5 20041 4
----------

What I need to be able to do right now is select only the keys where the difference between the values of semesters of a given year is > 0.

Right now I don't even have a bone to know where to start sniffing. Any help would be greatly appreciated.

~Chad

 

[KenHigg]

In this sample data, on the first row, you have '1'. Is there another field (a date field), that is combined with it to make the pk? Or is it combined with the '20041' to mak up the pk?

1 20041 3
1 20042 3
3 20042 4
4 20041 2
5 20041 4

kh

 

[cheuschober]

In this sample data, on the first row, you have '1'. Is there another field (a date field), that is combined with it to make the pk? Or is it combined with the '20041' to mak up the pk?

1 20041 3
1 20042 3
3 20042 4
4 20041 2
5 20041 4

kh

the 20041 is the numeric date field ((year*10)+semester)

The pk for the first record would be 1 and 20041

 

[KenHigg]

So, in this case, you'd see all these rows based on the test below?

1 20041 3
4 20041 2
5 20041 4
----------
        9


1 20042 3
3 20042 4
----------
        7

???
kh

 

[cheuschober]

Actually, I'm attempting to basically group by the first field but create a difference on the third field.

The issue is that I need to ensure that the difference is sorted as being the "greater" semester value subtracting the "lesser"

Theoretically, my third field, the one I called [value] is monotonic (in which case I could simply apply abs(dif([value])), but I can't be certain of it.

Out of the example dataset (my fault, really) I gave a rather poor example as none of the first fields have resultant differences between value fields greater than 0.

One case that would work would be, say:

6 20041 3
6 20042 4

All I want returned is the first field (for a later count)

I hope this clarify's. Thanks for the help Ken.

~Chad

 

[KenHigg]

Could you just work in a <>0 thing somewhere, instead of a >0?

kh

 

[cheuschober]

Sadly not, ken.

The source table for this query had a multiple field key.

What I want is a query that basically creates a single field key (the first field of the mfk).

So within each of what I'll call the 'new key' there could be one or two semesters and an associated value per semester. I want to extract those key fields in which the greater semester's related value minus the lesser semester's related value is greater than zero. So somewhere I have extract the values of a lesser semester to assign a lesser semester value a negative when I sum. But I'm quite confused how to go about it.

I think the answer lies somewhere in subqueries (which I'm none to familiar with though sql doesn't frighten me).

 

[KenHigg]

Chad -

I have to bug out in a few minutes...

Could you split out a couple hundred rows in a seperate db and post it and maybe I can look at it tomorrow?

I'm sure I'm making this harder than it really is - in the mean time, maybe someone else can give it a shot.

kh

 

[cheuschober]

Will do, Ken. Theoretically our database manager here should be back before tommorrow, it's just holding things up on our end.

But if he's got nothing I'll throw a sample up.

Thanks for the patience.
~Chad