I’m having trouble keeping the correct count within the correct date.
I am counting order numbers that have an ‘AA’ in the code column and an ‘in’ under the post column. Then checking if that order number has an ‘EE’ in the code column and ‘out’ in the post column. If all that’s true, I count as 1.
Next I would like to see if the order number has an ‘AA’ in the code column and ‘out’ in the post column. If that’s true, I would count that as 1.
The problem I’m having is that the ‘EE’ in the code column and ‘out’ in the post column are counted in one day, but the ‘AA’ in the code column and ‘out’ in the post column is counted in another day.
For example, a customer orders a product on one day but part of the order fails (EE=failed) later in the same day. The shop opens the next day, corrects the order and then its successful but since it was successful the next day, its counted that day and not against the original order date. I would like it counted in the original order date column.
**What I’m getting**
DATE EE-FAIL AA-GOOD
2024-10-01 2 0
2024-10-02 0 2
**What I want**
DATE EE-FAIL AA-GOOD
2024-10-01 2 2
2024-10-02 0 0
order_number date post code account
88728 2024-10-01 in AA 6063195
88728 2024-10-01 out EE 6063195
88728 2024-10-02 out AA 6063195
76347 2024-10-01 in AA 2854763
76347 2024-10-01 out EE 2854763
76347 2024-10-02 out AA 2854763
THANK YOU !!
What I have tried:
CREATE TABLE ##full_table
(
order_number INT
,date date
,post VARCHAR(5)
,code VARCHAR(2)
,account INT
)
INSERT INTO ##full_table VALUES ('88728','2024-10-01','in','AA','6063195')
INSERT INTO ##full_table VALUES ('88728','2024-10-01','out','EE','6063195')
INSERT INTO ##full_table VALUES ('88728','2024-10-02','out','AA','6063195')
INSERT INTO ##full_table VALUES ('76347','2024-10-01','in','AA','2854763')
INSERT INTO ##full_table VALUES ('76347','2024-10-01','out','EE','2854763')
INSERT INTO ##full_table VALUES ('76347','2024-10-02','out','AA','2854763')
CREATE TABLE ##research_count
(
date date
,fail_count INT
,good_count INT
)
INSERT INTO ##research_count (date, fail_count)
SELECT date, COUNT(DISTINCT order_number) AS ‘failed’
FROM ##full_table
WHERE code = 'EE'
AND post = 'out'
GROUP BY date
UPDATE t
SET good_count = (SELECT COUNT(DISTINCT order_number)
FROM ##full_table p
WHERE code = 'AA'
AND post = 'out'
AND p.date = t.date)
FROM ##research_count t
;