Sql server - keep count with original date

See more:

SQL

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
;