SQL QUERY using MAX function

Asked

Viewed 1,131 times

5

I’m having a problem with a basic consultation of SQL, is for a migration of database.

The query aims to bring data from the table of ASSOCIADOS/CLIENTES, but beyond this table I have another MENSALIDADES, and two of the fields I need in this extraction is the expiration of the last monthly payment and its value.

I’m using the MAX to bring the monthly fee and so far so good, when I put:

 CONVERT (VARCHAR,MAX(MEN.VENCIMENTO),102) as data_inicio_ciclo

the value returned is actually the last monthly payment and does not duplicate the information, the value in the query it brings all the data, and need only the value of the last:

 MEN.Valor AS valor_total_venda

Below the query in SQL and the return (PRINT) with the VALUE and WITHOUT THE VALUE

SELECT
        ASS.Inscricao as numero_contrato,
        CONVERT (VARCHAR, ASS.data, 102) as data_contrato,
        ASS.grupo as id_tipo_contrato,
        ASS.TipoVenda as id_tipo_venda,
        ASS.Inscricao as codigo_cliente_antigo,
        CONVERT (VARCHAR,ASS.data, 102) as data_vigencia_inicial,
        CONVERT (VARCHAR,ASS.DataContratoVigenciaFinal, 102) as data_vigencia_final,
        CONVERT (VARCHAR,ASS.DiaVenc, 102) as dia_vencimento,
        ASS.AssTipoCobranca as id_preferencia_faturamento,
        ASS.AssTipoCobranca as id_periodicidade,
        ASS.AssTipoCobranca as ciclo_atual,
        CONVERT (VARCHAR,MAX(MEN.VENCIMENTO),102) as data_inicio_ciclo,
        ASS.status as registro_ativo,
        CONVERT (VARCHAR,ASS.DataStatus, 102) as data_cancelamento,
        ASS.MotivoStat as id_motivo_cancelamento,
        ASS.Obs1 as observacoes1, -- REVALIDAR
        ASS.Obs2 as observacoes1, -- REVALIDAR
        MEN.Valor as valor_total_venda


from Associados as ASS
    inner join mensalidade as MEN ON MEN.INSCRICAO=ASS.INSCRICAO 
where ASS.Inscricao ='1'

GROUP BY    
            ASS.Inscricao, 
            ASS.Data, 
            ASS.Grupo, 
            ASS.TipoVenda, 
            ASS.DataContratoVigenciaFinal,
            ASS.DiaVenc,
            ASS.AssTipoCobranca,
            ASS.Status,
            ASS.DataStatus,
            ASS.MotivoStat,
            ASS.Obs1,
            ASS.Obs2,
            men.valor

CONSULTA SEM INCLUIR O VALOR

sql query

  • it seems (by description, should put more data to facilitate the analysis) that some of the fields grouped is with equal values so is doubling, try to sort to take the doubt

  • The column MEN.Valor returns the total of all grouped records?

  • It is not bringing equal records, brings several salaries and in some cases appears the men.value grouped.

2 answers

0

Good morning,

You can use a subquery to pick up the expiration date for each subscriber, then mount the Inner Join using this subquery, give a look

        SELECT
                ASS.Inscricao                                               AS numero_contrato,
                CONVERT(VARCHAR, ASS.data, 102)                             AS data_contrato,
                ASS.grupo                                                   AS id_tipo_contrato,
                ASS.TipoVenda                                               AS id_tipo_venda,
                ASS.Inscricao                                               AS codigo_cliente_antigo,
                CONVERT(VARCHAR,ASS.data, 102)                              AS data_vigencia_inicial,
                CONVERT(VARCHAR,ASS.DataContratoVigenciaFinal, 102)         AS data_vigencia_final,
                CONVERT(VARCHAR,ASS.DiaVenc, 102)                           AS dia_vencimento,
                ASS.AssTipoCobranca                                         AS id_preferencia_faturamento,
                ASS.AssTipoCobranca                                         AS id_periodicidade,
                ASS.AssTipoCobranca                                         AS ciclo_atual,
                CONVERT (VARCHAR,MEN.VENCIMENTO,102)                        AS data_inicio_ciclo,
                ASS.status                                                  AS registro_ativo,
                CONVERT (VARCHAR,ASS.DataStatus, 102)                       AS data_cancelamento,
                ASS.MotivoStat                                              AS id_motivo_cancelamento,
                ASS.Obs1                                                    AS observacoes1, -- REVALIDAR
                ASS.Obs2                                                    AS observacoes1, -- REVALIDAR
                MEN.Valor                                                   AS valor_total_venda


        from Associados as ASS
        inner join mensalidade as MEN ON MEN.INSCRICAO = ASS.INSCRICAO 
                                      and MEN.VENCIMENTO = (
                                                            SELECT MAX(C.VENCIMENTO)
                                                            FROM mensalidade AS C
                                                            WHERE  C.INSCRICAO = ASS.INSCRICAO 
                                                            )
        where ASS.Inscricao ='1'

With this you don’t even have to give GROUP BY

0

Initial section of the consultation (common to the two excerpts below):

SELECT
    ASS.Inscricao as numero_contrato,
    CONVERT (VARCHAR, ASS.data, 102) as data_contrato,
    ASS.grupo as id_tipo_contrato,
    ASS.TipoVenda as id_tipo_venda,
    ASS.Inscricao as codigo_cliente_antigo,
    CONVERT (VARCHAR,ASS.data, 102) as data_vigencia_inicial,
    CONVERT (VARCHAR,ASS.DataContratoVigenciaFinal, 102) as data_vigencia_final,
    CONVERT (VARCHAR,ASS.DiaVenc, 102) as dia_vencimento,
    ASS.AssTipoCobranca as id_preferencia_faturamento,
    ASS.AssTipoCobranca as id_periodicidade,
    ASS.AssTipoCobranca as ciclo_atual,
    CONVERT (VARCHAR,MEN.MaxVENCIMENTO,102) as data_inicio_ciclo,
    ASS.status as registro_ativo,
    CONVERT (VARCHAR,ASS.DataStatus, 102) as data_cancelamento,
    ASS.MotivoStat as id_motivo_cancelamento,
    ASS.Obs1 as observacoes1, -- REVALIDAR
    ASS.Obs2 as observacoes1, -- REVALIDAR
    MEN.Valor as valor_total_venda
from Associados as ASS
....

I divided into two excerpts, below, the first NOT sorting by Inscricao and the second, yes.

....
-- O trecho abaixo traz o maior valor geral para o 
-- campo Vencimento e o Valor, sem classificar por campo Inscricao
CROSS JOIN (
    SELECT TOP 1 Vencimento AS MaxVencimento,
           Valor,
           Inscricao
    FROM Mensalidade
    ORDER BY Vencimento DESC
) as MEN 
where ASS.Inscricao ='1'

See now the following excerpt to sort by Inscricao:

....
-- O trecho abaixo traz o maior valor para o 
-- campo Vencimento e o Valor, classificados por campo Inscricao
JOIN (
    SELECT m1.MaxVencimento, m1.Inscricao, m2.Valor
    FROM (
        SELECT MAX(Vencimento) AS MaxVencimento,
               Inscricao
        FROM Mensalidade
        GROUP BY Inscricao
        ) as m1
    JOIN 
        Mensalidade m2 
        ON m2.Inscricao = m1.Inscricao AND m2.Vencimento = m1.MaxVencimento
) as MEN ON MEN.Inscricao = Ass.Inscricao 
where ASS.Inscricao ='1'
  • 1

    I confess that I did not understand... I have little knowledge of SQL, I only do basic queries (as much as I know that this is basic), so I did not understand how to apply this concept explained and in which moment to use. I did some tests here and it didn’t work.

  • The two above sections are mutually exclusive, that is, if you use a, nay use the other. The initial passage is already common to the two.

  • What error appears?

  • I am ranking the maximum maturity date value by Inscricao at the bottom subquery and, at the top, I get the maximum value general due date.

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.