Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year.

SELECT YEAR(crdate) AS y,
       MONTH(crdate) AS m,
       COUNT(*) AS tally
FROM   MESSAGE
WHERE  YEAR(crdate) = 2012
       AND SUBJECT <> 'Welcome' --exclude default messages
GROUP BY
       YEAR(crdate),
       MONTH(crdate)

This is what I have but its limiting in that I need to manually change years. Can this be upgraded to output for all dates in the table in this month/year format?

Removing the year would clump all the months over may years together and distort the data. I am looking to get the counts from that particular month/year.

share|improve this question
1  
I don't understand your question. Removing the year from the where clause (not the group by clause) won't "clump all the months together". Why do you want that year filter in the where clause? –  Mat Feb 1 '13 at 17:03
2  
Not sure I understand either. Are you only every interested in a single year and month? If so, how do you expect SQL Server to know which year and month you're interested in, if you don't change it? –  Aaron Bertrand Feb 1 '13 at 17:13
add comment

3 Answers

Wrap this up in a stored procedure then:

CREATE PROCEDURE GetYearCounts
    @year INT
AS
    SELECT YEAR(crdate) AS y,
           MONTH(crdate) AS m,
           COUNT(*) AS tally
    FROM   MESSAGE
    WHERE  YEAR(crdate) = @year
           AND SUBJECT <> 'Welcome' --exclude default messages
    GROUP BY
           YEAR(crdate),
           MONTH(crdate);
GO

This will allow you to specify the year you want to get the data from in the call. I.e.

EXEC GetYearCounts @year = 2012;
GO

This is what I have but its limiting in that I need to manually change years.

Given your requirements there is no way to do this automatically.

share|improve this answer
2  
@brykneval I don't agree that you should dictate what "proper formatting" is in terms of upper- or lower-case keywords. While personally I like your convention better, that is a preference thing, and I recommend against editing other people's posts to suit your preference. –  Aaron Bertrand Feb 4 '13 at 17:26
    
@aaron actually I do it just for clarity for other users, generally for me its more like blog, mostly I would want to go through answers are far as possible so this happens with me :-) –  brykneval Feb 5 '13 at 4:28
add comment

Ths should work:

select year(crdate ) as y, month(crdate )  as m, count(*) as tally
from message  
where subject <> 'Welcome' --exclude default messages 
GROUP BY CAST(MONTH(crdate) AS VARCHAR(2)) + '-' + CAST(YEAR(crdate) AS VARCHAR(4))
share|improve this answer
2  
Casting to a string, though, will force a table scan. –  Aaron Bertrand Feb 1 '13 at 17:13
    
True, Didn't know a better way to do it tough –  amaters Feb 1 '13 at 17:14
    
Could you give an explanation, too? It is very welcome on this site. –  dezso Feb 1 '13 at 20:29
    
This doesn't compile. Why did you change the GROUP BY? –  Andriy M Feb 2 '13 at 10:51
add comment

You can create a procedure that takes month and year, and optionally just return all data when no parameters are specified, then you can do this (I'm adding a bunch of date handling here instead of string conversion of functions like month/year so that, if crdate is indexed, you'll still have a query that can take advantage of the index):

USE tempdb;
GO
CREATE TABLE dbo.messages
(
  x INT IDENTITY(1,1),
  crdate DATETIME
);

INSERT dbo.messages(crdate) VALUES 
('20100101'),('20100101'),('20100501'),('20110405'),('20120506')

GO

ALTER PROCEDURE dbo.GetYearCounts
  @y INT = NULL,
  @m INT = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sm DATETIME = DATEADD(MONTH, @m-1, DATEADD(YEAR, @y-1900, 0));
  DECLARE @em DATETIME = DATEADD(MONTH, 1, @sm);

  ;WITH x(m,c) AS
  (
    SELECT 
       DATEADD(MONTH, DATEDIFF(MONTH, 0, crdate), 0), 
       COUNT(*)
      FROM dbo.messages
      WHERE crdate >= COALESCE(@sm, '19000101')
      AND crdate < COALESCE(@em, '20300101')
      GROUP BY DATEDIFF(MONTH, 0, crdate)
  )
  SELECT y = YEAR(m), m = MONTH(m), tally = c
    FROM x
    ORDER BY y,m;
END
GO

Now results of two different calls:

EXEC dbo.GetYearCounts 2010, 1;

y      m    tally
----   ---  -----
2010   1    2

EXEC dbo.GetYearCounts;

y      m    tally
----   ---  -----
2010   1    2
2010   5    1
2011   4    1
2012   5    1

If you want to optionally specify a year only and get all 12 months, or specify a month only and get that month across multiple years, the procedure can be modified slightly. It's also tough to tell from your requirements if you want a row even for any months were there were no activity - that can be accomplished as well but we need better requirements.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.