Select data from previous day, previous week, previous month or previous year

DATEADD methodThe DATEADD method returns a specified date with the specified number interval (signed integer) added to a specified DATEPART of that date. (http://technet.microsoft.com/en-us/library/ms186819.aspx) With DATEADD, you can add a period of time to your date. Plus is in the future, minus is in the past. DATEADD gives you the results you want if you want to search for a specific time. It depends on the time frame you wish to search in. If you want the data from last day or only 5 days ago, then DATEADD is fine. If you want the data for...

Read More

Find the gaps in a sequence with SQL

Case At Allianz Global Assistance we sell, among other products, travel insurances. Every day of the year. Once a month our sold policies are extracted from a middleware system in several CSV files and need to be imported in our Datawarehouse. Before doing that, we want to check if there are no days missing in the files. So I needed to create something to check for gaps in the date sequence. The date in the file I need to check is in an ISO format: 20140321 Solution There are different approaches on this matter. In all cases we need to have...

Read More

SSRS Failed Subscription Notifications

CaseA nice feature of Reporting Services is that it can email reports on a regular basis to certain recipients including attachments in several file types. After having implemented several reports with a lot of subscriptions to different recipients it becomes a bit of a headache to monitor if reports are still delivered. SSRS is quite picky. It takes only one invalid email adres to cause the report not be sent. To no one. If one of the report parameters...

Read More

Mapping Stored Procedures in SSIS OLE DB Source Editor

Case Usually I use the "Table of view" or a plain SQL command in an OLE DB Source Editor to collect the data I need. That is rather straight forward and simple. Last week I needed to add some more steps and logic in my query and decided to go for a Stored Procedure. To make it more usable, I wanted to add a parameter. Peanuts one would think. Everything seemed to work fine on design mode, I even got a list of expected columns when clicking on...

Read More