none
AJUDA COM SQL RRS feed

  • Pergunta

  • Boa tarde Galera,

    Sou novo em SQL e to com um problema aparentemente fácil, mas não to conseguindo resolver, será que algume pode me ajudar  ???

    Eu marquei em negrito onde pode estar o problema.
    Eu coloquei a coluna 'teste' para ver se está trazendo a informação que eu preciso, e ele está retornando corretamente, no premeiro registro vem o numero  61   ESTA OK.

    O problema:

    No ICMS eu preciso que ele pegue o valor TAXSUM do registro onde ele ele satisfaz todas as condições mais essa
    t9.LineNum= '61'  porém eu fixei o número 61, e o correto é que ele viesse com as demais variáveis.

    Acho que o Carnaval não fez bem !! hehe

    Select c.U_NumContrato as [Nº Contrato],
    'BRL' as [Moeda],
    '55' as [Código País],
    c.U_CodCategoria as [Cód Categoria],
    c.U_Cod_Propriedade as [Cód Propriedade],
    b.ItemCode as [Item],
    b.Dscription as [Descrição],
    '101' as [Cód Tipo Venda],
    CAST(b.Quantity as Integer) as [Quantidade],


    BaseLine as 'teste',


    (isnull (b.LineTotal, '0')) as [Valor Bruto],

    (isnull (ic.ICMS, '0')) + (isnull (b.LineTotal, '0')*3.65/100) as 'Desconto',

    (isnull (b.LineTotal, '0')) - (isnull (ic.ICMS, '0')) - (isnull (b.LineTotal, '0')*3.65/100) as [Valor Líquido],

    --((isnull (b.LineTotal, '0') + isnull (b.VatSum, '0')) - (isnull (ic.ICMS, '0') + isnull (ip.IPI, '0') + isnull ( pis.PIS, '0') + isnull (cofins.COFINS, '0') + ----isnull ( icmsst.ICMSST, '0'))) as 'Valor_Liquido',

    c.U_Aliq_Royalties as [(%) Royalties],

    (((isnull (b.LineTotal,'0')  - (isnull (ic.ICMS, '0'))) - (isnull (b.LineTotal, '0')*3.65/100))  *c.U_Aliq_Royalties/100) as [Royalties],


    a.CardName as [Razão Social],
    d.U_CodVarejista as [Cód Varejista],
    c.CodeBars as 'EAN',
    a.Serial as 'Nota',
    a.TaxDate as [Data NF.],
    ic.ICMS AS [Valor ICMS],

    --(pis.PIS + cofins.COFINS) AS [PIS/COFINS],
     (isnull (b.LineTotal, '0')*3.65/100) AS [PIS/COFINS],


    a.BpLID as 'empresa'
    --ip.IPI AS 'VL IPI',
    --pis.PIS AS 'VL PIS',
    --cofins.COFINS AS 'VL COFINS',
    --icmsst.ICMSST AS 'VL ICMS ST'

    From oinv a
    inner join INV1 b on a.DocEntry = b.DocEntry
    inner join OITM c on b.ItemCode =c.ItemCode
    inner join OCRD d on a.CardCode = d.CardCode

    --ICMS
    left join (select t9.docentry,
    SUM(t9.taxsum)    as ICMS
    from inv4 t9 where t9.statype = 2    and   t9.LineNum= '61'    
    group by t9.docentry)
    ic on ic.docentry = a.docentry

    --IPI
    left join (select t9.docentry,
    SUM(t9.taxsum) as IPI
    from inv4 t9 where t9.statype = 3 and t9.groupnum = '-1' group by t9.docentry) ip on ip.docentry = a.docentry

    --PIS
    left join (select t9.docentry,
    SUM(t9.taxsum) as PIS
    from inv4 t9 where t9.statype = 10 and t9.groupnum = '-1' group by t9.docentry) pis on pis.docentry = a.docentry

    --COFINS
    left join (select t9.docentry,
    SUM(t9.taxsum) as COFINS
    from inv4 t9 where t9.statype = 11 and t9.groupnum = '-1' group by t9.docentry) cofins on cofins.docentry = a.docentry

    --ICMS ST
    left join (select t9.docentry,
    SUM(t9.taxsum) as ICMSST
    from inv4 t9 where t9.statype = 13 and t9.groupnum = '-1' group by t9.docentry) icmsst on icmsst.docentry = a.docentry

    --where b.TrgetEntry is null and  a.DocEntry = '35'

    Where a.DocDate between '[%0]' and '[%1]' AND  a.BpLID =  '[%3]' AND c.U_Royalties between '[%4]' and '[%5]' AND
    a.Model = '39' --and a.DocEntry = '35'
     AND a.DocEntry NOT IN ( SELECT dnf1.Baseentry FROM ORIN dnf INNER JOIN RIN1 dnf1 ON dnf.DocEntry = dnf1.DocEntry
                                               WHERE dnf.SeqCode = 1 AND dnf1.BaseType = 13)
    order by a.Serial

    terça-feira, 12 de fevereiro de 2013 15:03

Respostas

  • r4to,

    Veja se este exemplo ajuda:

    Declare @SeuValor Int
    
    Set @SeuValor = 100
    
    Select c.U_NumContrato as [Nº Contrato],
    'BRL' as [Moeda],
    '55' as [Código País],
    c.U_CodCategoria as [Cód Categoria],
    c.U_Cod_Propriedade as [Cód Propriedade],
    b.ItemCode as [Item], 
    b.Dscription as [Descrição], 
    '101' as [Cód Tipo Venda],
    CAST(b.Quantity as Integer) as [Quantidade], 
    
    
    BaseLine as 'teste',
    
    
    (isnull (b.LineTotal, '0')) as [Valor Bruto],
    
    (isnull (ic.ICMS, '0')) + (isnull (b.LineTotal, '0')*3.65/100) as 'Desconto',
    
    (isnull (b.LineTotal, '0')) - (isnull (ic.ICMS, '0')) - (isnull (b.LineTotal, '0')*3.65/100) as [Valor Líquido],
    
    --((isnull (b.LineTotal, '0') + isnull (b.VatSum, '0')) - (isnull (ic.ICMS, '0') + isnull (ip.IPI, '0') + isnull ( pis.PIS, '0') + isnull (cofins.COFINS, '0') + ----isnull ( icmsst.ICMSST, '0'))) as 'Valor_Liquido', 
    
    c.U_Aliq_Royalties as [(%) Royalties],
    
    (((isnull (b.LineTotal,'0')  - (isnull (ic.ICMS, '0'))) - (isnull (b.LineTotal, '0')*3.65/100))  *c.U_Aliq_Royalties/100) as [Royalties],
    
    
    a.CardName as [Razão Social], 
    d.U_CodVarejista as [Cód Varejista],
    c.CodeBars as 'EAN', 
    a.Serial as 'Nota', 
    a.TaxDate as [Data NF.],
    ic.ICMS AS [Valor ICMS],
    
    --(pis.PIS + cofins.COFINS) AS [PIS/COFINS],
     (isnull (b.LineTotal, '0')*3.65/100) AS [PIS/COFINS],
    
    
    a.BpLID as 'empresa'
    --ip.IPI AS 'VL IPI',
    --pis.PIS AS 'VL PIS',
    --cofins.COFINS AS 'VL COFINS',
    --icmsst.ICMSST AS 'VL ICMS ST'
    
    From oinv a
    inner join INV1 b on a.DocEntry = b.DocEntry 
    inner join OITM c on b.ItemCode =c.ItemCode
    inner join OCRD d on a.CardCode = d.CardCode
    
    --ICMS
    left join (select t9.docentry, 
    SUM(t9.taxsum)    as ICMS
    from inv4 t9 where t9.statype = 2    and   t9.LineNum= @SeuValor     
    group by t9.docentry)
    ic on ic.docentry = a.docentry 
    
    --IPI
    left join (select t9.docentry,
    SUM(t9.taxsum) as IPI
    from inv4 t9 where t9.statype = 3 and t9.groupnum = '-1' group by t9.docentry) ip on ip.docentry = a.docentry
    
    --PIS
    left join (select t9.docentry,
    SUM(t9.taxsum) as PIS
    from inv4 t9 where t9.statype = 10 and t9.groupnum = '-1' group by t9.docentry) pis on pis.docentry = a.docentry
    
    --COFINS
    left join (select t9.docentry,
    SUM(t9.taxsum) as COFINS
    from inv4 t9 where t9.statype = 11 and t9.groupnum = '-1' group by t9.docentry) cofins on cofins.docentry = a.docentry
    
    --ICMS ST
    left join (select t9.docentry,
    SUM(t9.taxsum) as ICMSST
    from inv4 t9 where t9.statype = 13 and t9.groupnum = '-1' group by t9.docentry) icmsst on icmsst.docentry = a.docentry
    
    --where b.TrgetEntry is null and  a.DocEntry = '35'
    
    Where a.DocDate between '[%0]' and '[%1]' AND  a.BpLID =  '[%3]' AND c.U_Royalties between '[%4]' and '[%5]' AND
    a.Model = '39' --and a.DocEntry = '35'
     AND a.DocEntry NOT IN ( SELECT dnf1.Baseentry FROM ORIN dnf INNER JOIN RIN1 dnf1 ON dnf.DocEntry = dnf1.DocEntry
                                               WHERE dnf.SeqCode = 1 AND dnf1.BaseType = 13)
    order by a.Serial
    


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 13 de fevereiro de 2013 15:47
    Moderador
  • Galera deu certo aqui !!

    Tive que mudar

    --ICMS
    left join (select t9.docentry, 
    SUM(t9.taxsum)    as ICMS
    from inv4 t9 where t9.statype = 2    and   t9.LineNum= @SeuValor     
    group by t9.docentry)
    ic on ic.docentry = a.docentry 

    para esse

    ic.ICMS AS [Valor ICMS],
    (select SUM(t9.taxsum)    as ICMS from inv4 t9  
    inner join INV1 b9 on t9.DocEntry = b9.DocEntry
    inner join OITM c9 on b9.ItemCode = c9.ItemCode
    where t9.statype = 2 and a.docentry = t9.docentry and c9.U_Royalties  between '09' and '09'  
    and b9.linenum = t9.linenum),

    valeu

    sábado, 16 de fevereiro de 2013 14:56

