need to calculate Group by date

  • nrdroque

    Old Hand

    Points: 344

    hi,

    i'm creating a view and need some help.

    i have the column "valorTotal" that i get the value PCPadrao*Quantidade but if Quantidade <0 then value equal to zero.

    mi problem is that this is working line by line, but i need the acumulate from "artigo" by "Data"

    on yellow what i need is ( -1672+2949+(-1672)+(-1332)= -1727)

    is it possible

    SELECT [Artigo]
    ,sum([Quantidade]) as [Quantidade]
    ,sum(Case when [Quantidade] <0 then 0 else [Quantidade] end) as [qtdpositiva]
    ,sum([PCPadrao] *Case when [Quantidade] <0 then 0 else [Quantidade] end) as [vt]
    ,avg([PCPadrao]) as PCPadrao
    /* ,[Data]*/
    ,[Descricao]
    ,[TipoArtigo]
    ,[NometipoArtigo]
    FROM [PRIKLC].[dbo].[Inc_vw_stkArmazem000]
    where data >='2019-09-13 00:00:00' and data <='2019-09-13 23:59:59'
    group by [artigo] ,[Descricao] ,[TipoArtigo] ,[NometipoArtigo]

     

    Capturar

  • MVDBA

    SSC-Insane

    Points: 20802

    I think what you are describing is done by using "WITH ROLLUP"  in the group by or perhaps a grouping set

    have a look at this article

    https://www.databasejournal.com/features/mssql/using-the-rollup-cube-and-grouping-sets-operators.html

    for each of your dates it will give you the individual rows and a sub total for each date, you cacn also get a grand total in the bottom row

     

    MVDBA

  • terry999

    SSCarpal Tunnel

    Points: 4788

    Pls ignore if I've misunderstood (the grid output doesn't seem to match the query)

    Would adding

    CAST(DATA as Date)

    into your group by clause solve it. Assuming DATA is datetime and you want to group by day?

  • nrdroque

    Old Hand

    Points: 344

    Yes i want to group by day and by artigo.

    ill try your sugesttions

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply