Skip to content
Advertisement

Rewrite MS Access SQL statement to SQL Server

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 with MAX(); 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]
;
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement