need to calculate Group by date

Question

Post reply

need to calculate Group by date

nrdroque

Old Hand

Points: 348

September 18, 2019 at 2:23 pm

#3681097

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

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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 SSCertifiable Points: 5033 More actions · Answer 2

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: 348 More actions · Answer 3

Yes i want to group by day and by artigo.

ill try your sugesttions