0

I a having issues grouping the query below into monthly aggregate. Table is cross joined with a table to pick up the rate and inner joined with another that contains just dates to show nulls for dates where data doesnt exist in the table (Client Request) It works fine with the daily grouping which is below. Please how can I group it monthly.

Select * from(select [Letter_Date] [Date],Council
SUM([Total_Corr])*[Rate][Total]
FROM Correspondence
cross join 
Input_Variable_Price
where [Revenue_Name] = 'Correspondence'
group by [Letter_Date],Council)AS ED

RIGHT JOIN 
(Select '21'[No],b_date,[Revenue_Name][Report],[Unit],[Rate]
From Blank_dates 
cross join 
Input_Variable_Price
where [Revenue_Name] = 'Correspondence') AS BD
ON ED.Date = BD.[b_date]

Cheers

Nugeswale
  • 63
  • 7
  • `GROUP BY` the extract of the month from your date/timestamp? – Kamil Gosciminski Oct 27 '14 at 15:25
  • It doesnt work. This is what I get: Column 'ED.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.. Dont know what else to do. – Nugeswale Oct 27 '14 at 16:12

1 Answers1

1

I would use the following: add in any other aggregations you need to the SELECT, and whatever items in the GROUP BY that you require.

Select DATEADD(month, DATEDIFF(month, 0, [Date]), 0) AS StartOfMonth, SUM(Total)
from
  (
    select [Letter_Date] [Date],Council,
        SUM([Total_Corr])*[Rate] [Total]
    FROM 
        Correspondence
         cross join 
        Input_Variable_Price
    where [Revenue_Name] = 'Correspondence'
    group by [Letter_Date],Council
  )AS ED

RIGHT JOIN 
  (
    Select 
        '21'[No],
        b_date,
        [Revenue_Name][Report],
        [Unit],
        [Rate]
    From 
        Blank_dates 
         cross join 
        Input_Variable_Price
    where [Revenue_Name] = 'Correspondence'
  ) AS BD ON 
    ED.Date = BD.[b_date]
GROUP BY DATEADD(month, DATEDIFF(month, 0, [Date]), 0)
AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • If you need to format the name of the month differently, you can use [this question's answer](http://stackoverflow.com/questions/185520/convert-month-number-to-month-name-function-in-sql) to get the month's name, then concatenate the year (after you cast the year to varchar). – AHiggins Oct 27 '14 at 15:28
  • Its only giving me results for the current month even after changing it to Month([Date]). Also I need it to show the rate, report etc in the query.. Cheers – Nugeswale Oct 27 '14 at 16:18
  • OK, why did you change it to `MONTH([Date])` and did it work before? If you need additional fields in the query, add them to the SELECT and GROUP BY clauses ... I don't know what your aggregations and grouping are intended to be, so you'll have to do that part yourself. – AHiggins Oct 27 '14 at 16:29