Perhaps you could post the DDL of the table(s) you are dealing with, so we
don't have to guess the structure by wading through a stored procedure that
contains a lot of irrelevant processing.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: AD [mailto:daissam@s...]
Sent: Monday, October 21, 2002 5:00 AM
To: sql language
Subject: [sql_language] RE: Challenge


I think I need to clarify this a bit.
I am using an SP where the account is a parameter not linked to any
eff_date itself, but has transactions underneath it. Pls take a look at
part of this sp, probably you can see what I mean:

CREATE proc spSSLowCostHouseRetention001
@paccountFrom char(6),
@paccountTo char(6),
@pbuFrom  char(4),
@pbuTo char(4),
@pprojectFrom char(8),
@pprojectTo char(8),
@psiteFrom char(3),
@psiteTo char(3),
@peff_dateFrom char(10),
@peff_dateTo char(10),
@pvchrMachineNo char(20)
As
Begin

Insert Into tblSSLowCostHouseRetention
    Select  DBSjpec.dbo.posted_jrnl_line.jrnl_id,
            DBSjpec.dbo.posted_jrnl_line.jrnl_seq_nbr,
            DBSjpec.dbo.posted_jrnl_line.eff_date,
            DBSjpec.dbo.posted_jrnl_line.account,
            DBSjpec.dbo.posted_jrnl_line.bu,
            DBSjpec.dbo.posted_jrnl_line.project,
            DBSjpec.dbo.posted_jrnl_line.site,
            DBSjpec.dbo.posted_jrnl_line.descp,
        CASE WHEN DBSjpec.dbo.posted_jrnl_line.trans_amt < 0 THEN
DBSjpec.dbo.posted_jrnl_line.trans_amt ELSE 0 END, /* DebitAmount */
        CASE WHEN DBSjpec.dbo.posted_jrnl_line.trans_amt > 0 THEN
DBSjpec.dbo.posted_jrnl_line.trans_amt ELSE 0 END, /* CreditAmount */
            SPACE(1) /* vendor_id*/,
        SPACE(1) /* organization_operating_name*/,
        SPACE(1) /* pmt_meth_id */,
            SPACE(1) /* pmt_ref_nbr */,
            DBSjpec.dbo.posted_jrnl_line.jrnl_user_alpha_fld_1,
            DBSglep.dbo.ldr_acct.acct_descp_1,
            DBSglep.dbo.ldr_acct.acct_descp_2,
        @pvchrMachineNo
FROM DBSjpec.dbo.posted_jrnl_line INNER JOIN
    DBSglep.dbo.ldr_acct ON
    DBSjpec.dbo.posted_jrnl_line.account = DBSglep.dbo.ldr_acct.account
     AND
    DBSjpec.dbo.posted_jrnl_line.bu = DBSglep.dbo.ldr_acct.bu AND
     DBSjpec.dbo.posted_jrnl_line.project = DBSglep.dbo.ldr_acct.project
     AND
    DBSjpec.dbo.posted_jrnl_line.site = DBSglep.dbo.ldr_acct.site
WHERE
(DBSjpec.dbo.posted_jrnl_line.eff_date BETWEEN @peff_dateFrom  AND
@peff_dateTo )
AND (DBSjpec.dbo.posted_jrnl_line.account BETWEEN @paccountFrom  AND
@paccountTo)
AND (DBSjpec.dbo.posted_jrnl_line.bu BETWEEN @pbuFrom AND @pbuTo)
AND (DBSjpec.dbo.posted_jrnl_line.site BETWEEN  @psiteFrom  AND @psiteTo)
AND (DBSjpec.dbo.posted_jrnl_line.amt_class_1_type = 'ACTUAL')
AND (DBSjpec.dbo.posted_jrnl_line.jrnl_user_alpha_fld_1 BETWEEN
@pprojectFrom AND @pprojectTo)
AND (DBSjpec.dbo.posted_jrnl_line.jrnl_origin_code <> 'PR')

End

When the report is generated, each account has transactions listed by
eff_date, transaction amount,....etc. If this is not clear let me know.