[SQL] Query vraag

[Tc-Chub]

Hallo allemaal,

Ik heb een vraag over het maken van een SQL Query.
Ik zou graag voor mijn administratie mijn klanten en leveranciers bestanden willen opschonen, maar omdat het er heel veel zijn zou ik graag dit automatisch willen doen.

Ik heb de volgende structuur:

- 1 tabel met daarin al mijn relaties (zowel debiteur als crediteur) met als sleutel RelatieID
- 1 tabel met daarin alle Verkooporders met daarin o.a. een column (RelatieID en VerkooporderDatum)
- 1 tabel met daarin alle Inkoopfacturen met daarin o.a. een column (RelatieID en InkoopfactuurDatum)

Nu zou ik graag twee uitkomsten willen:

- Een lijst met alle debiteuren die na een bepaalde datum (VerkooporderDatum) geen Verkooporders hebben geplaatst.
- Een lijst met alle crediteuren die na een bepaalde datum (InkoopfactuurDatum) geen inkoopfactuur meer heeft gestuurd.(en er dus niets meer is ingeboekt)

Aan de hand van deze gegevens kan ik dan concluderen dat ik deze leverancier of klant niet meer nodig heb en kan deze verwijderd worden.

Ik kan echter wel gewone SQl query's uitvoeren , maar ik krijg het niet voor elkaar om deze tabellen te koppelen.

[Verwijderd]

select * from tblRelaties where RelatieID not in (
select RelatieID from tblVerkooporders where VerkooporderDatum > 'datum-in-het-goede-format')
and RelatieID not in (
select RelatieID from tblInkoopfacturen)

select * from tblRelaties where RelatieID not in (
select RelatieID from tblInkoopfacturen where InkoopfactuurDatum > 'datum-in-het-goede-format')
and RelatieID not in (
select RelatieID from tblVerkooporders)


?

[Redshark]

Voordat je iets verwijderd:

Denk er nog eens goed of na of je voor je administratie daadwerkelijk zaken wilt verwijderen. Is het geheel daadwerkelijk zo veel dat je hier ook acht last van hebt of is het meer een gevoel wat je hebt?

Denk er ook aan dat je alle koppelingen kwijt bent (als er geen foreign key op je tabellen staat) met verkooporder en inkoopfacturen wanneer je deze uit de relaties verwijderd. Dat lijkt me iets wat je wilt voorkomen.

[Tc-Chub]

Redshark schreef op woensdag 10 juli 2013 @ 15:15:
Voordat je iets verwijderd:

Denk er nog eens goed of na of je voor je administratie daadwerkelijk zaken wilt verwijderen. Is het geheel daadwerkelijk zo veel dat je hier ook acht last van hebt of is het meer een gevoel wat je hebt?

Denk er ook aan dat je alle koppelingen kwijt bent (als er geen foreign key op je tabellen staat) met verkooporder en inkoopfacturen wanneer je deze uit de relaties verwijderd. Dat lijkt me iets wat je wilt voorkomen.

Ik stap over op een nieuw administratie pakket dus het oude pakket blijft gewoon staan, het is meer dat ik dus die nieuwe relaties niet mee wil nemen. Gegevens zijn dan meestal ook verouderd dus moet ik ze alsnog aanpassen.

Verwijderd schreef op woensdag 10 juli 2013 @ 14:45:
select * from tblRelaties where RelatieID not in (
select RelatieID from tblVerkooporders where VerkooporderDatum > 'datum-in-het-goede-format')
and RelatieID not in (
select RelatieID from tblInkoopfacturen)

select * from tblRelaties where RelatieID not in (
select RelatieID from tblInkoopfacturen where InkoopfactuurDatum > 'datum-in-het-goede-format')
and RelatieID not in (
select RelatieID from tblVerkooporders)


?

Ik denk dat deze dan moet worden omgedraaid. Is het gewenste resultaat dan dat ik een lijst krijg met RelatieID's ?

[Verwijderd]

