Running SUM problem
Jeg har følgende SQL:------------------------------------------------
SELECT CustomerName
,YearAndMonth, [Year], MonthNumber
,(
SELECT SUM([OriginalAmount]) 'Running'
FROM Fact_CustomerEntry
JOIN Dim_Date ON Fact_CustomerEntry.Entry_Date_ID = Dim_Date.Date_ID
WHERE (Customer_ID = 896 AND (([Year] = 2002 AND MonthNumber <= 12) OR [Year] < 2002))
) AS 'Running Total'
FROM Fact_CustomerEntry CE
JOIN Dim_Customer ON CE.Customer_ID = Dim_Customer.Customer_ID
JOIN Dim_Date ON CE.Entry_Date_ID = Dim_Date.Date_ID
WHERE (CustomerNumber = '08243' or CustomerNumber = '84926')
ORDER BY CustomerName
----------------------------------------------
Istedet for:
WHERE (Customer_ID = 896 AND (([Year] = 2002 AND MonthNumber <= 12) OR [Year] < 2002))
Kunne jeg godt tænke mig at den selv skulle vælge hvor langt den er kommet. Mit bud var:
WHERE (Customer_ID = 896 AND (([Year] = [CE].[Year] AND MonthNumber <= CE.MonthNumber) OR [Year] < [CE].[Year]))
Men det kan åbenbart ikke lade sig gøre?
Den giver mig følgende fejl:
Invalid column name 'MonthNumber'
Invalid column name 'Year'
Invalid column name 'Year'