Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Join Invoice Total Record to Details

    Hi all,

    It's been awhile since I asked for help here. Please allow me to try to explain this report. An example is below. If you will I am trying to join a master invoice table to its detail records. The problem is I can't quite get the records to match correctly. There is a master record that has the net total of the invoice that corresponds to however many detail records for that invoice. I am attempting to get the records to line up in a query. I am having trouble because the key fields match the total up with each detail record. So for example in this record set below the 3825.75 value appears for each detail record so when I total the invoice column the figure is way too high. The detail has a 4462.54 and a -636.79 for a net of 3825.75. I tried to line the example up for better illustration. I copied it off a pdf and I am trying to replicate it programmatically.

    0712RW-IN 7/31/2012 8/30/2012 4,462.54 0.00 3,825.75 INV 7/31/2012 4,462.54
    C/M 8/31/2012 636.79- Reference: 0712RW



    CREATE TABLE [dbo].[INVOICE](
    [SRC] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ARDIVISIONNO] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CUSTOMERNO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [INVOICENO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [INVOICETYPE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [INVOICEDATE] [datetime] NULL,
    [INVOICEDUEDATE] [datetime] NULL,
    [INVOICEDISCOUNTDATE] [datetime] NULL,
    [INVOICEHISTORYHEADERSEQNO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [POSTINGDATE] [datetime] NULL,
    [TERMSCODE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TAXSCHEDULE] [varchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SALESPERSONDIVISIONNO] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SALESPERSONNO] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SPLITCOMMISSIONFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [COMMENT] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CREDITMEMOINVOICEREFERENCE] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JOBNO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CUSTOMERPONO] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [POSTINGREFERENCE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CHECKNO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [COMMISSIONRATE] [decimal](8, 3) NULL,
    [TAXABLEAMT] [decimal](13, 2) NULL,
    [NONTAXABLEAMT] [decimal](13, 2) NULL,
    [FREIGHTAMT] [decimal](12, 2) NULL,
    [SALESTAXAMT] [decimal](12, 2) NULL,
    [COSTOFSALESAMT] [decimal](13, 2) NULL,
    [SALESSUBJECTTOCOMM] [decimal](13, 2) NULL,
    [COSTSUBJECTTOCOMM] [decimal](13, 2) NULL,
    [COMMISSIONAMT] [decimal](12, 2) NULL,
    [DISCOUNTAMT] [decimal](13, 2) NULL,
    [PAYMENTSTODAY] [decimal](13, 2) NULL,
    [BALANCE] [decimal](13, 2) NULL,
    [RETENTIONAMT] [decimal](13, 2) NULL,
    [DATEUPDATED] [datetime] NULL,
    [TIMEUPDATED] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [USERUPDATEDKEY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRIMARY KEY CLUSTERED
    (
    [SRC] ASC,
    [ARDIVISIONNO] ASC,
    [CUSTOMERNO] ASC,
    [INVOICENO] ASC,
    [INVOICETYPE] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]



    CREATE TABLE [dbo].[INVOICEDETAILHISTORY](
    [SRC] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ARDIVISIONNO] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CUSTOMERNO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [INVOICENO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [INVOICETYPE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [INVOICEHISTORYHEADERSEQNO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TRANSACTIONDATE] [datetime] NOT NULL,
    [SEQUENCENO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [PAYMENTREFERENCE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CHECKNO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TRANSACTIONTYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PAYMENTDATE] [datetime] NULL,
    [PAYMENTTYPE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OTHERPAYMENTTYPEREFNO] [varchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CARDHOLDERNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EXPIRATIONDATEYEAR] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EXPIRATIONDATEMONTH] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CREDITCARDAUTHORIZATIONNO] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ENCRYPTEDCREDITCARDNO] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LAST4UNENCRYPTEDCREDITCARDNOS] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CREDITCARDTRANSACTIONID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [RMANO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TRANSACTIONAMT] [decimal](12, 2) NULL,
    [DATEUPDATED] [datetime] NULL,
    [TIMEUPDATED] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [USERUPDATEDKEY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRIMARY KEY CLUSTERED
    (
    [SRC] ASC,
    [ARDIVISIONNO] ASC,
    [CUSTOMERNO] ASC,
    [INVOICENO] ASC,
    [INVOICETYPE] ASC,
    [TRANSACTIONDATE] ASC,
    [SEQUENCENO] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]


    Thanks again. You guys are super.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You didn't post your query.
    Why are you using an aggregate function on a value which is already aggregated?
    The answer to your problem problem lies in either a subquery or common table expression based off of the detail table, but without more clarity regarding your requirements I can't help you further than that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •