I am working on converting values to overpunch by joining an overpunch table to a purchases table, but for a report that sums up the purchases I am having trouble avoiding putting overpunch in the group by. Below is my current code and the overpunch table I am using. I think this could be solved with a CTE but I have not had luck with it yet.
SELECT
,ID
,LEFT(RIGHT('000000000000' + SUM(CustomerPaid),12),11) + o1.overpunch
,LEFT(RIGHT('000000000000' + SUM(CustomerSaved),12),11) + o2.overpunch
FROM Table
LEFT JOIN OverPunch_Values o1
ON RIGHT(CustomerPaid, 1) = o1.numeric AND SIGN(CustomerPaid) = o1.VarCharSign
LEFT JOIN OverPunch_Values o2
ON RIGHT(CustomerSaved, 1) = o2.numeric AND SIGN(CustomerSaved) = o2.VarCharSign
GROUP BY ID, o1.overpunch, o2.overpunch
| Overpunch | Numeric | Sign | VarCharSign |
|---|---|---|---|
| } | 0 | -1 | -1 |
| J | 1 | -1 | -1 |
| K | 2 | -1 | -1 |
| L | 3 | -1 | -1 |
| M | 4 | -1 | -1 |
| N | 5 | -1 | -1 |
| O | 6 | -1 | -1 |
| P | 7 | -1 | -1 |
| Q | 8 | -1 | -1 |
| R | 9 | -1 | -1 |
| { | 0 | 0 | 1 |
| A | 1 | 1 | 1 |
| B | 2 | 1 | 1 |
| C | 3 | 1 | 1 |
| D | 4 | 1 | 1 |
| E | 5 | 1 | 1 |
| F | 6 | 1 | 1 |
| G | 7 | 1 | 1 |
| H | 8 | 1 | 1 |
| I | 9 | 1 | 1 |
| { | 0 | 0 | 0 |