[RESOLVED] Joining a date range

[Res]

Hey guys,

I've got a problem that I can't figure out on my own, hoping one of you may shed some light on how I can work around this.

Goal: Pull a continuous record set with one entry for each date regardless of weather or not there is an entry for each date.
Problem: Not every day has a record, so the missing days must be generated some how.

Essentially I'm trying to generate a daily count. The table I'm counting records from stores the sales of several products, each with it's own brand id. Now I was implementing the date filling in code, which was tedious as I need multiple breakdown types. I then realized I could get what I wanted by creating a table with an entry for each day. I then do a right join on this table so I can get the missing dates. This works fine if I don't limit the results by the brand_id.

For instance, I have brand_id 1 and 2 in the product_sales table. I want to get a count of each distinct product_id grouped by brand_id within my date range. The issue I'm running into is that to include the blank days I have to say...

WHERE ... (brand_id = 2 OR brand_id IS NULL)

This will get me a continuous record set for the days that brand_id 2 had sales, and the missing dates for the days that had NO sales. That's the problem, if lets say on 12-18 brand_id 2 did not have a sale but brand_id 1 did, then the date is omitted from my query.

Is this date table going to work the way I want it to once I get my conditional right? Do I have to select all my brand data into a temp table first then join on it to fill in the missing dates? I'm a little bit at a loss as to how to get this to work for the days that do have sales by not from brand_id 2.

Let me know if I need to provide more examples and thank you very much in advance for any help you may be able to offer.

[dagon]

i would fill in the blank days with php, i don't know of any mysql way of doing it

*edit you could create a mysql table for date ranges and join to that, i think i refer the php way, as i assume your going to be looping the results for output anyway.

[Res]

I initially was doing this in code, I want to get away from doing it that way as it was over 200 lines of code and was only for a Monthly, Daily, Weekly breakdown. The whole tracking of what days have data and what ones don't meant I would have to re implement the logic for each result set I applied it to, complete with the logic to figure out where in a breakdown the day falls into (new week, quarter, etc). This led me to creating the date table as you describe and joining on it. This works great with the right join, as I can then fill in the holes. It's the conditional on the where that messes it up. If I select all brands I get exactly what I'm looking for.

The issue I'm having with the join method is that if I have no sales on 12/18, the brand_id is null. so when I say "Where (brand_id = 2 OR brand_id is null) I get that date filled in. When brand_id 6 has sales on the 18th but 2 doesn't, the JOIN no longer causes the brand_id to be selected as null, which in turn causes the where condition to fail, as the brand_id is 6.

[dagon]

left JOIN will return all, where as right will skip the NULL's

[Res]

It depends on how you write the join statement, it includes nulls from whichever table is on the side that you declared. In this case I'm using RIGHT and my calendar table is the right portion of the join statement so it includes the nulls. That portion works great, its the adding of the conditional that is causing me problems.

[dagon]

I've reached my theoretical limit, can you post a data set and your query, then i can play around with it, and see what i come up with.

[Res]

Thanks Dagon, here is what I'm working with, simplified versions.

Dates table / sample data:

