SQLBlog Nederland

Ik liep onlangs tegen een vraagstuk aan die ik wel interessant vond om op mijn blog te plaatsen. Het behandelt een veelvoorkomende situatie waarbij je een dataset meerdere keren gebruikt, ook wel een recursieve query genoemd. Men vroeg het aantal gewerkte weken per periode per uitzendkracht.

Per periode kan iemand meerdere declaraties hebben waarbij het getal “weken gewerkt” wordt bijgehouden. Om te bepalen hoeveel weken iemand in een periode heeft gewerkt pakken we de laatste “weken gewerkt” in een periode en trekken daar de laatste “weken gewerkt” uit de vorige periode vanaf, klinkt simpel toch?

Onderstaande afbeelding toont het resultaat wat we als basis gaan gebruiken om “weken gewerkt” per periode vast te kunnen stellen:

We hebben onze data gegroepeerd op jaar, Periode444Nummer, krachtcode en pakken de MAX(WekenGewerkt) per periode. Als we nu willen weten hoeveel weken iemand heeft gewerkt in periode 6-2008 moeten we 76-72 uitvoeren. Iemand heeft tenslotte aan het einde van periode 6-2008 76 weken gewerkt en aan het einde van periode 5-2008 72 weken gewerkt. Dit verschil is wat hij in periode 6 heeft gewerkt.

Om dit alles mogelijk te maken genereren we bovenstaande standaard dataset die we vervolgens kunnen hergebruiken, ook wel Common Table Expression genoemd (CTE). Ook voegen we een rijnummer toe per krachtcode en sorteren dit op krachtcode, jaar en periodenummer. Verder hebben we ook het vorige jaar nodig om te kunnen bepalen hoeveel weken iemand gewerkt heeft in de eerste periode van het huidige jaar! Dit zorgt voor een resultaat dat er ongeveer zo uitziet:

De sortering zorgt ervoor dat alles netjes in volgorde staat zodat we straks eenvoudig de weken van elkaar kunnen aftrekken!

Vervolgens maken we onderstaande query en voeren dit op de eerdere gegenereerde dataset uit:

De kracht zit hem in het stukje met de cirkel eromheen. Hierbij joinen we de tabel op zichzelf zodat we het resultaat van de vorige week gewerkt kunnen vinden. Dit trekken we van de huidige week af en vinden zo het resultaat.

De complete query ziet er ongeveer zo uit:

Uiteraard kun je dit voorbeeld in veel meer situaties gebruiken, bijvoorbeeld voor het berekenen van cumulatieven.

In Datawarehouseland gebruiken we in onze Datum-Dimensie integers als primaire sleutels voor de datum. Zo slaan we de datum “2008-12-01 00:00:00.000″ op als  “20081201″. Hierdoor kun je vanuit je feitentabel een snelle join leggen naar je datumdimensie, joins op integers gaan nou eenmaal sneller dan joins op datetime velden. 

Datums liggen in bronsystemen vaak opgeslagen in het DateTime-formaat. We zullen deze DateTime dus moeten converteren naar een integer. Dit kan met het volgende T-SQL Statement:

SELECT YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE()) AS DatetimeToInt

GETDATE() vervang je door het datumveld dat je wilt omzetten naar een integer.

Een andere methode is de volgende, deze is in de praktijk iets langzamer:

SELECT convert(varchar,dateadd(yy,-2, getdate()),112)

Je kunt je ook voorstellen dat je dit veld weer wilt gebruiken als een normaal datumveld, bijvoorbeeld in een rapportage in Reporting Services. Dit kun je eenvoudig realiseren door onderstaande functie aan te maken:

CREATE FUNCTION [dbo].[FromDateIDtoDateValue](@Date varchar(8))
RETURNS datetime
AS
 
DECLARE @FunctionResult datetime
 
IF isdate(@Date) = 1 SET @FunctionResult = cast(@Date as datetime)
Else SET @FunctionResult = null
 
RETURN(@FunctionResult)
END

Vervolgens kun je deze functie aanroepen:

SELECT
dbo.FromDateIDtoDateValue(20081201)

Je zoekt een bepaalde kolomnaam in een van je tabellen in je SQL Server database maar kan hem even niet vinden. Dit kan een tijdrovende klus zijn als je onderstaande truuk niet kent.  In SQL Server 2005 ontbreekt een zoekfunctionaliteit om snel een kolom te kunnen vinden in databases. Dit kan nog weleens handig zijn als je een bepaalde kolom zoekt maar niet weet in welke tabel deze zich bevindt. De oplossing hiervoor is een vrij eenvoudige Query.

SELECT O.NAME Tabelnaam,
C.NAME Kolomnaam
FROM sys.columns C
INNER JOIN sys.objects O ON C.object_id = O.object_id
WHERE C.NAME LIKE '%DebiteurNaam%'
ORDER BY O.NAME,
C.NAME

Resultaat

Het is natuurlijk niet erg handig om iedere keer dat je een kolom zoekt deze hele query in te typen, daarom is er ook de volgende Stored-Procedure die de taak wel heel eenvoudig maakt:

CREATE PROCEDURE [dbo].[pcdGetColumns]
@kolomnaam nvarchar(256)
-- Zoek een kolomnaam in een database
-- EXECUTE pcdGetColumns '%naam%'
AS
BEGIN
 
SELECT O.NAME Tabelnaam,
C.NAME Kolomnaam
FROM sys.columns C
INNER JOIN sys.objects O ON C.object_id = O.object_id
WHERE C.NAME LIKE @kolomnaam
ORDER BY O.NAME, C.NAME
 
END