I hate my database...

[gjsaturday]

but it's all I have to work with...

in this table
the fields correlate to days of the week...
tb1
id m t w th f
1 0 1 0 1 0
2 1 0 1 0 1
3 0 2 0 0 2
4 2 0 2 0 2

tb2
id date in out
1 03/21/2005 0 6
2 03/21/2005 10 5
3 03/21/2005 0 10
4 03/21/2005 30 2
1 03/22/2005 15 6
2 03/22/2005 0 3
3 03/22/2005 20 5
4 03/22/2005 0 8
1 03/23/2005 0 2
2 03/23/2005 12 2
3 03/23/2005 0 3
4 03/23/2005 50 4
1 03/24/2005 13 2
2 03/24/2005 0 2
3 03/24/2005 0 3
4 03/24/2005 0 4
1 03/25/2005 0 2
2 03/25/2005 12 2
3 03/25/2005 10 3
4 03/25/2005 50 4

I want a result set to look like the following...
id date in sum(out)
1 03/22/2005 15 8
1 03/24/2005 13 4
2 03/21/2005 10 8
2 03/23/2005 12 4
3 03/22/2005 20 11
3 03/25/2005 10 3
4 03/21/2005 30 10
4 03/23/2005 50 8
4 03/25/2005 50 4

I want to show the days where there is an "in" and the sum of the outs for that "in" day to the next "in" day, not including the next "in" day amounts.

I only have sql query analyzer to work with, nothing else.

Any help would be greatly appreciated.

Thanks in advance!

 

[MissouriTiger]

As far as I know, the only way to return sums with SQL, in the fashion you are describing, is by grouping. But in the GROUP BY clause, a column name(s) must be specified, and I'm not sure that grouping on column names is sufficient to generate exactly what you are looking for.

However, the following approach might give you something very useful, even if it's not exactly what you had in mind.

SELECT id, date, in, out
FROM TB2
GROUP BY date, id ;

Or something like that. If that doesn't interest you, you can always do a SELECT *, save to text & a simple Perl RegEx routine to parse out your custom results. Heck, this is probly your best bet, even if you have uprds of a zillion^2 rec...




OTOH you might always try punching it through a chaos engine...



________________________________________________
Constructed from 100% recycled electrons.

 

[ESquared]

select id, date, sum(in) from blah group by id, date

select b1.id, sum(b2.out)
from blah b1 inner join blah b2 on b1.id = b2.id and b1.date <=b2.date
where b2.date = b1.date or b2.date = (select min(date) from blah where date > b1.date)
group by b1.id

then join them together as derived tables?