Anzeigen aller Datümer

Question

Guten Tag

Mit folgendem Statement erhalte ich eine Liste mit Datum, TotalOperationen und AnzahlParallelOPs, aber nur die welche in AnzahlParallelOPs nicht NULL sind. Ich möchte nun alle anzeigen lassen, welche in TotalOperationen etwas enthalten. Dort wo AnzahlParallelOPs nichts enthält entweder NULL oder eine '0'. Ist das möglich mit einem LEFT JOIN? Aber wo und wie? Für Hilfe bin ich dankbar

Freundliche Grüsse

Walo Beck

with cte as (

select OPDATUM, TS_INVORBEREIT,TS_PATABGABE, row_number() over (order by TS_INVORBEREIT) as Row

from ISMEDEOPPS_OP I

WHERE OPDatum >= '20120101' AND OPDatum <='20120104'  AND GELOESCHT = 'N' AND (GESTRICHEN = '' OR GESTRICHEN IS NULL) AND ((substring(TS_INVORBEREIT,9,4) >= 2000 and substring(TS_INVORBEREIT,9,4) <= 2359) OR (substring(TS_INVORBEREIT,9,4) >= 0000 and substring(TS_INVORBEREIT,9,4) <= 0659)) AND I.PROTOERFASSOK ='3' AND ASAN <>''

)

select cast(C1.OPDATUM as Date) as OPDATUM

, (select COUNT(I1.OPDATUM) from ISMEDEOPPS_OP I1

            WHERE I1.OPDatum >='20120101' AND I1.OPDatum <='20120104'  AND I1.GELOESCHT = 'N' AND (I1.GESTRICHEN = '' OR I1.GESTRICHEN IS NULL) AND ((substring(I1.TS_INVORBEREIT,9,4) >= 2000 and                   substring(I1.TS_INVORBEREIT,9,4) <= 2359) OR (substring(I1.TS_INVORBEREIT,9,4) >= 0000 and substring(I1.TS_INVORBEREIT,9,4) <= 0659)) AND I1.PROTOERFASSOK ='3' AND I1.ASAN <>''

            AND C1.OPDATUM=I1.OPDATUM

) as TotalOperationen

, COUNT(C1.Row) AS AnzParalleleOPs

from cte c1

inner join cte c2

ON c1.OPDATUM = c2.OPDATUM and c1.Row = c2.Row - 1 and c2.TS_INVORBEREIT < c1.TS_PATABGABE

GROUP BY C1.OPDATUM

ORDER BY OPDATUM

Answer 1

Hallo Walo!

Verwende eine zweite CTE (TotalOperationen), wobei Du die Join-Bedingung dort rausnimmst. (C1.OPDATUM=I1.OPDATUM)

Verwende dann diese CTE in der FROM-Klausel und füge die anderen Tabellen mit LEFT JOIN hinzu.

BTW: Statt der Kombination von >= und <= kann man auch BETWEEN verwenden, was die Lesbarkeit erhöht. Leider sind viele WHERE-Bedingungen bei Dir Non-Sargable, was bei mir Zweifel am Datenmodell aufkommen lässt. Ausserdem vergleichst Du Strings mit numerischen Werten.

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP - Blog

Answer 2

Hallo Christoph

Herzlichen Dank für Deine Antwort. Habe Deine Anregung betreffs BETWEEN / Strings und nummerischen Werten aufgenommen. Habe auch eine zweite cte eingebaut. Da ich noch ziemlicher Anfänger bin wiess ich nicht wie ich nun die zweite cte mit einem LEFT JOIN verbinde. Könntest Du mir da noch ein wenig unter die Arme greifen.

Freundliche Grüsse und Danke

Walo Beck

with cte as (

select I.OPDATUM
, I.TS_INVORBEREIT
, I.TS_PATABGABE
, row_number() over (order by I.TS_INVORBEREIT) as Row

FROM ISMEDEOPPS_OP I

WHERE OPDatum BETWEEN '20120101' AND '20120104' AND I.GELOESCHT = 'N' AND (I.GESTRICHEN = '' OR I.GESTRICHEN IS NULL)
AND ((substring(I.TS_INVORBEREIT,9,4) BETWEEN '2000' AND '2359') OR (substring(I.TS_INVORBEREIT,9,4) BETWEEN '0000' AND '0659'))
AND I.PROTOERFASSOK ='3'
AND ASAN <>''
)
, cte2 as (select cast(I9.OPDATUM as Date) as OPDATUM
, COUNT(I9.OPDATUM) as TotalOperationen2
FROM ISMEDEOPPS_OP I9

WHERE OPDatum BETWEEN '20120101' AND '20120104'

AND I9.GELOESCHT = 'N'
AND (I9.GESTRICHEN = '' OR I9.GESTRICHEN IS NULL)
AND ((substring(I9.TS_INVORBEREIT,9,4) BETWEEN '2000' and '2359') OR (substring(I9.TS_INVORBEREIT,9,4) BETWEEN '0000' and  '0659'))
AND I9.PROTOERFASSOK ='3'
AND I9.ASAN <>''
GROUP BY OPDATUM
)