Todas as Respostas

  • r4to,

    Veja se este exemplo ajuda:

    Declare @SeuValor Int
    
    Set @SeuValor = 100
    
    Select c.U_NumContrato as [Nº Contrato],
    'BRL' as [Moeda],
    '55' as [Código País],
    c.U_CodCategoria as [Cód Categoria],
    c.U_Cod_Propriedade as [Cód Propriedade],
    b.ItemCode as [Item], 
    b.Dscription as [Descrição], 
    '101' as [Cód Tipo Venda],
    CAST(b.Quantity as Integer) as [Quantidade], 
    
    
    BaseLine as 'teste',
    
    
    (isnull (b.LineTotal, '0')) as [Valor Bruto],
    
    (isnull (ic.ICMS, '0')) + (isnull (b.LineTotal, '0')*3.65/100) as 'Desconto',
    
    (isnull (b.LineTotal, '0')) - (isnull (ic.ICMS, '0')) - (isnull (b.LineTotal, '0')*3.65/100) as [Valor Líquido],
    
    --((isnull (b.LineTotal, '0') + isnull (b.VatSum, '0')) - (isnull (ic.ICMS, '0') + isnull (ip.IPI, '0') + isnull ( pis.PIS, '0') + isnull (cofins.COFINS, '0') + ----isnull ( icmsst.ICMSST, '0'))) as 'Valor_Liquido', 
    
    c.U_Aliq_Royalties as [(%) Royalties],
    
    (((isnull (b.LineTotal,'0')  - (isnull (ic.ICMS, '0'))) - (isnull (b.LineTotal, '0')*3.65/100))  *c.U_Aliq_Royalties/100) as [Royalties],
    
    
    a.CardName as [Razão Social], 
    d.U_CodVarejista as [Cód Varejista],
    c.CodeBars as 'EAN', 
    a.Serial as 'Nota', 
    a.TaxDate as [Data NF.],
    ic.ICMS AS [Valor ICMS],
    
    --(pis.PIS + cofins.COFINS) AS [PIS/COFINS],
     (isnull (b.LineTotal, '0')*3.65/100) AS [PIS/COFINS],
    
    
    a.BpLID as 'empresa'
    --ip.IPI AS 'VL IPI',
    --pis.PIS AS 'VL PIS',
    --cofins.COFINS AS 'VL COFINS',
    --icmsst.ICMSST AS 'VL ICMS ST'
    
    From oinv a
    inner join INV1 b on a.DocEntry = b.DocEntry 
    inner join OITM c on b.ItemCode =c.ItemCode
    inner join OCRD d on a.CardCode = d.CardCode
    
    --ICMS
    left join (select t9.docentry, 
    SUM(t9.taxsum)    as ICMS
    from inv4 t9 where t9.statype = 2    and   t9.LineNum= @SeuValor     
    group by t9.docentry)
    ic on ic.docentry = a.docentry 
    
    --IPI
    left join (select t9.docentry,
    SUM(t9.taxsum) as IPI
    from inv4 t9 where t9.statype = 3 and t9.groupnum = '-1' group by t9.docentry) ip on ip.docentry = a.docentry
    
    --PIS
    left join (select t9.docentry,
    SUM(t9.taxsum) as PIS
    from inv4 t9 where t9.statype = 10 and t9.groupnum = '-1' group by t9.docentry) pis on pis.docentry = a.docentry
    
    --COFINS
    left join (select t9.docentry,
    SUM(t9.taxsum) as COFINS
    from inv4 t9 where t9.statype = 11 and t9.groupnum = '-1' group by t9.docentry) cofins on cofins.docentry = a.docentry
    
    --ICMS ST
    left join (select t9.docentry,
    SUM(t9.taxsum) as ICMSST
    from inv4 t9 where t9.statype = 13 and t9.groupnum = '-1' group by t9.docentry) icmsst on icmsst.docentry = a.docentry
    
    --where b.TrgetEntry is null and  a.DocEntry = '35'
    
    Where a.DocDate between '[%0]' and '[%1]' AND  a.BpLID =  '[%3]' AND c.U_Royalties between '[%4]' and '[%5]' AND
    a.Model = '39' --and a.DocEntry = '35'
     AND a.DocEntry NOT IN ( SELECT dnf1.Baseentry FROM ORIN dnf INNER JOIN RIN1 dnf1 ON dnf.DocEntry = dnf1.DocEntry
                                               WHERE dnf.SeqCode = 1 AND dnf1.BaseType = 13)
    order by a.Serial
    


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 13 de fevereiro de 2013 15:47
    Moderador
  • Quase,

    Eu preciso que esse @SeuValor  seja incrementado pela BaseLine, assim qdo fizer o Select ele vai trazer o valor que to precisando.

    quarta-feira, 13 de fevereiro de 2013 18:47
  • r4to,

    Em qual parte do código você deseja realizar o incremento?

    Acredito que seja o caso de você pegar a variável @SeuValor + o retorno da Baseline!!!!


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sábado, 16 de fevereiro de 2013 10:34
    Moderador
  • Galera deu certo aqui !!

    Tive que mudar

    --ICMS
    left join (select t9.docentry, 
    SUM(t9.taxsum)    as ICMS
    from inv4 t9 where t9.statype = 2    and   t9.LineNum= @SeuValor     
    group by t9.docentry)
    ic on ic.docentry = a.docentry 

    para esse

    ic.ICMS AS [Valor ICMS],
    (select SUM(t9.taxsum)    as ICMS from inv4 t9  
    inner join INV1 b9 on t9.DocEntry = b9.DocEntry
    inner join OITM c9 on b9.ItemCode = c9.ItemCode
    where t9.statype = 2 and a.docentry = t9.docentry and c9.U_Royalties  between '09' and '09'  
    and b9.linenum = t9.linenum),

    valeu

    sábado, 16 de fevereiro de 2013 14:56