Impossible MySQL Query?

mrcornelia · October 21st, 2003, 09:01 AM

Hi all, I'm struggling to find a single MySQL query for what I am trying to do. I've got a table that contains News posts that looks something like this:

Code:

[u]News:</u>
newsID     int(11)
post       text
startDate  timestamp
endDate    timestamp

The startDate and endDate times are used to specify how long the post is displayed on my website.

To archive these posts I want to find every month that had posts appearing in it and a count of the number of posts that it had. For example, if 3 different news posts appear sometime during October 2003, and 2 during September, then I need the MySQL query to return:
'October 2003' and '3'
'September 2003' and '2'

It gets tougher. The startDate and endDate of a news post can span multiple months and so if the startDate was in August and the endDate was in October for a particular news post I would need that to be counted as 1 for August, September, and October.

Is this at all possible to do with a single query? I'm not sure. I was thinking that an extra table that simply contains months might help.

I'm using "mysql Ver 12.20 Distrib 4.0.13, for pc-linux"
and PHP 4.0.6 if that helps.

Anyway, if anyone thinks they can help out, that would be friggin awesome and much appreciated. Thanks in advance!

BTW: If any one can answer this for me, I'll give them some killer FTP access. Just a little incentive :)

happygv · October 22nd, 2003, 02:28 AM

In my opinion it can be done in a better way from the frontend. Let me try my hands on do it in a query, but i doubt the performance of the same.

Still I wonder why you wanted this kind of report, and how can you make out which one you should archive.

Instead I would suggest you to try and see if you can add another column called "active" which hold value like "Yes" or "No" based on the active status of the news post.

And change it to inactive status once the enddate is reached.

But not sure what your requirements are.

Cheers

-Vijay G

mrcornelia · October 22nd, 2003, 07:49 AM

Quote:

quote:Originally posted by happygv
Still I wonder why you wanted this kind of report, and how can you make out which one you should archive.

I need this query to create a sort of Table of Contents page for the archive. You'll click on a particular month and it will bring you to a page with every news post that appeared during that month. (That's easy enough to do once I've got a month and year) I need the count so that you can see how many news posts are contained in a month before you click on the link.

Quote:

quote:
Instead I would suggest you to try and see if you can add another column called "active" which hold value like "Yes" or "No" based on the active status of the news post.

And change it to inactive status once the enddate is reached.

Thanks for the suggestion Vijay G, but that probably won't really help much. My problem is in determining which months have had posts in them and how many. I can tell which posts are no longer active just by including the clause : where endDate < CURRENT_DATE()

If anyone can come up with a working query, that'd be great. I'm really needing that now.

Jeff Mason · October 22nd, 2003, 08:43 AM

First off, I don't know very much at all about MySQL and the SQL it implements. I'll present my solution in standard SQL, and leave you to figure out whether your choice of a database was a good one. :D

As you suspected, I agree it would be handy to have a table of months. Let's define that table to contain one row per month, consisting of two columns indicating the start of the month and the end of the month. Something like:

Code:

CREATE TABLE Months(
    MonthStart datetime NOT NULL,
    MonthEnd datetime NOT NULL );

ALTER TABLE Months ADD CONSTRAINT
    PK_Months PRIMARY KEY(
      MonthStart,
      MonthEnd);

Now, I haven't completely worked it all out, but my idea is that you take this Months table and CROSS JOIN it to your News table and extract from that result the rows where the article's end date is not less than the month's start date, or the article's start date is not greater than the month's end date. Then, group by the month's start date and count 'em:

Code:

SELECT MonthStart, COUNT (*) FROM News CROSS JOIN MONTHS
WHERE NOT ((endDate < MonthStart) OR (MonthStart > startDate))
GROUP BY MonthStart
ORDER BY MonthStart;

Add other WHERE predicates to restrict the search to a range of months (this year's, for example).

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

defiant · November 18th, 2003, 03:08 AM

Here is my solution:

Code:

select count(*) as NewsCount,
    case (m.monthNo - 1) % 12 + 1 when 1 then 'January' when 2 then 'February' when 3 then 'March'
        when 4 then 'April' when 5 then 'May' when 6 then 'June' when 7 then 'July'
        when 8 then 'August' when 9 then 'September' when 10 then 'October'
        when 11 then 'November' when 12 then 'December' else '?' end
    + ', ' + convert(varchar(4), (m.monthNo - 1) / 12)
from News n
    inner join (
        select 1950 * 12 + m1.No + m2.No + m3.No as monthNo
        from (
                select 0 as No union select 1 union select 2 union select 3 union select 4
                union select 5 union select 6 union select 7 union select 8 union select 9
            ) m1 cross join (
                select 0 as No union select 10 union select 20 union select 30 union select 40
                union select 50 union select 60 union select 70 union select 80 union select 90
            ) m2 cross join (
                select 0 as No union select 100 union select 200 union select 300 union select 400
                union select 500 union select 600 union select 700 union select 800 union select 900
            ) m3
    ) m on year(n.startDate) * 12 + month(n.startDate) <= m.monthNo
        and year(n.endDate) * 12 + month(n.endDate) >= m.monthNo
group by m.monthNo
order by m.monthNo

This works in SQL Server. I never used MySQL, but maybe you can make it work in MySQL.

If anyone wants to test the query in SQL Server, here is the code for creating the News table and for inserting test data:

Code:

CREATE TABLE [dbo].[News] (
    [newsId] [int] IDENTITY (1, 1) NOT NULL ,
    [post] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [startDate] [datetime] NOT NULL ,
    [endDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[News] WITH NOCHECK ADD 
    CONSTRAINT [PK_News] PRIMARY KEY  CLUSTERED 
    (
        [newsId]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[News] ADD 
    CONSTRAINT [DF_News_post] DEFAULT ('') FOR [post]
GO

--insert test data
insert News (startDate, endDate) values ('20021201 10:10:10', '20030301 10:10:10')
insert News (startDate, endDate) values ('20030101 10:10:10', '20030101 12:10:10')
insert News (startDate, endDate) values ('20030501 10:10:10', '20030601 10:10:10')