Hi, I have written a report, that shows amounts per month and year. Currently the t-sql query looks like this: SELECT No_, [Search Name], Jahr, Monat, [Representative Code 1], SUM(VerkaufsbetragTatsächlich) AS VerkaufsbetragTatsächlich, SUM(EinstandsbetragTatsächlich) AS EinstandsbetragTatsächlich, SUM([Invoiced Quantity]) AS Menge, Name, [Item No_], ItemString, ItemDesc2 FROM (SELECT A.No_, A.[Search Name], YEAR(B.[Posting Date]) AS Jahr, MONTH(B.[Posting Date]) AS Monat, A.[Representative Code 1], (SELECT SUM([Sales Amount (Actual)]) AS Summe FROM [Proline ECHT$Value Entry] WHERE ([Item Ledger Entry No_] = B.[Entry No_])) AS VerkaufsbetragTatsächlich, (SELECT SUM([Cost Amount (Actual)]) AS Summe FROM [Proline ECHT$Value Entry] AS [Proline ECHT$Value Entry_1] WHERE ([Item Ledger Entry No_] = B.[Entry No_])) AS EinstandsbetragTatsächlich, C.Name, B.[Invoiced Quantity], B.[Item No_], (SELECT [Description 2] AS EXPR1 FROM [Proline ECHT$Item] WHERE (No_ = B.[Item No_])) AS ItemDesc2, (SELECT [No_ 2] + ' ' + Description AS EXPR1 FROM [Proline ECHT$Item] AS [Proline ECHT$Item_1] WHERE (No_ = B.[Item No_])) AS ItemString FROM [Proline ECHT$Dimension Value] AS C INNER JOIN [Proline ECHT$Item Ledger Entry] AS B ON C.Name = B.[Item Category Code] RIGHT OUTER JOIN [Proline ECHT$Customer] AS A ON B.[Source No_] = A.No_ WHERE (B.[Posting Date] BETWEEN @StartDate AND @EndDate) AND (B.[Entry Type] = 1) AND (A.No_ IN (@Debitor)) AND (C.[Dimension Code] LIKE 'AKG') AND (B.[Item No_] IN (@ItemNo))) AS drvTbl GROUP BY Jahr, Monat, No_, [Search Name], [Representative Code 1], Name, [Item No_ ], [Item No_], ItemString, ItemDesc2 Of course I have a group by clause, that contains all fields the report should be grouped by (especially important are the fields Year(Jahr) and Month(Monat). My problem is, that all month of a year should be returned, including those, that have no values (for these months no data is available in the database). I found a few threads that elaborate this problem, but somehow I couldn't get it up and running. I guess I lost the overview. Has anyone a hint, or even better a code example based on my code. Any help is greatly appreciated. Kind regads Michael

No one any ideas?

Hello Goblin, You can create a temp table (or actual table as well) and insert 12 rows for every month e.g. TempMonths. Then take a Left join in your query with this table on MONTH(B.[Posting Date]) = TempMonths.Month Thanks

Thanks for you fast answer. I will test this hopefully this afternoon.

I have rewritten my query, but something is still wrong: SELECT No_, [Search Name], Jahr, Monat, [Representative Code 1], SUM(VerkaufsbetragTatsächlich) AS VerkaufsbetragTatsächlich, SUM(EinstandsbetragTatsächlich) AS EinstandsbetragTatsächlich, SUM([Invoiced Quantity]) AS Menge, Name, [Item No_], ItemString, ItemDesc2 FROM (SELECT A.No_, A.[Search Name], YEAR(B.[Posting Date]) AS Jahr, Monate.Monat, A.[Representative Code 1], (SELECT SUM([Sales Amount (Actual)]) AS Summe FROM [Proline ECHT$Value Entry] WHERE ([Item Ledger Entry No_] = B.[Entry No_])) AS VerkaufsbetragTatsächlich, (SELECT SUM([Cost Amount (Actual)]) AS Summe FROM [Proline ECHT$Value Entry] AS [Proline ECHT$Value Entry_1] WHERE ([Item Ledger Entry No_] = B.[Entry No_])) AS EinstandsbetragTatsächlich, C.Name, B.[Invoiced Quantity], B.[Item No_], (SELECT [Description 2] AS EXPR1 FROM [Proline ECHT$Item] WHERE (No_ = B.[Item No_])) AS ItemDesc2, (SELECT [No_ 2] + ' ' + Description AS EXPR1 FROM [Proline ECHT$Item] AS [Proline ECHT$Item_1] WHERE (No_ = B.[Item No_])) AS ItemString, Monate.Monat AS EXPR1 FROM [Proline ECHT$Dimension Value] AS C INNER JOIN [Proline ECHT$Item Ledger Entry] AS B ON C.Name = B.[Item Category Code] RIGHT OUTER JOIN (SELECT 1 AS Monat UNION ALL SELECT 2 AS Monat UNION ALL SELECT 3 AS Monat UNION ALL SELECT 4 AS Monat UNION ALL SELECT 5 AS Monat UNION ALL SELECT 6 AS Monat UNION ALL SELECT 7 AS Monat UNION ALL SELECT 8 AS Monat UNION ALL SELECT 9 AS Monat UNION ALL SELECT 10 AS Monat UNION ALL SELECT 11 AS Monat UNION ALL SELECT 12 AS Monat) AS Monate ON MONTH(B.[Posting Date]) = Monate.Monat RIGHT OUTER JOIN [Proline ECHT$Customer] AS A ON B.[Source No_] = A.No_ WHERE (B.[Posting Date] BETWEEN @StartDate AND @EndDate) AND (B.[Entry Type] = 1) AND (A.No_ IN (@Debitor)) AND (C.[Dimension Code] LIKE 'AKG') AND (B.[Item No_] IN (@ItemNo))) AS drvTbl GROUP BY Jahr, Monat, No_, [Search Name], [Representative Code 1], Name, [Item No_ ], [Item No_], ItemString, ItemDesc2 Maybe it is the Where - Clause, or something else. Still I don't get the months without values. Do you see my mistake?

WHERE (B.[Posting Date] BETWEEN @StartDate AND @EndDate) AND (B.[Entry Type] = 1) AND (A.No_ IN (@Debitor)) AND This line is limiting the records between start and end date making it to behave like INNER JOIN.

Can I put this all in the From - Clause?

Yes, this can resolve the issue.