[MSSQL] Dynamische WHERE clausule o.b.v. query result

[Aesculapius]

Na mijn onverhoopt veel te ingewikkeld ingeschatte sql-probleem ([MSSQL] data uit volgende row gebruiken in select) met jullie hulp opgelost te hebben, zit ik met een volgend struikelpunt.

Er is op het net (en op dit forum) redelijk wat info te vinden over dynamische sql-queries; voornamelijk door gebruik te maken van exec of sp_executesql. Hoewel het daar uiteindelijk wel mee zou moeten lukken, vraag ik me af of er niet een elegantere oplossing is - ik zou dan namelijk de hele stored procedure in een string moeten mieteren en die executen...dat lijkt me niet de voorkeur hebben (het ding is nogal groot).

Situatie is de volgende;

grote procedure haalt uit een stuk of 10 tabellen (aan elkaar gejoined) productiegegevens op over een bepaalde periode (parameter). De procedure moet nu voor alle personen kijken wat diens productie is, en bijv. alleen voor de periodes waarin zij een bepaalde functie hadden (zeg: Staflid zijn), hun productiegegevens tellen.

Ik krijg dan dus een totaaloverzicht van getallen waarin alleen die getallen van mensen worden meegenomen over de periodes (!! kunnen er dus meer dan een zijn) waarin ze de gevraagde functie hebbben.

De stored procedure is overigens al af en draait naar behoren. De uitbreiding nu is om rekening te houden met de daadwerkelijke functieplaats van personen.

Die tabel - zeg: tbl_functiedetails - kan er als volgt uit zien;

Pers.Code	datum_vanaf	datum_tot	functie
PIET	2010-03-15	NULL	Staflid
JAN	2010-01-01	2010-06-20	Staflid
JAN	2010-06-20	2010-10-01	Inactief
JAN	2010-10-01	NULL	Staflid

Dit wordt dus door een subquery opgervaagd (zie vorig topic).

Mijn procedure moet nu echter rekening gaan houden met het resultaat van deze subquery. Want als ik een overzicht opvraag voor heel 2010, heb ik bij PIET geen probleem, maar voor JAN wil ik dan alleen getallen tellen tussen 01 januari 2010 en 20 juni 2010, en tussen 1 oktober 2010 en 1 jan 2011 (of: nu, for that matter).

Ik zou de hele stored procedure nu in een ntext kunnen declareren en de where clause dan dynamisch o.b.v. een loop over de results van die subquery samenstellen, en die vervolgens laten uitvoeren door sp_executesql. Maar dat maakt het verre van overzichtelijk en is imho ook niet "mooi".

Zoiets;

SQL:

for fpDate in
( SELECT ...subquery.... ) loop
  @totalQuery += "OR (datetime >= fpDate.datum_vanaf AND datetime < fpDate.datum_tot) ";
end loop;

Ik vraag me dus af of er geen IF, CASE of andere constructies denkbaar zijn die hetzelfde resultaat bereiken, maar wel een leesbare procedure over houden.

Edit:
Liefst zou ik het natuurlijk zo doen:

SQL:

{voorgaande procedure code}
WHERE 0
(
  for fpDate in 
  ( SELECT ....subquery...) loop
    OR (datetime >= fpDate.datum_vanaf AND datetime < fpDate.datum_tot)
  end loop;
)

Maar dat wil niet

[ Voor 4% gewijzigd door Aesculapius op 22-11-2010 12:58 ]

[N0stradamus]

Geen dynamisch sql nodig naar mijn idee, net zo min als een cursor met een loop...
Door parameters te gebruiken voor de periode kun je ook voor een maand, kwartaal of whatever de gewenste gegevens opvragen.
Join tussen de proddata en functie tabel is op perscode.
Produktie datum moet door de WHERE clause binnen de parameters liggen, maar ook binnen een functieperiode. De meerdere functieperiodes van Jan moeten dan geen probleem zijn.
Door dan ook nog gebruik te maken van de IsNull(datum_tot, @EindPeriode) vervang je eventueel lopende functies met de eindperiode van het rapport.

Tabel ProdData maar even verzonnen...

SQL:

DECLARE @StartPeriode AS DateTime, @EindPeriode AS DateTime 
SELECT @StartPeriode = '2010-1-1', @EindPeriode= '2010-12-31' 

SELECT P.PersCode, P.ProdData 
    FROM tbl_ProdData P LEFT JOIN tbl_functieDetails F ON P.PersCode=F.PersCode
    WHERE (P.ProdDatum BETWEEN @StartPeriode AND @EindPeriode)
        AND (P.ProdDatum BETWEEN F.datum_vanaf AND ISNULL(F.datum_tot , @EindPeriode))

[RobIII]

N0stradamus schreef op dinsdag 23 november 2010 @ 15:24:

offtopic:
Pas je even op dat je hiermee data van 31-12-2010 13:37:00 niet meeneemt? Wat jij wil is, vermoedelijk althans, het volgende:
SELECT @StartPeriode = '2010-1-1', @EindPeriode= '2011-1-1'

[Aesculapius]

Ga em eens proberen, bedankt voor de tip...

[N0stradamus]

RobIII schreef op dinsdag 23 november 2010 @ 15:38:
[...]

offtopic:
Pas je even op dat je hiermee data van 31-12-2010 13:37:00 niet meeneemt? Wat jij wil is, vermoedelijk althans, het volgende:
SELECT @StartPeriode = '2010-1-1', @EindPeriode= '2011-1-1'

Je hebt helemaal gelijk, 't heet natuurlijk niet voor niets BETWEEN...
Leuk testtijdstip heb je weer genomen