CREATE TABLE IF NOT EXISTS `dates` (
  `datevalue` int(8) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`datevalue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `dates`
--

INSERT INTO `dates` (`datevalue`, `date`) VALUES
(20101201, '2010-12-01'),
(20101202, '2010-12-02'),
(20101203, '2010-12-03'),
(20101204, '2010-12-04'),
(20101205, '2010-12-05'),
(20101206, '2010-12-06'),
(20101207, '2010-12-07'),
(20101208, '2010-12-08');

Sales table / sample data

CREATE TABLE IF NOT EXISTS `brand_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `brand_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `brand_sales`
--

INSERT INTO `brand_sales` (`id`, `date`, `brand_id`) VALUES
(1, '2010-12-01', 1),
(2, '2010-12-01', 2),
(3, '2010-12-03', 2),
(4, '2010-12-04', 1);

Here is the version of the query that gets me all dates with holes filled in, this does not limit which brand I'm pulling counts.

SELECT dates.date, brand_id, count(id) as sales FROM dates 
LEFT JOIN brand_sales ON dates.date =  brand_sales.date
WHERE dates.date >= '2010-12-01' AND dates.date <= '2010-12-08'
GROUP BY brand_id, dates.date

This is close to what I want. Days that do not have sales at all show up as null. I want days that do not have sales for the given brand to show up as null as well.

If I limit my results based on the brand_id, I'm back to having just days with sales:

SELECT dates.date, brand_id, count(id) FROM dates 
LEFT JOIN brand_sales ON dates.date =  brand_sales.date
WHERE dates.date >= '2010-12-01' AND dates.date <= '2010-12-08'
GROUP BY brand_id, dates.date HAVING brand_id = 1

If I put an OR brand_id is null, I get dates that have sales for my brand plus dates with no sales at all. Because brand 2 had sales on the days that brand 1 doesn't, the date is omitted to match my where clause.

SELECT dates.date, brand_id, count(id) FROM dates 
LEFT JOIN brand_sales ON dates.date =  brand_sales.date
WHERE dates.date >= '2010-12-01' AND dates.date <= '2010-12-08'
GROUP BY brand_id, dates.date HAVING (brand_id = 1 OR brand_id is null)

Let me know if there is anything else I can do to help you help me, your time is very much appreciated.

[Res]

Alright, I feel smart for figuring this out but at the same time I feel like and idiot for it being so blatantly simple.

After typing it out it became clear I needed my where condition to apply to the joining of the records, not the joined result set. This is where I was thinking I was going to need temporary tables or some more convoluted joins. Turns out I can add my conditional as a portion of the join statement, something I didn't read in enough detail.

This query does exactly what I need, it fills in the holes while ONLY considering the results that matched the brand_id which was the portion that was throwing things off.

SELECT dates.date, t1.brand_id, count(t1.id) as sales FROM dates 
LEFT JOIN brand_sales as t1 ON dates.date =  t1.date AND t1.brand_id = 2
WHERE dates.date >= '2010-12-01' AND dates.date <= '2010-12-08' 
GROUP BY brand_id, dates.date

Ignore the aliases, those are left over from trying something retarded.

Thanks again Dagon, at the very least you've been an extremely helpful rubber duck and I greatly appreciate that.

[bradgrafelman]

Out of curiosity, can you explain why you abandoned the PHP method? It seems silly to me to add the overhead to the DB of joining data to a full date range when it'd be a simple matter of having PHP output a range of dates if the next date retrieved isn't one day after the previous date.

[dagon]

Res;10971655 wrote:

Alright, I feel smart for figuring this out but at the same time I feel like and idiot for it being so blatantly simple.

well i have spent the last while on it with no success, so i think you did well

[Res]

If it was as simple as just getting a continuous date range I would have done it in code, in fact I started that way. As it got more complex with grouping results by week, month, quarter and detecting and filling the holes the code got a bit long and difficult to maintain. It also wasn't something that could be wrapped in a function all reports could make use of. Even a copy paste job replacing field names proved to be very error prone and tedious time consuming work at best. I also have a lot of additional data in my dates table signifying if the date is a weekend or a holiday etc, making the new system a bit more flexible as I can implement UI elements that change the group or where clause and not need to edit the logic for the break down.

I'm making what I think to be good use of indexes and partitions, so I'm not adding much overhead (few thousandths of a second) for the join on the dates table, even when I'm searching over a few million records. In fact I'm willing to bet it's a little bit faster as I've eliminated looping over, checking and building a few arrays.

In a nut shell:
1) Far easier to implement
2) Far easier to maintain
3) Far more flexible logically then when written in code (It will take me 15 minutes to update all reports to be able to group by week/month/quarter only counting results from Mondays or weekdays for example which would take me several hours to update the coded version in all places.