Mapping mit Regeltabelle und Prioritätslevel

Mittlerweile schon mehrmals hatte ich die Aufgabe Geschäfte anhand einer sehr variablen Regeltabelle inkl. Prioritätslevel zu mappen.
Da sich diese Regeltabelle regelmäßig ändern kann und nicht jedesmal Adaptierungen im Code notwendig sind, baue ich mir das Mapping-SQL dynamisch anhand der Tabelle auf.
Im unten angeführten Beispiel wird standardmäßig ein Vergleich mit = durchgeführt, jedoch bietet es auch die Funktionalität mehrere Werte mit "incl." bzw "excl." ein-/auszuschließen.
Die Sortierung dieser Regeln wird anhand der Spalte "Priority" vorgenommen. Beim ersten Treffer wird der Wert aus "Category" dem Geschäft zugewiesen. Weiters gibt es noch ein Auffangelement, bei dem alle Bedingungen auf "NULL" gesetzt wurden. Dieses wird für den ELSE-Zweig des CASE-WHEN-Konstrukts genutzt.

    CREATE TABLE [dbo].[PrioritySetup](
 [Priority]   [int]     NULL,
 [Product]   [varchar](255) NULL,
 [AccountType]  [varchar](255) NULL,
 [AssetLiability] [varchar](255) NULL,
 [BusinessUnit]  [varchar](255) NULL,
 [Category]   [varchar](255) NOT NULL
) ON [PRIMARY]


INSERT INTO dbo.PrioritySetup VALUES
(1,'Giro','ABC','A','Unit1','1000'),
(2,'Giro','ABC','P','Unit1','1001'),
(3,'Giro','ABC','A','excl. |Unit2|Unit3|Unit4|','1003'),
(4,'Giro',NULL,'P',NULL,'1100'),
(5,'Giro',NULL,NULL,NULL,'1200'),
(6,'Saving',NULL,'A',NULL,'2000'),
(7,'Saving',NULL,'P','incl. |Unit2|Unit3|','2100'),
(8,'Saving',NULL,'P',NULL,'2200'),
(8,'Credit',NULL,'A',NULL,'3000'),
(9,'Credit',NULL,'P',NULL,'3100'),
(10,NULL,NULL,'A',NULL,'5000'),
(11,NULL,NULL,'P',NULL,'5000'),
(12,NULL,NULL,NULL,NULL,'9000')

DECLARE @Priority      int
    ,@Product      varchar(255) 
    ,@AccountType     varchar(255) 
    ,@AssetLiability     varchar(255)
    ,@BusinessUnit     varchar(255) 
    ,@Category      varchar(255) 
DECLARE @query       varchar(max)
DECLARE @helper       int 
DECLARE @EmptyPrioTable     int = 0



