Merge two queries in SQL Server

Asked

Viewed 917 times

0

Hello, I have two queries of the same table, but I’m not able to join them parallel, I tried to use the UNION, but it didn’t work, is there any way to do it? Or else create a column with the accumulated difference (Backlog) between the totals of the two tables?

inserir a descrição da imagem aqui

SELECT 
    MONTH(created) AS 'MES'
   ,YEAR(created) AS 'ANO'
   ,COUNT(Number) AS 'TOTAL'
FROM [ServiceNow VR01].[dbo].[incident]
    WHERE created > '2013-01-01' 
    GROUP BY YEAR(created), MONTH(created) 
    ORDER BY ANO, MES

SELECT 
    MONTH([Closed AT]) AS 'MES'
   ,YEAR([Closed AT]) AS 'ANO'
   ,COUNT(Number) AS 'TOTAL' 
FROM [ServiceNow VR01].[dbo].[incident] 
    WHERE [Closed AT] > '2013-01-01' 
    GROUP BY HONTH([Closed AT]), YEAR([Closed AT]) 
    ORDER BY ANO, MES

Kind of:

 Total C  Total F   Balance    Backlog
 1420   - 424    =  996        996
 18433  - 9480   =  8953       9949
 26500  - 30137  = -3637       6312

Note: I am using SQL Server 2012 and the two tables have the same number of rows.

sql sql-server

  • Speak Thiago, this would help you? Alternative to UNION clause

  • Hello alive, in order to help you, explain better what you want, in the query that presents the output will be MES, YEAR and TOTAL, and the result table want something like this:' Total C, Total F, Balance and Backlog, additionally if you put on the sqlfiddle platform an example of your real table with some of the data along with your base query, it helps a lot and it will be easier to be helped.... platform: (http://sqlfiddle.com).

  • From what I could understand it seems that an INNER JOIN with month and year would meet what you want. If they do not always occur in both tables maybe an OUTER JOIN is better.

1 answer

1


From what I understood a Join using the two sql as virtual tables , rest is to manipulate the aligned fields.

SELECT * 
FROM
( SELECT MONTH(created) AS 'MES' ,
 YEAR(created) AS 'ANO' ,
 COUNT(Number) AS 'TOTAL' 
 FROM [ServiceNow VR01].[dbo].[incident] 
 WHERE created > '2013-01-01' 
 GROUP BY YEAR(created), 
 MONTH(created)) TC,
 (SELECT MONTH([Closed AT]) AS 'MES' ,
  YEAR([Closed AT]) AS 'ANO' ,
  COUNT(Number) AS 'TOTAL' 
  FROM [ServiceNow VR01].[dbo].[incident] 
  WHERE [Closed AT] > '2013-01-01' 
  GROUP BY HONTH([Closed AT]), 
  YEAR([Closed AT])) TS
WHERE TC.ANO = TS.ANO
AND TC.MES = TS.MES
ORDER BY TC.ANO,TC.MES

Browser other questions tagged sql sql-server

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