I am trying to pull the "Period" using referencing dates between two tables. Both tables have dates which are represented as 'dd-MM-yy'.
However, whenever I execute it gives me an arithmetic overflow error for type int. I have tried casting and converting to nvarchar. Nothing has worked.
The error only appeared after trying to inner join, when excluding this statement the query runs fine.
See below query.
SELECT so.BranchId,
year(InvoiceDate) as 'Year',
datepart(week,InvoiceDate) as 'Fiscal Week',
concat(so.branchid,'-', datepart(week,InvoiceDate)) as 'Primary Key',
cast(sum(TotalPounds) as INT) as 'Pounds Shipped',
cast(sum(sum(TotalPounds)) OVER (PARTITION BY so.branchID,year(InvoiceDate) ORDER BY datepart(week,InvoiceDate)) as int) as 'Running Total'
FROM SalesOrders as so
INNER JOIN GLTransactions as gl
ON convert(varchar,InvoiceDate,105) = convert(varchar,EntryDate,105)
WHERE year(invoicedate) = 2021 and BillTo != '6296'
GROUP BY so.BranchID,
year(InvoiceDate),
datepart(week,InvoiceDate)
ORDER BY so.BranchID,
year(InvoiceDate),
datepart(week,InvoiceDate);
