I wish to convert the following MS Access SQL statement to SQL Server. All of my attempts are resulting in different results from the old & original data.
SELECT Sum(ADA_LAST.MA) AS MA, Sum(ADA_LAST.DA) AS DA, ADA_LAST.ID_BAS, ADA_LAST.PRO_NUMBER, ADA_LAST.ACC_NUMBER, ADA_LAST.DATA, "" AS Q, "" AS P, Last(ADA_LAST.Date) AS [DATE], "" AS UNIT, 0 AS ID, [MA]-[DA] AS R FROM ADA_LAST GROUP BY ADA_LAST.ID_BAS, ADA_LAST.PRO_NUMBER, ADA_LAST.ACC_NUMBER, ADA_LAST.DATA, "", 0, [MA]-[DA], "", "" ;
The new Query is:
SELECT MA = Sum([ADA_LAST].[MA]), DA = Sum([ADA_LAST].[DA]), [ADA_LAST].[ID_BAS], [ADA_LAST].[PRO_NUMBER], [ADA_LAST].[ACC_NUMBER], [ADA_LAST].[DATA], Q = '', P = '', [DATE] = ADA_LAST.[Date], UNIT = '', ID = 0, Sum([ADA_LAST].[MA]) - Sum([ADA_LAST].[DA]) AS R FROM [ADA_LAST](@PRO_NAME,@SDAY) GROUP BY [ADA_LAST].[ACC_NUMBER], [ADA_LAST].[Date], [ADA_LAST].[PRO_NUMBER], [ADA_LAST].[ID_BAS], [ADA_LAST].[DATA]
The problem caused by grouping date column in new statement, but in old one it is used in Last function to avoid grouping it and still exists in the select statement, How can I do like this.
Advertisement
Answer
You can try the below query. Changes to the original:
- empty string is note as
''
instead of""
- I replace
LAST
withMAX()
; this is likely to do what you want, since you are using aggregation - constant columns do not need to be listed in the
GROUP BY
clause
Code:
SELECT SUM(ADA_LAST.MA) AS MA, SUM(ADA_LAST.DA) AS DA, ADA_LAST.ID_BAS, ADA_LAST.PRO_NUMBER, ADA_LAST.ACC_NUMBER, ADA_LAST.DATA, '' AS Q, '' AS P, MAX(ADA_LAST.Date) AS [DATE], '' AS UNIT, 0 AS ID, [MA] - [DA] AS R FROM ADA_LAST GROUP BY ADA_LAST.ID_BAS, ADA_LAST.PRO_NUMBER, ADA_LAST.ACC_NUMBER, ADA_LAST.DATA, [MA] - [DA] ;