Avatar billede Nicolai Nybegynder
27. maj 2013 - 20:07 Der er 17 kommentarer og
1 løsning

Sortere dubletter (absolutte) fra i tabel

Hej,

Jeg har følgende tabel:

Kunde    Beløb     Periode
Hansen    1.500     Jan
Hansen    1.200     Jan
Hansen    -1.500 Jan
Hansen    300     Feb
Petersen 400     Jan
Petersen 500     Feb
Jensen    240     Jan
Jensen    100     Feb
Jensen    -240     Feb
Jensen    300     Mar


hvor jeg ønsker at reproducere tabellen, men fjerne de "fejl" der er tastet. Det resultat jeg ønsker er:

Kunde    Beløb     Periode
Hansen    1.200     Jan
Hansen    300     Feb
Petersen 400     Jan
Petersen500     Feb
Jensen    240     Jan
Jensen    100     Feb
Jensen    -240     Feb
Jensen    300     Mar

Som I kan se skal min query fjerne de linjer hvor samme beløb er rettet i samme måned. I eksemplet er Hansen faktureret 1.500 i Januar, men i samme måned er fejlen rettet ved at fakturere -1.500. Begge disser linjer skal ikke være med. Derimod er Jensen's 240/-240 fra to forskellige perioder og skal dermed begge to med.

Jeg har prøvet mange forskellige queries, men kan ikke rigtig få grejet den...

hjælp :)
Avatar billede Nicolai Nybegynder
27. maj 2013 - 20:44 #1
Er i gang med noget der ligner:

SELECT Kunde, ABS(Beløb)
  FROM tabel
  GROUP BY SalesDoc, ABS(RevenueCleaned)
  having count(*) = 1

Meeeeennnn.... Jeg tror jeg er langt fra... Den ødelægger jo de reelle negative poster (som f.eks. -240 hos Jensen)
Avatar billede Nicolai Nybegynder
27. maj 2013 - 20:45 #2
Sorry mixede lige nogle kolonnenavne:

SELECT Kunde, ABS(Beløb)
  FROM tabel
  GROUP BY Kunde, ABS(Beløb)
  having count(*) = 1

:)
27. maj 2013 - 21:39 #3
Du vil have tabellen renset for rækker hvor der er en modpostering i samme måned.  Jeg vil mene, uden at have testet det, at vejen frem ikke er at slette de dårlige rækker (dem der har en modpostering i samme måned) men at flytte de gode rækker over i en ny tabel, så droppe den gamle tabel, og så 'rename' den nye tabel til det gamle navn.

Lyder det tosset?  Antag. at du laver en query der deleter rækker hvor der findes en anden række med samme periode men modsat beløb.  Så vil den første række, Hanset 1.500 Jan. blive slettet, fordi der er en modpostering fra samme måned.  Men når queryen kommer til rækken Hansen -1.500 Jan, så vil den ikke blive slettet, fordi der ikke (længere) er en modpostering.

Så jeg forestiller (ikke testet) følgende query for at flytte de gode data over i en ny tabel:

SELECT * INTO mytable1
FROM mytable t
WHERE NOT EXISTS(SELECT * FROM table WHERE Beløb = -t.Beløb AND Periode = t.Periode)

Derefter DROP table mytable

Derefter rename mytable1 til mytable.  I mysql ville det være RENAME TABLE mytable1 TO mytable
Men den virker vist ikke i MSSQL (som jeg ikke har installeret).  Jeg har googlet, de synes at du i mssql skal bruge
sp_rename mytable1, mytable
Avatar billede Slettet bruger
27. maj 2013 - 22:12 #4
Den meget simple:
Select kunde, periode, Sum(beløb)
From tabel
group by kunde, periode
having sum(beløb) != 0
Avatar billede Nicolai Nybegynder
27. maj 2013 - 22:33 #5
@Christian_Belgien:
Jeg ville rigtig gerne undgå at skulle oprette en ny tabel, da det er et resultat jeg skal forespørge på en del gange og grunddata ændrer sig hele tiden. Jeg har overvejet et "view", men igen ville det være mest "lækkert" med en ren SQL kode der fikser problemet :)

Desuden har jeg det problem er der på samme måned kan være tre poster: +150, +150 og -150. I dette tilfælde skal -150 og én (men kun én) af de +150 fjernes.

Jeg har knoklet med at finde en løsning de sidste par timer, men kan simpelthen ikke finde en god måde. Jeg er OK med en temp tabel som du forslår, hvis det er eneste vej ud...

@rahp:
Jeg havde "luret" at jeg kunne få den korrekte sum ved at spørge sådan. Men jeg er ikke interesseret i summen. Jeg er interesseret i at se de rækker der er... :)
Avatar billede Nicolai Nybegynder
27. maj 2013 - 22:36 #6
Jeg er selv ude i noget ala:

1) lave alle værdier om (i ny kolonne) til ABS(x) - altså positive tal
2) sortere på kunde, periode, værdi
3) slette alle rækker som opfylder kriteriet at den identisk med den forrige...

Men jeg kan ikke lige "fornemme" om den bringer mig i mål...?
Avatar billede Slettet bruger
27. maj 2013 - 22:38 #7
Andet forsøg:

select t.Kunde, t.Periode, t.Beløb
from Tabel t,
(
select kunde, periode, Sum(beløb)
from tabel
group by kunde, periode
having sum(beløb) != 0
) s
where t.Kunde = s.Kunde
  and t.periode = s.periode
Avatar billede Slettet bruger
27. maj 2013 - 23:00 #8
tredie forsøg:

select t.Kunde, t.Periode, t.Beløb
from Tabel t,
(
select kunde, periode, abs(beløb) ab, Sum(beløb)
from tabel
group by kunde, periode, abs(Beløb)
having sum(beløb) != 0
) s
where t.Kunde = s.Kunde
  and t.periode = s.periode
  and abs(t.beløb) = s.ab
Avatar billede Nicolai Nybegynder
27. maj 2013 - 23:20 #9
@rahp (tak for indsatsen - nu med trejde forsøg:) :
Det virker som om at den giver mig flere linjer end der reelt er... Rå data for periode FEB, Kunde JENSEN har 18 linjer (se herunder)´- efter den "rigtige" query skal den have 6 linjer - din tredje query giver 30 linjer... :)

@Alle:
Her er et super godt eksempel på en række posteringer i samme periode (FEB) på samme kunde (lad os sige Jensen):

-2513,4
-2513,4
-2513,4
-2513,4
2513,4
2513,4
-2541,05
-2541,05
-2541,05
-2541,05
2541,05
2541,05
-2634,42
-2634,42
2634,42
-2672,97
-2672,97
2672,97

Der er 18 linjer i rå-data... Når jeg kører min forhåbentlige kommende query skal den komme ud med følgende:

-2513,4
-2513,4
-2541,05
-2541,05
-2634,42
-2672,97
28. maj 2013 - 09:06 #10
Jamen det er nok mig der har misforstået spørgsmålet.  Din tabel indeholder rækker der har modposteringer i samme periode.  Jeg havde forstået, at du ville have disse modposteringer slettet fra tabellen.  Jeg gik videre ud fra, at det skulle være en periodisk, for eksempel en månedlig, rensning.  Men det synes nu ikke at være tilfældet, rækkerne med modposteringer skal forblive i tabellen, du vil blot lave et dataudtræk der kun viser rækker uden modposteringer i samme måned, og sådanne dataudtræk har du brug for ofte.  Så skal min query jo vendes om såsom:

SELECT * FROM mytable WHERE NOT EXISTS(SELECT * FROM table WHERE Beløb = -t.Beløb AND Periode = t.Periode AND Kunde = t.Kunde)

Men hovedproblemet ligger i, at der, som du siger i #5 og viser i #9, at der kan være værdier som

Hansen 150 Jan
Hansen 150 Jan
Hansen -150 Jan

Med min kode vil ingen af disse komme med, så det duer ikke.  Du vil altså vise alle rækker i tabellen undtagen de rækker der parvist modposterer hinanden.  Jeg gad vide, om det er en opgave, der er egnet for et databasesystem, eller om man skal over i en applikation.  For eksempel i php ville det være rimeligt simpelt at forespørge databasen for alle rækker og placere disse i en array, og derefter, i php, fjerne elementer fra arrayen to og to hvis de matcher, og derefter udskrive de resterende rækker i arrayen.  Med hensyn til at finde en løsning direkte i ms sql må jeg nok bakke ud.
Avatar billede Slettet bruger
28. maj 2013 - 10:22 #11
Her er en ny løsning:

