Query voor een zelf refererende tabel > Forum

Forum

Unanswered

Active Topics

Forums

Forums > Forums > Ontwikkelen

Subject: Query voor een zelf refererende tabel

Prev Next

You are not authorized to post a reply.

Author

Messages

Stephan Bussing User is Offline

Posts:67

14-04-2008 20:22:15	Alert
Hoi, Ik heb een leuke uitdaging, maar het gaat te ver om deze nu helemaal uit te schrijven, vandaag dat ik voorzicht begin. Ik heb een tabel die PK heeft maar ook een FK die weer verwijst naar de PK van de tabel. Op deze manier bouw ik een keten op. Er zitten nog een aantal andere velden in waaronder een datum veld en een aantal velden die verwijzen naar een item in andere tabellen. Het betreft hier een tabel waarin een rapportage op wordt geslagen over bepaalde items. Deze rapportages kunnen in keten-vorm worden opgeslagen. Voorbeeld: 1e rapportage op item x 2e rapportage op item y 3e rapportage op item x Deze 3 records zijn gelinkt aan elkaar met de constructie zoals hierboven beschreven. De 3 records gelden dus als een rapportage. Ik mag hier echter elk item maar een keer tellen. In dit geval zou de uitkomst dus zijn 1x X en 1x Y gerapporteerd. Ik moet nu over een periode (van- en tot-datum) zien te achterhalen hoe vaak er op een bepaald item is gerapporteerd in een periode. De periode beslaat twee weken. Daartoe bereken ik eerst hoeveel perioden in tussen de van en tot-datum zitten. Vervolgens loop ik met een cursor door de items en insert deze in een tijdelijke tabel. Dan bouw ik met een While-loop voor het aantal berkende periode, een selectstatement waarin ik met een distinct achterhaal hoeveel unieke records in een periode zitten. Een heel verhaal, maar je begrijpt dat dit nogal een performance hit kan zijn. Heeft iemand hier een oplossing voor? Of een andere aanpak. Dit is een deel van het probleem, maar zoals gezegd wordt het nogal veel om alles uit te schrijven. Voor het gemaak voeg ik hier mijn query ook even in: DECLARE @FactorKaartId INT DECLARE @StartDate DATETIMe DECLARE @EndDate DATETIME DECLARE @Interval INT DECLARE @PeriodeCount INT DECLARE @Restant INT DECLARE @DynSQL VARCHAR(8000) DECLARE @Index INT DECLARE @PeriodeStart DATETIME DECLARE @PeriodeEnd DATETIME SET @FactorKaartId = 43 SET @StartDate = DATEADD(day,-280,GETDATE()) SET @EndDate = GETDATE() SET @PeriodeCount = 0 SET @Restant = 0 SET @Interval = 2 SET @Index = 1 SET @PeriodeStart = @StartDate SET @PeriodeEnd = @EndDate SELECT @PeriodeCount = dbo.CountPeriode(@StartDate,@EndDate,@Interval) IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME = '#TellingById') DROP TABLE #TellingById CREATE TABLE #TellingById ( [Datum] [DATETIME] NOT NULL , [DetailId] [INT] NOT NULL, [GedragId] [INT] NULL, [MethodiekId] [INT] NULL, [BaseId] [INT] NULL) DECLARE Rapportage_Cursor CURSOR FOR SELECT A.FLD_RapportageDatum, A.FLD_FactorDetail_id, A.FLD_FactorKaartGedrag_id, A.FLD_FactorKaartGedragMethodiek_Id, KetenStart = CASE WHEN A.FLD_FactorRapportageClient_Id IS NULL THEN 1 ELSE 0 END, BaseId = CASE WHEN A.FLD_FactorRapportageClient_Id IS NULL THEN FLD_FactorRapportageClientId ELSE 0 END FROM TBL_FactorRapportageClient A --JOIN TBL_FactorRapportageClient B ON A.FLD_FactorRapportageClientId = B.FLD_FactorRapportageClient_Id WHERE (A.FLD_FactorKaart_Id = 43) AND A.FLD_RapportageDatum BETWEEN dbo.GetDateOnly(@StartDate) AND dbo.GetDateOnly(@EndDate) AND (A.FLD_FactorKaartGedrag_Id is not null AND A.FLD_FactorKaartGedragMethodiek_Id is not null) ORDER BY A.FLD_FactorRapportageClientId, A.FLD_FactorRapportageClient_Id, A.FLD_RapportageDatum, A.FLD_Gebruiker_Id, A.FLD_FactorDetail_id, A.FLD_FactorKaartGedrag_id, A.FLD_FactorKaartGedragMethodiek_Id DECLARE @Datum DATETIME DECLARE @DetailId INT DECLARE @GedragId INT DECLARE @MethodiekId INT DECLARE @KetenStart BIT DECLARE @BaseId INT DECLARE @Id INT OPEN Rapportage_Cursor FETCH NEXT FROM Rapportage_Cursor INTO @Datum,@DetailId,@GedragId,@MethodiekId,@KetenStart,@BaseId WHILE @@FETCH_STATUS = 0 BEGIN IF @KetenStart = 1 BEGIN SET @Id = @BaseId END INSERT INTO #TellingById ([Datum], [DetailId], [GedragId], [MethodiekId], [BaseId]) VALUES ( dbo.GetDateOnly(@Datum), @DetailId, @GedragId, @MethodiekId, @Id) FETCH NEXT FROM Rapportage_Cursor INTO @Datum,@DetailId,@GedragId,@MethodiekId,@KetenStart,@BaseId END CLOSE Rapportage_Cursor DEALLOCATE Rapportage_Cursor IF @PeriodeCount >= 1 BEGIN WHILE @Index <= @PeriodeCount BEGIN SET @PeriodeEnd = DATEADD(wk, @Index * @Interval, @StartDate) IF (@PeriodeEnd > @EndDate) BEGIN SET @PeriodeEnd = @EndDate END SELECT DISTINCT * FROM #TellingById WHERE Datum BETWEEN dbo.GetDateOnly(@PeriodeStart) AND dbo.GetDateOnly(@PeriodeEnd) ORDER BY DetailId,GedragId,MethodiekId SET @Index = @Index + 1 SET @PeriodeStart = @PeriodeEnd END END

