-- CREATE A CALENDAR TABLE.
CREATE TABLE #Calendar(Dt DATE NOT NULL PRIMARY KEY CLUSTERED);

;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 31) -- number of days you want to consider
INSERT INTO #Calendar SELECT DATEADD(dd,n-1,'20120701') 
                      FROM N OPTION (MAXRECURSION 0);

-- join with the calendar table
SELECT
	c.Dt AS DATA,
	SUM(erp.MSEG.MENGE * erp.MARM.BRGEW) AS pes
FROM
	#Calendar c LEFT JOIN 
	(erp.MKPF
	INNER JOIN erp.MSEG
		ON  erp.MKPF.MANDT = erp.MSEG.MANDT AND erp.MKPF.MBLNR = erp.MSEG.MBLNR
	INNER JOIN erp.MARM
		ON  erp.MSEG.MANDT = erp.MARM.MANDT AND erp.MSEG.MATNR = erp.MARM.MATNR AND 
		    erp.MSEG.MEINS = erp.MARM.MEINH
	INNER JOIN erp.MARA
		ON  erp.MSEG.MANDT = erp.MARA.MANDT AND erp.MSEG.MATNR = erp.MARA.MATNR)
        ON rp.MKPF.BUDAT = c.Dt
WHERE
	erp.MKPF.MANDT = '100'
	AND erp.MKPF.BUDAT >= '20120720'
	AND erp.MKPF.BUDAT <= CONVERT(VARCHAR(8), GETDATE(), 112) -1
	AND erp.MSEG.LGORT IN ('1001', '1069')
	AND erp.MSEG.BWART IN ('101', '102', '311', '312')
	AND erp.MSEG.WERKS = '1001'
	AND erp.MARA.MTART IN ('Z001', 'Z010', 'Z002', 'Z02E')
GROUP BY
	erp.MKPF.BUDAT

quote:

---

Originally posted by aesquis

Ok. Thanks a lot, I'm sure it's a good solution and I'll try. Now i'm looking for a calendar table in SAP.

---