Schichtumsätze über ein Jahr

Storm · 18 Juni 2015

Guten Tag liebe Profis,

ich wende mich als Forums Jungfrau an euch. Ich versuch das Problem jetzt schon eine ganze Weile zu lösen aber komm einfahc nicht mehr weiter vllt kann mir von euch jemand helfen.
Vorab, ich bin noch relativer Anfänger mit SQL, zwar im Studium mal gehabt aber nur am Rande und jetzt beim ersten produktiven mini Projekt.

Im Unternehmen gibt es 40 SB Läden. Jetzt soll täglich (und rückwirkend bis Januar) eine Abfrage gemacht werden wieviel Umsatz jeder einzelne dieser SB Märkte in der Früh-, Spät- und Nachtschicht generiert.

Wegen der Einteilung hab ich schon das erste (kleine) Problem. Den genau genommen habe ich ja 3 Dimensionen: Datum, Schicht und SB Markt. Das muss aber ggf. garnicht final gelöst werden, da die Kollegin die Daten per VBA in eine Excel Datei exportieren (und anordnen) will. Allerdings ist es auch für sie das erste Projekt dieser Art.
Das größere Problem ist das die Abfragen nach möglichkeit Automatisch ablaufen sollten. ALso von eonem JAVA gebranntmarkten gesprochen: eine Schleife die die Tage von Januar bis jetzt hochzählt.

Hier noch grob die Abfrage momentan (Frühschicht)

Code:

SELECT SUM(Umsätze) AS UmsatzSB1_Früh
FROM Tabelle1
WHERE
Date >= '2015-01-15 06:00:01' AND Date < '2015-01-15 14:00:00'
AND
StationName LIKE 'SB 1 Kasse%'

Falls ihr weitere Infos braucht oder ich einen Fehler gemacht hab (sorry

) gebt mir bitte bescheid.

akretschmer · 18 Juni 2015

Storm schrieb:
Wegen der Einteilung hab ich schon das erste (kleine) Problem. Den genau genommen habe ich ja 3 Dimensionen: Datum, Schicht und SB Markt.

Wo genau ist das Problem? Du kannst in SQL darauf aggregieren/gruppieren.

Storm · 18 Juni 2015

Erstmal danke für die schnelle Antwort ^^
ist wie gesagt eher ein neben Kriegsschauplatz, ich bin mir noch unsicher wie ich das am sinnvollsten darstell/einteil auch mit hinblick auf den Zugriff durch die VBA-Kollegin.

Aber zu deiner Frage: ich weiß nicht dass ich das kann und auch nicht wie ich das kann?

Distrilec · 18 Juni 2015

Ein einfaches... (Die Syntax ist wahrscheinlich falsch... Bin aus der Oracle-Ecke... Aber ich hab mir mühe gegeben

)

Code:

Select stationname
  ,convert(Date, getdate())
  ,Case
  When Cast(t.date As Time) Between Cast('06:00:01' As Time) And Cast('14:00:00' As Time) Then
  'Frühschicht'
  When Cast(t.date As Time) Between Cast('14:00:01' As Time) And Cast('22:00:00' As Time) Then
  'Mittagschicht'
  When Cast(t.date As Time) Between Cast('22:00:01' As Time) And Cast('23:59:59' As Time) Or
  Cast(t.date As Time) Between Cast('00:00:00' As Time) And Cast('06:00:00' As Time) Then
  'Nachtschicht'
  End As schicht
  ,Sum(t.umsätze) As umsatzsb1_früh
From  tabelle1 t
Where  datepart(hour, t.date) Between 6 And 14
Group  By stationname
  ,convert(Date, getdate())
  ,Case
  When Cast(t.date As Time) Between Cast('06:00:01' As Time) And Cast('14:00:00' As Time) Then
  'Frühschicht'
  When Cast(t.date As Time) Between Cast('14:00:01' As Time) And Cast('22:00:00' As Time) Then
  'Mittagschicht'
  When Cast(t.date As Time) Between Cast('22:00:01' As Time) And Cast('23:59:59' As Time) Or
  Cast(t.date As Time) Between Cast('00:00:00' As Time) And Cast('06:00:00' As Time) Then
  'Nachtschicht'
  End

akretschmer · 18 Juni 2015

Storm schrieb:
Aber zu deiner Frage: ich weiß nicht dass ich das kann und auch nicht wie ich das kann?

Angenommen, Du hast diese Daten:

Code:

test=*# select * from storm ;
  ts  | sb_markt_id | umsatz
---------------------+-------------+--------
 2015-06-18 05:01:01 |  1 |  10
 2015-06-18 07:10:01 |  2 |  20
 2015-06-18 08:10:01 |  2 |  30
(3 rows)

Dann kannst das z.B. so abfragen:

Code:

test=*# select ts::date, sb_markt_id,case when extract(hour from ts) < 6 then 'F' else 'N' end as schicht, sum(umsatz) from storm group by 1,2,3;
  ts  | sb_markt_id | schicht | sum
------------+-------------+---------+-----
 2015-06-18 |  2 | N  |  50
 2015-06-18 |  1 | F  |  10
(2 rows)

Das ist jetzt PostgreSQL, sollte prinzipiell ähnlich in allen anderen SQL-Datenbaken gehen.

Storm · 18 Juni 2015

Vielen lieben dank ihr 2, ich versuch jetzt mal das ganze umzusetzen. Ich meld mich dann ob ich weitergekommen oder nur verzweifelt bin

Storm · 18 Juni 2015

Hier mal der aktuelle Stand. Ich hoffe ich nähere mich etwas an, funktionieren tuts leider noch nicht.
Gerne Ideen und gemecker.

Code:

SELECT SUM(Umsätze) AS UmsätzeSB1

        CASE

WHEN Date >= '2015-01-15 06:00:01' AND Date < '2015-01-15 14:00:00'    THEN 'Früh'
WHEN Date >= '2015-01-15 14:00:01' AND Date < '2015-01-15 22:00:00'    THEN 'Spät'
WHEN Date >= '2015-01-15 02:00:01' AND Date < '2015-01-16 06:00:00'    THEN 'Nacht'

       END AS Schicht
     
FROM Datenbank

WHERE
StationName LIKE 'SB 1 K%'

GROUP BY Schicht

akretschmer · 18 Juni 2015

Storm schrieb:
funktionieren tuts leider noch nicht

Klasse Beschreibung.

Storm · 18 Juni 2015

Incorrect syntax near the keyword 'CASE'.

Entsprechend schätz ich mal das ich irgendwas in der Syntax von dem CASE statement verhauen hab. Nach dem Motto "Google ist dein Freund" bin ich gerade auf der Suche was den falsch ist.

ukulele · 18 Juni 2015

Code:

SELECT    t.Standort,
        t.Datum,
        t.Schicht,
        sum(t.Umsatz) AS Umsatz
FROM    (

SELECT    left(StationName,charindex('K',StationName)-1) AS Standort,
        cast([Date] AS DATE) AS Datum,
        (    CASE
            WHEN    datepart(hh,[Date]) BETWEEN 2 AND 5        THEN    'Nacht'
            WHEN    datepart(hh,[Date]) BETWEEN 6 AND 13    THEN    'Früh'
            WHEN    datepart(hh,[Date]) BETWEEN 14 AND 22    THEN    'Spät'
            ELSE    'Fehler/Zeit'
            END ) AS Schicht,
        Umsätze AS Umsatz
FROM    Tabelle1

        ) t
GROUP BY t.Standort,t.Datum,t.Schicht

In diesem Fall würde die Nachschicht bis 05:59 laufen und ab 06:00 die Frühschicht beginnen, ähnlich bei der Spätschicht. Es geht notfalls auch mit 1 Minute Offset aber ich glaube gar nicht das du das willst.

Ansonsten bietet der Ansatz durch den Subselect einfach mehr flexibilität, es geht auch in einem Select.

ukulele · 18 Juni 2015

Achja wie der Titel sagt Einschränkung auf ein Jahr und eine Sortierung kommt auch immer gut.

Code:

SELECT    t.Standort,
        t.Datum,
        t.Schicht,
        sum(t.Umsatz) AS Umsatz
FROM    (

SELECT    left(StationName,charindex('K',StationName)-1) AS Standort,
        cast([Date] AS DATE) AS Datum,
        (    CASE
            WHEN    datepart(hh,[Date]) BETWEEN 2 AND 5        THEN    'Nacht'
            WHEN    datepart(hh,[Date]) BETWEEN 6 AND 13    THEN    'Früh'
            WHEN    datepart(hh,[Date]) BETWEEN 14 AND 22    THEN    'Spät'
            ELSE    'Fehler/Zeit'
            END ) AS Schicht,
        Umsätze AS Umsatz
FROM    Tabelle1
WHERE    datepart(year,[Date]) = 2015

        ) t
GROUP BY t.Standort,t.Datum,t.Schicht
ORDER BY t.Standort,t.Datum,t.Schicht

Storm · 18 Juni 2015

Cool, das sieht ziemlich gut aus.

ukulele schrieb:
SELECT left(StationName,charindex('K',StationName)-1) AS Standort, cast([Date] AS DATE) AS Datum,

Den Teil versteh ich noch nicht wirklich aber solangs funktioniert ist das ja kein Problem. Die Stionen kann er aber fürcht ich aber nicht automatisch durchlaufen, es gäbe theoretisch noch mehr SB Märkte bzw. es kann sein das es z.B. SB Markt 18 nichtmehr gibt. ich würde hier halt von Hand dann die Abfrage für jeden SB Markt anpassen, das wäre ja nur einmal etwas Arbeit.

Ich habs nur versucht soweit umzubauen das er wirklich nur den Datum, Umsatz und Schicht angezeigt werden.
Ich hab das Gefühl ich mach mit den Bezeichnungen noch nen Fehler. Peinlich peinlich :oops:

Meldung ist:
incorrect Syntax near "."
incorrect Syntax near "t"

Code:

SELECT   t.Datum
            t.Schicht,
            SUM(Umsätze) AS UmsatzSB1

FROM    (

SELECT    left(StationName,charindex('K',StationName)-1) AS Standort,
        cast([Date] AS DATE) AS Datum,
        (    CASE
            WHEN    datepart(hh,[Date]) BETWEEN 2 AND 5     THEN    'Nacht'
            WHEN    datepart(hh,[Date]) BETWEEN 6 AND 13    THEN    'Früh'
            WHEN    datepart(hh,[Date]) BETWEEN 14 AND 22   THEN    'Spät'
            ELSE    'Fehler/Zeit'
            END ) AS Schicht,
        Umsätze AS UmsatzSB1
       
FROM    Tabelle1
WHERE    datepart(year,[Date]) = 2015

            ) t
       
GROUP BY t.Datum,t.Schicht, Umsätze

Storm · 18 Juni 2015

Eine Kleinigkeit noch zur Nachtschicht: Die würde in dem Fall jetzt ja nur von 2 bis 5 Uhr morgens gehen oder? Sie müsste aber von 22 Uhr am Vortag bis 5 Uhr Morgens gehn. Sry falls ich da nur was falsch versteh.

akretschmer · 18 Juni 2015

Storm schrieb:
Meldung ist:
incorrect Syntax near "."
incorrect Syntax near "t"

Im Select zwischen den einzelnen Feldern ein Komma plaziert kann Wunder bringen.

ukulele · 18 Juni 2015

Storm schrieb:
Den Teil versteh ich noch nicht wirklich aber solangs funktioniert ist das ja kein Problem.

Code:

left(StationName,charindex('K',StationName)-1) AS Standort,

Liefert dir zu der Spalte StationName immer den Teil bis eine Stelle vor den Buchstaben K. Aus "SB 1 Kasse 23" würde "SB 1" und aus "SB 123 Kasse 23" würde "SB 123". So kann der Markt eine mehrstellige Nummer haben und alle Kassen des Marktes würden im GROUP BY zusammen gefasst werden. Ich habe das einfach mal so angenommen aber ich weiß nicht genau wie deine Daten in der Spalte aussehen.

Code:

cast([Date] AS DATE) AS Datum,

Liefert dir nur das Datum und läßt die Zeit-Information wegfallen, so kann ich gut nach Tag gruppieren, die Zeitinformation verwende ich ja dann zum Ermitteln der Schicht.

Die Stionen kann er aber fürcht ich aber nicht automatisch durchlaufen, es gäbe theoretisch noch mehr SB Märkte bzw. es kann sein das es z.B. SB Markt 18 nichtmehr gibt.

Doch das würde ich auf jeden Fall automatisch lösen, in dem Fall das es Markt 18 nicht gibt würde derzeit auch keine Zeile zu Markt 18 erscheinen. Wenn es keine Umsätze in einer Schicht gäbe, würde auch keine Zeile zu dem Markt und der Schicht erscheinen. Wenn man das will geht das, ist aber etwas aufwendiger.

Code:

SELECT   t.Datum
            t.Schicht,
            sum(t.UmsatzSB1) AS UmsatzSB1

FROM    (

SELECT    left(StationName,charindex('K',StationName)-1) AS Standort,
        cast([Date] AS DATE) AS Datum,
        (    CASE
            WHEN    datepart(hh,[Date]) BETWEEN 2 AND 5     THEN    'Nacht'
            WHEN    datepart(hh,[Date]) BETWEEN 6 AND 13    THEN    'Früh'
            WHEN    datepart(hh,[Date]) BETWEEN 14 AND 22   THEN    'Spät'
            ELSE    'Fehler/Zeit'
            END ) AS Schicht,
        Umsätze AS UmsatzSB1
    
FROM    Tabelle1
WHERE    datepart(year,[Date]) = 2015

            ) t
    
GROUP BY t.Datum,t.Schicht, Umsätze

Du holst in der inneren Abfrage Umssätze und nennst sie UmsatzSB1, dann heißt die Spalte in der äußeren Abfrage auch UmsatzSB1, hier wäre also sum(UmsatzSB1) AS UmsatzSB1 richtig, siehe meine Änderung.

Schichtumsätze über ein Jahr

Storm

Benutzer

akretschmer

Datenbank-Guru

Storm

Benutzer

Distrilec

Datenbank-Guru

akretschmer

Datenbank-Guru

Storm

Benutzer

Storm

Benutzer

akretschmer

Datenbank-Guru

Storm

Benutzer

ukulele

Datenbank-Guru

ukulele

Datenbank-Guru

Storm

Benutzer

Storm

Benutzer

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru