SQL Server

Loading specific dates from a given interval into one line

Oracle Database has a very simple way to aggregate multiple rows into one row. It can be done easily using a listagg function. When I started to work with Microsoft SQL, I was surprised that it has no direct alternative to Oracle listagg function. SQL Server developers recommend using FOR XML PATH expression to get similar results (like here). This work really good despite it is not so easy to use. Because this operation is not so popular among SQL Server developers, I would like to share a task which I was solving this week. I believe somebody could find it useful.

This was the task definition: I have a table with employees’ holiday terms. Each term has a holiday start and holiday end dates. I have a table with a list of all public holidays in the Czech Republic. I needed to list all public holidays during each holiday term. Because there can be multiple public holidays during each request I needed to concatenate multiple days into one row with a separator. Finally, I decided to write a function which returns all public holidays from a specific interval as a varchar. This is the code of the function.

Bulk generating SSRS reports by C# application

We send a specific report to our customers every month. This report is generated by SQL Server Reporting Services on SQL Server 2014. The report has a parameter called Category which determines a project for which is the report generated. This parameter is dynamically generated because we send reports only for project with some activity in last month. That’s why we cannot use SSRS standard subscription function. The only standard way how to generate the report for all projects is clicking it by hand. This is time-consuming because there are usually more than one hundred projects to report. The other problem is that user could make an error and skip some project. This error would be hard to track in further process.

I decided to solve this by C# command line application. This application loads all active projects in last month. Then it generates the report in XLS format for each project. The generated files are stored in a program’s directory. The program is quite simple as you can see from the following source code.