Site icon Trip Cow Boy

How can I merge a third table on a monthly basis [closed]

I managed to merge 2 tables on a monthly basis. Now I want to add a third table. Can someone give me a tip.

Here are the 2 queries that should be joined

The first one:

 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)
 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)  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) 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
Order by T.Month 

the second one:

SELECT 
MONTH(b.bearb_date) AS [Month] ,
COUNT(CASE WHEN m.user_c_29 = 'J' THEN m.user_c_29 END) as Restcoils 
FROM [hydra1].[hydadm].[v_auftrags_bestand] b
JOIN [hydra1].[hydadm].[v_mlst_hy] m
ON b.auftrag_nr = m.auftrag_nr
WHERE b.masch_nr = 'QTA5'    
AND YEAR(b.bearb_date)=YEAR(CURRENT_TIMESTAMP)
AND b.bearb_date >= DATEADD(YEAR,DATEDIFF(YEAR,0,CURRENT_TIMESTAMP),0)
AND b.bearb_date <  DATEADD(YEAR,DATEDIFF(YEAR,0,CURRENT_TIMESTAMP)+1,0)
GROUP BY  MONTH(b.bearb_date), masch_nr
ORDER BY [Month]

Thanks a lot.

enter image description here enter image description here

Exit mobile version