I've been googling for a while and can not find the solution to this.
I'm trying to Join a Sales Table with two separate discount tables. When I join the tables together the Sales is repeated on each line. I want to have the sales number show up only once and have the rest of the rows show zero. I also want the two discount columns to be in the same column.
If anyone can point me in the right direction I would eternally grateful.
Sale Data
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Discount Table 1
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Discount Table 2
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
End Result
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
I'm trying to Join a Sales Table with two separate discount tables. When I join the tables together the Sales is repeated on each line. I want to have the sales number show up only once and have the rest of the rows show zero. I also want the two discount columns to be in the same column.
If anyone can point me in the right direction I would eternally grateful.
Sale Data
Transaction Number | Transaction Line | Gross Sales |
361449 | 1 | 1563.12 |
361449 | 2 | 1302.6 |
361449 | 3 | 8676.36 |
361449 | 4 | 7850.04 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Discount Table 1
Invoice Number | Invoice Line Number | Promotion Code | Discount Value |
361449 | 1 | SCB1.00 | 15.63 |
361449 | 2 | SCB1.00 | 13.03 |
361449 | 3 | SCRM1.00 | 86.76 |
361449 | 1 | PDS | 20 |
361449 | 4 | SCRM1.00 | 78.5 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Discount Table 2
Invoice Number | Invoice Line Number | Adjustment Code | Total Misc Adj Value |
361449 | 1 | .20COFFEE | -15.6 |
361449 | 1 | CA-CRV10 | 46.8 |
361449 | 2 | .20COFFEE | -13 |
361449 | 2 | CA-CRV10 | 39 |
361449 | 3 | .17CRMRS | -78.54 |
361449 | 4 | .17CRMRS | -71.06 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
End Result
Transaction Number | Transaction Line | Gross Sales | Promotion Code | Line Level.Discount Value |
361449 | 1 | 1563.12 | SCB1.00 | 15.63 |
361449 | 1 | 0 | PDS | 20 |
361449 | 1 | 0 | .20COFFEE | -15.6 |
361449 | 1 | 0 | CA-CRV10 | 46.8 |
361449 | 2 | 1302.6 | SCB1.00 | 13.03 |
361449 | 2 | 0 | .20COFFEE | -13 |
361449 | 2 | 0 | CA-CRV10 | 39 |
361449 | 3 | 8676.36 | SCRM1.00 | 86.76 |
361449 | 3 | 0 | .17CRMRS | -78.54 |
361449 | 4 | 7850.04 | SCRM1.00 | 78.5 |
361449 | 4 | 0 | .17CRMRS | -71.06 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>