Hi all! This time I’ll try to dig a little bit into the fabulous realm of SQL dynamic queries 😀
The need: I had to generate a report showing some counts divided by month, basically the columns represent the months and the user has the possibility to pick a date range.
For example, imagine that you want to select the number of the orders placed by all the customers by month.
Using the standard Northwind database as reference, the first thing to do is to generate a list of months along with the relative start and end days:
The next step is “quite” easy: all we have to do is to generate a string containing a main query that picks all the customers and have many sub-queries, one for each month, SELECTING the count of the orders.
As you may see, most of the work is done with lines 5-10 where we use the COALESCE function to concatenate the sub-queries created using the #dates temp table. Note that each query will contain WHERE clause that filters the Order by Customer.
On lines 12-13 we create the final query to be executed, and finally, on line 16 we ask sp_executesql to run our code.
Here’s a screenshot of the results:
Don’t forget to DROP the #dates table! 😀