[MSSQL] Datums door elkaar, hoe te sorteren?

[SEQREEL]

Ok, ik loop al een poosje vast... Ik heb daarom besloten om dit op Tweakers te zetten. Wellicht dat een van jullie mij hiermee verder kan helpen.

Ik ben in MSSQL een database aan het bouwen wat als basis moet gaan dienen voor een fietsverhuur bedrijf. Echter heb ik een probleem, de data staat verkeerd in het insert scripts. Er is namelijk sprake van een startdatum (aanvang huur) einddatum (einde huur) en een betaaldatum. Vanzelfsprekend is dit oplopend, echter is dit niet het geval in het insert script.

Hierbij de tabel waar het om gaat:

code:

CREATE TABLE Huurovereenkomst (
 HuurovereenkomstNr INT NOT NULL,
 Startdatum DATE NOT NULL,
 Einddatum DATE NOT NULL,
 Betaaldatum DATE,
 StatusNr INT NOT NULL,
 KlantNr INT NOT NULL,
 Inlognaam CHAR(30)
);

ALTER TABLE Huurovereenkomst ADD CONSTRAINT PK_Huurovereenkomst PRIMARY KEY (HuurovereenkomstNr);

Het insert script bevat de data wat in deze tabellen moet komen

code:

INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(1,'2015-01-04 00:23:15','2016-06-19 02:50:05','2011-06-15 02:11:51','3',81,'soups');
INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(2,'2011-07-19 04:40:18','2012-12-16 16:27:45','2010-11-12 05:56:35','3',65,'cereals');
INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(3,'2011-09-21 02:05:03','2011-10-15 02:36:21','2016-04-24 01:55:36','1',42,'cereals');
INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(4,'2015-06-13 13:18:59','2013-05-21 09:25:03','2011-09-09 06:07:40','2',23,'pasta');
INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(5,'2010-11-08 07:19:13','2011-01-14 20:21:06','2011-05-19 18:43:08','2',44,'cereals');
INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(6,'2014-12-24 00:08:11','2015-09-25 20:59:38','2011-12-14 19:39:14','3',78,'pasta');
INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(7,'2011-12-29 01:57:57','2014-09-05 05:11:15','2013-03-14 09:50:46','3',76,'sandwiches');

Zoals je ziet zijn de data voor aanvang, einde en betalen niet oplopend en staan deze door elkaar heen. Ik ben dus opzoek naar een oplossing om de data oplopend te krijgen in het insert script om te voorkomen dat ik alles handmatig moet aanpassen...

Wellicht denk je nu "oh handmatig doen.." Maar het zijn meer dan 1000 records dus met een scriptje zou dit toch wat sneller gaan.

Wie kan mij op weg helpen?

[orf]

Waarom moet het gesorteerd de tabel in? Je kunt het bij het ophalen toch gewoon sorteren op datum?

[Rhapsody]

Ik begrijp je probleem niet zo goed.

Klopt de data niet? Of wil je het gesorteerd in je database stoppen?

Dat laatste hoeft helemaal niet omdat je het bij het opvragen natuurlijk prima kan sorteren met een order by clause in je query.

[SEQREEL]

Nee ik ben wellicht wat onduidelijk. De bedoeling is de oudste datum bij startdate komt te staan, de datum daarna bij end date en de datum daarna weer bij betaaldatum. Nu zie je in het insert script dat deze data niet goed staat

code:

INSERT INTO Huurovereenkomst([HuurovereenkomstNr],[Startdatum],[Einddatum],[Betaaldatum],[StatusNr],[KlantNr],[Inlognaam]) VALUES(1,'2015-01-04 00:23:15','2016-06-19 02:50:05','2011-06-15 02:11:51','3',81,'soups');

Wanneer ik dit script run dan is de startdatum 04-01-2015 einddatum 19-06-2016 en de betaaldatum 15-06-2011 terwijl dit moet zijn:

startdatum: 15-06-2011 einddatum: 04-01-2015 en betaaldatum 19-06-2016

En zo staan alle data door elkaar en niet op de plek waar het zou moeten komen. Is dit duidelijker?

[DaMoUsYs]

Is dat niet gewoon een kwestie van de datums aanpassen in de query

[Gonadan]

Oh je bron was al vervuild? Ik zou iets doen als een paar updates waar je wanneer datum A recenter is dan B wissel om. Dat twee of drie keer een je bent er.

[SEQREEL]

DaMoUsYs schreef op woensdag 7 december 2016 @ 21:40:
Is dat niet gewoon een kwestie van de datums aanpassen in de query

Ja dat zou kunnen, maar gezien het meer dan 1000 records betreft is dit wel tijdrovend. Als er een manier is om alle oudste datums bij StartDatum te krijgen en de datums welke in het midden liggen bij Einddatum en de datum welke het meest recent is bij Betaaldatum, bijvoorbeeld met een script geniet dat mijn voorkeur.

[epic007]

Als de velden op een vaste manier zijn gehusseld dan zou ik gewoon de import draaien en daarna de kolomnamen hernoemen.

[The Eagle]

Aan bronvervuiling doe je idd weinig. Ja, zorgen dat degene die het aan moet leveren dat ook op de juiste manier doet, niet jouw probleem dus
Maar je zegt "vanzelfsprekend is dit oplopend". Zo vanzelfsprekend is dat helemaal niet. Een betaaldatum kan natuurlijk best voor de startdatum liggen. Ik bel je op in mei, wil een fiets reserveren voor een week in juli, betaal hem vooraf in mid juni. Vrij realistisch scenario als je het mij vraagt.
Dus nogmaals: klopt je bron?

[SEQREEL]

Gonadan schreef op woensdag 7 december 2016 @ 21:42:
Oh je bron was al vervuild? Ik zou iets doen als een paar updates waar je wanneer datum A recenter is dan B wissel om. Dat twee of drie keer een je bent er.

Ja inderdaad, de bron was al vervuild... Maar dat omzetten is daar een scriptje voor? Het is namelijk zo dat echt alles door elkaar heen staat met betrekking tot de datums.

[Tsurany]

Webdexter® schreef op woensdag 7 december 2016 @ 21:43:
[...]


Ja dat zou kunnen, maar gezien het meer dan 1000 records betreft is dit wel tijdrovend. Als er een manier is om alle oudste datums bij StartDatum te krijgen en de datums welke in het midden liggen bij Einddatum en de datum welke het meest recent is bij Betaaldatum, bijvoorbeeld met een script geniet dat mijn voorkeur.

Wijken alle rijden op dezelfde manier af? Anders is het simpelweg de veldnamen even omdraaien zodat die in de juiste volgorde staan in je insert script, dat is zo gedaan.

[SEQREEL]

The Eagle schreef op woensdag 7 december 2016 @ 21:46:
Aan bronvervuiling doe je idd weinig. Ja, zorgen dat degene die het aan moet leveren dat ook op de juiste manier doet, niet jouw probleem dus
Maar je zegt "vanzelfsprekend is dit oplopend". Zo vanzelfsprekend is dat helemaal niet. Een betaaldatum kan natuurlijk best voor de startdatum liggen. Ik bel je op in mei, wil een fiets reserveren voor een week in juli, betaal hem vooraf in mid juni. Vrij realistisch scenario als je het mij vraagt.
Dus nogmaals: klopt je bron?

Het klopt helemaal wat je zegt, echter bij navraag is behoort de oudste datum bij Startdatum de daaropvolgende datum bij Einddatum en is de meest recente datum de betaaldatum.

[SEQREEL]

Tsurany schreef op woensdag 7 december 2016 @ 21:47:
[...]

Wijken alle rijden op dezelfde manier af? Anders is het simpelweg de veldnamen even omdraaien zodat die in de juiste volgorde staan in je insert script, dat is zo gedaan.

Helaas... De rijen wijken niet op dezelfde manier af. Dan had ik het kunnen oplossen maar kom er nu niet uit

[Gonadan]

Webdexter® schreef op woensdag 7 december 2016 @ 21:46:
Ja inderdaad, de bron was al vervuild... Maar dat omzetten is daar een scriptje voor? Het is namelijk zo dat echt alles door elkaar heen staat met betrekking tot de datums.

Ja gewoon update queries schrijven die datum 1 die datum2 vervangen en andersom in de regels waar datum2 ouder is.

Dan daarna voor datum 2 en 3 en dan de eerste nog een keer.

[ Voor 7% gewijzigd door Gonadan op 07-12-2016 21:57 ]

[SEQREEL]

Gonadan schreef op woensdag 7 december 2016 @ 21:54:
[...]

Ja gewoon update queries schrijven die datum 1 die datum2 vervangen en andersom in de regels waar datum2 ouder is.

Dan daarna voor datum 2 en 3 en dan de eerste nog een keer.

Heb je enig idee hoe ik die update moet formuleren?

[Gomez12]

ik zou zeggen dump het in een temp-table met dit script en trek het daarna uit de temp-table met een paar case statements naar of weer een script of een andere tabel. Dan moet het goed staan volgens jouw specificaties (waarvan ik betwijfel of ze juist zijn).

Ik zelf zou echt bij de bron gaan navragen wat nu wat is en wat er verkeerd is gegaan, want in wezen ga je nu je hele historie verneuken wat ook nog wel eens door kan werken richting belastingdienst etc.

[ Voor 41% gewijzigd door Gomez12 op 07-12-2016 22:05 ]

[The Eagle]

Als TS al niet snapt hoe ie het kan importeren dan gaat ie dat ook niet snappen ben ik bang
Maar in a nutshell heb je wel gelijk.
Direct importeren in de doeltabel zou ik sowieso ook niet doen.

[SEQREEL]

Gonadan schreef op woensdag 7 december 2016 @ 21:54:
[...]

Ja gewoon update queries schrijven die datum 1 die datum2 vervangen en andersom in de regels waar datum2 ouder is.

Dan daarna voor datum 2 en 3 en dan de eerste nog een keer.

Ja thanks! Ik heb hem, je hebt me op het juiste spoor gezet! Mijn dank hiervoor, ik heb de volgende updates toegevoegd:

code:

UPDATE [Huurovereenkomst] SET Einddatum=Startdatum WHERE Startdatum>Einddatum;
UPDATE [Huurovereenkomst] SET Betaaldatum=Einddatum WHERE Einddatum>Betaaldatum;

Nu staat alles juist, mijn dank is groot

[Gomez12]

The Eagle schreef op woensdag 7 december 2016 @ 22:04:
Als TS al niet snapt hoe ie het kan importeren dan gaat ie dat ook niet snappen ben ik bang
Maar in a nutshell heb je wel gelijk.
Direct importeren in de doeltabel zou ik sowieso ook niet doen.

huidig script zou hij zo moeten kunnen draaien neem ik aan, oftewel het is enkel een kwestie van googlen op case-statements in mssql en daar krijg je ook 100.000 hits op oid.

Oftewel ik zie het probleem niet. Of het moet een probleem zijn dat TS zelf iets moet uitzoeken.

[SEQREEL]

Het is opgelost, er kan een slotje op!

[Gomez12]

Webdexter® schreef op woensdag 7 december 2016 @ 22:06:
[...]


Ja thanks! Ik heb hem, je hebt me op het juiste spoor gezet! Mijn dank hiervoor, ik heb de volgende updates toegevoegd:

code:

UPDATE [Huurovereenkomst] SET Einddatum=Startdatum WHERE Startdatum>Einddatum;
UPDATE [Huurovereenkomst] SET Betaaldatum=Einddatum WHERE Einddatum>Betaaldatum;

Nu staat alles juist, mijn dank is groot

Ok, dus als er nu stond :
Startdatum : 2016-06-01
Einddatum : 2016-05-01
Betaaldatum : 2016-04-01

Dan heb je dus na regel 1 :
Startdatum : 2016-06-01
Einddatum : 2016-06-01
Betaaldatum : 2016-04-01

En dan heb je dus na regel 2 :
Startdatum : 2016-06-01
Einddatum : 2016-06-01
Betaaldatum : 2016-06-01

En dat is goed? Of snap ik het nou niet?

[Gonadan]

Ja het is op zich wel handig om velden om te wisselen in één update.

[SEQREEL]

Gomez12 schreef op woensdag 7 december 2016 @ 22:10:
[...]

Ok, dus als er nu stond :
Startdatum : 2016-06-01
Einddatum : 2016-05-01
Betaaldatum : 2016-04-01

Dan heb je dus na regel 1 :
Startdatum : 2016-06-01
Einddatum : 2016-06-01
Betaaldatum : 2016-04-01

En dan heb je dus na regel 2 :
Startdatum : 2016-06-01
Einddatum : 2016-06-01
Betaaldatum : 2016-06-01

En dat is goed? Of snap ik het nou niet?

Nee dat klopt nog niet maar ben nu wel op weg geholpen waardoor ik verwacht er te gaan komen! Bedankt allemaal voor de hulp!

[Gomez12]

Webdexter® schreef op woensdag 7 december 2016 @ 22:13:
[...]


Nee dat klopt nog niet maar ben nu wel op weg geholpen waardoor ik verwacht er te gaan komen! Bedankt allemaal voor de hulp!

Voeg anders gewoon 3 velden toe aan je tabel, update die nieuwe velden naar hoe je het moet hebben en als alles goed staat laat je de oude velden vervallen en hernoem je de nieuwe velden.

Alhoewel ik toch echt van mening blijf dat je het in de bron moet zoeken en niet in dit resultaat. Ik zou in ieder geval in volledige cover your ass modus schieten in dit soort gevallen en alles zwart-op-wit laten bevestigen door een leidinggevende, dit soort geintjes kan pas jaren later ontdekt worden en dan zou ik mijn naam er niet onder willen hebben staan.

[SEQREEL]

Gomez12 schreef op woensdag 7 december 2016 @ 22:23:
[...]

Voeg anders gewoon 3 velden toe aan je tabel, update die nieuwe velden naar hoe je het moet hebben en als alles goed staat laat je de oude velden vervallen en hernoem je de nieuwe velden.

Alhoewel ik toch echt van mening blijf dat je het in de bron moet zoeken en niet in dit resultaat. Ik zou in ieder geval in volledige cover your ass modus schieten in dit soort gevallen en alles zwart-op-wit laten bevestigen door een leidinggevende, dit soort geintjes kan pas jaren later ontdekt worden en dan zou ik mijn naam er niet onder willen hebben staan.

Bedankt voor deze reactie, ik snap je punt en heb dit bevestigd gekregen. Ik ga er dan ook vanuit, email is voor mij ook zwart op wit.

Ik ben in ieder geval op weg geholpen en een stuk wijzer nu.

Overigens het niet willen zoeken, een reactie die ik ook voorbij zag komen, is niet aan de orde. Soms loop je nou eenmaal vast en weet je niet meer hoe verder, gelukkig vind je dan hier de mensen die je net even weer dat zetje geven om verder te komen.

Bedankt en nu is het tijd voor een slotje

[RobIII]

Webdexter® schreef op woensdag 7 december 2016 @ 22:07:
Het is opgelost, er kan een slotje op!

Webdexter® schreef op woensdag 7 december 2016 @ 22:41:

Bedankt en nu is het tijd voor een slotje

Euh, nee. Zo werkt 't niet
Een slotje is niet nodig op een topic als je je oplossing hebt. Zie daarvoor ook onze faq betreffende topiceinde.

[SEQREEL]

Ok ok ik ga het morgen afmaken en zal de uiteindelijke oplossing hier plaatsen. Wellicht dat er nog iemand in de toekomst wat aan heeft.

[Fish]

Kweenie 1000 is niet zoveel. ik zou het even door excel trekken denk ik

[SEQREEL]

Fish schreef op woensdag 7 december 2016 @ 23:00:
Kweenie 1000 is niet zoveel. ik zou het even door excel trekken denk ik

Daar had ik nog niet aan gedacht maar dat is inderdaad ook nog een mogelijkheid.

[Gomez12]

Fish schreef op woensdag 7 december 2016 @ 23:00:
Kweenie 1000 is niet zoveel. ik zou het even door excel trekken denk ik

Tja, het is uiteraard ieder voor zich.

Maar excel en datums/tijden heb ik extreem slechte ervaringen mee als dat vanuit txt/csv bestanden moet gaan komen. Daarvoor heeft excel iets te veel magie in zich die je eerst helemaal moet doodslaan en dan nogmaals checken of het echt allemaal dood is om daarna er nog eens met een stoomwals overheen te gaan en te hopen dat die magie nu echt dood is.

Excel heeft iets te veel grappen dat als je een datum-tijd als string hebt staan dat als je dan dubbelklikt dat hij hem omzet naar een datum, waarbij hij in een export oid opeens op 00:00:00 qua tijd staat etc.