Board index » delphi » Grouping by year

2006-03-14 05:23:18 AM
delphi62
I need to do some reporting by year. I need the some of the money
values from 3 detail tables grouped by the year that the transaction
took place.
The relationship between claimmaster and the other tables are all 1 to
many. My problem is that the joins are causing all the values to get
multiplied when there are multiple detail records. Is there away to
make this work without using temp tables (ie select from (select
from)), I don't think they are in interbase, or at least the version
the customer has.
Sorry about the formatting.
select c.CLAIMNUMBER, r.RESERVETYPE, extract(YEAR from r.CREATEDATE) ,
sum(coalesce(r.RESERVECHANGEAMT,0)),
sum(coalesce(p.DETAILPAYMENTAMT,0)),
sum(coalesce(s.RECOVERYAMOUNT,0))
from CLAIMMASTER c left join RESERVEHISTORY r
on (c.CLAIMMASTERRECORDID = r.CLAIMMASTERRECORDID) left join
PAYMENTDETAIL p
on (c.CLAIMMASTERRECORDID = p.CLAIMMASTERRECORDID and r.RESERVETYPE
= p.RESERVETYPE and
(extract(YEAR from r.CREATEDATE) = extract(YEAR from
p.CREATEDATE))) left join SALVSUBRORECOVERY s
on(c.CLAIMMASTERRECORDID = s.CLAIMMASTERRECORDID and
r.RESERVETYPE = s.RESERVETYPE and
(extract(YEAR from r.CREATEDATE) = extract(YEAR from
s.CREATEDATE)))
group by 1, 2, 3
order by extract(YEAR from r.CREATEDATE), c.CLAIMNUMBER, r.RESERVETYPE
----------------
Joe Bain
www.iegsoftware.com
 

Joe Bain writes:
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their
server: support.borland.com/entry.jspa
 

Craig Stuntz [TeamB] writes:
--------------
Joe Bain
www.iegsoftware.com
 

Joe Bain writes:
then you are not using InterBase.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
How to ask questions the smart way:
www.catb.org/~esr/faqs/smart-questions.html
 

Craig Stuntz [TeamB] writes:
program IB PLANalyzer.
--------------
Joe Bain
www.iegsoftware.com
 

Joe Bain writes:
GROUP BY <number>, and never has. Period. Nor does InterBase support
"ORDER BY <function>," (shown in your screenshot) and never has. Hence,
you are not using InterBase. The SQL shown in your screenshot will not
work on any version of InterBase ever released.
Are you really unaware of what server you are using? That could be
part of the problem, I guess.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their
server: support.borland.com/entry.jspa
 

Craig Stuntz [TeamB] writes:
off-topic. The diffreances between them are trivial. Just pretend I am
using a view and I have the view field names in the the last two
clauses.
It does not change the fact the the SQL logic is the same. This group
is about SQL queries, and I am asking a question about SQL logic. The
whole reason I did not post the dreaded name here is so that I would
not get into the EXACT same discussion that everyone else gets here.
Borland and TeamB both sould realise that IB has a very close cousin
(or maybe sugically removed conjoined twin is more apt) and they should
not *chase* people off because they are using it.
--
--------------
Joe Bain
www.iegsoftware.com
 

Joe Bain writes:
actively lying about it. And then insisting that your prevarications
are somehow somebody else's problem.
That isn't a good way to get volunteer help from others, sorry.
be the same when your SQL does not work. If you knew the answer to this
you would have never posted the question in the first place, so don't
try and tell me what does and does not work. InterBase and Firebird
have different SQL syntax for GROUP BY and you need to find the best
solution for the server you are using, which is not InterBase.
It is not Borland's problem or TeamB's problem that you are
misrepresenting the facts of your problem. it is yours. And it is a
terrible way to ask for free help.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Useful articles about InterBase development:
blogs.teamb.com/craigstuntz/category/21.aspx
 

I am sorry for misrepresenting myself. I was just trying to avoid this
argument.
Here is the query with the offending clauses removed. Now can you help
me? I can take the year stuff out that makes me use non standard SQL
and I still get the problem. IE is there is 2 records in RESERVEHISTORY
and only one in PAYMENTDETAIL the amount in PAYMENTDETAIL gets doubled.
Is there a way to over come this?
select c.CLAIMNUMBER, r.RESERVETYPE, sum(r.RESERVECHANGEAMT),
sum(p.DETAILPAYMENTAMT), sum(s.RECOVERYAMOUNT)
from CLAIMMASTER c left join RESERVEHISTORY r
on (c.CLAIMMASTERRECORDID = r.CLAIMMASTERRECORDID) left join
PAYMENTDETAIL p
on (c.CLAIMMASTERRECORDID = p.CLAIMMASTERRECORDID and r.RESERVETYPE
= p.RESERVETYPE) left join SALVSUBRORECOVERY s
on(c.CLAIMMASTERRECORDID = s.CLAIMMASTERRECORDID and
r.RESERVETYPE = s.RESERVETYPE)
group by c.CLAIMNUMBER,r.RESERVETYPE
--------------
Joe Bain
www.iegsoftware.com
 

"Joe Bain" <XXXX@XXXXX.COM..c.o.m>writes:
If you would have just posted your question to the appropriate Firebird
group, you would likely have had an answer by now. Instead you've wasted
Craig's time as well as mine since I got suckered into reading this thread.
<plonk>
Kozmo
 

Joe Bain writes:
foundered on SQL that is valid in Firebird and not in InterBase thereby
proving that the differences between them are anything but trivial.<g>
--
Bill Todd (TeamB)