How to perform a query that shows me data from the current month and the previous month in SQL Serve-CodePudding

I receive information to my SQL Server table by uploading an Excel file on the web.

I have to create a stored procedure where I have to select a line (part of my table), the name of the month and the year that I want to get that information. So there are three parameters.

I show an example of the query I'm doing.

ALTER PROCEDURE SP_NIVELES(
@NIVEL VARCHAR(15),
@MES VARCHAR(12),
@AÑO INT
)
AS
BEGIN
    IF @NIVEL = 'LINEA MC'
        BEGIN
            SELECT LINEAMC, SUM(MONTODEBITO) AS DEBITO, SUM(MONTOCREDITO) AS CREDITO, SUM(MONTODEBITO) - SUM(MONTOCREDITO) AS TOTAL 
            FROM PRUEBAOPEX p 
            WHERE MES = @MES
            AND AÑO = @AÑO
        GROUP BY LINEAMC
    END
        
    ELSE
        RAISERROR('Linea Incorrecta', 16, 1)
END

My problem is that the "MES" column is of type varchar and my "AÑO" column is of type int. I need to get the information from the previous month (in addition to the month I select as a parameter), is there a way to get the previous month when the "MONTH" field is of type varchar? Or is there a way to include the date data type for this query (would be more efficient I think)? If it existed, what would it be like?

CodePudding user response：

I think you're going to have to CONVERT to a real date and do your math on that. Hopefully your server localized to your language, try this and see if it recognizes you rmonth names

ALTER PROCEDURE SP_NIVELES(
@NIVEL VARCHAR(15),
@MES VARCHAR(12),
@AÑO INT
)
AS
BEGIN
    IF @NIVEL = 'LINEA MC'
        BEGIN
          with cteDates as (
             SELECT DATEADD(month, 1, CONVERT(DATE, CONCAT(@MES, ' 1, ', CONVERT(char(5), @Ano)))) as TooLate
             , DATEADD(month, -1, CONVERT(DATE, CONCAT(@MES, ' 1, ', CONVERT(char(5), @Ano)))) as Earliest
           )
          SELECT LINEAMC, SUM(MONTODEBITO) AS DEBITO, SUM(MONTOCREDITO) AS CREDITO, SUM(MONTODEBITO) - SUM(MONTOCREDITO) AS TOTAL 
          FROM PRUEBAOPEX as p CROSS JOIN cteDates as D
          WHERE CONVERT(DATE, CONCAT(@MES, ' 1, ', CONVERT(char(5), @Ano))) < TooLate
              AND CONVERT(DATE, CONCAT(@MES, ' 1, ', CONVERT(char(5), @Ano))) >= Earliest

        GROUP BY LINEAMC
    END
        
    ELSE
        RAISERROR('Linea Incorrecta', 16, 1)
END

CodePudding user response：

I presume @MES will contain the name of the month in Spanish. You could try and get the month number out of its name but that can be problematic depending on the localization of your database. In your case I would do something like the following:

ALTER PROCEDURE SP_NIVELES(
@NIVEL VARCHAR(15),
@MES VARCHAR(12),
@AÑO INT
)
AS
BEGIN
    DECLARE @MesAnterior NVARCHAR(12), @AñoMesAnterior INT;
    SET @MesAnterior =  CASE @MES    
                            WHEN 'Enero' THEN 'Diciembre'
                            WHEN 'Febrero' THEN 'Enero'
                            WHEN 'Marzo' THEN 'Febrero'
                            WHEN 'Abril' THEN 'Marzo'
                            WHEN 'Mayo' THEN 'Abril'
                            WHEN 'Junio' THEN 'Mayo'
                            WHEN 'Julio' THEN 'Junio'
                            WHEN 'Agosto' THEN 'Julio'
                            WHEN 'Septiembre' THEN 'Agosto'
                            WHEN 'Octubre' THEN 'Septiembre'
                            WHEN 'Noviembre' THEN 'Octubre'
                            WHEN 'Diciembre' THEN 'Noviembre'
                        END;
    SET @AñoMesAnterior = CASE @MES WHEN 'Enero' THEN @AÑO - 1 ELSE @AÑO END; 

    IF @NIVEL = 'LINEA MC'
        BEGIN
            SELECT LINEAMC, SUM(MONTODEBITO) AS DEBITO, SUM(MONTOCREDITO) AS CREDITO, SUM(MONTODEBITO) - SUM(MONTOCREDITO) AS TOTAL 
            FROM PRUEBAOPEX p 
            WHERE (MES = @MES AND AÑO = @AÑO) OR (MES = @MesAnterior AND AÑO = @AñoMesAnterior)            
        GROUP BY LINEAMC
    END
    
    ELSE
        RAISERROR('Linea Incorrecta', 16, 1)
END

Of course, this would work if the names of the months are "known" and uniform across your table.

Hope it helps