June 15, 2007 at 7:05 am
Can you without temp tables group a parent table that requires grouping on the child?
Parent (tp) contains rollup figures on balances, costs.
Child (tc) on various types of payments on each parent.
Std. Report would look like
Select tp.name, tp.group, tp.date, tp.balance, tp.cost,
Sum(case when tc.pay_type = 'X' then tc.payment else 0 end) [X PAY],
Sum(case when tc.pay_type = 'Y' then tc.payment else 0 end) [Y PAY],
From tbl_prnt tp
Left Join tbl_chld tc on tp.key = tc.key
Where tp.date >='01/01/2007'
Group by tp.name, tp.group, tp.date, tp.balance, tp.cost
Can I somehow go the next step to also group the parent?
sum(tp.balance) , sum(tp.cost)
group by tp.group?
June 15, 2007 at 7:35 am
I think this will do what you want:
Select name, [group], [date], sum(balance), sum(cost), sum([X Pay]),sum([Y Pay])
from (Select tp.name, tp.[group], tp.[date], tp.balance, tp.cost,
Sum(case when tc.pay_type = 'X' then tc.payment else 0 end) [X PAY],
Sum(case when tc.pay_type = 'Y' then tc.payment else 0 end) [Y PAY],
From tbl_prnt tp Left Join tbl_chld tc on tp.key = tc.key
Where tp.date >='01/01/2007'
Group by tp.name, tp.group, tp.date, tp.balance, tp.cost) t
group by t.name,t.[group],t.[date]
James.
June 15, 2007 at 7:39 am
Interestingly enough, I'm off experimenting with 'derived' tables. I have the first part done. I'm taking your snap-shot and seeing how it line's up with what I've got so far.
I used to do this with temp tables. Trying to get away from that.
June 15, 2007 at 8:19 am
Didn't work.
I think the inner grouping, by joining the child, is also causing some-sort of catesean product. Remeber the child can have multiple record, if your grouping at the parent inside, won't you end up with greater values because of this?
June 15, 2007 at 8:24 am
Found an error , running new structure....
June 15, 2007 at 8:29 am
When you track it down please post final solution. Looking at what I posted other than maybe an ambiguous column name/reference I would think it should work. But I'm a very visual person and work best when I have test data to run against which allows me to refine my work as I go. Not as strong when just writing things off the cuff (so to speak).
James.
June 15, 2007 at 8:34 am
Will do. Interogating piece-by-piece. I'll either let you know what I find, or your going to see a much 'longer' post!
June 15, 2007 at 8:54 am
It has to do 'null' records on the child left-join. Because here I'm aggregating the values on the child records, it's throwing out the parent when no child found.
I need to have the parent, regardless if 'childless'.
June 15, 2007 at 9:04 am
And you tried:
From tbl_prnt tp Left outer Join tbl_chld tc on tp.key = tc.key
June 15, 2007 at 9:36 am
Yes.
"From Parent
Left Join child on parent.key = child.key"
However, I beleive this falls apart if you implicitly filter on the child -
" Where Child.Filed_name = value "
I never realized this until I ran accross it in Crystal Reports. Tried it in SQL, same thing. I think I need to left-join and also allow NULL in where clause. I just tried something... failed.
June 15, 2007 at 9:40 am
use an OUTER join:
parent left OUTER Join child on parent.key = child.key
that will tell sql to take everything from the LEFT table, parent even when nothing in the right, child table (it will return nulls for each column of the child table when it can't match to a parent).
James.
June 15, 2007 at 1:45 pm
Got It.
What was going on was that the inner select was grouped, but NOT to the lowest (single) record. In otherwords, the grouping for the payment sums was not to the proper individual level. Once I place the account ID on the parent record , it worked.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply