--- title: "Hitta datum som _inte_ finns i tabell" type: "forum-thread" url: "https://www.webforum.nu/amne/databaser-sql/182062-hitta-datum-som-inte-finns-i-tabell" topic: "Databaser & SQL" topic_url: "https://www.webforum.nu/amne/databaser-sql" author: "MickeA.com" published: "2010-01-18T14:13:24.000Z" updated: "2010-04-20T11:38:02.000Z" replies: 36 views: 2059 page: 1 pages: 2 language: "sv-SE" site: "webForum — webforum.nu" rights: "Upphovsrätten till varje inlägg tillhör dess författare." attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/182062-hitta-datum-som-inte-finns-i-tabell" --- # Hitta datum som _inte_ finns i tabell _Sida 1 av 2._ ## #1 — MickeA.com, 2010-01-18T14:13Z Hej Har en tabell i en MySQL databas där jag lagrar statistik. Det kan finnas x antal rader med samma datum. Det jag vill göra är att hämta ut MIN() och MAX() datum och ta reda på om det saknas någon dag mellan dessa två. T.ex. ``` 2010-01-18 = MAX 2010-01-17 2010-01-16 2010-01-15 2010-01-13 2010-01-12 2010-01-11 = MIN ``` I exemplet ovan saknas 2010-01-14, går det att med enbart SQL ta reda på det? Den enda lösningen jag kan komma på direkt är att ta ut antalet dagar mellan MIN och MAX och sen räkna antal dagar mellan dom. På så sätt kan jag ser att "någon" dag saknas, men jag kan inte se vilken eller vilka. Finns det någon smidig lösning på detta? Tack! Permalänk: https://www.webforum.nu/p/182062 ## #2 — MickeA.com, 2010-01-19T15:44Z 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. Permalänk: https://www.webforum.nu/p/2223454 ## #3 — @nders, 2010-01-19T20:32Z Kör man inte bara create procedure-koden? Permalänk: https://www.webforum.nu/p/2223487 ## #4 — MickeA.com, 2010-01-19T20:36Z 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 Permalänk: https://www.webforum.nu/p/2223488 ## #5 — SPiN, 2010-01-19T20:42Z 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. :) Permalänk: https://www.webforum.nu/p/2223489 ## #6 — MickeA.com, 2010-01-19T20:46Z Tack ska du ha, det uppskattas! Permalänk: https://www.webforum.nu/p/2223490 ## #7 — MickeA.com, 2010-01-19T20:55Z 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. Permalänk: https://www.webforum.nu/p/2223494 ## #8 — SPiN, 2010-01-19T21:54Z 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. Permalänk: https://www.webforum.nu/p/2223501 ## #9 — MickeA.com, 2010-01-20T08:39Z 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! Permalänk: https://www.webforum.nu/p/2223540 ## #10 — MickeA.com, 2010-01-20T08:41Z 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? Permalänk: https://www.webforum.nu/p/2223541 ## #11 — SPiN, 2010-01-20T10:14Z 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? Permalänk: https://www.webforum.nu/p/2223556 ## #12 — nitro2k01, 2010-01-20T10:17Z 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; ``` Permalänk: https://www.webforum.nu/p/2223557 ## #13 — MickeA.com, 2010-01-20T11:27Z > **[SPiN skrev:](https://www.webforum.nu/p/1495162)** > > 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:](https://www.webforum.nu/p/1495162)** > > 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. Permalänk: https://www.webforum.nu/p/2223562 ## #14 — MickeA.com, 2010-01-20T11:58Z 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' ``` Permalänk: https://www.webforum.nu/p/2223568 ## #15 — nidron, 2010-01-20T12:25Z Är det inte HAVING du är ute efter? :) ([Snabbreferens](http://www.webdevelopersnotes.com/tutorials/sql/mysql_development_tutorial_having_clause.php3)) Permalänk: https://www.webforum.nu/p/2223574 ## #16 — MickeA.com, 2010-01-20T20:09Z 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". Permalänk: https://www.webforum.nu/p/2223617 ## #17 — SPiN, 2010-01-20T20:40Z '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 ; ``` Permalänk: https://www.webforum.nu/p/2223619 ## #18 — MickeA.com, 2010-01-20T21:21Z 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. Permalänk: https://www.webforum.nu/p/2223622 ## #19 — SPiN, 2010-01-20T22:32Z 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; ... ``` Permalänk: https://www.webforum.nu/p/2223628 ## #20 — MickeA.com, 2010-01-21T08:26Z > **[SPiN skrev:](https://www.webforum.nu/p/1495246)** > > 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! Permalänk: https://www.webforum.nu/p/2223648 --- Tråden på webben: https://www.webforum.nu/amne/databaser-sql/182062-hitta-datum-som-inte-finns-i-tabell Nästa sida: https://www.webforum.nu/amne/databaser-sql/182062-hitta-datum-som-inte-finns-i-tabell/page2.md