with Positiv as
(
select Kunde, Periode, abs(Beløb) BeløbType, Beløb
,row_number() over(partition by Kunde, Periode order by Periode) Nr
from Tabel
where Beløb > 0
)
,
Negativ as
(
select Kunde, Periode, abs(Beløb) BeløbType, Beløb
,row_number() over(partition by Kunde, Periode order by Periode) Nr
from Tabel
where Beløb < 0
)
select coalesce(pKunde,nKunde) Kunde
      ,coalesce(pPeriode,nPeriode) Periode
      ,coalesce(pBeløb,nBeløb) Beløb
from
(
select p.Kunde pKunde, P.Periode pPeriode, P.Beløb pBeløb
      ,n.Kunde nKunde, n.Periode nPeriode, n.Beløb nBeløb
from Positiv p full outer join Negativ n
on p.Kunde = n.Kunde
and p.Periode = n.Periode
and p.BeløbType = n.BeløbType
and p.Nr = n.Nr
) s
where (pKunde is null or nKunde is null)
Avatar billede Slettet bruger
28. maj 2013 - 10:24 #12
row_number() over(partition by Kunde, Periode order by Periode) Nr

skal rettes til:

row_number() over(partition by Kunde, Periode, BeløbType order by Periode) Nr
Avatar billede Slettet bruger
28. maj 2013 - 10:29 #13
og BeløbType skal selvfølgelig være abs(Belløb) - så lige en gang til....

row_number() over(partition by Kunde, Periode order by Periode) Nr

skal rettes til:

row_number() over(partition by Kunde, Periode, abs(Beløb) order by Periode) Nr
Avatar billede Nicolai Nybegynder
28. maj 2013 - 15:26 #14
@rahp:
Du skal vide at jeg er imponeret over din hjælp og imponeret over at du kan tænke en sådan løsning frem. Desværre giver det mig stadig ikke det rigtige resultat, men det ser bedre ud... Men nu får du lige alle de reelle data jeg arbejder med:

Den query du har lavet, men med de rigtige navne og hvor jeg har sat ind så jeg kun kigger på en kunde og én periode (februar 2013). Så kan jeg bedre checke om det er rigtigt:

with Positiv as
(
select SalesDoc, PeriodB, abs(RevenueCleaned) BeløbType, RevenueCleaned
,row_number() over(partition by SalesDoc, PeriodB order by PeriodB) Nr
from dbo.tblXSAP_Revenue_Optimized
where RevenueCleaned > 0 AND PeriodB = '002.2013' AND SalesDoc = '40535523'
)
,
Negativ as
(
select SalesDoc, PeriodB, abs(RevenueCleaned) BeløbType, RevenueCleaned
,row_number() over(partition by SalesDoc, PeriodB, abs(RevenueCleaned) order by PeriodB) Nr
from dbo.tblXSAP_Revenue_Optimized
where RevenueCleaned < 0 AND PeriodB = '002.2013' AND SalesDoc = '40535523'
)
select coalesce(pKunde,nKunde) SalesDoc
      ,coalesce(pPeriode,nPeriode) PeriodB
      ,coalesce(pBeløb,nBeløb) RevenueCleaned
from
(
select p.SalesDoc pKunde, P.PeriodB pPeriode, P.RevenueCleaned pBeløb
      ,n.SalesDoc nKunde, n.PeriodB nPeriode, n.RevenueCleaned nBeløb
from Positiv p full outer join Negativ n
on p.SalesDoc = n.SalesDoc
and p.PeriodB = n.PeriodB
and p.BeløbType = n.BeløbType
and p.Nr = n.Nr
) s
where (pKunde is null or nKunde is null)

Her er rå-data fra dbo.tblXSAP_Revenue_Optimized hvis jeg kun vælger Periode 002.2013 og SalesDoc (som er kunden): 40535523.

SalesDoc    PeriodB    RevenueCleaned
40535523    002.2013    2634.42
40535523    002.2013    2513.40
40535523    002.2013    2541.05
40535523    002.2013    1319.96
40535523    002.2013    -2634.42
40535523    002.2013    -2513.40
40535523    002.2013    -2541.05
40535523    002.2013    -1319.96
40535523    002.2013    -2634.42
40535523    002.2013    -2513.40
40535523    002.2013    -2541.05
40535523    002.2013    -270.00
40535523    002.2013    -1349.61
40535523    002.2013    2672.97
40535523    002.2013    2513.40
40535523    002.2013    2541.05
40535523    002.2013    369.64
40535523    002.2013    -2672.97
40535523    002.2013    -2513.40
40535523    002.2013    -2541.05
40535523    002.2013    -369.64
40535523    002.2013    -2672.97
40535523    002.2013    -2513.40
40535523    002.2013    -2541.05
40535523    002.2013    -1807.09
40535523    002.2013    1691.11

