Grouping and ordering by dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping and ordering by dates

Author

Topic

travbod57
Starting Member

6 Posts

Posted - 2011-07-20 : 17:33:26

Hello,

Imagine a blog where you create posts each month. Down the side there is usually a view like this:

* July 2011 (1)
* March 2011 (1)
* May 2011 (2)
* August 2010 (1)
* February 2010 (1)

I have a table and need to group results by month and year and order chronologically. This is the best I have so far but it is not ordered chronologically.


select 	
DATENAME(month, CreatedDate) + ' ' + DATENAME(year, CreatedDate) as Month,
Count(Id) as Count 
from Journal_Post 
where idJournal = @JournalId
group by DATENAME(year, CreatedDate), DATENAME(month, CreatedDate)
order by DATENAME(year, CreatedDate) asc, DATENAME(month, CreatedDate) asc

results:

* August 2010 (1)
* February 2010 (1)
* July 2011 (1)
* March 2011 (1)
* May 2011 (2)

I must be almost there but not quite as the data is correct but not chronological. If I had an extra table just tracking months/year and associating and Id to them and then using the Id as a foreign key in the Journal_Post table this would be a walk in the park but entering a new month into this table each time a new month ticks over is something I would rather avoid.

Cheers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-20 : 18:35:01

Try:

ORDER BY
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)

travbod57
Starting Member

6 Posts

Posted - 2011-07-20 : 18:40:01

it now looks like:

select 	
DATENAME(month, CreatedDate) + ' ' + DATENAME(year, CreatedDate) as Month,
Count(Id) as Count 
from Journal_Post 
where idJournal = @JournalId
group by DATENAME(year, CreatedDate), DATENAME(month, CreatedDate), DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)
ORDER BY
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)

but same result as before i.e. not chronologically ordered

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-20 : 20:18:59

CREATE TABLE ReportPeriods
(report_period_name CHAR(10) NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date < period_end_date));

Then do your query.

SELECT R.report_period_name, COUNT(*) AS event_cnt
FROM ReportPeriods AS R
LEFT OUTER JOIN
Events AS E
ON E.event_time BETWEEN R.period_start_date AND R.period_end_date
GROUP BY R.report_period_name;

I like the MySQL trick for months and years that follows he ISO-8601 format. "yyyy-mm-00" has a dobule zero day, and "yyyy-00-00" is the year with the double zero month and double zero day.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

travbod57
Starting Member

6 Posts

Posted - 2011-07-21 : 14:15:37

But that requires populating and maintaining a new table holding the report months. Can I not have a query that doesn't involve an extra table??

rattlehead02
Starting Member

7 Posts

Posted - 2011-07-21 : 15:58:53

Just wanted to chime in that I'm having a similar issue. I have this:

left(convert(varchar, (DATENAME(month, status.dateadded))), 3) + ' ' + right(convert(varchar, year(dateadded)), 2) as sellingmonthyear

as my group by column so dates will appear as Jan 11, Feb 11, Mar 11, etc

But it is ordered alphabetically. And anytime I try to use status.dateadded in an Order By clause I get an error "Column "status.dateadded" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

Jeff

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-21 : 16:08:05

Can we look at your query?

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-21 : 16:10:37

quote:
Originally posted by travbod57

But that requires populating and maintaining a new table holding the report months. Can I not have a query that doesn't involve an extra table??

Can you have both dates and date in word in the SELECT and order by Dates instead?

rattlehead02
Starting Member

7 Posts

Posted - 2011-07-21 : 16:25:49

Here's mine:

select left(convert(varchar, (DATENAME(month, status.dateadded))), 3) +  ' ' + right(convert(varchar, year(dateadded)), 2) as sellingmonthyear,
isnull(sum(case 
	when status.status <> 'Sold' then 1
end), 0) as valuecount,
isnull(avg(case
	when status.status <> 'Sold' then listingprice
end), 0) as AvgPrice
from mls_data inner join status on mls_data.mlssource = status.mlssource and mls_data.listingnumber = status.listingnumber
where mls_data.mlssource = 'Cincy' 
	and mls_data.county = 'Butler' 
	and status.dateadded between '1/1/2011' 
	and '7/31/2011' 
	and new = 'Yes'
group by left(convert(varchar, (DATENAME(month, status.dateadded))), 3) +  ' ' + right(convert(varchar, year(dateadded)), 2)

I tried having dates in date and word formats both, but it didn't group that way.

And if it helps, here's the result (with no sorting, obviously):

sellingmonthyear	valuecount	AvgPrice
Apr 11	                     215	252707.2418
Feb 11	                     211	254671.5829
Jan 11	                     207	250506.4637
Jul 11	                     221	263352.0859
Jun 11	                     233	258307.7253
Mar 11	                     220	254860.3772
May 11	                     236	254941.4661

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-21 : 16:28:36

Is that the actuall query you are using? Becuase that order by works for me.

DECLARE @Post TABLE (CreatedDate DATETIME)
INSERT @Post (CreatedDate)
VALUES
(DATEADD(MONTH, -17, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -14, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -14, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -12, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -10, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -10, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -8, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -4, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -4, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -4, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, -2, CURRENT_TIMESTAMP)),
(DATEADD(MONTH, 0, CURRENT_TIMESTAMP))

select 	
DATENAME(month, CreatedDate) + ' ' + DATENAME(year, CreatedDate) as Month
--,Count(Id) as Count 
from @Post--Journal_Post 
--where idJournal = @JournalId
group by DATENAME(year, CreatedDate), DATENAME(month, CreatedDate), DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)
ORDER BY
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-21 : 16:30:37

quote:
Originally posted by rattlehead02

Just wanted to chime in that I'm having a similar issue. I have this:

left(convert(varchar, (DATENAME(month, status.dateadded))), 3) + ' ' + right(convert(varchar, year(dateadded)), 2) as sellingmonthyear

as my group by column so dates will appear as Jan 11, Feb 11, Mar 11, etc

But it is ordered alphabetically. And anytime I try to use status.dateadded in an Order By clause I get an error "Column "status.dateadded" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

Jeff

You should probably start a new thread. But, the code you posted has no ORDER BY clasue, so the order is random.

rattlehead02
Starting Member

7 Posts

Posted - 2011-07-21 : 16:43:36

Right. I couldn't come up with an Order By clause that worked, including examples in this thread. So I pasted the code without one.

But yeah, I'll start my own thread on it. Just figured I'd pipe in here while a thread was already going on with a similar issue.

travbod57
Starting Member

6 Posts

Posted - 2011-07-21 : 17:34:50

Lamprey that code you posted does work but the Count for each month has been commented out and that is the part thats complicationg the query. Am I right in thinking this shouldnt be too hard to achieve or is this something a bit strange I am trying to do here?

Also, I am happy to have rattlehead02 on the thread as it is a very similar problem

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-21 : 17:40:19

quote:
Originally posted by travbod57

Lamprey that code you posted does work but the Count for each month has been commented out and that is the part thats complicationg the query. Am I right in thinking this shouldnt be too hard to achieve or is this something a bit strange I am trying to do here?

Also, I am happy to have rattlehead02 on the thread as it is a very similar problem

Oh forgot to enable the count. But, it still seems to work just fine.

select 	
DATENAME(month, CreatedDate) + ' ' + DATENAME(year, CreatedDate) as Month
,Count(*) as Count 
from @Post--Journal_Post 
--where idJournal = @JournalId
group by DATENAME(year, CreatedDate), DATENAME(month, CreatedDate), DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)
ORDER BY
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)

travbod57
Starting Member

6 Posts

Posted - 2011-07-21 : 18:43:29

Ah your example is good now but still doesnt work correctly in my scenario, its not in chronological order. I have scripted the table, my test data and the stored procedure too to show this:

Table


CREATE TABLE [dbo].[Journal_Post]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[idJournal] [int] NOT NULL,
[Title] [varchar](250) NOT NULL,
[Text] [text] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [int] NOT NULL
)

Test Data


INSERT INTO [Journal_Post] ([Id],[idJournal],[Title],[Text],[CreatedDate],[CreatedBy])VALUES(1,1,'My First Post','this is the first post','2011-03-02',2)
INSERT INTO [Journal_Post] ([Id],[idJournal],[Title],[Text],[CreatedDate],[CreatedBy])VALUES(2,1,'My Second Post','this is the second post','2011-05-08',2)
INSERT INTO [Journal_Post] ([Id],[idJournal],[Title],[Text],[CreatedDate],[CreatedBy])VALUES(3,1,'Third One Title','THIRD POST','2011-05-02',2)    
INSERT INTO [Journal_Post] ([Id],[idJournal],[Title],[Text],[CreatedDate],[CreatedBy])VALUES(4,1,'Foruth Post','Post','2011-07-18',2) 
INSERT INTO [Journal_Post] ([Id],[idJournal],[Title],[Text],[CreatedDate],[CreatedBy])VALUES(6,1,'tester','tester','2010-02-02',2)
INSERT INTO [Journal_Post] ([Id],[idJournal],[Title],[Text],[CreatedDate],[CreatedBy])VALUES(9,1,'Testing','testing','2010-08-03',2)
set Identity_Insert Journal_Post off

Stored Procedure


    select 	
DATENAME(month, CreatedDate) + ' ' + DATENAME(year, CreatedDate) as Month
,Count(Id) as Count 
from Journal_Post 
where idJournal = 1
group by DATENAME(year, CreatedDate), DATENAME(month, CreatedDate), DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)
ORDER BY
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-21 : 18:55:19

Try this:

SELECT
    DATENAME(MONTH, CreatedMonth) + ' ' + DATENAME(YEAR, CreatedMonth) as [Month]
    ,[Count]
FROM
    (
        SELECT 	
            DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0) AS CreatedMonth
            ,Count(Id) as [Count]
        FROM 
            Journal_Post 
        WHERE 
            idJournal = 1
        GROUP BY 
            DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)
    ) AS T
ORDER BY
    CreatedMonth

travbod57
Starting Member

6 Posts

Posted - 2011-07-23 : 13:23:04

works a treat. Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources