Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Question Unanswered: MySQL find unused months

    Is there an way to use an MySQL query to find the missing months between 2 dates in an table?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's easy if you use an auxiliary numbers table...
    Code:
    CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
    INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),... ;]
    could you do a SHOW CREATE TABLE for your table please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    It's about this table:

    CREATE TABLE IF NOT EXISTS `iso_werkplekinspecties` (
    `id` int(3) NOT NULL AUTO_INCREMENT,
    `datum` date NOT NULL,
    `ehbo_doos` varchar(3) NOT NULL,
    `looppaden` varchar(3) NOT NULL,
    `blus_app` varchar(3) NOT NULL,
    `nooduitgang` varchar(3) NOT NULL,
    `geluidsniveau` varchar(3) NOT NULL,
    `potentieel` varchar(3) NOT NULL,
    `pbm` varchar(3) NOT NULL,
    `loopgang` varchar(10) NOT NULL,
    `verlengkabel` varchar(10) NOT NULL,
    `afval` varchar(10) NOT NULL,
    `positie` varchar(10) NOT NULL,
    `opmerkingen` varchar(255) NOT NULL,
    `status` varchar(15) NOT NULL,
    `user_id` int(2) NOT NULL,
    `user_id_rejected` int(2) NOT NULL,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

    This is my date field:
    `datum` date NOT NULL,

    Thanks for helping me out!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    find the missing months between which two dates?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Date one is the oldest date in the field 'datum'. The second date is NOW() + 3 months.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, we'll get there, in stages

    could you please run this query and report what it produces...
    Code:
    SELECT EXTRACT(YEAR_MONTH FROM datum)) AS from_yyyymm
         , COUNT(*) AS rows
      FROM iso_werkplekinspecties 
    GROUP
        BY from_yyyymm ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •