Hitta datum som _inte_ finns i tabell

Hittade den här, en STORED PROCEDURE som gör exakt det jag vill.
Men hur "får jag in den" i MySQL? Har tillgång till phpMyAdmin, men det inte hur jag ska göra.

http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-either

Kör man inte bara create procedure-koden?

Det trodde jag med, men får felmeddelande på declare. Läste på lite mer om det här idag och jag tror att jag måste köra delimiter // innan jag kör själva koden. Sen tror jag att den måste skrivas om något för att passa MySQL.

Såhär har jag gjort:

DELIMITER //
CREATE PROCEDURE sp1(d1 DATE, d2 DATE)
  DECLARE d DATETIME;

  CREATE TEMPORARY TABLE foo (d DATE NOT NULL);

  SET d = d1
  WHILE d <= d2 DO
    INSERT INTO foo (d) VALUES (d)
    SET d = DATE_ADD(d, INTERVAL 1 DAY)
  END WHILE

  SELECT foo.d, COUNT(datetime)
  FROM foo 
  LEFT JOIN table ON foo.d = table.datetime
  GROUP BY foo.d
  ORDER BY foo.d ASC;

  DROP TEMPORARY TABLE foo;
END PROCEDURE

Har inte hunnit testa det här ännu, men om någon är hajj på PROCEDURES så kolla gärna igenom och skrik till om ni hittar några fel i ovanstående.

Tack

Innan jag har hunnit kolla igenom hela, så kan jag direkt se att du inte avslutar proceduren rätt. Du måste avsluta med din avgränsare (delimiter):

delimiter //
create procedure...
...
end//

Ska kolla igenom lite till, för att se om den är anpassad för MySQL. :)

Tack ska du ha, det uppskattas!

Det ser ut som om min procedur ovan inte returnerar något? Jag vill ju kunna få ut en lista i formatet YYYY-MM-DD på alla dagar som saknas.

Ok, nu har jag skrivit om proceduren lite. Så här hade jag gjort:

delimiter //

drop procedure if exists checkDateAvailability//
create procedure checkDateAvailability(d1 date, d2 date)
begin
   declare currentDate datetime;

   create temporary table foo(tmpDate date not null);

   set currentDate = d1;
   while currentDate <= d2 do
      insert into foo(tmpDate) values(currentDate);
      set currentDate = date_add(currentDate, interval 1 day);
   end while;

   select
      foo.tmpDate, count(DIN_RIKTIGA_TABELL.datum_fält) as hits 
   from
      foo
      left join
         DIN_RIKTIGA_TABELL
         on foo.tmpDate = DIN_RIKTIGA_TABELL.datum_fält
   where
      hits = 0
   group by
      foo.tmpDate
   order by
      foo.tmpDate asc;

   drop temporary table foo;
end//
delimiter ;

Sen anropar du den med;

call checkDateAvailability('2010-01-11', '2010-01-18');

Då får du ut ett resultatset med dom dagar som inte finns med i DIN_RIKTIGA_TABELL och som ligger i intervallet mellan 11/1 -10 och 18/1 -10.

Det ovan ser helrätt ut, men får några fel:

mysql> CALL checkDateAvailability('2009-01-01', '2009-12-31');
ERROR 1146 (42S02): Table 'aim.din_riktiga_tabell' doesn't exist
-- Här ändrade funktionen till mina riktiga namn (på tabell + fält)

mysql> CALL checkDateAvailability('2009-01-01', '2009-12-31');
ERROR 1050 (42S01): Table 'foo' already exists
-- Jag lade in CREATE .... IF NOT EXISTS ...

mysql> CALL checkDateAvailability('2009-01-01', '2009-12-31');
ERROR 1054 (42S22): Unknown column 'hits' in 'where clause'
mysql>

Dom första två felen åtgärdade jag utan problem, men nu gnäller den på "hits" som inte verkar hittas.

En annan sak.. tanken är sen att jag ska anropa den här funktionen via PHP och loopa ut de datum som funktionen returnerar. Måste jag inte lägga till en "OUT" till proceduren och sen SELECT ... ONTO @min_out_var?

Känns som om det är nära en lösning nu, men behöver reda ut de här sista sakerna.

Tack!

Just det, kan tillägga att fältet i min statistiktabell heter "datetime", kan det ställa till det? Har fungerat bra hittills, men hur funkar det med en PROCEDURE?

Om din datatyp är datetime och inte date så har du med en tidsstämpel i fälten också, ändra i så fall alla date-variabler i proceduren till typen datetime och anropa med:

$ call checkDateAvailability('2009-01-01 00:00:00', '2009-12-31 23:59:59');

Märkligt att den inte hittar 'hits', du har väl specificerat den i select-listan som jag gjorde?

Så här fick jag till det utan procedure. Den ger tillbaka alla lediga dagar. Om flera dagar i följd är lediga anges startdagen plus antalet lediga dagar i vacancylength.

Frågan har dock bristen att den inte kan känna av ett ledigt spann som ligger före den första dagen i intervallet som finns i databasen.
Exempel: I databasen ligger 2010-01-13 och 2010-01-15. mindate=2010-01-12 eller tidigare. I det fallet kommer det första lediga datumet listas som 2010-01-14. Det kan jag dock hitta en lösning för om du vill, hojta till isf. Dessutom kan frågan behöva lite finputsning.

I exemplet definierar jag två variabler men du kan självklart peta in värdena på lämpliga ställen i frågan istället.

SET @maxdate = '2010-01-28';
SET @mindate = '2010-01-14';
SELECT
    DATE_ADD(g1.date, INTERVAL 1 DAY) AS vacancystart,
    DATEDIFF(COALESCE(MIN(g2.date),@maxdate),g1.date)-1 AS vacancylength
FROM `test_dategap` g1
LEFT JOIN `test_dategap` g2 ON g2.date > g1.date 
WHERE g1.date < @maxdate AND g1.date > @mindate
GROUP BY g1.date
HAVING vacancylength>0;

SPiN skrev:

Om din datatyp är datetime och inte date så har du med en tidsstämpel i fälten också...

Fältet heter "datetime" och är av type date, alltså YYYY-MM-DD.

SPiN skrev:

Märkligt att den inte hittar 'hits', du har väl specificerat den i select-listan som jag gjorde?

Såhär ser hela funktionen ut:

DELIMITER //
DROP PROCEDURE IF EXISTS checkDateAvailability//
CREATE PROCEDURE checkDateAvailability(d1 date, d2 date)
BEGIN
   DECLARE currentDate DATETIME;

   DROP TEMPORARY TABLE IF EXISTS foo;
   CREATE TEMPORARY TABLE foo(tmpDate DATE NOT NULL);

   SET currentDate = d1;
   WHILE currentDate <= d2 DO
      INSERT INTO foo(tmpDate) VALUES(currentDate);
      SET currentDate = DATE_ADD(currentDate, INTERVAL 1 DAY);
   END WHILE;

   SELECT
      foo.tmpDate, COUNT(stats_daily_statistical.datetime) AS hits 
   FROM
      foo
      LEFT JOIN
         stats_daily_statistical
         ON foo.tmpDate = stats_daily_statistical.datetime
   WHERE
      hits = 0
   GROUP BY
      foo.tmpDate
   ORDER BY
      foo.tmpDate ASC;

   DROP TEMPORARY TABLE foo;
END//
DELIMITER ;

Så jag undrar varför det blir såhär, känns nära till hands att "misstänka" mitt val av namn på kolumenen?

Nitro2k01: Tack för det där, ska kolla mer på det senare. Anledningen till att jag efterfrågar en procedure är mest för att det är helt nytt för mig och alltid kul att lära sig lite mer.

Tanken är att jag ska köra proceduren med PHP och sen få tillbaka ett recordset, så att jag kan lista dagar som saknas för administratören.

Nu testade jag att byta ut kolumnnamnet från "datetime" till "myDate" men får fortfarande:

ERROR 1054 (42S22): Unknown column 'hits' in 'where clause'

Är det inte HAVING du är ute efter? :)
(Snabbreferens)

Inga fler alternativ? Det känns som om det är så otroligt nära en bra lösning, bara jag får till det här med "hits".

'Having' är nyckelordet, ja. :)

delimiter //

drop procedure if exists checkDateAvailability//
create procedure checkDateAvailability(d1 date, d2 date)
begin
   declare currentDate datetime;

   create temporary table foo(tmpDate date not null);

   set currentDate = d1;
   while currentDate <= d2 do
      insert into foo(tmpDate) values(currentDate);
      set currentDate = date_add(currentDate, interval 1 day);
   end while;

   select
      foo.tmpDate, count(DIN_RIKTIGA_TABELL.datum_fält)
   from
      foo
      left join
         DIN_RIKTIGA_TABELL
         on foo.tmpDate = DIN_RIKTIGA_TABELL.datum_fält
   group by
      foo.tmpDate
   having
      count(DIN_RIKTIGA_TABELL.datum_fält) = 0
   order by
      foo.tmpDate asc;

   drop temporary table foo;
end//
delimiter ;

Yes, nu fungerar det äntligen!
Men, det tar ~5 min att köra funktionen, vilket inte är så bra. Nu har jag inte nämnt det, men min "RIKTIGA_TABELL" innehäller ~200.000 rader.

Bör man inte gruppera efter datum i den, innan man kör jämförelsen? Har testat att lägga till en PRIMARY KEY i foo tabellen, men det gör ingen som helst skillnad.

Det borde inte spela någon roll alls vilket fält du grupperar på. Däremot kan du skriva om select-frågan till att hämta enbart distinkta värden från DIN_RIKTIGA_TABELL, det är möjligt att det blir bättre. Tabellen foo kommer enbart att innehålla antalet dagar mellan d1 och d2, så den tabellen är antagligen inte flaskhalsen.

Select-satsen i proceduren som enbart plockar ut distinkta värden:

...
   select
      foo.tmpDate, count(joiner.dte)
   from
      foo
      left join
         (select
             distinct(DIN_RIKTIGA_TABELL.datum_fält) as dte
          from
             DIN_RIKTIGA_TABELL
         ) as joiner
         on foo.tmpDate = joiner.dte
   group by
      foo.tmpDate
   having
      count(joiner.dte) = 0
   order by
      foo.tmpDate asc;
...

SPiN skrev:

Däremot kan du skriva om select-frågan till att hämta enbart distinkta värden från DIN_RIKTIGA_TABELL, det är möjligt att det blir bättre.

Hehe, med gamla SELECT tog det 5 min 59 sek och med den nya 1.01 sek. Så skillnad kan man ju säga att det blev ;)

Tack ska du ha!