Bestimmte Zeile einer Partition gem. einer Logik aus Abfrageresultset filtern

Question

Hallo zusammen,

ich habe eine Abfrage, die folgendes Ergebnis (gekürzt) liefert:

Mein Code sieht so aus:

WITH CTE AS

(
SELECT	
						  
IM.IMObjektnummer		AS Objektnummer
, IM.IMSK
	, CASE 
		WHEN SHTOBWaehrung  =  'DEM'
		THEN Cast(SHTOBBewertung / 1.95583 AS money)
		ELSE Cast(SHTOBBewertung AS money)
	END	        AS Bewertung
, SHTOBVom		AS BewertungPer
, SHTOBBewertungVom	AS EingabePer
, bwart.KEYBegriff	AS Begriff
, bwArt.KEYKurztext	AS Beschreibung
	, CASE WHEN b.SHTOBBeruecksich0 = 0 
			THEN 'Ja' 
			ELSE 'Nein' 
	END		AS Informatorisch

													
FROM DATA.tSHTOB b 

-- Immobilie
LEFT JOIN DATA.tIM IM
ON IM.IMID = b.SHTOID AND IM.IMAktiv = 1

-- Bewertungsart
LEFT JOIN DATA.tKEY bwArt
ON b.KEYSK_Bewertungsa0  =  bwArt.KEYSK

WHERE b.SHTOBAktiv  =  1 AND 
)
, InfoJa AS
(
SELECT

*
, ROW_NUMBER() OVER(PARTITION BY IMSK ORDER BY BewertungPer DESC, EingabePer DESC) AS rn

 FROM CTE

 WHERE Informatorisch = 'JA' AND IMSK IS NOT NULL
 )

, InfoNein AS
(
SELECT

*
, ROW_NUMBER() OVER(PARTITION BY IMSK ORDER BY BewertungPer DESC, EingabePer DESC) AS rn

 FROM CTE

 WHERE Informatorisch = 'NEIN' AND IMSK IS NOT NULL
 )

,result AS 
(
select * from infoja

union

select * from infonein
)


SELECT * FROM result


order by 2,8 desc,9

Jetzt mein Problem.

Ich muss folgende Logik anwenden und o.g. Abfrageresultset filtern nach (in der Reihenfolge):

1. Gibt es in der Partition (IMSK) eine oder mehrere Zeile/n mit dem Begriff 20 (Bewertung Servicer), dann soll diese Zeile mit MIN(rn) und Informatorisch = 'NEIN' ausgegeben werden. Beispiel IMSK 1673
   
   
2. Trifft obige Bedingung nicht zu, soll aus der Partition (IMSK) die Zeile mit MIN(rn) und Informatorisch = 'NEIN' ausgegeben werden. Beispiel IMSK 169
   
   
   
3. Gibt es keine Ergebnisse mit Informatorisch = 'NEIN' soll die Zeile mit Informatorisch = 'JA' und MIN(rn) ausgegeben werden. Beispiel IMSK 8 bzw. 48
   
   

Ich hoffe ich konnte das Problem ordentlich erklären und jemand kann mir helfen.

Answer 1

Na, ned ganz. Denn MIN(rn) ist konstant gleich 1... Kannst du mal ein komplettes Beispiel posten?

Answer 2

Hallo Stefan,

mit MIN(rn) meine ich eher die kleinste rn die nach Anwendung der Logik aus obigem Ergebnis übrig bleibt (ROW_NUMBER() aus meinem Code).

Beispiel

1. Gibt es in der Partition (IMSK) eine oder mehrere Zeile/n mit dem Begriff 20 (Bewertung Servicer), dann soll diese Zeile mit MIN(rn) und Informatorisch = 'NEIN' ausgegeben werden.
   
   Hier sollte aus obigen Beispiel-Abfrageresultselt Zeilen 4, 12, 14 und 16 als Ergebnis ausgegeben werden.
   
2. Trifft obige Bedingung nicht zu, soll aus der Partition (IMSK) die Zeile mit MIN(rn) und Informatorisch = 'NEIN' ausgegeben werden.
   
   Hier sollte aus obigen Beispiel-Abfrageresultselt Zeilen 7 und 10 als Ergebnis ausgegeben werden.
   
3. Gibt es keine Ergebnisse mit Informatorisch = 'NEIN' soll die Zeile mit Informatorisch = 'JA' und MIN(rn) ausgegeben werden.
   
   Hier sollte aus obigen Beispiel-Abfrageresultselt Zeilen 24 und 25 als Ergebnis ausgegeben werden.

Als Ergebnis hätte ich also gerne die Zeilen 4,12,14,16,7,10,24,25

Ich hoffe es ist etwas klarer geworden.....Danke und ein schönes Wochenende.

Answer 3

Hallo Daniel,

könntest Du bitte auch etwas DDL posten (Tabellenstrukturen und Beispieldaten).
So fällt es schwer, die von Dir erstellen Beispiele und Anforderungen zu reproduzieren.

---

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 4

Hallo Uwe,

ich habe mal die drei Tabellen die angesprochen werden als CREATE TABLE beigefügt. Mit Beispieldaten habe ich ein Problem, weil ich nicht genau weiß, wie ich die beifügen soll. Insgesamt reden wir von über 100.000 Zeilen.

/****** Object:  Table [DATA].[tSHTOB]    Script Date: 04.02.2013 08:55:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [DATA].[tSHTOB](
	[SHTOBSK] [int] NOT NULL,
	[SHTOBID] [varchar](35) NULL,
	[SHTOB_D_SK] [int] NULL,
	[SHTOSK] [int] NULL,
	[PFOSK] [int] NULL,
	[WHGSK] [int] NULL,
	[SHTSK] [int] NULL,
	[IMSK] [int] NULL,
	[SHTOID] [varchar](35) NULL,
	[PFOID] [varchar](35) NULL,
	[SHTID] [varchar](35) NULL,
	[KEYSK_Bewertungsa0] [int] NULL,
	[SHTOBangelegt] [varchar](25) NULL,
	[SHTOBangelegtdur0] [varchar](35) NULL,
	[SHTOBBeruecksich0] [decimal](17, 0) NULL,
	[SHTOBBewertung] [decimal](19, 2) NULL,
	[SHTOBBewertungVom] [datetime] NULL,
	[SHTOBBG5Prozent] [decimal](19, 2) NULL,
	[SHTOBErsteller] [varchar](32) NULL,
	[SHTOBFungibel] [decimal](17, 0) NULL,
	[SHTOBgeaendert] [varchar](25) NULL,
	[SHTOBgeaendertdu0] [varchar](35) NULL,
	[SHTOBIAS39Ratier0] [decimal](17, 0) NULL,
	[SHTOBIASAktive] [decimal](17, 0) NULL,
	[SHTOBIstManWert] [decimal](17, 0) NULL,
	[SHTOBKalkErloese] [decimal](19, 2) NULL,
	[SHTOBKalkKosten] [decimal](19, 2) NULL,
	[SHTOBManRealwert] [decimal](19, 2) NULL,
	[SHTOBRelevantHGBH] [decimal](17, 0) NULL,
	[SHTOBRelevantHGBS] [decimal](17, 0) NULL,
	[SHTOBRelevantIAS0] [decimal](17, 0) NULL,
	[SHTOBRelevantKre0] [decimal](17, 0) NULL,
	[SHTOBRelevantSan] [decimal](17, 0) NULL,
	[SHTOBSolltermin] [datetime] NULL,
	[SHTOBStellungnah0] [varchar](8000) NULL,
	[SHTOBtAVKW] [decimal](19, 2) NULL,
	[SHTOBtBLW] [decimal](19, 2) NULL,
	[SHTOBtVKW] [decimal](19, 2) NULL,
	[SHTOBVom] [datetime] NULL,
	[SHTOBWaehrung] [varchar](25) NULL,
	[SHTOBGenIDC] [int] NOT NULL,
	[SHTOBDateC] [datetime] NOT NULL,
	[SHTOBGenIDM] [int] NOT NULL,
	[SHTOBDateM] [datetime] NOT NULL,
	[SHTOBAktiv] [bit] NOT NULL,
 CONSTRAINT [PK_SHTOB] PRIMARY KEY CLUSTERED 
(
	[SHTOBSK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [DATA].[tSHTOB] ADD  CONSTRAINT [DF_SHTOB_SHTOBGenIDC]  DEFAULT ((0)) FOR [SHTOBGenIDC]
GO

ALTER TABLE [DATA].[tSHTOB] ADD  CONSTRAINT [DF_SHTOB_SHTOBDateC]  DEFAULT (getdate()) FOR [SHTOBDateC]
GO

ALTER TABLE [DATA].[tSHTOB] ADD  CONSTRAINT [DF_SHTOB_SHTOBGenIDM]  DEFAULT ((0)) FOR [SHTOBGenIDM]
GO

ALTER TABLE [DATA].[tSHTOB] ADD  CONSTRAINT [DF_SHTOB_SHTOBDateM]  DEFAULT (getdate()) FOR [SHTOBDateM]
GO

ALTER TABLE [DATA].[tSHTOB] ADD  CONSTRAINT [DF_SHTOB_SHTOBAktiv]  DEFAULT ((1)) FOR [SHTOBAktiv]
GO




/****** Object:  Table [DATA].[tIM]    Script Date: 04.02.2013 08:59:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [DATA].[tIM](
	[IMSK] [int] NOT NULL,
	[IMID] [varchar](35) NULL,
	[PFOSK] [int] NULL,
	[WHGSK] [int] NULL,
	[PFOID] [varchar](35) NULL,
	[KEYSK_Bundesland] [int] NULL,
	[KEYSK_Land] [int] NULL,
	[KEYSK_Sicherheite0] [int] NULL,
	[KEYSK_Struktur] [int] NULL,
	[KEYSK_VDVerwertba0] [int] NULL,
	[KEYSK_VDVerwertun0] [int] NULL,
	[KEYSK_Vermietungs0] [int] NULL,
	[IMLeitungsVer0] [decimal](17, 0) NULL,
	[IMLetzterAusz0] [datetime] NULL,
	[IMMietePM] [decimal](19, 2) NULL,
	[IMModernJahr] [decimal](17, 0) NULL,
	[IMAdresszusatz] [varchar](30) NULL,
	[IMangelegt] [varchar](25) NULL,
	[IMangelegtdur0] [varchar](35) NULL,
	[IMBaujahr] [decimal](17, 0) NULL,
	[IMEndeVerwert0] [datetime] NULL,
	[IMErlaeutVKW] [varchar](400) NULL,
	[IMgeaendert] [varchar](25) NULL,
	[IMgeaendertdu0] [varchar](35) NULL,
	[IMGesamterloes] [decimal](19, 2) NULL,
	[IMGesamtQM] [decimal](17, 0) NULL,
	[IMHausnummer] [varchar](25) NULL,
	[IMIDExtern] [varchar](35) NULL,
	[IMObjektnumme0] [decimal](17, 0) NULL,
	[IMObjektnumme1] [varchar](35) NULL,
	[IMObjektnummer] [decimal](17, 0) NULL,
	[IMOrt] [varchar](32) NULL,
	[IMPLZ] [varchar](25) NULL,
	[IMSonstRisiko] [varchar](35) NULL,
	[IMStartVerwer0] [datetime] NULL,
	[IMStrasse] [varchar](35) NULL,
	[IMSturmHagelV0] [decimal](17, 0) NULL,
	[IMVerwertungs0] [varchar](25) NULL,
	[IMVerwertungs1] [decimal](19, 2) NULL,
	[IMVerwertungSB] [varchar](100) NULL,
	[IMWohneinheit0] [decimal](17, 0) NULL,
	[IMWaehrung] [varchar](25) NULL,
	[IMBemerkung] [varchar](8000) NULL,
	[IMGesamtkosten] [decimal](19, 2) NULL,
	[IMVollstreckgericht] [varchar](10) NULL,
	[IMGenIDC] [int] NOT NULL,
	[IMDateC] [datetime] NOT NULL,
	[IMGenIDM] [int] NOT NULL,
	[IMDateM] [datetime] NOT NULL,
	[IMAktiv] [bit] NOT NULL,
	[IMBasel2Faehig] [bit] NULL,
 CONSTRAINT [PK_tIM] PRIMARY KEY CLUSTERED 
(
	[IMSK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA],
 CONSTRAINT [UQ_tIM_IMID] UNIQUE NONCLUSTERED 
(
	[IMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [DATA].[tIM] ADD  CONSTRAINT [DF_tIM_IMGenIDC]  DEFAULT ((0)) FOR [IMGenIDC]
GO

ALTER TABLE [DATA].[tIM] ADD  CONSTRAINT [DF_tIM_IMDateC]  DEFAULT (getdate()) FOR [IMDateC]
GO

ALTER TABLE [DATA].[tIM] ADD  CONSTRAINT [DF_tIM_IMGenIDM]  DEFAULT ((0)) FOR [IMGenIDM]
GO

ALTER TABLE [DATA].[tIM] ADD  CONSTRAINT [DF_tIM_IMDateM]  DEFAULT (getdate()) FOR [IMDateM]
GO

ALTER TABLE [DATA].[tIM] ADD  CONSTRAINT [DF_tIM_IMAktiv]  DEFAULT ((1)) FOR [IMAktiv]
GO


/****** Object:  Table [DATA].[tKEY]    Script Date: 04.02.2013 09:11:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [DATA].[tKEY](
	[KEYSK] [int] IDENTITY(1,1) NOT NULL,
	[KEYMSK] [int] NOT NULL,
	[PFOSK] [int] NOT NULL,
	[KEYSK_B] [int] NOT NULL,
	[KEYQuellsystem] [varchar](10) NULL,
	[KEYBegriff] [varchar](10) NOT NULL,
	[KEYSchluesselgruppe] [varchar](40) NOT NULL,
	[KEYKurztext] [varchar](80) NULL,
	[KEYLangtext] [varchar](80) NULL,
	[KEYInaktiv] [bit] NULL,
	[KEYIstAdmin] [bit] NULL,
	[KEYParameter1] [varchar](60) NULL,
	[KEYParameter2] [varchar](60) NULL,
	[KEYParameter3] [varchar](60) NULL,
	[KEYParameter4] [varchar](60) NULL,
	[KEYParameter5] [varchar](60) NULL,
	[KEYParameter6] [varchar](60) NULL,
	[KEYParameter7] [varchar](60) NULL,
	[KEYParameter8] [varchar](60) NULL,
	[KEYParameter9] [varchar](60) NULL,
	[KEYParameter10] [varchar](60) NULL,
	[KEYParameter11] [varchar](60) NULL,
	[KEYParameter12] [varchar](60) NULL,
	[KEYParameter13] [varchar](60) NULL,
	[KEYParameter14] [varchar](60) NULL,
	[KEYParameter15] [varchar](60) NULL,
	[KEYParameter16] [varchar](60) NULL,
	[KEYParameter17] [varchar](60) NULL,
	[KEYParameter18] [varchar](60) NULL,
	[KEYParameter19] [varchar](60) NULL,
	[KEYParameter20] [varchar](60) NULL,
	[KEYParameter21] [varchar](60) NULL,
	[KEYParameter22] [varchar](60) NULL,
	[KEYParameter23] [varchar](60) NULL,
	[KEYParameter24] [varchar](60) NULL,
	[KEYParameter25] [varchar](60) NULL,
	[KEYParameter26] [varchar](60) NULL,
	[KEYParameter27] [varchar](60) NULL,
	[KEYParameter28] [varchar](60) NULL,
	[KEYParameter29] [varchar](60) NULL,
	[KEYParameter30] [varchar](60) NULL,
	[KEYUserParamet0] [varchar](40) NULL,
	[KEYUserParamet1] [varchar](40) NULL,
	[KEYUserParamet2] [varchar](40) NULL,
	[KEYUserParamet3] [varchar](40) NULL,
	[KEYUserParamet4] [varchar](40) NULL,
	[KEYUserParamet5] [varchar](40) NULL,
	[KEYUserParamet6] [varchar](40) NULL,
	[KEYUserParamet7] [varchar](40) NULL,
	[KEYUserParamet8] [varchar](40) NULL,
	[KEYUserParamet9] [varchar](40) NULL,
	[KEYUserParametA] [varchar](40) NULL,
	[KEYUserParametB] [varchar](40) NULL,
	[KEYUserParametC] [varchar](40) NULL,
	[KEYUserParametD] [varchar](40) NULL,
	[KEYUserParametE] [varchar](40) NULL,
	[KEYUserParametF] [varchar](40) NULL,
	[KEYUserParametG] [varchar](40) NULL,
	[KEYUserParametH] [varchar](40) NULL,
	[KEYUserParametI] [varchar](40) NULL,
	[KEYUserParametJ] [varchar](40) NULL,
	[KEYUserParametK] [varchar](40) NULL,
	[KEYUserParametL] [varchar](40) NULL,
	[KEYUserParametM] [varchar](40) NULL,
	[KEYUserParametN] [varchar](40) NULL,
	[KEYUserParametO] [varchar](40) NULL,
	[KEYUserParametP] [varchar](40) NULL,
	[KEYUserParametQ] [varchar](40) NULL,
	[KEYUserParametR] [varchar](40) NULL,
	[KEYUserParametS] [varchar](40) NULL,
	[KEYUserParametT] [varchar](40) NULL,
	[KEYangelegt] [varchar](25) NULL,
	[KEYangelegtdurch] [varchar](35) NULL,
	[KEYgeaendert] [varchar](25) NULL,
	[KEYgeaendertdurch] [varchar](35) NULL,
	[KEYGenIDC] [int] NOT NULL,
	[KEYDateC] [datetime] NOT NULL,
	[KEYGenIDM] [int] NOT NULL,
	[KEYDateM] [datetime] NOT NULL,
	[KEYAktiv] [bit] NOT NULL,
 CONSTRAINT [PK_tKEY1_KEYSK] PRIMARY KEY CLUSTERED 
(
	[KEYSK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [DATA].[tKEY] ADD  CONSTRAINT [DF_tKEY1_KEYGenIDC]  DEFAULT ((-1)) FOR [KEYGenIDC]
GO

ALTER TABLE [DATA].[tKEY] ADD  CONSTRAINT [DF_tKEY1_KEYDateC]  DEFAULT (getdate()) FOR [KEYDateC]
GO

ALTER TABLE [DATA].[tKEY] ADD  CONSTRAINT [DF_tKEY1_KEYGenIDM]  DEFAULT ((-1)) FOR [KEYGenIDM]
GO

ALTER TABLE [DATA].[tKEY] ADD  CONSTRAINT [DF_tKEY1_KEYDateM]  DEFAULT (getdate()) FOR [KEYDateM]
GO

ALTER TABLE [DATA].[tKEY] ADD  CONSTRAINT [DF_tKEY1_KEYAktiv]  DEFAULT ((1)) FOR [KEYAktiv]
GO