none
Select mit Over() und Reset des Wertes RRS feed

  • Frage

  • Hallo,

    habe Artikel Veränderungen in einer Verlaufstabelle

    Datum             Artikelnummer VeränderungsMenge    Inventurmenge            Saldo

    01.01.2014      A                      100                             100                              100

    2.1.2914          A                      +4                                                                  104

    19.01.2014      A                      -2                                                                    102                 

    28.12.2014      A                      5                                                                     107

    01.01.2015      A                      99                               99                                99

    2.1.2015          A                      -2                                                                    97

    Möchte nun den Saldo bekommen mit

    Select …  OVER(PARTITION BY Artikelnummer order by Datum)

    Klappt soweit, was ich nicht hinbekomme ist, dass der Saldo quasi von neuem gezählt werden soll wenn es einen neuen Inventurbestand gibt, der evtl. auch dem eigentlichen Verlaufssaldo bis dahin nicht entspricht.

    Quasi ein Reset der Menge mittendrin.

    Wie kann ich das lösen ?


    • Bearbeitet OliverDupp Montag, 22. Februar 2016 19:04
    Montag, 22. Februar 2016 19:03

Antworten

  • Hallo Oliver,

    ich glaube, dass ich eine Lösung für Dein Problem habe. Der Trick ist, dass Du über eine CTE gehen musst, die dir markiert, wann eine Inventur stattfand. Das mache ich über einen kleinen Trick, ich markiere nämlich die Zeilen, die einen Inventur-Eintrag haben mit 1 und alle anderen mit 0 und summiere dann die Spalte. So haben alle Zeilen nach der ersten Inventur den Wert 1 in der neuen Spalte, alle Zeilen nach der zweiten Inventur den Wert 2 usw. 

    Am Ende kann ich diese neue Spalte als zusätzliche Partitionierungs-Spalte in deinem Kumuliert-Statement verwenden. Das ganze sieht dann so aus: 

    -- housekeeping in case you run this script multiple times
    begin try drop table #test end try begin catch end catch 
    
    create table #test (datum date not null, artikelnummer varchar(1) not null, Delta int not null, Inventur int null)
    insert into #test (datum, artikelnummer, delta, inventur) 
    values 
    ('2014-01-01', 'A', 100, 100),
    ('2014-01-02', 'A', 4, NULL),
    ('2014-01-19', 'A', -2, NULL),
    ('2014-12-28', 'A', 5, NULL),
    ('2015-01-01', 'A', 99, 99),
    ('2015-01-02', 'A', -2, NULL)
    
    ;with cte as (
      Select 
        datum, artikelnummer, delta, inventur, 
        -- hier entsteht die Partitionierungs-Spalte: 
        sum(case when Inventur is null then 0 else 1 end) over (partition by artikelnummer order by datum) as inv_change
      from #test
    
    )
    select Datum, artikelnummer, Delta, Inventur, inv_change,
    	Kummuliert= sum(delta) over (PARTITION BY Artikelnummer ORDER BY Datum),
    	Kummuliert2= sum(delta) over (PARTITION BY Artikelnummer, inv_change ORDER BY Datum)
    from cte

    Ich hoffe, das hilft Dir weiter. 

    Viele Grüße

    Ben

    Dienstag, 1. März 2016 09:10

Alle Antworten

  • Hallo Oliver,

    wie kann man den einen "neuen Inventurbestand" erkennen?

    Damit es leichter nachvollziehen können, wäre es gut, wenn Du die Tabellenstruktur als DDL, ein paar Beispieldaten als DML und das erwartete Ergebnis posten würdest. 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Dienstag, 23. Februar 2016 08:16
  • Hallo Olaf,

    mit

    Select Kummuliert= sum(Veränderungsmenge) over (PARTITION BY Artikelnummer ORDER BY Datum)

    from table order by datum

    Kann ich ja die kumulierte Summe berechnen und dann auch in den Saldo speichern. Nun sollte der Saldo aber innerhalb der Partition neu beginnen, wenn in der Spalte Inventurmenge ein positiver Bestand ist.
    Also im obigen Beispiel wäre es mit der Abfrage am 01.01.2015 107+99 = 206, am 2.1.2015 dann entsprechend 204,  ich möchte in der Spalte am 1.1.2015 aber die 99 haben und am 2.1. somit die 97. 

    Derzeit kann ich immer nur bis zum Anfang zurück rechnen, ohne zu erkennen das eine weitere Inventurmenge den Startwert eigentlich zurücksetzt.
    Ich weis gar nicht ob man das mir dieser einen Abfrage lösen kann, oder über eine andere Möglichkeit?


    Oliver Dupp


    • Bearbeitet OliverDupp Mittwoch, 24. Februar 2016 13:11
    Mittwoch, 24. Februar 2016 13:09
  • Hallo Oliver,

    ich glaube, dass ich eine Lösung für Dein Problem habe. Der Trick ist, dass Du über eine CTE gehen musst, die dir markiert, wann eine Inventur stattfand. Das mache ich über einen kleinen Trick, ich markiere nämlich die Zeilen, die einen Inventur-Eintrag haben mit 1 und alle anderen mit 0 und summiere dann die Spalte. So haben alle Zeilen nach der ersten Inventur den Wert 1 in der neuen Spalte, alle Zeilen nach der zweiten Inventur den Wert 2 usw. 

    Am Ende kann ich diese neue Spalte als zusätzliche Partitionierungs-Spalte in deinem Kumuliert-Statement verwenden. Das ganze sieht dann so aus: 

    -- housekeeping in case you run this script multiple times
    begin try drop table #test end try begin catch end catch 
    
    create table #test (datum date not null, artikelnummer varchar(1) not null, Delta int not null, Inventur int null)
    insert into #test (datum, artikelnummer, delta, inventur) 
    values 
    ('2014-01-01', 'A', 100, 100),
    ('2014-01-02', 'A', 4, NULL),
    ('2014-01-19', 'A', -2, NULL),
    ('2014-12-28', 'A', 5, NULL),
    ('2015-01-01', 'A', 99, 99),
    ('2015-01-02', 'A', -2, NULL)
    
    ;with cte as (
      Select 
        datum, artikelnummer, delta, inventur, 
        -- hier entsteht die Partitionierungs-Spalte: 
        sum(case when Inventur is null then 0 else 1 end) over (partition by artikelnummer order by datum) as inv_change
      from #test
    
    )
    select Datum, artikelnummer, Delta, Inventur, inv_change,
    	Kummuliert= sum(delta) over (PARTITION BY Artikelnummer ORDER BY Datum),
    	Kummuliert2= sum(delta) over (PARTITION BY Artikelnummer, inv_change ORDER BY Datum)
    from cte

    Ich hoffe, das hilft Dir weiter. 

    Viele Grüße

    Ben

    Dienstag, 1. März 2016 09:10
  • Vielen Dank,

    für die Rückmeldung. Ich werde es probieren.


    Oliver Dupp

    Dienstag, 1. März 2016 09:32
  • Vielen Dank,

    für die Rückmeldung. Ich werde es probieren.


    Oliver Dupp

    Hallo Oliver,

    hat das das gewünschte Resultat geliefert? Bist du damit zurande gekommen?

    Viele Grüße
    Ben

    Donnerstag, 3. März 2016 17:52