Volgens mij kloppen mijn voorbeeld query's wel. Je wilt toch een aparte lijst met crediteuren en met debiteuren? Als je die niet uit elkaar kunt houden in je tblRelaties dan kun je dat alleen zien aan het feit of ze in de ene danwel in de andere tabel staan?

Je krijgt dan trouwens wel een probleem als relaties tegelijk debiteur en crediteur kunnen zijn, als dat kan dan daar moet je dan nog een query voor maken.


'select *' betekent dat je ALLES uit die tabel te zien krijgt, ik neem aan in dit geval naast RelatieID ook NAW enzo. Als je dat niet wilt dan moet je de kolommen apart benoemen. Dus bv 'select RelatieID, achternaam' oid.


edit: of misschien begrijp je subquery's in het algemeen niet?

[ Voor 5% gewijzigd door Verwijderd op 10-07-2013 15:46 ]

[Tc-Chub]

Verwijderd schreef op woensdag 10 juli 2013 @ 15:44:
Volgens mij kloppen mijn voorbeeld query's wel. Je wilt toch een aparte lijst met crediteuren en met debiteuren? Als je die niet uit elkaar kunt houden in je tblRelaties dan kun je dat alleen zien aan het feit of ze in de ene danwel in de andere tabel staan?

Je krijgt dan trouwens wel een probleem als relaties tegelijk debiteur en crediteur kunnen zijn, als dat kan dan daar moet je dan nog een query voor maken.


'select *' betekent dat je ALLES uit die tabel te zien krijgt, ik neem aan in dit geval naast RelatieID ook NAW enzo. Als je dat niet wilt dan moet je de kolommen apart benoemen. Dus bv 'select RelatieID, achternaam' oid.


edit: of misschien begrijp je subquery's in het algemeen niet?

In de tabel Relatie staat inderdaad een Relatietype 1, 2 of 3. 1 = Klant , 2 = Leverancier en 3 = Beiden.
Ik moet dan denk ik ook daarop zoeken he, anders krijg ik leveranciers naar voren die geen verkooporder hebben geplaatst (Logisch)

Moet ik er dan toevoegen AND RelatieType = 1 ?

Tevens moet ik de datum in het juiste formaat zetten. Ik wil alles van voor het jaar 2007 er dan uit hebben, tot welk datumformaat kom ik dan? In mijn tabellen zie ik nu dit staan als datum: 06/15/2002 12:27:04

[Verwijderd]

Kom je nou mee

SQL:

--klanten
select * from tblRelaties where RelatieID not in (
select RelatieID from tblVerkooporders where VerkooporderDatum > 'datum-in-het-goede-format')
and RelatieType  = 1

--leveranciers
select * from tblRelaties where RelatieID not in (
select RelatieID from tblInkoopfacturen where InkoopfactuurDatum > 'datum-in-het-goede-format')
and RelatieType = 2

--beide
select * from tblRelaties where RelatieID not in (
select RelatieID from tblVerkooporders where VerkooporderDatum > 'datum-in-het-goede-format')
and RelatieID not in (
select RelatieID from tblInkoopfacturen where InkoopfactuurDatum > 'datum-in-het-goede-format')
and RelatieType = 3

V.w.b. die datum, probeer dit eens:

SQL:

select * from tblVerkooporders where VerkooporderDatum > '20061231')

Als dat werkt dan weet je het format.

PS 06/15/2002 12:27:04 is trouwens groter dan '20020615'.

[Tc-Chub]

Helemaal top! Het werkt. Zo zie ik dat ik al een hoop klanten kan filteren waar ik de afgelopen jaren geen zaken meer mee heb gedaan. Gezien ik in het nieuwe pakket extra tags moet toevoegen scheelt mij dat een hoop werk.

Ik heb overigens als datum gewoon '01-01-2007' ingevoerd en dat werkt gewoon mijn inziens? (ik werk in SQL entprise manager op windows server 2003).

Zou ik nu alleen nog een extra command line kunnen schrijven waar ik inzet dat ik bij die klanten in een bepaalde kolom een 'n' (van Niet importeren) kan zetten zodat ik die zostraks kan filteren?

