How can I merge 2 different queries on a monthly basis-CodePudding

I have 2 queries where I get the results displayed on a monthly basis. These are so perfect for me. Now I would like the two tables to be displayed side by side.

SELECT 

MONTH(begin_ts) AS [Month]     
,SUM(CASE WHEN bmktonr = '7' 
THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
THEN dauer ELSE reserve1 END) 
AS Prozess_Verfügbarkeit

,SUM(CASE WHEN bmktonr = '1' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '2' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '3' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '4' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '5' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '6' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '7' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '8' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '9' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '10' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
  THEN dauer ELSE reserve1 END)
  AS Verfügbarkeit

FROM [hydra1].[hydadm].[v_ereignis]
WHERE masch_nr = 'FIMI1'
AND YEAR(begin_ts)=YEAR(CURRENT_TIMESTAMP)
GROUP BY MONTH(begin_ts)
ORDER BY [Year], [Month]

and this query

SELECT p.masch_nr
,SUM(b.ruest_zeit) as SOLLRüsten
,SUM(b.bearb_zeit) as SOLLProduktion
,SUM(b.ruest_zeit_zuschl) as SOLLZuschlag
,SUM(p.bmk_07) as ISTRüsten
,SUM(p.bmk_11) as ISTProduktion
,MONTH(prot_dat) as Month
FROM [hydra1].[hydadm].[v_auftrag_status] p
JOIN [hydra1].[hydadm].[v_auftrags_bestand] b
ON b.auftrag_nr = p.auftrag_nr
WHERE p.masch_nr = 'GEORG'
AND a_status = 'E'
AND YEAR(prot_dat)=YEAR(CURRENT_TIMESTAMP)

GROUP BY p.masch_nr, MONTH(prot_dat)

1 Query 2 Query

CodePudding user response：

You can try below query to combine the two dataset.

  SELECT * FROM 
  (
     SELECT 
     MONTH(begin_ts) AS [Month]     
     ,SUM(CASE WHEN bmktonr = '7' 
      THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
      THEN dauer ELSE reserve1 END) 
     AS Prozess_Verfügbarkeit

    ,SUM(CASE WHEN bmktonr = '1' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '2' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '3' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '4' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '5' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '6' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '7' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '8' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '9' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '10' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
     THEN dauer ELSE reserve1 END)
     AS Verfügbarkeit

    FROM [hydra1].[hydadm].[v_ereignis]
    WHERE masch_nr = 'FIMI1'
    AND YEAR(begin_ts)=YEAR(CURRENT_TIMESTAMP)
    GROUP BY MONTH(begin_ts)
  )T
  INNER JOIN
  (
    SELECT p.masch_nr
    ,SUM(b.ruest_zeit) as SOLLRüsten
    ,SUM(b.bearb_zeit) as SOLLProduktion
    ,SUM(b.ruest_zeit_zuschl) as SOLLZuschlag
    ,SUM(p.bmk_07) as ISTRüsten
    ,SUM(p.bmk_11) as ISTProduktion
    ,MONTH(prot_dat) as Month
    FROM [hydra1].[hydadm].[v_auftrag_status] p
    JOIN [hydra1].[hydadm].[v_auftrags_bestand] b
    ON b.auftrag_nr = p.auftrag_nr
    WHERE p.masch_nr = 'GEORG'
    AND a_status = 'E'
    AND YEAR(prot_dat)=YEAR(CURRENT_TIMESTAMP)

    GROUP BY p.masch_nr, MONTH(prot_dat)
  )T1 on T.Month = T1.Month
  Order by T.Month

CodePudding user response：

Hello, I modified the query to join 3 tables. But I get the following error message: Msg 1033, Level 15, State 1, Line 82 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."

Can you help me?

SELECT * FROM 
(
SELECT 
MONTH(begin_ts) AS [Month]     
,SUM(CASE WHEN bmktonr = '7' 
THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
THEN dauer ELSE reserve1 END) 
AS Prozess_Verfügbarkeit

,SUM(CASE WHEN bmktonr = '1' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '2' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '3' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '4' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '5' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '6' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '7' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '8' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '9' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '10' 
 THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
 THEN dauer ELSE reserve1 END)
 AS VerfügbarkeitP

 ,SUM(CASE WHEN bmktonr = '1' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '2' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '3' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '4' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '5' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '6' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '7' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '8' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '10' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
  THEN dauer ELSE reserve1 END)  AS Tech_Verfügbarkeit

  ,SUM(CASE WHEN bmktonr = '1' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '2' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '3' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '4' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '5' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '6' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '7' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '8' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '9' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '10' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
  THEN dauer ELSE reserve1 END) AS VerfügbarkeitT
  FROM [hydra1].[hydadm].[v_ereignis]
  WHERE masch_nr = 'FIMI1'
  AND YEAR(begin_ts)=YEAR(CURRENT_TIMESTAMP)
  GROUP BY MONTH(begin_ts)
  )T
  INNER JOIN
  (
  SELECT p.masch_nr
,SUM(b.ruest_zeit) as SOLLRüsten
,SUM(b.bearb_zeit) as SOLLProduktion
,SUM(b.ruest_zeit_zuschl) as SOLLZuschlag
,SUM(p.bmk_07) as ISTRüsten
,SUM(p.bmk_11) as ISTProduktion
,MONTH(prot_dat) as Month
FROM [hydra1].[hydadm].[v_auftrag_status] p
JOIN [hydra1].[hydadm].[v_auftrags_bestand] b
ON b.auftrag_nr = p.auftrag_nr
WHERE p.masch_nr = 'FIMI1'
AND a_status = 'E'
AND YEAR(prot_dat)=YEAR(CURRENT_TIMESTAMP)

GROUP BY p.masch_nr, MONTH(prot_dat)
)T1 on T.Month = T1.Month
INNER JOIN
(SELECT
MONTH(begin_ts) AS [Month] ,
COUNT(m_status) as Restcoils,
SUM(dauer) AS gesamt_dauer
FROM [hydra1].[hydadm].[v_ereignis]
WHERE masch_nr = 'QTA5'
AND m_status ='4'
AND YEAR(begin_ts)=YEAR(CURRENT_TIMESTAMP)
GROUP BY MONTH(begin_ts)
ORDER BY [Month]) T2 on T1.Month = T2.Month
Order by T2.Month