Ruud Boots User is Offline

Posts:62

15-04-2008 22:21:18	Alert
Stephan, Ik denk dat je dit het beste met een CTE (common table expression) kan oplossen, ik heb je voorbeeld als volgt vertaald en er een CTE op geschreven: create table TBL_FactorRapportageClient (pk int identity(1,1),item varchar (10),FLD_RapportageDatum datetime, FK int) --voorbeeld data insert into TBL_FactorRapportageClient values ('x', getdate()-4,2) insert into TBL_FactorRapportageClient values ('x', getdate()-2,1) insert into TBL_FactorRapportageClient values ('y', getdate()-4,4) insert into TBL_FactorRapportageClient values ('z', getdate()-4,2) insert into TBL_FactorRapportageClient values ('z', getdate()-3,4) insert into TBL_FactorRapportageClient values ('x', getdate()-16,4) insert into TBL_FactorRapportageClient values ('x', getdate()-25,4) insert into TBL_FactorRapportageClient values ('a', getdate()-16,4) insert into TBL_FactorRapportageClient values ('a', getdate()-9,4) --query met CTE expressie ;with uniek_aantal (periode,item,aantal) as (select datepart(ww,FLD_RapportageDatum)/2, item, count (item)as aantal from TBL_FactorRapportageClient a where FLD_RapportageDatum >= dateadd(day,-280,getdate()) group by datepart(ww,FLD_RapportageDatum)/2,item) select sum(periode)2 as [weeknr vanaf],(sum(periode)2)+1 as [weeknr t/m], item, count(aantal) aantal from uniek_aantal group by item,periode order by 1 /* resultaat v/d query weeknr vanaf weeknr t/m item aantal 12 13 x 1 14 15 a 1 14 15 x 1 14 15 y 1 14 15 z 1 16 17 x 1 */

Stephan Bussing User is Offline

Posts:67

16-04-2008 09:03:02	Alert
Hallo Ruud, Bedankt dat je de moeite neemt het hele verhaal te lezen. Ik zie dat het nogal door elkaar staat. Dat komt omdat in FF de editor niet werkt en ik dus naar HTML overga, waardoor de tekst er niet zo geordend uitziet. Helaas was ik vergete dat het hier om SQL 2000 ging. Voorzover ik nu op i-net heb gekeken, kent MS SQL 2000 geen CTE. Maar je hebt mij weer op iets wijzer gemaakt mbt 2005. Daarvoor ook bedankt. Ondertussen heb ik een oplossing. Deze ga ik vandaag testen. Maar ik had er wel twee resultsets voor nodig die ik in code combineer. Op zich heb ik daar geen probleem mee, maar het mooiste is dat als alles in een query kan. Bedankt dus.

Ruud Boots User is Offline

Posts:62

16-04-2008 17:23:31	Alert
Klopt, SQL 2000 ondersteunt geen CTE. Je kunt de query evt. omzetten naar een "inline view" met de volgende syntax: select sum(periode)2 as [weeknr vanaf],(sum(periode)2)+1 as [weeknr t/m], item, count(aantal) aantal from (select datepart(ww,FLD_RapportageDatum)/2 as periode, item, count (item)as aantal from TBL_FactorRapportageClient where FLD_RapportageDatum >= dateadd(day,-280,getdate()) group by datepart(ww,FLD_RapportageDatum)/2,item) uniek_aantal group by item, periode order by 1

You are not authorized to post a reply.

Forums > Forums > Ontwikkelen > Query voor een zelf refererende tabel

ActiveForums 3.6