I have data defined like below in AF.
I have applied this logic to get the month number in stead of text.
SELECT * FROM OPENQUERY([PIAF_DEMO],'
SELECT ea.Name, i.Value,MONTH(''Mar'')
FROM [EntegaDB].[Asset].[ElementHierarchy] eh
INNER JOIN [EntegaDB].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,
WHERE eh.Name = ''Haiger''
AND i.ElementAttributeID = ea.ID
AND ea.Name =''monthlyplanned''
OPTION (FORCE ORDER, EMBED ERRORS)')
My requirement is if i want to get the sum value for March, it should give me the result for (jan value+ feb Value+march value).
I was trying to get apply the logic like select sum(values) from table_name where month number between 1 and current month number, but did not work. somewhere I must be syntactically incorrect.
Then I changed my idea stating :
CASE WHEN MONTH(''feb'') = 2 THEN SUM(jan .value+fen.value) AS feb
but I'm unsure about sql server syntax.
Please can anyone help me how to apply the logic for my requirement?