Nettoerlös

[Hoppel]

Hallo,

ich habe folgendes Problem:

Ich möchte neben dem Umsatz und der Absatzmenge von Produkten den "Nettoerlös" ausgegeben bekommen. Dieser ergibt sich aus der Differenz zwischen Einkaufspreis*Menge und Verkaufspreis * Menge.

select b.Artikelnummer, SUM(b.menge) as Absatzmenge, count(distinct b.adressnummer) as AnzahlKunden, ROUND(SUM(b.Menge*b.Einzelpreis),2) as Umsatz
from Bestellungen b
where convert(date,b.datum) >='01.01.2013' and  convert(date,b.datum) < '01.01.2014'
group by b.Artikelnummer
having ROUND(SUM(Menge*Einzelpreis),2)>0
order by b.Artikelnummer

Soweit funktioniert die Abfrage, aber was fehlt ist der Nettoerlös.

select b.Artikelnummer, ((b.Einzelpreis*b.Menge)-(a.EinkaufsPreis*b.Menge)) as Test
from Artikel a, bestellungen
b where a.ArtNr = b.Artikelnummer

Diese Abfrage liefert mir das Ergebnis aber nicht nach Artikel gruppiert. Füge ich ein group by b.artikelnummer hinzu, dann bekomme ich eine Fehlermeldung.

Wo liegt mein Denkfehler?

 

[Distrilec]

Wieso fehlen im zweiten Teil denn deine Aggregationen? Logisch dass dann ein Fehler geschmissen wird

 

[Hoppel]

Ups.. Danke für den Hinweis ;-)!

Nun habe ich ein weiteres "kleines" Problem. Ich möchte mir alle Kunden ausgeben lassen, die im Jahr 2014 einen Artikel gekauft haben, aber nicht im Jahr 2015. Mit dem Umsatz des vergangenen Jahres:

(select a.Adressnummer, a.Name, SUM(b.menge*b.Einzelpreis) as Umsatz
from Bestellungen b, Adresse a
where b.artikelnummer  = 'Test' and
convert(date, b.datum) >= '01.01.2013' and  convert(date, b.datum) < '01.01.2014' and
b.Adressnummer = a.Adressnummer
group by a.Adressnummer, a.Name
having ROUND(SUM(Menge*Einzelpreis),2)>0)

except

(select a.Adressnummer, a.Name, SUM(b.menge*b.Einzelpreis) as Umsatz
from Bestellungen b, Adresse a
where b.artikelnummer  =  'Test' and
convert(date, b.datum) >= '01.01.2014' and  convert(date, b.datum) <  '01.01.2015' and
b.Adressnummer = a.Adressnummer
group by a.Adressnummer, a.Name
having ROUND(SUM(Menge*Einzelpreis),2)>0)

Die Abfrage ist syntaktisch fehlerfrei, aber das Ergebnis kann nicht stimmen. Mir wird ein Kunde angezeigt, der den Artikel im Jahr 2015 gekauft hat. Wo ist hier mein Denkfehler?

Vielen lieben Dank für Eure Anregungen!

 

[Distrilec]

Deine Jahre stimmen nicht...
Du selektierst alle Kunden die im laufenden Jahr 2013 (also zwischen dem 01.01.2013 und dem 01.01.2014) einen Artikel gekauft haben mit Ausnahme von denen die im laufenden Jahr 2014 (also zwischen dem 01.01.2014 und dem 01.01.2015) einen Artikel gekauft haben...
Du willst wahrscheinlich sowas... (Warum auch immer du ein Datum zu einem Datum konvertierst... Ich habs einfach mal drinn gelassen...)

Select a.adressnummer
      ,a.name
      ,Sum(b.menge * b.einzelpreis) As umsatz
From   adresse a

Inner  Join bestellungen b
On     b.adressnummer = a.adressnummer

Where  b.artikelnummer = 'Test'
And    Convert(Date, b.datum) Between '01.01.2014' And '31.12.2014'
And    Not Exists (Select 1
                   From   bestellungen b2
                   Where  b2.adressnummer = a.adressnummer
                   And    b2.artikelnummer = b.artikelnummer
                   And    Convert(Date, b2.datum) Between '01.01.2015' And '31.12.2015')

 

[Hoppel]

Meldung 8120, Ebene 16, Status 1, Zeile 1
Die adresse.Adressnummer-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

Ich erhalte diese Fehlermeldung, wenn ich die Anfrage ausführen möchte.

 

[Distrilec]

Ja, sry.... Mein Fehler.... Hab das Group By vergessen...

Select a.adressnummer
      ,a.name
      ,Sum(b.menge * b.einzelpreis) As umsatz
From   adresse a

Inner  Join bestellungen b
On     b.adressnummer = a.adressnummer

Where  b.artikelnummer = 'Test'
And    Convert(Date, b.datum) Between '01.01.2014' And '31.12.2014'
And    Not Exists (Select 1
                   From   bestellungen b2
                   Where  b2.adressnummer = a.adressnummer
                   And    b2.artikelnummer = b.artikelnummer
                   And    Convert(Date, b2.datum) Between '01.01.2015' And '31.12.2015')
Group  By a.adressnummer
         ,a.name

 

[Hoppel]

Vielen Dank für die wunderbare Lösung.

Leider hat sich nun folgendes Problem ergeben:

Wenn ich in Excel über "Externe Daten" eine Verbindung zum Server herstelle und über "Daten"->"Verbindungen" die Abfrage hinterlege, bekomme ich bei folgendem Code eine Fehlermeldung:

Select a.adressnummer
      ,a.name
      ,Sum(b.menge * b.einzelpreis) As umsatz
From   adresse a

Inner  Join bestellungen b
On     b.adressnummer = a.adressnummer

Where  b.artikelnummer = ?
And    Convert(Date, b.datum) >= ? And Convert(Date, b.datum) <  ?
and    Not Exists (Select 1
                   From   bestellungen b2
                   Where  b2.adressnummer = a.adressnummer
                   And    b2.artikelnummer = b.artikelnummer
                   And      Convert(Date, b.datum) >= ? And Convert(Date, b.datum) < ?)
Group  By a.adressnummer
         ,a.name

Wenn ich die Parameter durch statische Werte ersetze funktioniert die Abfrage einwandfrei. Habt ihr eine Idee?

P.S:

convert >= ? and convert <

habe ich benutzt, da es mit between einen Fehler gab.

 

[Hoppel]

Die Fehlermeldung lautet:

..Ungültiger Deskriptorinex

 

[ukulele]

Die Interpretation deines "?" obliegt hier wohl dem Client der mit der DB spricht, das ist jedenfalls so kein SQL Code. Excel wird das so nicht unterstützen, eventuell kann man das mit VB machen aber da bin ich überfordert.