select cast(C1.OPDATUM as Date) as OPDATUM
, (select COUNT(I1.OPDATUM) from ISMEDEOPPS_OP I1
 WHERE I1.OPDatum BETWEEN '20120101' AND '20120104' 
 AND I1.GELOESCHT = 'N'
 AND (I1.GESTRICHEN = '' OR I1.GESTRICHEN IS NULL)
 AND ((substring(I1.TS_INVORBEREIT,9,4) BETWEEN '2000' AND '2359') OR (substring(I1.TS_INVORBEREIT,9,4) BETWEEN '0000' and '0659'))
 AND I1.PROTOERFASSOK ='3'
 AND I1.ASAN <>''
 AND C1.OPDATUM=I1.OPDATUM ) as TotalOperationen
, COUNT(C1.Row) AS AnzParalleleOPs

FROM cte c1

INNER JOIN cte c2 ON
c1.OPDATUM = c2.OPDATUM
AND c1.Row = c2.Row - 1
AND c2.TS_INVORBEREIT < c1.TS_PATABGABE

GROUP BY C1.OPDATUM

ORDER BY OPDATUM

Answer 3

Hallo Walo,

ich hoffe den Zweck des SQLs verstanden zu haben. Ansonsten wird hoffentlich zumindest klar, wie das mit zwei CTEs gehen kann. Die Derived Table TotalOperationen ist bei mir zur CTE TotalOperationen geworden. Die CTE2 habe ich ersatzlos gestrichen. Die erste CTE würde ich zur besseren Lesbarkeit mit einem sprechenden Namen versorgen, z. B. NummerierteOperationen.

HTH!

with cte as (
 
select I.OPDATUM
 , I.TS_INVORBEREIT
 , I.TS_PATABGABE
 , row_number() over (order by I.TS_INVORBEREIT) as Row 

FROM ISMEDEOPPS_OP I
 
WHERE OPDatum BETWEEN '20120101' AND '20120104' 
AND I.GELOESCHT = 'N' 
AND (I.GESTRICHEN = '' OR I.GESTRICHEN IS NULL)
AND ((substring(I.TS_INVORBEREIT,9,4) BETWEEN '2000' AND '2359') OR (substring(I.TS_INVORBEREIT,9,4) BETWEEN '0000' AND '0659'))
AND I.PROTOERFASSOK ='3' 
AND ASAN <>''
 )
 , TotalOperationen as
 (select I1.OPDATUM, COUNT(I1.OPDATUM) as OPDATUM_Count
	from ISMEDEOPPS_OP I1
  WHERE I1.OPDatum BETWEEN '20120101' AND '20120104'  
 AND I1.GELOESCHT = 'N' 
 AND (I1.GESTRICHEN = '' OR I1.GESTRICHEN IS NULL) 
 AND ((substring(I1.TS_INVORBEREIT,9,4) BETWEEN '2000' AND '2359') OR (substring(I1.TS_INVORBEREIT,9,4) BETWEEN '0000' and '0659'))
 AND I1.PROTOERFASSOK ='3' 
 AND I1.ASAN <>'' 
 group by I1.OPDATUM)
 
select T.OPDATUM as OPDATUM
 , T.OPDATUM_Count
 , COUNT(C1.Row) AS AnzParalleleOPs
 
FROM TotalOperationen T
Left Join cte c1 on
T.OPDATUM=C1.OPDATUM 
Left JOIN cte c2 ON 
c1.OPDATUM = c2.OPDATUM 
AND c1.Row = c2.Row - 1 
AND c2.TS_INVORBEREIT < c1.TS_PATABGABE
 
GROUP BY T.OPDATUM, T.OPDATUM_Count
ORDER BY OPDATUM;

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP - Blog

Answer 4

Hallo Christoph

Nach einer kleinen Anpassung, ich musste 'COUNT(C1.Row) AS AnzParalleleOPs' auf  'COUNT(C2.Row) AS AnzParalleleOPs' wechseln, funktioniert nun alles tadellos.

Das mit der cte ist mir ein wenig klarer geworden, muss ich mich aber noch ein wenig mehr beschäftigen.

Herzlichen Dank für Deine Hilfe

Walo Beck