sql server table tmp , case , TRY_CONVERT

date.sql

CREATE PROCEDURE "SP_Statistique_Date"(
	@dateStart DATE,
	@dateFin DATE
)

AS
BEGIN


DROP TABLE IF EXISTS #tmp_umsatz , #tmp_google ,  #tmp_offen , #tmp_auftrage ;



--- CREATE TABLE #tmp_umsatz

CREATE TABLE #tmp_umsatz
(
	website INT,
	nom VARCHAR(50),
	umsatz float
)

INSERT INTO #tmp_umsatz
-- Query SELECT ALL website total umsatz WHERE date Start to End 

		select  DISTINCT wr.website AS website, w.nom,
		 sum(ISNULL(wr.sales, 0 )+ISNULL(wr.sales2, 0 )+ISNULL(wr.costt, 0 )) as umsatz
		from iso.works wr
		 INNER JOIN  iso.website w ON  w.id = wr.website
		WHERE TRY_CONVERT(DATE,wr.stardate) >= @dateStart AND TRY_CONVERT(DATE,wr.stardate) <= @dateFin --'2020-11-13'
		group by --wr.stardate ,
		 wr.website ,w.nom
      ORDER BY wr.website
		
	 
--- TABLE Google ADS #tmp_google   
      
CREATE TABLE #tmp_google
(
	website INT,
	google float
)

INSERT INTO #tmp_google 

-- Query google Ads by Website id , dateTime  
select  gbc.website ,sum(ISNULL(gbc.coasten, 0 )) as google
		from iso.gebits_coasten gbc
		inner join iso.costenday cd on gbc.id=cd.gbid
		WHERE TRY_CONVERT(DATE,cd.dayst) >= @dateStart AND TRY_CONVERT(DATE,cd.dayst) <= @dateFin
group BY gbc.website 


--- TABLE Google ADS #tmp_google   
      
CREATE TABLE #tmp_offen
(
	website INT,
	offen INT
)

INSERT INTO #tmp_offen

--- the offen works in each website 

select wk.website as website,count(*) as Aufträge_Offen
		from iso.works wk
		inner join iso.gebit gb on gb.id = wk.gbits
		WHERE  
		(TRY_CONVERT(DATE,wk.stardate) >= @dateStart AND TRY_CONVERT(DATE,wk.stardate) <= @dateFin )
		AND wk.statuss='offen' --- open
	 
group by wk.website



CREATE TABLE #tmp_auftrage
(
	website INT,
	auftrage INT
)

INSERT INTO #tmp_auftrage

select wk.website as website,count(wk.id) as auftrage
		from iso.works wk
		inner join iso.gebit gb on gb.id = wk.gbits
		WHERE  
		(TRY_CONVERT(DATE,wk.stardate) >= @dateStart AND TRY_CONVERT(DATE,wk.stardate) <= @dateFin )
		--AND wk.statuss='Abgeschlossen' --- done Successly
	 
group by wk.website



SELECT 
	 ISNULL(u.website,g.website) AS idwebsite ,
		-- if the website exist in table google and works not exist , how create this database is not Correct at All
	 (CASE WHEN u.nom IS NULL THEN (SELECT TOP 1 nom FROM iso.website WHERE id = g.website) ELSE u.nom END) AS websiteName, 
	ISNULL(o.offen, 0) AS offen,
    ISNULL(a.auftrage,0) AS Auftrage ,
    ISNULL(FORMAT( u.umsatz , 'N2'), 0)  AS umsatz,
    ISNULL(FORMAT(u.umsatz * 0.55,'N2'), 0)  AS Anteil ,
    ISNULL(FORMAT(u.umsatz / ISNULL(a.auftrage,0) ,'N2'), 0) AS Drush , 
    ISNULL(FORMAT(ISNULL(g.google,0) ,'N2'), 0) AS googleads ,
    ISNULL(FORMAT( ISNULL( g.google , 0) / ISNULL(a.auftrage,1) ,'N2'), 0)    AS KostenpAuftrag ,
    --ISNULL(FORMAT( ( u.umsatz * 0.55) - ISNULL(g.google,0) ,'N2'), 0)  AS gewinn
	FORMAT(ISNULL(u.umsatz * 0.55 ,0 ) -  ISNULL(g.google,0), 'N2' ) AS gewinn
	    
 
   FROM #tmp_umsatz u 
 
	FULL OUTER JOIN #tmp_google g on
	u.website = g.website FULL OUTER JOIN #tmp_offen o ON
	o.website = u.website FULL OUTER JOIN #tmp_auftrage a ON
	a.website = u.website
--	WHERE u.website IS NOT NULL
		ORDER BY FORMAT( ( u.umsatz * 0.55) - ISNULL(g.google,0) ,'N2') asc


END