Jump to content
Sign in to follow this  
pessantiago

somar valores por anos e por meses

Recommended Posts

pessantiago

boas preciso de uma ajuda vossa tenho esta query  preciso de ter valores  para  mes,2015,2016,2017

 

alguma ideia como posso adptar a query??

mes,2017

   1    4725,30
    2    16952,34
    3    8146,59
    4    17806,95
    5    15769,71
    6    8474,02
    7    10311,86
    8    3996,55
    9    4428,49
 10    11098,54

 

isto é o que me da está query

SELECT R.MES,R.TOTALR - E.TOTALE AS '2017' FROM 


 (SELECT YEAR (dtestado) as ano, MONTH (dtestado) as mes, SUM(total) AS TOTALR FROM tabela1

  where  dtestado  between '01-01-2017'  and  getdate() and (estado='C' or estado='N')
  
  
  group  by MONTH (dtestado),YEAR (dtestado)) R,

     (SELECT  YEAR (dtestado) as ano,MONTH (dtestado) as mes , SUM(total) AS TOTALE FROM tabela2 
      where  dtestado  between '01-01-2017'  and  getdate() and (estado='C' or estado='N')
  group  by MONTH (dtestado ),YEAR (dtestado)) E 

    where 
   R.mes=e.mes and    
   E.mes  between MONTH ('01-01-2017')  and  MONTH(getdate() ) and  R.mes  between MONTH ('01-01-2017')  and  MONTH(getdate() ) 


  
 

Share this post


Link to post
Share on other sites
pessantiago

Se nao tiveres valores naquele mes apresenta 0

assim esta-me a repetir valores

SELECT R.MES,R.TOTALR - E.TOTALE AS '2017',R1.TOTALR - E1.TOTALE AS '2016' FROM 
(SELECT YEAR (dtestado) as ano, MONTH (dtestado) as mes, SUM(total) AS TOTALR FROM tabela1

  where  dtestado  between '01-01-2017'  and  getdate() and (estado='C' or estado='N')
  
  
  group  by MONTH (dtestado),YEAR (dtestado)) R,

     (SELECT  YEAR (dtestado) as ano,MONTH (dtestado) as mes , SUM(total) AS TOTALE FROM tabela2 
      where  dtestado  between '01-01-2017'  and  getdate() and (estado='C' or estado='N')
  group  by MONTH (dtestado ),YEAR (dtestado)) E, 

 (SELECT YEAR (dtestado) as ano, MONTH (dtestado) as mes, SUM(total) AS TOTALR FROM tabela1

  where  dtestado  between '01-01-2016'  and  getdate()-365  and (estado='C' or estado='N')
  
  
  group  by MONTH (dtestado),YEAR (dtestado)) R1,

     (SELECT  YEAR (dtestado) as ano,MONTH (dtestado) as mes , SUM(total) AS TOTALE FROM tabela2 
      where  dtestado  between '01-01-2016'  and  getdate()-365 and (estado='C' or estado='N')
  group  by MONTH (dtestado ),YEAR (dtestado)) E1 
    where 
   R.mes=e.mes and    
   E.mes  between MONTH ('01-01-2017')  and  MONTH(getdate() ) and  R.mes  between MONTH ('01-01-2017')  and  MONTH(getdate() ) 
  or
    ( R1.mes=e1.mes and    
   E1.mes  between MONTH ('01-01-2016')  and  MONTH(getdate()-365 ) and  R1.mes  between MONTH ('01-01-2016')  and  MONTH(getdate()-365 ) )

 

Edited by pessantiago

Share this post


Link to post
Share on other sites
pessantiago

os campos nao sao os mesmo mas da para perceber

precisava de ter mes,2016,2016

e por baixo os valores ajudem



SELECT 
C.MES ,
	C.[2017]-D.[2017] as '2017'

FROM(
		SELECT  MES, [2017]
		FROM
		(SELECT  YEAR(dtestado) as ANO,  MONTH(dtestado) as MES,(totalcomlq) AS TOTAL
		FROM RECIBOS   where  dtestado  between '01-01-2017'  and  getdate()   and (estado='C' or estado='N') )AS R
		PIVOT
		(
		SUM(R.TOTAL)
		FOR ANO IN ( [2017])
      ) AS TABELA
	 )C 
	 INNER JOIN(
		SELECT Mes,  [2017]
		FROM
		(SELECT  YEAR(dtestado) as ANO, MONTH(dtestado) as MES,(ES.totalcomlq) AS TOTALE
		FROM 
		ESTORNOS as ES  where  ES.dtestado  between '01-01-2017'  and  getdate()  and (estado='C' or estado='N') ) AS E 
		PIVOT
		(
		SUM(E.TOTALE)
		FOR ANO IN ( [2017])
		) AS TABELA1
 )D on D.MES=C.MES

 
SELECT 
C1.MES ,
	C1.[2016]-D1.[2016] as '2016'
FROM(
 	SELECT  mes,[2016]
		FROM
		(SELECT   YEAR(dtestado) as ANO,  MONTH(dtestado) as MES,(totalcomlq) AS TOTAL
		FROM RECIBOS   where  dtestado   between '01-01-'+cast(YEAR(getdate()-(365)) as Varchar(10))  and '31-12-'+cast(YEAR(getdate()-(365)) as Varchar(10))    and (estado='C' or estado='N') )AS R
		PIVOT
		(
		SUM(R.TOTAL)
		FOR ANO IN ([2016])
		) AS TABELA11
 )C1
  INNER JOIN(
	SELECT mes,[2016]
		FROM
		(SELECT  YEAR(dtestado) as ANO, MONTH(dtestado) as MES,(ES.totalcomlq) AS TOTALE
		FROM 
		ESTORNOS as ES  where  ES.dtestado  between '01-01-'+cast(YEAR(getdate()-(365)) as Varchar(10))  and '31-12-'+cast(YEAR(getdate()-(365)) as Varchar(10)) and (estado='C' or estado='N') ) AS E 
		PIVOT
		(
		SUM(E.TOTALE)
		FOR ANO IN ( [2016])
		) AS TABELA22
 ) D1 on D1.MES= C1.MES
  

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.