Select all data between two dates

Question

Hello!

It is necessary to choose values for some dates, how to do this I know, but there is one problem that does not work to solve.

My table in MySQL:

[User]. [Wins]. [DATE]
#
Ivan ....... 4 ..... 05/06/2010
#
Ivan ....... 3 ..... 06/15/2010
#
Ivan ........ 6 ..... 06/18/2010
#
Ivan ........ 1 ..... 29/06/2010

The problem is that if the user has not visited the site, a row in the database is not created and missed the date obtained.

How do I get a statistics (such Wins 0) such as the period from 06/06/2010 to 06/14/2010? Help make a make this query.

Answer 1

try the 'sum' aggregate function on the 'wins' field and restrict it (using where) to that specific date range (so date<='6/14/2010' AND date>='06/06/2010').

So something like SELECT SUM(wins) FROM [tbl_name] WHERE [DATE] between [date1] AND [date2]

to get the number of wins in each date range; add a 'group by [user]' in order to see the number of wins each user had in that date range.

Answer 2

Here's an answer i suspect would do the trick. I create a temporary table to give you a list of the dates (which i insert using a loop with the start and end date. The counter has to start at 0 because you want to include the start date itself).

Then, i just join the DateList to the Wins table to give you the list of all dates in the range, the users and their visits on those dates. I haven't tested it but i suspect the code is close to being exactly what you need.

DECLARE Counter INT DEFAULT 0
DECLARE NumDays INT DEFAULT DATEDIFF(StartDate, EndDate)

CREATE TEMPORARY TABLE DateList
(
    [Date] DATETIME
)

WHILE Counter <= NumDays
    INSERT INTO DateList ([Date]) VALUES (DATE_ADD(StartDate, INTERVAL Counter Day))
    SET Counter = Counter + 1
END WHILE

SELECT
    [User]  
    ,COUNT(User) AS Wins
    ,DateList.[Date] AS [Date]
FROM
    DateList LEFT JOIN Wins ON Wins.[Date] = DateList.[Date]
WHERE
    DateList.[Date] BETWEEN StartDate AND EndDate
GROUP BY
    [User]
    ,DateList.[Date]
ORDER BY
    DateList.[Date]

Edit: I made some changes to my joins and my count. I've tested the MSSQL version and it works fine.

Answer 3

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value:

   SELECT x.*
     FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y') 
             FROM numbers n
            WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 DAY) <= '2010-06-14 ) x
   

4. LEFT JOIN onto your table of data based on the time portion:

      SELECT x.ts AS timestamp,
             COALESCE(COUNT(y.wins), 0) AS cnt
        FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y') AS ts
                FROM numbers n
               WHERE DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY) <= '2010-06-14') x
   LEFT JOIN TABLE y ON STR_TO_DATE(y.date, '%m/%d/%Y') = x.ts
    GROUP BY x.ts