# MSSQL: Zeile nach Datums bereich auswählen



## brauh (12. März 2021)

Hallo,

ich habe eine Tabelle ( FIELDHISTORY) mit folgenden Spalten (siehe Anhang):

Ticket Nr = mrId
Zeitstempel bei Statusänderung = mrTimestamp
Ticket Status = mrNEWFIELDVALUE

In der Tabelle sind alle Status Zustände eingetragen, die ein Ticket im Laufe der Zeit durchlaufen hat.
Ich möchte nun in einer Abfrage erstellen, die mir pro Ticket nur einen Status listet und zwar der am 01. eines Monats.

Da ich nur rudimentär SQL kann bitte ich um eure Unterstützung.
Ich denke die Abfrage müsste so sein das man pro Ticket und Zeile der Reihe nach durchgeht und prüft welcher Zeitbereich (Zeitstempel bis zum nächsten Zeitstempel) im 01. Tag des Monats lag.

Ich hoffe es ist etwas verständlich.
Wäre super wenn mir hier jemand weiterhelfen kann.

Vielen Dank schon  mal.

VG
Burghard


----------



## Sempervivum (12. März 2021)

> Ich denke die Abfrage müsste so sein das man pro Ticket und Zeile der Reihe nach durchgeht und prüft welcher Zeitbereich (Zeitstempel bis zum nächsten Zeitstempel) im 01. Tag des Monats lag.


Das tut die Datenbank für dich.
Ich nehme an, dass dies auf deine Datenbank zutrifft:
Datums- und Uhrzeitdatentypen und zugehörige Funktionen - SQL Server (Transact-SQL)
Wenn ich das richtig lese, gibt die Funktion DAY den Tag im Monat zurück. Und deine Spalte mrTimestamp ist vom Typ DATETIME? Dann müsste deine Abfrage so aussehen:

```
SELECT mrId, mrTimestamp, mrNEWFIELDVALUE
    FROM FIELDHISTORY
    WHERE DAY(mrTimestamp) = 1
```


----------



## brauh (13. März 2021)

Hallo Sempervivum, Danke für die Infos! 

ja mrTimestamp ist vom Typ DATETIME.

Wo ich bisher scheitere ist 
Beispiel:
Ticket ID 1234 Zeit = 27.01.2021 Status Open
Ticket ID 1234 Zeit = 05.02.2021 Status Closed

Am 01.02. hatte das Ticket noch den Status Open. Da es ja bis dahin keine neue Status Änderung gab. Wie muss meine Abfrage lauten, damit der Eintrag vom 27.01.2021 für den 01 des Monats gezählt wird.

Wenn ich dazu ein ein Tip bekommen könnte wäre super!

Danke aber schon einmal für die Rückmeldung

VG
Burghard


----------



## Sempervivum (13. März 2021)

Willst Du das nur für ein einziges Ticket abfragen oder für alle?
Gibt ja fast nichts, was die Datenbank nicht kann, wahrscheinlich kennt einer der DB-Spezialisten eine Lösung nur mit einer Abfrage, ohne Nachverarbeitung.


----------



## brauh (13. März 2021)

Für alle Tickets also es würde auch genügen wenn ich eine feste Anzahl z.B. 80000 Datensätze vorgeben kann.


----------



## Sempervivum (13. März 2021)

Mit Nachverarbeitung ist das kein Problem, Du hast den Ablauf ja schon beschrieben. Programmierst Du das in PHP und kannst PDO nutzen?


----------



## brauh (13. März 2021)

Leider nein. Die Nachbearbeitung wollte ich so gering wie möglich halten. Ich nutze Tableau mit einer Verbindung zur SQL Datenbank. Schaffe ich es in der SQL Abfrage nur die Datensätze anzuzeigen die ich auch auswerten möchte, dann brauche ich nur noch ein paar Filter in Tableau zu setzen.


----------



## brauh (13. März 2021)

Die SQL Abfrage muss auch nicht besonders performant sein. Da ich in Tableau die Daten speichere und mit sogenannten Extrakts arbeite und ich würde ohne zusätzliche Programmiersprachen / Modulen etc. auskommen.


----------



## Zvoni (15. März 2021)

Was ist mit Sätzen, welche mehrmals am 01. eines Monats vorhanden sind?
Welcher von denen? Der erste? der letzte?

EDIT: Was ist mit Tickets, welche mehr als einen Monat laufen?
Beispiel:
Gestartet am 25.01.2021
Beendet/Current: Heute
Wir hätten zwei Monats-Erste (01.02.2021 und 01.03.2021).
Welcher soll gezeigt werden?

EDIT2: Kannst du mal deinen Screenshot als csv hier reinhängen?


----------



## Zvoni (15. März 2021)

OK, nach ein wenig Recherche läufts wohl auf folgendes hinaus:
Lückenlose Calendar-Table/View mit nem LEFT JOIN auf deine Daten, und dort ein IFNULL kombiniert mit der LAG-Funktion auf das Status-Feld sollte es wohl richten, und dann sempers Lösung aus Post #2 auf das Result-Set anwenden


----------



## brauh (15. März 2021)

Hallo Zvoni,
Was ist mit Tickets, welche mehr als einen Monat laufen?
=> Immer im jeweiligen Monat zählen

Welcher soll gezeigt werden?
=> Alle Monate in denen ein Ticket nicht geschlossen ist soll gezählt werden

VG
Burghard


----------



## brauh (15. März 2021)

with base as (
     select mrId, mrTimestamp, mrNEWFIELDVALUE,
       row_number() over(partition by mrId order by mrTimestamp) as 'row'
     from footprints.dbo.MASTER8_FIELDHISTORY
)

Meine Abfrage um die CSV Liste zu erstellen ( die Liste habe ich dann gekürzt)


----------



## brauh (15. März 2021)

Zvoni hat gesagt.:


> OK, nach ein wenig Recherche läufts wohl auf folgendes hinaus:
> Lückenlose Calendar-Table/View mit nem LEFT JOIN auf deine Daten, und dort ein IFNULL kombiniert mit der LAG-Funktion auf das Status-Feld sollte es wohl richten, und dann sempers Lösung aus Post #2 auf das Result-Set anwenden


Da muss ich leider passen...


----------



## brauh (15. März 2021)

Ziel ist es ein Tabelle zu haben, die mir alle Tickets listet die am 1. eines Monats nicht Status Closed hatten. Wenn ein Ticket z.B. 3 Monate nicht geschlossen war dann jeweils immer am 1. des Monats eine Zeile mit anzeigen.

VG
Burghard


----------



## Zvoni (15. März 2021)

OK, verstanden.

Was ist mit Tickets, welche innerhalb eines Monats "erledigt" werden?
Beispiel:
Ticket eröffnet: 04.03.2021
Ticket geschlossen: 15.03.2021
Hier gibt es keinen "Monats-Ersten"


----------



## brauh (15. März 2021)

Zvoni hat gesagt.:


> OK, verstanden.
> 
> Was ist mit Tickets, welche innerhalb eines Monats "erledigt" werden?
> Beispiel:
> ...


Diese Tickets werden dann nicht mit "gezeigt"


----------



## Zvoni (15. März 2021)

In den Beispiel-Daten sehe ich kein einziges Ticket mit einem "Monatssprung"?!?!?!


----------



## brauh (15. März 2021)

Sorry anbei bessere Daten


----------



## Zvoni (15. März 2021)

OK, ich kann dir ein Teilergebnis liefern.
Ich hab das mit der LAG-Funktion nicht hinbekommen, weil man es wahrscheinlich per Rekusrion machen muss.
Setzt  voraus dass du eine Tabelle "dates" hast, welche eine Spalte "Datum" hat, welches einfach fortlaufende Tagesdaten sind.

