I have an SQL view (joins invoice header and detail files) that's used for reporting... The header file contains the invoice total, tax total, and freight total. When joined to the detail file, I need those values to not repeat for each row - otherwise, users could inadvertently sum them when grouping by fields in the detail file.
My view currently contains a case statement that sets these columns to zero unless the Invoice Detail Sequence number is 1 (representing the first line of the invoice). That works nearly all of the time. However, for some unknown reason, we have invoices where the first detail line starts with a sequence number other than 1.
There has to be a better way of handling this... is there an SQL function or method that would accomplish the same thing based on the first detail row found for each corresponding header row?
My view currently contains a case statement that sets these columns to zero unless the Invoice Detail Sequence number is 1 (representing the first line of the invoice). That works nearly all of the time. However, for some unknown reason, we have invoices where the first detail line starts with a sequence number other than 1.
There has to be a better way of handling this... is there an SQL function or method that would accomplish the same thing based on the first detail row found for each corresponding header row?
Comment