Board index » delphi » sql stumpy (GL "on the fly")

sql stumpy (GL "on the fly")

Hi, here is a SQL question that I know has nothing specifically
to do with delphi, other than the fact that I'm using BDE 5.1 and
Delhpi 4 C/S

Normally, ( in dbase) I've used a summary file for General Ledger
and "Posted" the totals to the sumarry fields for YTD inc/exp and MONTH
inc/exp.  Now I'm experimenting with the idea of GL Ledger "on the fly"
where it will be created by summing up the detail.  If anything, I think
such a thing may be usefull in reports.  However, how to do...

I have a
    General Ledger File
              GLACCOUNT, GLNUMBER, ...
    GLJournal
              GLNumber, CashID, Cr,Dr...
    CashJournal
               CashID, Entereded, Description

I need the annual totals, and the monthly totals for CR, and DR

Below is very slow, but it appears to work. I would like some suggestions
if possible. The example assumes that month = 8 (august)  Thanks,

SELECT glaccount, annualbudget,
     (SELECT SUM(cr) FROM gljournal WHERE glnumber =
genledger.glnumber),
     (SELECT SUM(dr)FROM gljournal WHERE glnumber =
genledger.glnumber),
    (SELECT SUM(GLJournal.DR) AS MonthExp FROM GLJournal INNER
        JOIN  CashJournal ON GLJournal.CashID = CashJournal.ID
       WHERE DATEPART(mm, CashJournal.Entered) = 8)) ,
   (SELECT SUM(GLJournal.CR) AS MonthInc FROM GLJournal INNER
       JOIN   CashJournal ON GLJournal.CashID = CashJournal.ID
       WHERE DATEPART(mm, CashJournal.Entered) = 8))

FROM genledger
ORDER BY glaccount

 

Re:sql stumpy (GL "on the fly")


Couple of suggestions ....

1) Have a totals table that is updated via triggers (actually works very well)
2) Look up using cube and rollup. Both do summing work for you.
3) Look at OLAP that comes with Sql 7.0 (Huge overkill probably)

Craig Baugh

Quote
riddell <ridd...@clcm.net> wrote in message news:7pmmco$at714@forums.borland.com...
> Hi, here is a SQL question that I know has nothing specifically
> to do with delphi, other than the fact that I'm using BDE 5.1 and
> Delhpi 4 C/S

> Normally, ( in dbase) I've used a summary file for General Ledger
> and "Posted" the totals to the sumarry fields for YTD inc/exp and MONTH
> inc/exp.  Now I'm experimenting with the idea of GL Ledger "on the fly"
> where it will be created by summing up the detail.  If anything, I think
> such a thing may be usefull in reports.  However, how to do...

> I have a
>     General Ledger File
>               GLACCOUNT, GLNUMBER, ...
>     GLJournal
>               GLNumber, CashID, Cr,Dr...
>     CashJournal
>                CashID, Entereded, Description

> I need the annual totals, and the monthly totals for CR, and DR

> Below is very slow, but it appears to work. I would like some suggestions
> if possible. The example assumes that month = 8 (august)  Thanks,

> SELECT glaccount, annualbudget,
>      (SELECT SUM(cr) FROM gljournal WHERE glnumber =
> genledger.glnumber),
>      (SELECT SUM(dr)FROM gljournal WHERE glnumber =
> genledger.glnumber),
>     (SELECT SUM(GLJournal.DR) AS MonthExp FROM GLJournal INNER
>         JOIN  CashJournal ON GLJournal.CashID = CashJournal.ID
>        WHERE DATEPART(mm, CashJournal.Entered) = 8)) ,
>    (SELECT SUM(GLJournal.CR) AS MonthInc FROM GLJournal INNER
>        JOIN   CashJournal ON GLJournal.CashID = CashJournal.ID
>        WHERE DATEPART(mm, CashJournal.Entered) = 8))

> FROM genledger
> ORDER BY glaccount

Other Threads