DECLARE C_Prio CURSOR FOR
    SELECT   [Priority]
      ,RTRIM([Product])
      ,RTRIM([AccountType])
      ,RTRIM([AssetLiability])
      ,RTRIM([BusinessUnit])
      ,[Category]
     FROM [dbo].[PrioritySetup]

 -- Open - Fetch - While - Close - Deallocate
 OPEN C_Prio
 FETCH NEXT FROM C_Prio INTO  @Priority      
        ,@Product     
        ,@AccountType     
        ,@AssetLiability  
        ,@BusinessUnit    
        ,@Category    
 
        
 SET @query = 'ALTER PROCEDURE [dbo].[PriorityMapper] AS BEGIN ' + CHAR(13)+CHAR(10) 
 IF @@FETCH_STATUS = 0
 BEGIN
  SET @query = @query + 'INSERT INTO dbo.MappedResult (PositionKey,Planungskategorie) ' + CHAR(13)+CHAR(10) 
  SET @query = @query + ' SELECT A.PositionKey ' + CHAR(13)+CHAR(10) 
  SET @query = @query + '       ,CASE ' + CHAR(13)+CHAR(10)          
 END
 ELSE
 BEGIN
  -- Falls Tabelle Planungskategorie_Prio leer ist
  SET @query = @query + 'PRINT  ''Planungskategorietabelle leer!'' ' + CHAR(13)+CHAR(10)  
  SET @EmptyPrioTable = 1
 END
                
 WHILE @@FETCH_STATUS = 0
 BEGIN
     SET @helper = 0
  SET @query = @query + ' WHEN '
     IF (@Product IS NOT NULL) 
     BEGIN
   IF @helper = 1 
    SET @query = @query + ' AND '
      IF CHARINDEX('excl.', @Product) > 0 OR CHARINDEX('incl.', @Product) > 0
   BEGIN
    IF CHARINDEX('excl.', @Product) > 0
    BEGIN
     SET @Product = REPLACE(REPLACE(@Product,'excl. ',''),' ','')
     SET @Product = SUBSTRING(@Product, 2, (LEN(@Product) - 2)) 
     SET @Product = '''' + REPLACE(@Product,'|',''',''') + ''''
     SET @query = @query + 'A.Product NOT IN ( ' + @Product + ')'
    END
    IF CHARINDEX('incl.', @Product) > 0
    BEGIN
     SET @Product = REPLACE(REPLACE(@Product,'incl. ',''),' ','')
     SET @Product = SUBSTRING(@Product, 2, (LEN(@Product) - 2)) 
     SET @Product = '''' + REPLACE(@Product,'|',''',''') + ''''
     SET @query = @query + 'A.Product IN ( ' + @Product  + ')'
    END
   END
   ELSE
   BEGIN
    SET @query = @query + 'A.Product = ''' + @Product + ''' '
   END
         SET @helper = 1
        END         
     IF (@AccountType IS NOT NULL) 
     BEGIN
   IF @helper = 1 
    SET @query = @query + ' AND '
      IF CHARINDEX('excl.', @AccountType) > 0 OR CHARINDEX('incl.', @AccountType) > 0
   BEGIN
    IF CHARINDEX('excl.', @AccountType) > 0
    BEGIN
     SET @AccountType = REPLACE(REPLACE(@AccountType,'excl. ',''),' ','')
     SET @AccountType = SUBSTRING(@AccountType, 2, (LEN(@AccountType) - 2)) 
     SET @AccountType = '''' + REPLACE(@AccountType,'|',''',''') + ''''
     SET @query = @query + 'A.AccountType NOT IN ( ' + @AccountType   + ')'
    END
    IF CHARINDEX('incl.', @AccountType) > 0
    BEGIN
     SET @AccountType = REPLACE(REPLACE(@AccountType,'incl. ',''),' ','')
     SET @AccountType = SUBSTRING(@AccountType, 2, (LEN(@AccountType) - 2)) 
     SET @AccountType = '''' + REPLACE(@AccountType,'|',''',''') + ''''
     SET @query = @query + 'A.AccountType IN ( ' + @AccountType  + ')'
    END
   END
   ELSE
   BEGIN
    SET @query = @query + 'A.AccountType = ''' + @AccountType + ''' '
   END
         SET @helper = 1
        END   
     IF (@AssetLiability IS NOT NULL) 
     BEGIN
   IF @helper = 1 
    SET @query = @query + ' AND '
      IF CHARINDEX('excl.', @AssetLiability) > 0 OR CHARINDEX('incl.', @AssetLiability) > 0
   BEGIN
    IF CHARINDEX('excl.', @AssetLiability) > 0
    BEGIN
     SET @AssetLiability = REPLACE(REPLACE(@AssetLiability,'excl. ',''),' ','')
     SET @AssetLiability = SUBSTRING(@AssetLiability, 2, (LEN(@AssetLiability) - 2)) 
     SET @AssetLiability = '''' + REPLACE(@AssetLiability,'|',''',''') + ''''
     SET @query = @query + 'A.AssetLiability NOT IN ( ' + @AssetLiability   + ')'
    END
    IF CHARINDEX('incl.', @AssetLiability) > 0
    BEGIN
     SET @AssetLiability = REPLACE(REPLACE(@AssetLiability,'incl. ',''),' ','')
     SET @AssetLiability = SUBSTRING(@AssetLiability, 2, (LEN(@AssetLiability) - 2)) 
     SET @AssetLiability = '''' + REPLACE(@AssetLiability,'|',''',''') + ''''
     SET @query = @query + 'A.AssetLiability IN ( ' + @AssetLiability  + ')'
    END
   END
   ELSE
   BEGIN
    SET @query = @query + 'A.AssetLiability = ''' +@AssetLiability + ''' '
   END
         SET @helper = 1
        END            
     IF (@BusinessUnit IS NOT NULL) 
     BEGIN
   IF @helper = 1 
    SET @query = @query + ' AND '
      IF CHARINDEX('excl.', @BusinessUnit) > 0 OR CHARINDEX('incl.', @BusinessUnit) > 0
   BEGIN
    IF CHARINDEX('excl.', @BusinessUnit) > 0
    BEGIN
     SET @BusinessUnit = REPLACE(REPLACE(@BusinessUnit,'excl. ',''),' ','')
     SET @BusinessUnit = SUBSTRING(@BusinessUnit, 2, (LEN(@BusinessUnit) - 2)) 
     SET @BusinessUnit = '''' + REPLACE(@BusinessUnit,'|',''',''') + ''''
     SET @query = @query + 'A.BusinessUnit NOT IN ( ' + @BusinessUnit  + ')'
    END
    IF CHARINDEX('incl.', @BusinessUnit) > 0
    BEGIN
     SET @BusinessUnit = REPLACE(REPLACE(@BusinessUnit,'incl. ',''),' ','')
     SET @BusinessUnit = SUBSTRING(@BusinessUnit, 2, (LEN(@BusinessUnit) - 2)) 
     SET @BusinessUnit = '''' + REPLACE(@BusinessUnit,'|',''',''') + ''''
     SET @query = @query + 'A.BusinessUnit IN ( ' + @BusinessUnit  + ')'
    END
   END
   ELSE
   BEGIN
    SET @query = @query + 'A.BusinessUnit = ''' + @BusinessUnit  + ''' '
   END
         SET @helper = 1
        END   
 
  SET @query = @query + ' THEN ''' + @Category + '''' + CHAR(13)+CHAR(10)
  
  FETCH NEXT FROM C_Prio INTO  @Priority      
         ,@Product     
         ,@AccountType     
         ,@AssetLiability  
         ,@BusinessUnit    
         ,@Category  
 END;
 CLOSE C_Prio
 DEALLOCATE C_Prio

 IF @EmptyPrioTable = 0
 BEGIN
  SET @query = REPLACE(@query,'WHEN  THEN', 'ELSE')
  SET @query = @query + '             END AS MappedCategory'+ CHAR(13)+CHAR(10) 

  SET @query = @query + '           FROM (  ' + CHAR(13)+CHAR(10)  
  SET @query = @query + '                 SELECT PositionKey, AssetLiability, Product, AccountType, BusinessUnit FROM EXP.GiroAccounts'  + CHAR(13)+CHAR(10)
  SET @query = @query + '           UNION SELECT PositionKey, AssetLiability, Product, AccountType, BusinessUnit FROM EXP.SavingAccounts'  + CHAR(13)+CHAR(10)
  SET @query = @query + '           UNION SELECT PositionKey, AssetLiability, Product, AccountType, BusinessUnit FROM EXP.LoanAccounts'  + CHAR(13)+CHAR(10)
  SET @query = @query + '           UNION SELECT PositionKey, AssetLiability, Product, AccountType, BusinessUnit FROM EXP.TreasuryAccounts ' + CHAR(13)+CHAR(10)
  SET @query = @query + '           UNION SELECT PositionKey, AssetLiability, Product, AccountType, BusinessUnit FROM EXP.OtherAccounts'  + CHAR(13)+CHAR(10)
  SET @query = @query + '                ) A' + CHAR(13)+CHAR(10) 
 END

 SET @query = @query + CHAR(13)+CHAR(10)  + ' END'
 
 -- Absetzen des dynamisch generierten UPDATE-SQLs
 PRINT @query
 EXEC (@query) 

Sätze verdoppeln bzw. vervielfältigen mit Kriterien

Habe wieder mal eine interessante Anfrage bekommen, die recht einfach umsetzbar war:
Es müssen einige Sätze einer Tabelle im Zuge eines SELECTs verdoppelt, einige jedoch verdreifacht werden. Abhängig von dem Wert einer Spalte in der Basistabelle.

    SELECT A.*
      FROM Basistabelle A
 LEFT JOIN (values (1), (2), (3)) X(valueID)
        ON A.CODE = 3
 LEFT JOIN (values (1), (2), (3)) Y(valueID)
        ON A.CODE != 3
       AND Y.valueID <= 2

Der erste LEFT JOIN ist für die SatzverDREIfachung zuständig. Nur Sätze mit dem Kriterium "A.CODE = 3" werden verdreifacht.
Alle anderen Sätze - also "A.CODE != 3" werden nur verDOPPELT - ("Y.valueID <= 2").
Natürlich könnte man beim zweiten Join auch einfach weniger Werte in die Tabelle Y schreiben. Diese Lösung dient nur zur Veranschaulichung.

Liste von allen Datumswerten zwischen zwei Daten generieren

Manchmal benötigt man für Abfragen eine Liste von allen Datumswerten in einem definierten Bereich.
Hat man keine spezielle Kalendertabelle zur Verfügung, kann man sich eine solche Liste schnell und performant mit Hilfe einer systemtabelle generieren. 
Mein Ansatz für diese Problemstellung ist folgender:

DECLARE @StartDate date = '2016-12-31'
DECLARE @EndDate date = GETDATE()

SELECT DATEADD(DAY,number+1,@StartDate) [Date]
  FROM master..spt_values
 WHERE type = 'P'
   AND DATEADD(DAY,number+1,@StartDate) < @EndDate  

Das Statement generiert eine Datumsliste bis zum aktuellen Datum.
Falls wer eine einfachere oder performantere Lösung hat, einfach ein Kommentar hinterlassen. 

Zufallszahlen

Nachdem ich schon lange nichts mehr gepostet habe eine kleine Spielerei, die durchaus nützlich sein kann, für zwischendurch. Manchmal möchte man sich selbst eine Reihe von Testdaten rein zufällig generieren. Eine Zufallszahl selbst, kann einfach mit der Funktion RAND() generiert werden. Sofern nur Ganzzahlen im INT Wertebereich gewünscht sind, umgrenzt man die Funktion einfach mit der ROUND-Funktion.
Wer noch einen Schritt weiter gehen will und nur Werte in einem Bestimmten Bereich haben will, der kann dies mit unten angeführtem SQL einfach realisieren. 

---- Generiert Zufallszahl in einem Wertebereich
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 20   -- Untergrenze
SET @Upper = 80   -- Obergrenze
SELECT ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) -- Ganzzahl
SELECT (@Upper - @Lower -1) * RAND() + @Lower -- Float

CSV File Merger - schneller Weg

Nachdem ich vor kurzem mit meinem CSV File Merger Skript Laufzeitprobleme hatte (beide Files im GB Bereich), musste ich mir eine andere bzw schnellere Lösung überlegen.

Diese habe ich nun mit dem System.IO.StreamReader/Writer umgesetzt. Läuft für zwei Stück CSV Files mit je 500 MB nur wenige Sekunden lang. Somit um ein vielfaches schneller als meine alte Lösung und auch ressourcensparender. Die alte Version ist bei zu großen Files irgendwann wegen zuwenig Speicher abgestürzt. Hoffentlich läuft die neue Version besser.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-09-23
   Purpose       : CSV mergen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript kopiert 1-n CSV-Dateien zusammen, nimmt den Header-Satz jedoch nur einmal mit
        
.EXAMPLE
.\CSVMerger Inpfile1,Inpile2,Inpfile3 Outputfile

#>
         
param(            
    [string[]]$CSVFiles,            
    [string]$OutputFile = "c:\merged.csv"            
)            
$i = 0
$outs = New-Object System.IO.StreamWriter $OutputFile
            
foreach($CSV in $CSVFiles) {            
    $ins = New-Object System.IO.StreamReader $CSV

    while( !$ins.EndOfStream ) {
        $line = $ins.ReadLine();
        if( $i -ne 0 ) {
            $outs.WriteLine($line);
        }
        $i = $i+1;
    }
    $ins.Close();
            
}            

$outs.Close();     

CSV-File mergen

Ja, eigentlich heißt der Blog "sqlwithpleasure", aber manchmal muss man auch über den Tellerrand blicken. Deshalb - und weil ich in letzter Zeit mehr mit Control-M etc verbringe - heute wieder ein kleiner Post bzgl PowerShell.

Wir hatten ein recht simples Problem, dass wir tourlich verschiedene CSV-Files angeliefert bekommen, die jedoch dann auf eines zusammengeführt werden sollen. Natürlich wieder mal eine kleine Powershell-Lösung. Vielleicht benötigt ja jemand mal was ähnliches.

Syntax:

<#
.VERSION
   Produced By   : Stefan Perner
   URL           : www.sqlwithpleasure.blogspot.co.at
   Author        : Stefan Perner
   Date          : 2015-09-23
   Purpose       : CSV mergen
   Modifications : no changes, as perfect           

.DESCRIPTION
Das Skript kopiert 1-n CSV-Dateien zusammen, nimmt den Header-Satz jedoch nur einmal mit
        
.EXAMPLE
.\CSVMerger Inpfile1,Inpile2,Inpfile3 Outputfile

#>
         
param(            
    [string[]]$CSVFiles,            
    [string]$OutputFile = "c:\merged.csv"            
)            
$Output = @();            
foreach($CSV in $CSVFiles) {            
    if(Test-Path $CSV) {                               
        $temp = Import-CSV -Path $CSV             
        $Output += $temp                   
    } else {            
        Write-Warning "$CSV : No such file found"            
    }            
            
}            
$Output | Export-Csv -Path $OutputFile -NoTypeInformation            
Write-Output "$OutputFile successfully created"      

Statistik über aufgetretene Wait Types

Wenn dieser Artikel gepostet wird, befind ich mich schon im Urlaub und somit auf meiner Reise in den Kaukasus.
Aber ich habe schon vorab ein interessantes Skript vorbereitet:
Dieses Skript summiert die aufgetretenen Waits nach Typ auf und zeigt an, wie oft sie auftreten, wie lange gewartet wurde, stellt sie in prozentualer Relation dar und berechnet auch die durchschnittliche Wartezeit.
Anhand dieser Liste bekommt man oft einen Überblick, wo es am MS SQL Server kränkelt. Einen Artikel über die diversen Wait-Types, welche gut und welche böse sind, werde ich nach meiner Rückkehr verfassen.

Syntax:

WITH [Waits] AS
    (SELECT [wait_type]
           ,[wait_time_ms] / 1000.0 AS [WaitS]
           ,([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS]
           ,[signal_wait_time_ms] / 1000.0 AS [SignalS]
           ,[waiting_tasks_count] AS [WaitCount]
           ,100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage]
           , ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER'             
       ,N'BROKER_RECEIVE_WAITFOR'
       ,N'BROKER_TASK_STOP'               
       ,N'BROKER_TO_FLUSH'
       ,N'BROKER_TRANSMITTER'              
       ,N'CHECKPOINT_QUEUE'
       ,N'CHKPT'                           
       ,N'CLR_AUTO_EVENT'
       ,N'CLR_MANUAL_EVENT'                
       ,N'CLR_SEMAPHORE'
       ,N'DBMIRROR_DBM_EVENT'              
       ,N'DBMIRROR_EVENTS_QUEUE'
       ,N'DBMIRROR_WORKER_QUEUE'           
       ,N'DBMIRRORING_CMD'
       ,N'DIRTY_PAGE_POLL'                 
       ,N'DISPATCHER_QUEUE_SEMAPHORE'
       ,N'EXECSYNC'                        
       ,N'FSAGENT'
       ,N'FT_IFTS_SCHEDULER_IDLE_WAIT'     
       ,N'FT_IFTSHC_MUTEX'
       ,N'HADR_CLUSAPI_CALL'               
       ,N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
       ,N'HADR_LOGCAPTURE_WAIT'            
       ,N'HADR_NOTIFICATION_DEQUEUE'
       ,N'HADR_TIMER_TASK'                 
       ,N'HADR_WORK_QUEUE'
       ,N'KSOURCE_WAKEUP'                  
       ,N'LAZYWRITER_SLEEP'
       ,N'LOGMGR_QUEUE'                    
       ,N'ONDEMAND_TASK_QUEUE'
       ,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
       ,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
       ,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
       ,N'REQUEST_FOR_DEADLOCK_SEARCH'     
       ,N'RESOURCE_QUEUE'
       ,N'SERVER_IDLE_CHECK'               
       ,N'SLEEP_BPOOL_FLUSH'
       ,N'SLEEP_DBSTARTUP'                 
       ,N'SLEEP_DCOMSTARTUP'
       ,N'SLEEP_MASTERDBREADY'             
       ,N'SLEEP_MASTERMDREADY'
       ,N'SLEEP_MASTERUPGRADED'            
       ,N'SLEEP_MSDBSTARTUP'
       ,N'SLEEP_SYSTEMTASK'                
       ,N'SLEEP_TASK'
       ,N'SLEEP_TEMPDBSTARTUP'             
       ,N'SNI_HTTP_ACCEPT'
       ,N'SP_SERVER_DIAGNOSTICS_SLEEP'     
       ,N'SQLTRACE_BUFFER_FLUSH'
       ,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
       ,N'SQLTRACE_WAIT_ENTRIES'           
       ,N'WAIT_FOR_RESULTS'
       ,N'WAITFOR'                         
       ,N'WAITFOR_TASKSHUTDOWN'
       ,N'WAIT_XTP_HOST_WAIT'              
       ,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
       ,N'WAIT_XTP_CKPT_CLOSE'             
       ,N'XE_DISPATCHER_JOIN'
       ,N'XE_DISPATCHER_WAIT'              
       ,N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
 )
SELECT
    MAX ([W1].[wait_type])          AS [WaitType],
    CAST (MAX ([W1].[WaitS])        AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS])    AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS])      AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount])          AS [WaitCount],
    CAST (MAX ([W1].[Percentage])                           AS DECIMAL (5,2))  AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount]))     AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount]))   AS DECIMAL (16,4)) AS [AvgSig_S]
 FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
   ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95
;