```
SELECT
T1.Datum,
T2.mrID,
T3.mrnewFieldvalue
FROM
dates T1
INNER JOIN
(SELECT
mrID,
Date(Min(mrTimeStamp)) As MinStamp,
Date(Max(mrTimeStamp)) As MaxStamp
FROM tickets
GROUP BY
mrID) As T2
ON
T1.Datum BETWEEN T2.MinStamp And T2.MaxStamp
LEFT JOIN
tickets T3
ON
T3.mrID=t2.mrID AND
Date(T3.mrTimeStamp)=T1.Datum
ORDER BY
T2.mrID,
T1.Datum
```
Ergibt für mrID=694185 (ich habe deine ursprüngliche CSV benutzt)
Rest dann in der Nachbereitung.
Oder Yaslaw hat noch ne Idee

2021-03-11694185Open2021-03-126941852021-03-136941852021-03-146941852021-03-15694185In__bProgress2021-03-15694185Closed


----------



## Zvoni (15. März 2021)

HA!
Ich habs!

```
WITH cte As (
    SELECT
    mrID,
    Date(Min(mrTimeStamp)) As MinStamp,
    Date(Max(mrTimeStamp)) As MaxStamp
    FROM
    tickets
    GROUP BY mrID
)
SELECT
cte.mrID,
dates.datum,
(
SELECT T1.mrNewFieldValue
FROM tickets T1
WHERE
T1.mrID=cte.mrID AND
Date(T1.mrTimeStamp)<=dates.datum
ORDER BY T1.mrTimeStamp DESC LIMIT 1) As mrNewFieldValue
FROM
cte
LEFT OUTER JOIN
dates
ON
dates.datum BETWEEN cte.MinStamp AND cte.MaxStamp
```
Ergibt für 694185
Es fehlt der Eintrag  In__bProgress
aber der war am gleichen Tag wie Closed jedoch früher, und ich ziehe nur den letzten Eintrag pro Tag

mrIDdatummrNewFieldValue6941852021-03-11Open6941852021-03-12Open6941852021-03-13Open6941852021-03-14Open6941852021-03-15Closed


----------



## Zvoni (15. März 2021)

Und hier mit der Erweiterung immer nur die Monatsersten anzuzeigen:
Achtung: Ist in SQLite, für MSSQL musst du den Dialekt finden.

```
SELECT
A1.mrID,
A1.Datum,
A1.mrNewFieldValue
FROM
(WITH cte As (
    SELECT
    mrID,
    Date(Min(mrTimeStamp)) As MinStamp,
    Date(Max(mrTimeStamp)) As MaxStamp
    FROM
    tickets2
    GROUP BY mrID
)
SELECT
cte.mrID,
dates.datum,
(
SELECT T1.mrNewFieldValue
FROM tickets2 T1
WHERE
T1.mrID=cte.mrID AND
Date(T1.mrTimeStamp)<=dates.datum
ORDER BY T1.mrTimeStamp DESC LIMIT 1) As mrNewFieldValue

FROM
cte
LEFT OUTER JOIN
dates
ON
dates.datum BETWEEN cte.MinStamp AND cte.MaxStamp) As A1
WHERE
A1.mrID=401057 AND
Date(A1.Datum)=Date(A1.Datum,'start of month')
```

Ergebnis für ID 401057

mrIDdatummrNewFieldValue4010572010-12-01Feedback__bReceived4010572011-01-01Feedback__bReceived


----------



## Zvoni (15. März 2021)

Für die Calendar-Table (Achtung: Das ist ein Brett!)

```
create table dates (id integer primary key);
insert into dates default values;
insert into dates default values;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
alter table dates add date datetime;
update dates set date=date('2000-01-01',(-1+id)||' day');
```
Ist für SQLite. Kann für MSSQL anderst sein.


----------



## Zvoni (15. März 2021)

Und hier dasselbe aber ohne Calendar-Table!
Achtung: Anfang und Ende der Calendar-CTE ist willkürlich!
Keine Ahnung ob man die dynamisch setzen kann

```
SELECT
A1.mrID,
A1.Datum,
A1.mrNewFieldValue
FROM
(
WITH
RECURSIVE cnt(Datum) AS (SELECT Date('2010-01-01') As Datum UNION ALL SELECT Date(Datum,'+1 day') As Datum FROM cnt WHERE Datum<Date('2030-12-31')),
cte As (SELECT mrID, Date(Min(mrTimeStamp)) As MinStamp, Date(Max(mrTimeStamp)) As MaxStamp FROM tickets2 GROUP BY mrID)
SELECT
cte.mrID,
cnt.Datum,
(
SELECT T1.mrNewFieldValue
FROM tickets2 T1
WHERE
T1.mrID=cte.mrID AND
Date(T1.mrTimeStamp)<=cnt.Datum
ORDER BY T1.mrTimeStamp DESC LIMIT 1) As mrNewFieldValue

FROM
cte
LEFT OUTER JOIN
cnt
ON
cnt.Datum BETWEEN cte.MinStamp AND cte.MaxStamp) As A1
WHERE
A1.mrID=401057 AND
Date(A1.Datum)=Date(A1.Datum,'start of month')
```


----------



## brauh (15. März 2021)

Hallo Zvoni, alter Schwede herzlichen Dank schon mal. Ich probiere das aus und melde mich wieder!
Danke!


----------



## Zvoni (16. März 2021)

brauh hat gesagt.:


> Hallo Zvoni, alter Schwede herzlichen Dank schon mal. Ich probiere das aus und melde mich wieder!
> Danke!


Wie gesagt: In Ermangelung eines MSSQL-Servers hier, hab ich das alles in SQLite gemacht.
Musst nur rausfinden, wie es in MSSQL heisst (und ich tippe vor allem auf das "Date"-Zeug, was in MSSQL anderst sein dürfte).
Ich weiss dass das "LIMIT 1" in MSSQL irgendwie "SELECT TOP(1)" oder so heisst


----------



## brauh (16. März 2021)

Ok Danke dir! Ich hoffe ich kann es heute im Laufe des Tages testen.


----------



## brauh (16. März 2021)

Sorry muss noch mal in Ruhe mir das anschauen, deshalb habe ich den letzten Post rausgenommen.


----------



## brauh (16. März 2021)

Hallo Zvoni, ich muss gestehen mit dem Übersetzen tue ich mich etwas schwer. Mit der Calendar-Table bekomme ich eine Fehlermeldung., die darauf hinweist das ich keine Schreibrechte haben. Ich denke der Letzte Code ist dann das was ich bräuchte aber da blick ich noch nicht so richtig durch   Ich muss da  noch mehr ausprobieren mit dem Übersetzen. Ich melde mich wieder. Dennoch schon mal ganz herzlichen Dank.


----------



## brauh (18. März 2021)

Hallo Zvoni, ich hätte noch einmal ein Bitte. Wäre es möglich das du so eine Tabelle zauberst?




ich würde mich da sicher wesentlich leichter tun beim Übersetzen und evl. ist das auch performanter und wie im letzten code muss ich ohne Create Table oder Ähnliches zurecht kommen da ich keine Schreibrechte auf die Datenbank habe.

Die Idee ist, MinStamp und MaxStamp zu erstellen (hast du ja schon) und dann 3 zusätzliche Spalten anlegen für den jetzigen Monat, einen Monat zurück und zwei Monate zurück. Darin sollen dann die jeweils gültigen Zeitstempel vom 01. des Monats erzeugt werden in Abhängigkeit vom Zeitbereich MinStamp und MaxStamp.

Das wäre echt super!  Eine CSV Datei habe ich ebenfalls angehängt.

Tausend Dank  !
VG
Burghard


----------



## Zvoni (19. März 2021)

Wozu? In meiner letzten Variante brauchst du kein Create Table, weil die CTE on the fly eine temp. View erzeugt.
Für MSSQL sind glaube ich zwei Teile betroffen, die zu übersetzen sind: Das Date-Zeug, und das LIMIT
Ich versuchs mal (ungetestet):

```
SELECT
    A1.mrID,
    A1.Datum,
    A1.mrNewFieldValue
    FROM
    (
    WITH
        RECURSIVE cnt(Datum) AS (SELECT Date('2010-01-01') As Datum UNION ALL SELECT DATEADD(day,1,Datum) As Datum FROM cnt WHERE Datum<Date('2030-12-31')),
        cte As (SELECT mrID, Date(Min(mrTimeStamp)) As MinStamp, Date(Max(mrTimeStamp)) As MaxStamp FROM tickets2 GROUP BY mrID)
        SELECT
        cte.mrID,
        cnt.Datum,
        (
            SELECT TOP(1) T1.mrNewFieldValue
            FROM tickets2 T1
            WHERE
            T1.mrID=cte.mrID AND
            Date(T1.mrTimeStamp)<=cnt.Datum
            ORDER BY T1.mrTimeStamp DESC) As mrNewFieldValue

            FROM
            cte
            LEFT OUTER JOIN
            cnt
            ON
            cnt.Datum BETWEEN cte.MinStamp AND cte.MaxStamp) As A1
WHERE
DATEPART(day.Datum)=1
```
Keine Ahnung, ob noch das "Date('2010-01-01') konvertiert werden muss.


----------



## brauh (19. März 2021)

Das Date Zeug bekomme ich in den Griff. Nur bei FROM weiss ich nicht wie das gemeint ist und wie man das ggf. anpassen muss in Bezug auf temp. View. 
Siehe Bild


----------



## brauh (19. März 2021)

die ursprungstabelle ist ja footprints.dbo.MASTER8_FIELDHISTORY bei FROM cte und cnt ist meines Erachtens auch noch etwas anzupassen.


----------



## brauh (19. März 2021)

Ich bekomme es einfach nicht hin .....


----------



## Zvoni (19. März 2021)

NE, nix footprints-blablabla da unten zum schluss
cnt und cte sind keine Aliase für Footprints

Genau so wie ich es geschrieben hatte
das cte und cnt bleibt im From/LEFT JOIN wie es ist

Ablauf:
das cte und cnt wird zuerst ausgeführt, und erzeugt eine InMemory-View, worauf dann die Innere SQL mit deinem Footprints-Zeug dann gejoint wird

Dein Footprints kommt genau an zwei stellen rein: Schau dir mal meine letzte Version an.
Da hatte ich genau zweimal meine Ur-Tabelle "tickets2" --> genau das muss durch dein Footprints ersetzt werden


----------



## Zvoni (19. März 2021)

Kannst du mal das Query nicht als Screenshot, sondern als Text hier reinhängen?


----------



## brauh (19. März 2021)

als Text.


----------



## Zvoni (19. März 2021)

Hmmm..... sieht gut aus.
Bekommst du noch Fehler? Wenn ja, welche?


----------



## brauh (19. März 2021)

ja alles was rot unterstrichen ist kann nicht übersetzt werden


----------



## brauh (19. März 2021)

cte und cnt wird meines Erachtens nicht erkannt


----------



## brauh (19. März 2021)

Das sind die Fehlermeldungen:

Meldung 156, Ebene 15, Status 1, Zeile 7
Incorrect syntax near the keyword 'WITH'.
Meldung 319, Ebene 15, Status 1, Zeile 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Meldung 102, Ebene 15, Status 1, Zeile 13
Incorrect syntax near ','.
Meldung 102, Ebene 15, Status 1, Zeile 28
Incorrect syntax near ')'.


----------



## Zvoni (19. März 2021)

Bin jetzt im Wochende. Montag wieder.
zeile 7 könnte es das RECURSIVE sein
zeile 13 kann es sein, dass man das WITH wiederholen muss


