SQLTeam.com | Weblogs | Forums

Summing Totals is not Working for me

Hi,m Hopnig you can help me. New enough to SQL. I have 2 tables. 1 is Customer information with dates we tried to contact them and the other is product information that each customer has purchased.
1st table - CUSTOMER
|Customer Key|Contact Date|
|317984|03/10/2021|
|317984|06/10/2021|
|317984|31/10/2021|

CUSTPRODUCT
|Customer Key|Product ID|Product Purchase Date|Product Cost|
|317984|A14781|01/10/2021|20|
|317984|A14781|01/09/2021|20|
|317984|A14781|04/08/2021|20|

WHat I am trying to get is the latest contact date in the month and the total purchases in the 90 days previous to the latest contact. Desired result would be
Customer Key|MAX(CONTACT DATE)| SUM(PRODUCT COST)
317984|31/10/22021|60

What I am getting
Customer Key|MAX(CONTACT DATE)| SUM(PRODUCT COST)
317984|31/10/22021|180

Code is (DA DATE is just a date table which you can join a date and it will return a fiscal period / month)

SELECT MAX(AL2.CONTACT_DATE), AL2.CUSTOMER_KEY, SUM(AL4.PRODUCT_COST) FROM CUSTOMER AL2, DA_DATE AL3, CUSTPRODUCT AL4 WHERE AL2.CONTACT_DATE=AL3.DA_DATE AND AL2.SUBSCRIBER_KEY=AL4.SUBSCRIBER_KEY) AND AL3.DA_FISCAL_PERIOD='202110' AND AL4.PRODUCT_PURCHASE_DATE<= AL2.CONTACT_DATE AND AL4.PRODUCT_PURCHASE_DATE >= AL2.CONTACT_DATE - 90 AND AL2.CUSTOMER_KEY='317984') GROUP BY 2

Please provide consumable test data in future with dates in ISO format:

-- *** Test Data ***
CREATE TABLE #CustomerContact
(
	Customer_Key int NOT NULL
	,Contact_Date date NOT NULL
	,PRIMARY KEY (Customer_Key, Contact_Date)
);
INSERT INTO #CustomerContact
VALUES (317984, '20211003')
	,(317984, '20211006')
	,(317984, '20211031');

CREATE TABLE #CustProduct
(
	Customer_Key int NOT NULL
	,Product_ID varchar(20) NOT NULL
	,Product_Purchase_Date date NOT NULL
	,Product_Cost money NOT NULL
	,PRIMARY KEY (Customer_Key, Product_ID, Product_Purchase_Date)
);
INSERT INTO #CustProduct
VALUES (317984, 'A14781', '20211001', 20)
	,(317984, 'A14781', '20210901', 20)
	,(317984, 'A14781', '20210804', 20);
-- *** End Test Data ***

WITH MaxDates
AS
(
	SELECT Customer_Key, MAX(Contact_Date) AS Contact_Date
	FROM #CustomerContact
	GROUP BY Customer_Key, EOMONTH(Contact_Date)
)
SELECT M.Customer_Key,  M.Contact_Date, SUM(P.Product_Cost) AS Total_Product_Cost
FROM #CustProduct P
	JOIN MaxDates M
		ON P.Customer_Key = M.Customer_Key
			AND P.Product_Purchase_Date
				BETWEEN DATEADD(day, -90, M.Contact_Date) AND M.Contact_Date
GROUP BY M.Customer_Key,  M.Contact_Date;

Which dbms engine are you using? That doesn't look like SQL Server syntax.