SQL - SUM() und FieldType Bit

[jensw_2000]

Hab da mal ne Frage ...

Folgende Problematik:

ich brauche eine Abfrage (oder besser function) die Summe einer Spalte mit Bit-Werten als Bit oder Bool zurückgibt...


Teilauftrag.istAbgerechnet (bit)

SQL-Code:

  SELECT  sum(Teilauftrag.istAbgerechnet) AS Alle_TA_Abgerechnet
  FROM    Teilauftrag
  WHERE   Teilauftrag.Auftragsnummer = 'A2004-0012'

Dachte eigentlich dass das so funktioniert,
aber die MSDE sagt mit bein Ausführen der Abfrage:

"The sum or average aggregate operation cannot take a bit data type as an argument"

Hat das schon mal jemand versucht ein Bit Feld mit einer SQL-Function zu berechnen ?
Klappt das oder kann ich mit die Arbeit sparen ?

[jim_raynor]

Willst du die Anzahl wissen wieviele Abgerechnet sind?

Dann musst du mit Count arbeiten:

SQL-Code:

SELECT  count(Teilauftrag.istAbgerechnet) AS Alle_TA_Abgerechnet
  FROM    Teilauftrag
  WHERE   Teilauftrag.Auftragsnummer = 'A2004-0012' and Teilauftrag.istAbgerechnet

Count gibt die Anzahl der Datensätze zurück. über das zusätzliche Where werden nur die abgerechneten gezählt. (weiss nicht ob es so 100% syntaktisch richtig ist, am besten mal probieren).

[jensw_2000]

Ich will wissen, ob alle Teilaufträge eines Auftrags abgerechnet sind oder nicht ...

[jim_raynor]

Dann musst du vermutlich zwei Abfragen machen. In der ersten ermittelst du die Anzahl aller Fälle (also ohne das "and Teilauftrag.istAbgerechnet" in Where) und beim zweite mal damit. Dann hast du die Gesamtzahl der Fälle und die Anzahl der abgerechneten. Ob es eine einfachere Möglichkeit gibt weis ich nicht, aber so würde ich es erstmal machen, bis mir was besseres einfällt.

[jensw_2000]

ist eventuell etwas zu ungenau

Also:

Alle Teilaufträge werden beim Erfassen in die Tabelle "Teilauftrag" geschrieben.
Nun sollen Teilaufträge zu einem Gesamtauftrag zusammengefasst werden.

Dazu eine Tabelle Auftrag.
Der Kunde erstellt einen Auftrag und ordnet ihm alle dazugehörenden Teilaufträge zu.

Der Unique Key aus "Auftrag" wird in die Spalte Teilauftrag.Auftragsnummer bei allen ausgewählten Teilaufträgen geschrieben ...

Nun sollen die Teilaufträge einzeln abgerechnet werden können.
Ich muss nun herausfinden können, ob alle Teilaufträge eines Auftrags abgerechnet sind.

Mit nem Stückchen Code nicht schwer, aber schneller und eleganter wäre das sicher per SQL.
Schade nur, das SUM() keine Bit Addition unterstützt ...

[jim_raynor]

Du könntest doch das Feld istAbgerechnet als NUMBER mit einem Zeichen machen. 1 = bedeutet abgerechnet, 0 = nicht abgerechnet. Jetzt könntest du eine Abfrage machen:

SQL-Code:

SELECT  sum(Teilauftrag.istAbgerechnet) AS TA_Abgerechnet,
        count(*) AS TA_Anzahl
  FROM    Teilauftrag
  WHERE   Teilauftrag.Auftragsnummer = 'A2004-0012'

Sind beide Felder gleich des Selects gleich, wäre alle Aufträge abgerechnet. Um deiner Forderung nach zu kommen, dass wenn möglich ohne programm zu machen, könnte man vielleicht das Select leicht anpassen:

SQL-Code:

SELECT  sum(Teilauftrag.istAbgerechnet)=count(*) AS TA_alle_Abgerechnet
  FROM    Teilauftrag
  WHERE   Teilauftrag.Auftragsnummer = 'A2004-0012'

Dann müsste eigentlich True oder false rauskommen.

[jensw_2000]

OK, werde mal ein paar Tests in diese Richtung starten ...
Danke :cheers:

[jensw_2000]

OK, das ist es:

SQL-Code:

CREATE FUNCTION Alle_TA_Abgerechnet (@Auftragsnummer nvarchar(19))
RETURNS bit

AS
BEGIN
  declare @gesamtTA smallint
  declare @abgerechnetTA smallint
  declare @KomplettErledigt bit

  select @GesamtTA          = count(Teilauftrag.istAbgerechnet)
                              From Teilauftrag
                              Where Teilauftrag.Auftragsnummer = @Auftragsnummer
                      
  select @AbgerechnetTA     = count(Teilauftrag.istAbgerechnet)
                              From Teilauftrag
                              Where Teilauftrag.Auftragsnummer = @Auftragsnummer AND
                                    (Teilauftrag.istAbgerechnet=1 OR Teilauftrag.istStorniert=1)
                            
 SELECT  @KomplettErledigt  = CASE
                                WHEN @GesamtTA-@AbgerechnetTA = 0 THEN 1
                       ELSE 0
                        END

return @KomplettErledigt

END

Erbebnis: funktioniert :P :

SQL-Code:

  SELECT  Auftragsnummer, 
          ...,
          Alle_TA_Abgerechnet(Auftragsnummer) as KomplettAbgerechnet
  FROM    Auftrag INNER JOIN Teilauftrag ON Auftrag.Auftragsnummer = Teilauftrag.Auftragsnummer
  WHERE   ...

[PRehders]

Hallo,

warum fragst du die Tabelle zweimal ab? Ziehe dir doch einfach die Anzahl aller nicht (!) abgerechneten Teilaufträge; ist die größer 0, dann ist der Auftrag nicht komplett abgerechnet...

Bei großen Tables (evtl. ohne entsprechende Indizes) kann das eine Menge Arbeit für die DB sparen!

Viel Spaß

Peter

[jensw_2000]

Stimmt.

:wall:

Danke, das spart bei der jetzigen Datenmenge schon ca. 0,4 Sekunden pro Abfrage.
In 10 Jahren müssen die Leute wegen dir auf die Kaffepause verzichten ... :mrgreen:

Ne...
habe ich echt nicht gesehen. Ich war froh endlich eine funktionierende Lösung zu haben.
Danke

[Leuselator]

nur der Vollständigkeit halber:

SQL-Code:

SELECT count(*)                                   AS Ges
     , SUM(CASE WHEN Bitfeld=1 THEN 1 ELSE 0 END) AS BitOn 
     , SUM(CASE WHEN Bitfeld=1 THEN 0 ELSE 1 END) AS BitOff 
  FROM TABELLE
 WHERE <BEDINGUNG>

funktioniert auch mit Bitfeldern