[RobIII]

Tc-Chub schreef op woensdag 10 juli 2013 @ 16:50:
Ik heb overigens als datum gewoon '01-01-2007' ingevoerd en dat werkt gewoon mijn inziens? (ik werk in SQL entprise manager op windows server 2003).

Gebruik beter ISO 8601 y-m-d notatie. 01-01-2007 gaat misschien wel goed want 01-01 kan maar op één manier geïnterpreteerd worden. Maar wat is 02-12? 2 december? 12 februari? Ik zou er een gewoonte van maken, ook al gaat 't in dit geval goed, deze jaar-maand-dag notatie te hanteren; dat gaat in alle bekende RDBMS'en AFAIK altijd goed.

@Primeur:

Give a man a fish and feed him for a day. Teach a man how to fish and feed him for a lifetime.

Je intentie is goed, maar uiteindelijk leert TS er niets van anders dan bij een volgende keer eenzelfde probleem tegen te komen weer een topic te openen

@Tc-Chub over de openstaande/resterende vragen/vraagjes: Wat heb je zelf al geprobeerd/gezocht/gevonden? Je weet stiekem ook wel dat we iets meer inzet verwachten hier hé? Je topic begint aardig op een scriptrequest / Kan iemand even...? te lijken zo.

[ Voor 61% gewijzigd door RobIII op 10-07-2013 17:20 ]

[Verwijderd]

@Primeur:

[...]

Je intentie is goed, maar uiteindelijk leert TS er niets van anders dan bij een volgende keer eenzelfde probleem tegen te komen weer een topic te openen

Zit wat in. Genoteerd

[The Eagle]

@TS: leuk dat je overstapt op een nieuw systeem. Hou je er rekening mee dat je financiele gegevens (en daarmee ook stamgegevens) wettelijk 7 jaar moet bewaren?
Ik zou je oude systeemje dus niet zomaar weggooien / optimaliseren.

Bovendien: als je een extra veld opneemt bij je leveranciers met als waarde I of A (inactief / actief) kun je de boel 1 op 1 overnemen in je nieuwe systeem. Hoef je oude klanten ook niet teleur te stellen als ze opnieuw aankloppen

[RobIII]

The Eagle schreef op woensdag 10 juli 2013 @ 17:32:
@TS: leuk dat je overstapt op een nieuw systeem. Hou je er rekening mee dat je financiele gegevens (en daarmee ook stamgegevens) wettelijk 7 jaar moet bewaren?

Los van 't feit dat ik die data ook gewoon zou bewaren (en dus als I/A zou markeren) en met de kanttekening dat ik niet écht in die materie thuis ben en dus mogelijk onzin praat: volgens mij is 't allang goed als je die 7 jaar "ergens op papier" bewaart. Als je dus je facturen en whatnots ingeklappert hebt zou 't al goed moeten zijn. Dat 't een ellende is om uit te spitten is hunnie probleem als ze op controle komen

[ Voor 3% gewijzigd door RobIII op 10-07-2013 17:48 ]

[The Eagle]

Mocht je controle krijgen dan heb je vziw een bepaalde termijn waarop je op moet kunnen leveren. Dus dan ben jij degene die mag gaan spitten. En dat wil je niet

Het medium maakt bij mijn weten idd niet uit, als het maar compleet is.
Maar van een aantal jaren financiele administratie op papier bewaren wordt je bij een beetje bedrijf ook niet vrolijk kan ik je melden

[ Voor 39% gewijzigd door The Eagle op 10-07-2013 18:00 ]

[orf]

RobIII schreef op woensdag 10 juli 2013 @ 17:48:
[...]

Los van 't feit dat ik die data ook gewoon zou bewaren (en dus als I/A zou markeren) en met de kanttekening dat ik niet écht in die materie thuis ben en dus mogelijk onzin praat: volgens mij is 't allang goed als je die 7 jaar "ergens op papier" bewaart. Als je dus je facturen en whatnots ingeklappert hebt zou 't al goed moeten zijn. Dat 't een ellende is om uit te spitten is hunnie probleem als ze op controle komen

Wat je digitaal binnen krijgt als bedrijf moet je digitaal bewaren, wat je op papier binnen krijgt moet je op papier bewaren. Hetzelfde geldt voor wat je (aan bijvoorbeeld facturen) verstuurd.

Bron

[Tc-Chub]

RobIII schreef op woensdag 10 juli 2013 @ 17:15:
[...]

Gebruik beter ISO 8601 y-m-d notatie. 01-01-2007 gaat misschien wel goed want 01-01 kan maar op één manier geïnterpreteerd worden. Maar wat is 02-12? 2 december? 12 februari? Ik zou er een gewoonte van maken, ook al gaat 't in dit geval goed, deze jaar-maand-dag notatie te hanteren; dat gaat in alle bekende RDBMS'en AFAIK altijd goed.

@Primeur:

[...]

Je intentie is goed, maar uiteindelijk leert TS er niets van anders dan bij een volgende keer eenzelfde probleem tegen te komen weer een topic te openen

@Tc-Chub over de openstaande/resterende vragen/vraagjes: Wat heb je zelf al geprobeerd/gezocht/gevonden? Je weet stiekem ook wel dat we iets meer inzet verwachten hier hé? Je topic begint aardig op een scriptrequest / Kan iemand even...? te lijken zo.

Mijn excuses in dat geval, maar het probleem is dat ik nu aan het werk ben in de SQL database en ik dus niet zomaar een willekeurige Query even kan "testen" omdat ik voorzichtig met deze data om moet gaan.

Daarbij heb ik alleen ervaring met SQL bij het opbouwen van een website database en daar zijn de query's die ik gemaakt heb ietwat simpeler omdat ik dan mijn eigen tabellen inricht en dan al voor kan zorgen dat ik de query simpel kan houden.

Tevens is dit dus een eenmalige actie waarbij ik verder niet meer van plan ben om met SQL te gaan rommelen.

The Eagle schreef op woensdag 10 juli 2013 @ 17:32:
@TS: leuk dat je overstapt op een nieuw systeem. Hou je er rekening mee dat je financiele gegevens (en daarmee ook stamgegevens) wettelijk 7 jaar moet bewaren?
Ik zou je oude systeemje dus niet zomaar weggooien / optimaliseren.

Bovendien: als je een extra veld opneemt bij je leveranciers met als waarde I of A (inactief / actief) kun je de boel 1 op 1 overnemen in je nieuwe systeem. Hoef je oude klanten ook niet teleur te stellen als ze opnieuw aankloppen

Ik gooi het oude systeempje zeker niet weg, dat blijft gewoon draaien totdat de bewaartermijn van 7 jaar verstreken is. Ik wil graag deze informatie zuiveren omdat ik voor de complementatie van het nieuwe systeem meer informatie per klant / leverancier moet toevoegen. Gezien ik er een hoop niet gebruik is het zonde van de tijd om deze dan wel toe te gaan voegen. Daarbij loop ik ook de kans dat de informatie die ik dan uit het oude systeem meeneem verouderd is, en dus niet klopt.

Om dan inderdaad maar zelf weer aan de slag te gaan, zou ik graag nog willen weten of ik dan wel nog een WRITE commando aan deze query kan toevoegen?

@Primeur, bedankt voor je hulp!

[Alain]

Tc-Chub schreef op woensdag 10 juli 2013 @ 21:50:

... dat ik nu aan het werk ben in de SQL database en ik dus niet zomaar een willekeurige Query even kan "testen" omdat ik voorzichtig met deze data om moet gaan.

Zou het niet handig zijn om een kopie van de db te maken en daarop te oefenen?

[Verwijderd]

AlainS schreef op woensdag 10 juli 2013 @ 22:49:
[...]


Zou het niet handig zijn om een kopie van de db te maken en daarop te oefenen?

+1

Voor select queries maakt het niet zoveel uit natuurlijk. Maar als je met updates/inserts etc gaat werken, altijd EERST een kopie maken.


P.S. WRITE ken ik niet als SQL commando

[ Voor 31% gewijzigd door Verwijderd op 11-07-2013 10:22 ]

[Alain]

Verwijderd schreef op donderdag 11 juli 2013 @ 10:19:
Voor select queries maakt het niet zoveel uit natuurlijk.

Dat ben ik niet helemaal met je eens. Als je een select query probeert op een live systeem en hij draait onbedoeld vrij zwaar, dan heeft het live systeem er last van.

Testen doe je in een testomgeving.

[Verwijderd]

Misschien moet het live systeem daar maar een beetje tegen kunnen?

Ik snap je op zich wel, maar ik draai zoveel (select)queries op productie databases (en laat er nog een aantal draaien bij/door klanten als we er zelf niet bij kunnen), als we daarvoor steeds kopie databases naar testservers zouden moeten (laten) overhalen dan kunnen we er mensen bij in dienst nemen, en bovendien willen veel klanten geeneens dat er productie data in een testomgeving komt te staan.

Ik heb nog nooit een klacht ontvangen of zelf gezien dat het systeem trager werd door een query, en we hebben niet altijd de meest geoptimaliseerde queries

[sig69]

Oh ik wel hoor. WITH(NOLOCK) scheelt een hoop dan

[PolarBear]

The Eagle schreef op woensdag 10 juli 2013 @ 17:32:
@TS: leuk dat je overstapt op een nieuw systeem. Hou je er rekening mee dat je financiele gegevens (en daarmee ook stamgegevens) wettelijk 7 jaar moet bewaren?
Ik zou je oude systeemje dus niet zomaar weggooien / optimaliseren.

Bovendien: als je een extra veld opneemt bij je leveranciers met als waarde I of A (inactief / actief) kun je de boel 1 op 1 overnemen in je nieuwe systeem. Hoef je oude klanten ook niet teleur te stellen als ze opnieuw aankloppen

Je moet voor de belastingdienst minimaal 7 jaar administratie kunnen overleggen. Als je de in- en verkoopfacturen, loonstroken etc uitprint voldoe je daar aan. Je hoeft echt geen stamggevens te bewaren. Als de administratie maar te onderbouwen is.

[orf]

PolarBear schreef op vrijdag 12 juli 2013 @ 13:44:
[...]


Je moet voor de belastingdienst minimaal 7 jaar administratie kunnen overleggen. Als je de in- en verkoopfacturen, loonstroken etc uitprint voldoe je daar aan. Je hoeft echt geen stamggevens te bewaren. Als de administratie maar te onderbouwen is.

Heb je ook mijn reactie onder je quote gezien?

[PolarBear]

Nee, maar nu ik verder kijk in de onderstaande brochure is het nog veel erger:

http://download.belasting...aarplicht_al0401z10fd.pdf

[drm]

Primeur:
Ik snap je op zich wel, maar ik draai zoveel (select)queries op productie databases (en laat er nog een aantal draaien bij/door klanten als we er zelf niet bij kunnen), als we daarvoor steeds kopie databases naar testservers zouden moeten (laten) overhalen dan kunnen we er mensen bij in dienst nemen, en bovendien willen veel klanten geeneens dat er productie data in een testomgeving komt te staan.

Als je ontwikkelt tegen een productie-database moet je met de zweep krijgen. Er is geen klant die liever wil dat jij risico's neemt met een productie-database dan dat je een kopie van de database neemt. En als die klant die risico's wel accepteert, heb je ze niet goed genoeg uitgelegd. Er is geen enkel excuus om te ontwikkelen tegen een productie-database. Nee, echt niet. En als de data dan geanomimiseerd moet worden is het een fluitje van een cent om daar een paar queries of scripts voor te schrijven.

Ik heb nog nooit een klacht ontvangen of zelf gezien dat het systeem trager werd door een query, en we hebben niet altijd de meest geoptimaliseerde queries

Dat je geen klachten hoort wil nog niet zeggen dat ze er niet zijn. "Ik heb nog nooit iemand doodgereden, dus ik kan best met alcohol op rijden". Gechargeerd, natuurlijk, maar dat is een zelfde soort arrogantie.

A propos: niemand heeft altijd de meest geoptimaliseerde queries.

[Tc-Chub]

Hey all,

Ik ben ondertussen weer bezig geweest om een UPDATE zin toe te voegen aan bovenstaande Query's.
Ik wil namelijk een waarde toevoegen aan alle resultaten die ik te zien krijg:

UPDATE Relatie
SET kolomnaam = ‘waarde’
WHERE (
select * from Relatie where RelatieID not in (
select RelatieID from VerkoopOrder where VerkoopOrderDatum > '01-01-2007')
and RelatieType = 1
)
)

Deze Query geeft echter een syntax fout en ik snap eigenlijk niet wat er mis mee is.

[ Voor 9% gewijzigd door Tc-Chub op 29-07-2013 15:02 ]

[Verwijderd]

Ik snap niet helemaal wat je nou precies wil, ik ga een poging doen:

Wil je bestaande waardes in records aanpassen? SET kolomnaam = 'waarde' where kolomnaam in (....)

Ga dan even zoeken hoe subquery's werken.

[Tc-Chub]

Ja. Ik heb dus mijn query waaruit ik alle tabellen krijg waarvan er geen verkooporders meer zijn in een bepaalde tabel (Zie bovenstaande Query's.)

Nu wil ik alleen nog toevoegen dat hij dan een waarde gaat toevoegen in de kolom 'BedrijfsToevoeging' in de tabel 'Relatie' (waar ik ook op zoek). De kolom bestaat al dus een INSERT INTO werkt niet. Ik gebruik dus UPDATE, maar als ik daar geen WHERE query aan toevoeg gaat hij alle waardes updaten.

[Verwijderd]

Uit jouw query:

WHERE (
select *

Dat is toch geen WHERE query, dat is een SELECT. Zoals ik al zei, ga even googlen hoe subquery's werken. WELKE records wil je dat die query update, dat moet in je WHERE. Dus WHERE 'iets' IS 'iets'.


PS INSERT INTO voegt een record toe, een regel dus en geen kolom.

[Tc-Chub]

Verwijderd schreef op maandag 29 juli 2013 @ 16:00:
Uit jouw query:

WHERE (
select *

Dat is toch geen WHERE query, dat is een SELECT. Zoals ik al zei, ga even googlen hoe subquery's werken. WELKE records wil je dat die query update, dat moet in je WHERE. Dus WHERE 'iets' IS 'iets'.


PS INSERT INTO voegt een record toe, een regel dus en geen kolom.

Ja ik snap wat je bedoeld maar ik krijg het even niet voor elkaar om die select query om te bouwen tot een WHERE.

Ik heb nu dit en ik geloof dat dit het gewenste resultaat oplevert:

UPDATE Relatie
SET BedrijfsToevoeging = 'waarde'
WHERE RelatieID not in (
select RelatieID from VerkoopOrder where VerkoopOrderDatum > '01-01-2007')
and RelatieType = 1
and SelectieCode != 'A'

Ik krijg nu 'waarde' in de kolom BedrijfsToevoeging te staan waarbij relatietype = 1, selectiecode niet 'A' is, en waarbij er in de tabel VerkoopOrder later dan 01-01-2007 geen verkooporders geregistreerd meer staan. Klopt dat?

[Verwijderd]

Ja, volgens mij gaat dat goed.

[The Eagle]

Wat ook een mooie oplossing is:
Je oude leveranciers in een archieftabel zetten. Data bewaard maar toch niet direct selecteerbaar.

Plus vrij simpel te maken met twee queries
In Oracle zou het iets zijn als:

Create table as select * from tblRelaties where relatieID in (select relatieId from verkooporders where.....);

Delete from tblRelaties where relatieID in (select relatieId from verkooporders where.....);

Hou je toch alles bij de hand en zelfs een backup voor als je iets mist

[ Voor 58% gewijzigd door The Eagle op 29-07-2013 22:12 ]