----------



## brauh (19. März 2021)

Alles klar Danke   schönes Wochenende!


----------



## Zvoni (22. März 2021)

Jupp.
MSSQL versteht das RECURSIVE nicht
WITH common_table_expression (Transact-SQL) - SQL Server


----------



## brauh (22. März 2021)

OK... dann doch diese Tabelle also Lösung?



Wäre für mich auch vollkommen in Ordnung.
Oder den bisherigen Code weiter anpassen?


----------



## Zvoni (22. März 2021)

MSSQL kann definitiv rekursive CTE's.
Kommt jetzt nur noch drauf an, es herauszufinden wie es geht (Bin kein MSSQL-Experte)
Mal schauen ob @Yaslaw den Thread findet


----------



## brauh (22. März 2021)

Sowas würde funktionieren:
Recursive CTEs Explained - Essential SQL


WITH   cte
AS     (SELECT 1 AS n -- anchor member
        UNION ALL
        SELECT n + 1 -- recursive member
        FROM   cte
        WHERE  n < 50 -- terminator
       )
SELECT n
FROM   cte
OPTION (MAXRECURSION 200);


----------



## brauh (22. März 2021)

Sowas wäre möglich (getestet):

    declare @fromDate Date = '2010-01-01',
                 @ToDate   Date = '2030-12-31'

    ;WITH cnt (Date)
    AS 
    (
        --initialization
        SELECT @fromDate
        UNION ALL 

        --recursive execution
        SELECT DATEADD(day, 1, Date)
        FROM cnt 
        WHERE Date < @ToDate
     )
     select Date
     From cnt
     OPTION (MAXRECURSION 0); -- remove limit

ein Beispiel dafür habe ich hier gefunden:
Microsoft SQL Server - Generating Date Range With Recursive CTE | sql-server Tutorial


----------



## brauh (25. März 2021)

Hallo @Zvoni ,

dieser "teil code' funktioniert. Mir fehlt jetzt nur noch wie man das Ganze noch in
SELECT A1.mrID, A1.Date, A1.mrNewFieldValue
FROM ( ....

)As A1
WHERE
DATEPART(day.Date)=1
reinpackt.

Vielleicht hast du ja noch eine Idee.

Viele Grüße

```
Declare @FromDate Date = '2021-01-01',
               @ToDate   Date = '2021-03-25'

        ;WITH cnt (Date) AS
        (
            SELECT @FromDate
            UNION ALL
    
            SELECT DATEADD(day, 1, Date)
            FROM cnt
            WHERE Date < @ToDate

        ), cte As
        (
            SELECT mrID, Min(mrTimestamp) As MinStamp, Max(mrTimeStamp) As MaxStamp
            FROM footprints.dbo.MASTER8_FIELDHISTORY GROUP BY mrID
        )
    
        SELECT cte.mrID, cnt.Date, cte.MinStamp, cte.MaxStamp,
        (
            SELECT TOP 1 T1.mrNewFieldValue
            FROM footprints.dbo.MASTER8_FIELDHISTORY T1
            WHERE T1.mrID = cte.mrID AND T1.mrTimeStamp <= cnt.Date
            ORDER BY T1.mrTimeStamp DESC
        )
        As mrNewFieldValue
        FROM cte LEFT OUTER JOIN cnt
        ON cnt.Date BETWEEN cte.MinStamp AND cte.MaxStamp
        OPTION (MAXRECURSION 0) -- remove limit
```


----------



## Yaslaw (20. April 2021)

Zvoni hat gesagt.:


> MSSQL kann definitiv rekursive CTE's.
> Kommt jetzt nur noch drauf an, es herauszufinden wie es geht (Bin kein MSSQL-Experte)
> Mal schauen ob @Yaslaw den Thread findet


Sorry für die späte Antwort.
MSSQL kann ich leider nicht. Mit MySQL, Oracle und MS Access kann ich helfen.


----------