Og her er resultat din query giver mig:
40535523    002.2013    -369.64
40535523    002.2013    369.64
40535523    002.2013    1319.96
40535523    002.2013    -1349.61
40535523    002.2013    -2513.40
40535523    002.2013    -2541.05
40535523    002.2013    -2541.05
40535523    002.2013    2541.05
40535523    002.2013    -2634.42
40535523    002.2013    -270.00
40535523    002.2013    -1319.96
40535523    002.2013    1691.11
40535523    002.2013    -1807.09
40535523    002.2013    -2513.40
40535523    002.2013    -2513.40
40535523    002.2013    2513.40
40535523    002.2013    -2541.05
40535523    002.2013    -2634.42
40535523    002.2013    2634.42
40535523    002.2013    -2672.97

Dette er resultatet hvis man manuelt renser tabellen i Excel, og sådan som den skal ende:

SalesDoc    PeriodB    RevenueCleaned
40535523    002.2013    -2634.42
40535523    002.2014    -2513.40
40535523    002.2015    -270.00
40535523    002.2016    -1349.61
40535523    002.2017    -2541.05
40535523    002.2018    -2672.97
40535523    002.2019    -2513.40
40535523    002.2020    -2541.05
40535523    002.2021    -1807.09
40535523    002.2022    1691.11


Så den renser nogen væk, men desværre ikke alle..

Kan du give den en sidste tand, eller skal jeg opgive og programmere mig ud af det i f.eks. Visual Basic som CHristian_Belgien forslog?
Avatar billede Slettet bruger
28. maj 2013 - 15:41 #15
du har glemt , abs(RevenueCleaned) i første select - skal være:

with Positiv as
(
select SalesDoc, PeriodB, abs(RevenueCleaned) BeløbType, RevenueCleaned
,row_number() over(partition by SalesDoc, PeriodB, abs(RevenueCleaned) order by PeriodB) Nr
from dbo.tblXSAP_Revenue_Optimized
where RevenueCleaned > 0 AND PeriodB = '002.2013' AND SalesDoc = '40535523'
)
Avatar billede Slettet bruger
28. maj 2013 - 15:45 #16
En ren SQL-løsning bedst, da sql-serverne som regel er meget bedre/hurtigere til at behandle data end clienten.
Avatar billede Nicolai Nybegynder
29. maj 2013 - 10:21 #17
Jeg bukker og klapper. Løsningen fungerer 100% perfekt! Du har sparet mig for meget arbejde og frustration.

Tak! Smider du et svar...
Avatar billede Slettet bruger
29. maj 2013 - 10:52 #18
Glad for at kunne hjælpe.

Select kan måske reduceres lidt:

with Positiv as
(
select SalesDoc, PeriodB, abs(RevenueCleaned) BeløbType, RevenueCleaned
,row_number() over(partition by SalesDoc, PeriodB, abs(RevenueCleaned) order by PeriodB) Nr
from dbo.tblXSAP_Revenue_Optimized
where RevenueCleaned > 0 AND PeriodB = '002.2013' AND SalesDoc = '40535523'
)
,
Negativ as
(
select SalesDoc, PeriodB, abs(RevenueCleaned) BeløbType, RevenueCleaned
,row_number() over(partition by SalesDoc, PeriodB, abs(RevenueCleaned) order by PeriodB) Nr
from dbo.tblXSAP_Revenue_Optimized
where RevenueCleaned < 0 AND PeriodB = '002.2013' AND SalesDoc = '40535523'
)
select coalesce(p.Kunde,n.Kunde) SalesDoc
      ,coalesce(p.Periode,n.Periode) PeriodB
      ,coalesce(p.Beløb,n.Beløb) RevenueCleaned
from Positiv p full outer join Negativ n
on p.SalesDoc = n.SalesDoc
and p.PeriodB = n.PeriodB
and p.BeløbType = n.BeløbType
and p.Nr = n.Nr
where (p.Kunde is null or n.Kunde is null)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester