💎SQL Reloaded: Oracle vs. SQL Server (Running Totals)

    One of the SQL Server 2005+ functionalities I kind of ignored in the past years is the CROSS APPLY operator that allows returning rows from a table-valued function of a correlated query, allowing for example to calculate running totals, cumulated amounts from the previous records and the current record in a data set. Before the introduction of CROSS APPLY operator such a query could be written as follows:
-- SQL Server/Oracle: Running Totals (simple join)
SELECT POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
, SUM(POT.SubTotal) RunningSubTotal
, SUM(POT.TaxAmt) RunningTaxAmt
, SUM(POT.Freight) RunningFreight
FROM Purchasing.PurchaseOrderHeader POH
    LEFT JOIN Purchasing.PurchaseOrderHeader POT
        ON POH.PurchaseOrderID >= POT.PurchaseOrderID
GROUP BY POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
ORDER BY POH.PurchaseOrderID

   Each of the attributes not aggregated needs to be added in the GROUP BY clause, fact that could be quite a hit from a performance standpoint. If only one aggregated value is needed then the query could be rewritten, the cumulated value being calculated in a correlated query:
-- SQL Server/Oracle: Running Totals (correlated query)
SELECT POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
, ( -- calculating aggregated running total
    SELECT SUM(POT.SubTotal)
    FROM Purchasing.PurchaseOrderHeader POT
    WHERE POH.PurchaseOrderID >= POT.PurchaseOrderID
) RunningSubTotal
FROM Purchasing.PurchaseOrderHeader POH

   In theory could be added a correlated query for each aggregated value, though this means an additional table scan for each attribute, not the best approach for sure. The CROSS APPLY operator simplifies such queries allowing to do multiple calculations within the same correlated query:

-- SQL Server 2005: Running Totals (CROSS APPLY)
SELECT POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
, DAT.RunningSubTotal
, DAT.RunningTaxAmt
, DAT.RunningFreight
FROM Purchasing.PurchaseOrderHeader POH
    CROSS APPLY (-- calculating aggregated running total
        SELECT SUM(POT.SubTotal) RunningSubTotal
        , SUM(POT.TaxAmt) RunningTaxAmt
        , SUM(POT.Freight) RunningFreight
        FROM Purchasing.PurchaseOrderHeader POT
        WHERE POH.PurchaseOrderID >= POT.PurchaseOrderID) DAT
ORDER BY POH.PurchaseOrderID

     The advantage of  the first two approaches is that they work also in Oracle, though also Oracle comes with an alternative using analytic aggregate functions:
-- Oracle: Running Totals (analytic aggregate function)
SELECT POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
, SUM(POH.SubTotal) OVER (ORDER BY POH.PurchaseOrderID ) RunningSubTotal
, SUM(POH.TaxAmt) OVER (ORDER BY POH.PurchaseOrderID) RunningTaxAmt
, SUM(POH.Freight) OVER (ORDER BY POH.PurchaseOrderID) RunningFreight
FROM PurchaseOrderHeader POH
ORDER BY POH.PurchaseOrderID

Notes:
1.  The calculation of running totals requires the existence of a unique identifier for each record that can be sorted, in this case being used the PurchaseOrderID attribute, though typically could be used the creation date (timestamp) when the record was modified, alone or in combination with other attributes; when used in combination then maybe it makes sense to add a running number in order to apply the above techniques.  In Oracle the use of analytic aggregate functions offers less headaches from this perspective, being enough to specify the unique combination of attributes in the ORDER BY clause in the body of the function.
2.  SQL Server 2005’s window aggregate functions, even if similar with Oracle’s analytic aggregate functions, unlike the window ranking functions they don’t allow the ORDER BY clause in the functions, therefore running totals can’t be modeled in SQL Server with the help of window aggregate functions.
3. In case the running totals are based on other table, then might be needed to use the OUTER APPLY operator, returning thus also the records for which not match is retrieved in the outer dataset.

   Sometimes might be requested to create running totals within a given partition, for example by Vendor, in this case all is needed to do is to add the VendorID in the WHERE or JOIN constraint, while in Oracle’s analytic functions case in the PARTITION BY clause. Here are the three queries modified:
-- SQL Server/Oracle: Running Totals per Vendor (simple join)
SELECT POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
, SUM(POT.SubTotal) RunningSubTotal
, SUM(POT.TaxAmt) RunningTaxAmt
, SUM(POT.Freight) RunningFreight
FROM Purchasing.PurchaseOrderHeader POH
    LEFT JOIN Purchasing.PurchaseOrderHeader POT
        ON POH.VendorID = POT.VendorID
     AND POH.PurchaseOrderID >= POT.PurchaseOrderID
GROUP BY POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
ORDER BY POH.VendorID
, POH.PurchaseOrderID

-- SQL Server 2005: Running Totals per Vendor (CROSS APPLY)
SELECT POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
, DAT.RunningSubTotal
, DAT.RunningTaxAmt
, DAT.RunningFreight
FROM Purchasing.PurchaseOrderHeader POH
     CROSS APPLY ( -- calculating aggregated running total
        SELECT SUM(POT.SubTotal) RunningSubTotal
        , SUM(POT.TaxAmt) RunningTaxAmt
        , SUM(POT.Freight) RunningFreight
        FROM Purchasing.PurchaseOrderHeader POT
        WHERE POH.VendorID = POT.VendorID
          AND POH.PurchaseOrderID >= POT.PurchaseOrderID) DAT
ORDER BY POH.VendorID
, POH.PurchaseOrderID

-- Oracle: Running Totals per Vendor (analytic aggregate function)
SELECT POH.PurchaseOrderID
, POH.VendorID
, POH.OrderDate
, POH.SubTotal
, POH.TaxAmt
, POH.Freight
, SUM(POH.SubTotal) OVER (PARTITION BY POH.VendorID ORDER BY POH.PurchaseOrderID ) RunningSubTotal
, SUM(POH.TaxAmt) OVER (PARTITION BY POH.VendorID ORDER BY POH.PurchaseOrderID) RunningTaxAmt
, SUM(POH.Freight) OVER (PARTITION BY POH.VendorID ORDER BY POH.PurchaseOrderID) RunningFreight
FROM PurchaseOrderHeader POH
ORDER BY POH.VendorID
, POH.PurchaseOrderID

🕋Data Warehousing: Drill Down (Definitions)

"A technique for navigating through levels of data ranging from the most summarized (up) to the most detailed (down). For example, to view the details of sales data by year, a user can drill down to display sales data by quarter, and drill down further to display data by month." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"The act of adding a row header or replacing a row header in a report to break down the rows of the answer set more finely." (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit" 2nd Ed., 2002)

"To delve deeper into data by going from a summary value to more detailed values." (Margaret Y Chu, "Blissful Data ", 2004)

"The process of exposing progressively more detail by making selections of items in a dynamic report or further enhancing a query." (Sharon Allen & Evan Terry, "Beginning Relational Data Modeling" 2nd Ed., 2005)

"Method of analysis for retrieving lower levels of detailed data starting from summary data." (Paulraj Ponniah, "Data Warehousing Fundamentals for IT Professionals", 2010)

"A method of exploring detailed data that was used in creating a summary level of data. Drill down levels depend on the granularity of data within a dimension." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"To decompose data into more atomic components, that is, data at lower levels of aggregation." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"An analytical technique that lets a DSS user navigate among levels of data ranging from the most summarized (up) to the most detailed (down)." (Ciara Heavin & Daniel J Power, "Decision Support, Analytics, and Business Intelligence 3rd Ed.", 2017)

"A method of exploring detailed data that was used in creating a summary level of data. Drill Down levels depend on the granularity of the data in the data warehouse." (Intrafocus) 

🕋Data Warehousing: Granularity (Definitions)

"The degree of specificity of information contained in a data element. A fact table that has fine granularity contains many discrete facts, such as individual sales transactions. A table that has coarse granularity stores facts that are summaries of individual elements, such as sales totals per day." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"The level of detail captured in the data warehouse." (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit" 2nd Ed., 2002)

"Granularity level is the level of detail of the data in a data warehouse or data mart." (Claudia Imhoff et al, "Mastering Data Warehouse Design", 2003)

"A classification of data based upon the level of detail at which data is recorded. Atomic, base, and summary are all classifications of data granularity." (Sharon Allen & Evan Terry, "Beginning Relational Data Modeling" 2nd Ed., 2005)

"The level of detail contained in a unit of data. The more detail there is, the lower the level of granularity. The less detail there is, the higher the level of granularity." (William H Inmon, "Building the Data Warehouse", 2005)

"Refers to the level of detail represented by the values stored in a table’s row. Data stored at their lowest level of granularity are said to be atomic data." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed, 2011)

"Granularity refers to the smallest reusable element of information. See also physical granularity." (Charles Cooper & Ann Rockley, "Managing Enterprise Content: A Unified Content Strategy, 2nd Ed.", 2012)

"A term sometimes used to describe the level of detail used in obtaining information." (Kenneth A Shaw, "Integrated Management of Processes and Information", 2013)

"The level of detail found in a record of data" (Daniel Linstedt & W H Inmon, "Data Architecture: A Primer for the Data Scientist", 2014)

"Granularity refers to the level of detail or precision for a specific information resource property. For instance, the postal address of a particular location might be represented as several different data items, including the number, street name, city, state, country and postal code (a high-granularity model). It might also be represented in one single line including all of the information above (a low-granularity model)." (Robert J Glushko, "The Discipline of Organizing: Professional Edition" 4th Ed, 2016)

🕋Data Warehousing: Extraction, transformation, and loading [ETL] (Definitions)

"A process for acquiring data from source systems, reformatting and cleansing it based on business requirements, and loading it, usually into a data warehouse. The term is more generally used as a means of moving data between systems." (Jill Dyché & Evan Levy, "Customer Data Integration", 2006)

"The process of taking data from one source - the source - and transforming it and loading it into another location - the target." (William H Inmon & Anthony Nesavich, "Tapping into Unstructured Data", 2007)

[ETL system:] "Extract, transformation, and load system consisting of a set of processes by which the operational source data is prepared for the data warehouse. Consists of extracting operational data from source applications, cleaning and conforming the data, and delivering the data to the presentation servers, along with the ongoing management and support functions." (Ralph Kimball, "The Data Warehouse Lifecycle Toolkit", 2008)

"The very essence of business intelligence, this is the process of removing raw data from a data system, processing and cleaning it, and then making it available in a business-intelligence database." (Stuart Mudie et al, "BusinessObjects™ XI Release 2 for Dummies", 2008)

"The process of connecting to a source database, pulling data out of the source database, transforming the data into a standard format, and then loading the data into a destination system." (Ken Withee, "Microsoft Business Intelligence For Dummies", 2010)

"A term that describes the activities used to prepare a collection of data sources for loading into a database, but also used to describe data mapping and transformation processes in general." (John R Talburt, "Entity Resolution and Information Quality", 2011)

"Generally, an approach to data integration from multiple source databases to integrated target databases." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"In computing, extract, transform, and load refers to a process in database usage and especially in data warehousing that: extracts data from outside sources; transforms it to fit operational needs, which can include quality levels, and loads it into the end target (database, more specifically, operational data store, data mart, or data warehouse)." (Keith Holdaway, "Harness Oil and Gas Big Data with Analytics", 2014)

"Data warehouse process used to extract data from disparate or homogeneous data sources. Transforms the data to align with data structure for storing and analysis." (Dennis C Guster, "Scalable Data Warehouse Architecture: A Higher Education Case Study", 2018)

"An acronym for the Extract, Transform and Load process. This refers to extracting data from the source system, transforming (cleaning and manipulating etc.) the data and then loading the data into the data warehouse." (BI System Builders)

"ETL is a process that extracts, transforms, and loads data from multiple sources to a data warehouse or other unified data repository." (IBM) [source] 

"Refers to the process by which data is:

– Extracted from sources

– Transformed or standardized for storing in the proper heterogeneous format

– and Loaded into the final store or warehouse. The ETL process is commonly run in parallel with transformation processes executing as data is being extracted from sources." (Insight Software)

"Short for extraction, transformation, and loading. The process used to populate a data warehouse from disparate existing database systems." (Microstrategy)

"The act of extracting data from various sources, transforming data to consistent types, and loading the transformed data for use by applications." (Microsoft)

"a process in data warehousing responsible for pulling data out of one source, transforming them so that they meet the needs of the processes that will be using them on next stages, and placing them into target database." (KDnuggets)

"ETL is shorthand for – extract, transform, load. An ETL solution facilitates the replication of data from one or more sources that is converted into format suitable for use in analytics and moved into a destination system." (Qlik) [source]

"ETL (extract, transform, load) is three combined processes that are used to pull data from one database and move it to another database. It is a common function in data warehousing." (snowflake) [source]

"ETL (extract, transform, load) processes are three common functions performed on databases. Extract or read data from a database, transform the extracted data into a structure that can be placed into another database, and load or write the data into the target database." (kloudless)

"ETL (extract, transform, load) processes are three database functions. They extract or read data from a database, transform the extracted data into a structure that can be placed into another database, and load or write the data into the target database." (MuleSoft) 

"ETL stands for Extract-Transform-Load and it refers to the process used to collect data from numerous disparate databases, applications and systems, transforming the data so that it matches the target system’s required formatting and loading it into a destination database." (Databricks) [source]

"Extract Transform Load refers to a trio of processes that are performed when moving raw data from its source to a data warehouse, data mart, or relational database." (Informatica) [source]

"Extract, Transform and Load (ETL) refers to the process in data warehousing that concurrently reads (or extracts) data from source systems; converts (or transforms) the data into the proper format for querying and analysis; and loads it into a data warehouse, operational data store or data mart). ETL systems commonly integrate data from multiple applications or systems that may be hosted on separate hardware and managed by different groups or users. ETL is commonly used to assemble a temporary subset of data for ad-hoc reporting, migrate data to new databases or convert database into a new format or type." (Teradata) [source]

"Refers to a process in database usage consisting of three phases: extracting data from external sources, transforming it to fit operational needs (can include a quality check), and loading it into a target database or data warehouse." (Board International)

"In business intelligence, an ETL tool extracts data from one or more data-sources, transforms it and cleanses it to be optimized for reporting and analysis, and loads it into a data store or data warehouse. ETL stands for extract, transform, and load." (Logi Analytics) [source]

🕋Data Warehousing: Roll/Drill Up (Definitions)

"The act of removing a row header or replacing a row header in a report to summarize the rows of the answer set. Sometimes called dynamic aggregation." (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit" 2nd Ed , 2002)

"To present higher levels of summarization. " (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit" 2nd Ed., 2002)

"Method of analysis for retrieving higher levels of summary data starting from detailed data." (Paulraj Ponniah, "Data Warehousing Fundamentals for IT Professionals", 2010)

"Data analysis performed on a data set with applied mathematical functions, associated with fewer dimensions, higher levels of hierarchy in one or more dimensions, or both." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"In SQL, an OLAP extension used with the GROUP BY clause to aggregate data by different dimensions. (Rolling up the data is the exact opposite of drilling down the data.)" (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"To collect subsets of data from multiple locations in one location." (Microsoft, "SQL Server 2012 Glossary", 2012)

"The act of summarizing or aggregating data to a level higher than the previous level of detail." (Sharon Allen & Evan Terry, "Beginning Relational Data Modeling 2nd Ed.", 2005)

🕋Data Warehousing: Conformed Dimension (Definitions)

"A shared dimension that applies to two subject areas or data marts. By utilizing conformed dimensions, comparisons across data marts are meaningful." (Microsoft Corporation, "Microsoft SQL Server 7.0 Data Warehouse Training Kit", 2000)

"Dimensions are conformed when they are either exactly the same (including the keys) or one is a perfect subset of the other." (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit 2nd Ed ", 2002)

"A conformed dimension is one that is built for use by multiple data marts. Conformed dimensions promote consistency by enabling multiple data marts to share the same reference and hierarchy information." (Claudia Imhoff et al, "Mastering Data Warehouse Design", 2003)

"A dimension whose data is reused by more than one dimensional design. When modeling multiple data marts, standards across marts with respect to dimensions are useful. Warehouse users may be confused when a dimension has the similar meaning but different names, structures, levels, or characteristics among multiple marts. Using standard dimensions throughout the warehousing environment can be referred to as 'conformed' dimensions." (Sharon Allen & Evan Terry, "Beginning Relational Data Modeling" 2nd Ed., 2005)

"Dimension tables that are the same, or where one dimension table contains a perfect subset of the attributes of another. Conformance requires that data values be identical, and that the same combination of attribute values is present in each table." (Christopher Adamson, "Mastering Data Warehouse Aggregates", 2006)

"A dimension that is shared between two or more fact tables. It enables the integration of data from different fact tables at query time. This is a foundational principle that enables the longevity of a data warehousing environment. By using conformed dimensions, facts can be used together, aligned along these common dimensions. The beauty of using conformed dimensions is that facts that were designed independently of each other, perhaps over a number of years, can be integrated. The use of conformed dimensions is the central technique for building an enterprise data warehouse from a set of data marts." (Laura Reeves, "A Manager's Guide to Data Warehousing", 2009)

"Two sets of business dimensions represented in dimension tables are said to be conformed if both sets are identical in their attributes or if one set is an exact subset of the other. Conformed dimensions are fundamental in the bus architecture for a family of STARS." (Paulraj Ponniah, "Data Warehousing Fundamentals for IT Professionals", 2010)

"A dimension that means and represents the same thing when linked to different fact tables." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

💎SQL Reloaded: More Date-related Functionality II

Arithmetic Progression Sets

In yesterday’s post on the same topic I was showing how to create a sequence of dates on the fly with the help of common table expressions and multi-statement table-valued functions. I was mentioning that similar functionality could be obtained using a numeric sequence (interval), following to create the dates when calling the function. Here’s the function that provides the respective functionality, the difference from fGetDatesInInterval, excepting the different data types, is that it has a third parameter for the increment value (default 1), being thus possible to generate a list of odd/even numbers or any other type of arithmetic progressions. 

-- SQL Server 2005+: Numeric Interval
CREATE FUNCTION dbo.fGetInterval(
@Start int = 0
, @End int = 0
, @Step int=1)
RETURNS @Interval TABLE(
Value int PRIMARY KEY)
AS
BEGIN
DECLARE @Sign smallint
SET @Sign = IsNull(NullIf(Sign(@End-@Start), 0), 1);
WITH CTE(Sequence)
AS(
     SELECT COALESCE(@Start, @End, 0) Sequence
     UNION ALL
     SELECT CTE.Sequence + @Step * @Sign Sequence
     FROM CTE
     WHERE (@End-CTE.Sequence-@Step)*@Sign>=0
)
INSERT @Interval
SELECT Sequence
FROM CTE
OPTION(MAXRECURSION 0)
RETURN
END

-- numeric interval
SELECT *
FROM dbo.fGetInterval(0, 1000, 1)
-- numeric interval (3rd parameter NULL)
SELECT *
FROM dbo.fGetInterval(0, 999, NULL)
-- even numbers
SELECT *
FROM dbo.fGetInterval(0, 1000, 2)
-- odd numbers
SELECT *
FROM dbo.fGetInterval(1, 1000, 2)
-- single value
SELECT *
FROM dbo.fGetInterval(1, 1, 2)

 
     The numeric values returned by the function could be used to form a date sequence by using the DateAdd function and a Cast to the date data type. The downside with this approach is that if the dates are used in more expressions within the same query, the expression used to calculate the dates increases the visual complexity of the query, a function like fGetDatesInInterval could be easier to use instead.

-- creating a date sequence
SELECT Value
, Cast(DATEADD(d, Value, GETDATE()) as Date) DateSequence
FROM dbo.fGetInterval(0, 1000, 1)

     The fGetInterval has a second particularity, the Value attribute from the table used to retrieve the data was declared as PRIMARY KEY, fact that allows to improve queries’ performance when searches are performed on table’s values. The DateSequence from the table returned by fGetDatesInInterval could be defined as PRIMARY KEY too.

    The common table expression and its use resumes in writing a single statement, and considering that the logic from fGetInterval function could be written as a single statement, in theory we could write an inline table-valued function instead. This technique works as long the OPTION clause is not used within the inline function, when this is attempted is returned the following error: Incorrect syntax near the keyword 'OPTION'. As it seems the OPTION clause can’t be used with single-statement objects like views or inline table-valued functions. Therefore common table expressions can be used in single-statement objects, the functionality being thus restrained to the 100 default limit number of recursion.

Missing Values in a Data Set

    While searching for something on the Web, today I found Aaron Akin’s post on Recursive Common Table Expressions, he’s using them in order to determine the missing numbers/dates in a sequence. This can be easily achieved using for example the fGetDatesInInterval  or fGetInterval functions with a left join to the tested data set. Using the AdventureWorks database and fGetDatesInInterval function, supposing that I would like to see the working dates within a time interval with no POs, the query could be written as follows:

-- working days with no PO
SELECT DI.DateSequence
FROM dbo.fGetDatesInInterval('2003-01-01', '2003-12-31') DI
LEFT JOIN ( -- list of PO dates
    SELECT OrderDate
    FROM Purchasing.PurchaseOrderHeader 
    GROUP BY OrderDate
) POH
ON DATEDIFF(d, DI.DateSequence, POH.OrderDate)=0
WHERE DATEPART(dw, DI.DateSequence) BETWEEN 2 AND 6 --working days
AND POH.OrderDate IS NULL

     A similar approach could be used also in case of numeric data sets, here’s an example based on fGetInterval  and SplitList table-valued functions:

SELECT DI.Value
FROM dbo.fGetInterval(1, 10, 1) DI
   LEFT JOIN dbo.SplitList('1,3,6,7,10', ',') SL
      ON DI.Value = SL.Value
WHERE SL.Value IS NULL

💎SQL Reloaded: More Date-related Functionality I

Date Intervals Generation 

 
     In reports is sometimes requested to aggregate transactional data at day level, showing all existing data for a given time interval. This can be easily solved by limiting the time interval in the WHERE clause, though the problem is that it might happen that there are no data in the system for one or more dates and the users want to see that in the report. The easiest approach is to do a left join between the table in which are stored the calendar days and the actual data aggregation, though such a table it’s not always available. What shall we do then? So we need a table-valued function that takes two parameters – the start and end date – and creates all the dates in between. Actually it would be enough to have a sequence of numbers between 0 and the number of days between the two dates, and use the DateAdd function on them. Both problems could be easily solved with the help of common table expressions introduced with SQL Server 2005, while for previous versions could be used a simple loop.
    Here’s the multi-statement table valued function supposed to create the sequence of dates:
-- SQL Server 2005+: Date Sequence in Interval
CREATE FUNCTION dbo.fGetDatesInInterval(
@StartDate
date , @EndDate date)
RETURNS @Dates TABLE (
DateSequence date)
AS
BEGIN
    DECLARE @Sign smallint
    SET @Sign = Sign(DateDiff(d, @StartDate, @EndDate));  
  
   WITH CTE(DateSequence)
   AS (
   SELECT @StartDate DateSequence
    UNION ALL
    SELECT Cast(DATEADD(d, @Sign, CTE.DateSequence) as Date) DateSequence
   FROM CTE
   WHERE DATEDIFF(d, CTE.DateSequence, @EndDate)*@Sign>0 )
 
    INSERT @Dates
    SELECT DateSequence
    FROM CTE
    OPTION(MAXRECURSION 0)
RETURN
END
Note:
     The OPTION (MAXRECURSION 0) has been introduced in order to avoid the 100 default limit for the number of recursions allowed for a query, a value of 0 equating with an unlimited number of recursions. The function might run also without this option, though if the number of days between the two input dates is greater or equal than 100 then you’ll get the following error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
 
   For example in order to get the dates within a year the function call would be:
-- days of a given year (positive increment)
SELECT DateSequence
, DATEPART(wk, DateSequence) WeekOfYear
, DATEPART(mm, DateSequence) MonthOfYear
, DATENAME(w, DateSequence) DayOfWeekName
, DATEPART(dw, DateSequence) DayOfWeek
FROM dbo.fGetDatesInInterval('2010-01-01', '2010-12-31')
     The DatePart and DateName functions could be used to get the various date parts of a date. As can be seen in this case the EndDate is greater than the StartDate, what happens if the users inverse the two dates? The above function already handles this situation with the help of @Sign variable, the following query in which the two parameters are inversed returning the same date sequence as above:
-- days of a given year (negative increment)
SELECT DateSequence
FROM dbo.fGetDatesInInterval('2010-12-31', '2010-01-01')
   The function handles also NULL values, returning the not-NULL value, and in case both parameters are NULL then it returns the current date:
-- second parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval('2010-12-31', NULL)
-- first parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval(NULL, '2010-12-31')
-- both parameters are NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval(NULL, NULL)

   The above function can’t be used in earlier versions than SQL Server 2005, though the same output could be obtained using a loop – here’s the modified function:
-- SQL Server 2000: Date Sequence in Interval
CREATE FUNCTION dbo.fGetDatesInIntervalOld(
@StartDate
date , @EndDate date)
RETURNS @Dates TABLE
(DateSequence date)
AS
BEGIN
    DECLARE @NumberDays int
    DECLARE @Counter int
    DECLARE @Sign smallint
    SET @NumberDays = IsNull(DateDiff(d, IsNull(@StartDate, @EndDate), @EndDate), 0)
    SET @Counter = 0
    SET @Sign = IsNull(NUllIf(Sign(@NumberDays), 0), 1)
    WHILE(@Counter* @Sign<=@NumberDays * @Sign)
    BEGIN
   INSERT @Dates
   SELECT DateAdd(d, @Counter, COALESCE(@StartDate, @EndDate, GetDate()))

   SET @Counter = @Counter + @Sign
END
RETURN
END
   As can be seen from the below test cases the function returns the same output as the common table expression-based implementation:
-- days of a given year (positive increment)
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-01-01', '2010-12-31')
-- days of a given year (negative increment)
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-12-31', '2010-01-01')
-- second parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-12-31', NULL)
-- first parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld(NULL, '2010-12-31')
-- both parameters are NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld(NULL, NULL)

Number of Working Days
   Given the fact that for each date could be obtained the day of the week, any of the two above functions could be used also to determine the number of (not) working days in a certain time interval:
-- number of (not) working days
SELECT SUM(CASE WHEN DATEPART(dw, DateSequence) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) WorkingDays
, SUM(CASE WHEN DATEPART(dw, DateSequence) IN (1,7) THEN 1 ELSE 0 END) WorkingDays
, COUNT(1) NumberDays
FROM dbo.fGetDatesInIntervalOld('2010-01-01', '2010-12-31')
 
   Even if handy maybe this isn’t the best approach in order to get the number of (not) working days. Some time ago I developed a similar function in VBA, the respective function subtracting the number of weekends falling in the extended time interval. Because I’ve calculated the number of weekends falling in the extended time interval (including first day of the week in which the Start Date falls, respectively the last day of the week in which the End Date falls), I have to add them again – this being the meaning of the two CASE uses.
-- SQL Server 2000+: Date Sequence in Interval
CREATE FUNCTION dbo.fGetWorkingDays(
@StartDate
date , @EndDate date)
RETURNS int
AS
BEGIN
DECLARE @NumberDays int
SET @NumberDays = IsNull(DateDiff(d, IsNull(@StartDate, @EndDate), @EndDate), 0)
RETURN @NumberDays -- difference days
- 2 * (@NumberDays + DatePart(dw, @StartDate) + (7 - DatePart(dw, @EndDate))) / 7 -- number weekends in extended weeks
+ (1 - CASE DatePart(dw, @StartDate) WHEN 1 THEN 1 ELSE 0 END) -- weekend to be added (first week)
+ (1 - CASE DatePart(dw, @EndDate) WHEN 7 THEN 1 ELSE 0 END) -- weekend to be added (last week)
+ 1 -- first day
END

   Should be enough to test the week for the various combinations of weekend/working days for 2-3 consecutive weeks:
SELECT dbo.fGetWorkingDays('2010-01-30', '2010-02-13') -- Sa-So (10 days)
SELECT dbo.fGetWorkingDays('2010-01-31', '2010-02-13') -- So-So (10 days)
SELECT dbo.fGetWorkingDays('2010-02-01', '2010-02-13') -- Mo-So (10 days)
SELECT dbo.fGetWorkingDays('2010-02-01', '2010-02-12') -- Mo-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-01-30', '2010-02-12') -- So-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-01-31', '2010-02-12') -- So-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-12') -- Sa-Sa (5 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-13') -- Sa-Sa (5 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-11') -- Sa-Th (4 days)
SELECT dbo.fGetWorkingDays('2010-02-07', '2010-02-11') -- So-Th (4 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-07') -- Sa-So (0 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-08') -- Sa-Mo (1 day)
SELECT dbo.fGetWorkingDays('2010-02-05', '2010-02-08') -- Fr-Mo (2 days)
SELECT dbo.fGetWorkingDays('2010-02-05', '2010-02-05') -- same day (1 day)
 
   In case of doubts the dbo.fGetDatesInIntervalOld could be used to check whether the results are correct. For easier checking of the output of the above test queries here’s the calendar covering the 2-3 weeks considered.

🎡SSIS: Visual Studio 2010 CR doesn’t support SSIS Projects

I’ve installed today Visual Studio 2010 CR (Candidate Release) without any problems - nice look and feel, new types of projects targeting several new Microsoft solutions (SharePoint 2010, MS Office 2010, Windows Azure, ASP.NET MVC 2, Silverlight 3), everything looked nice until I wanted to create a SSIS project – unfortunately no support for SSIS 2005/2008 within VS 2010. As it seems I’m not the only person looking for that functionality, several discussion forums approaching already this topic on MSDN, SQL Server Developer Center or Microsoft Connect.

The next version of SQL Server is not 2010, as many would expect, but Microsoft is working on SQL Server 2008 R2 avoiding somehow to synchronize the SQL Server Service solutions with VS 2010. Not sure if this strategic decision was taken for the sake of profit, but there is something fishy about it.

So, if I want to buy VS 2010 and SQL Server 2008 then I need to install also VS 2008, having thus 2 IDEs on my system, they might work together without problems though that means more space, more effort on managing the patches for the two IDE and I would expect that there will be synchronization differences between them, migrate forth and back solutions between frameworks, more issues to troubleshoot. Is this what Microsoft understands by “Integration”?! In the end Microsoft looses because I would expect there will be customers that would avoid moving to VS 2010 just because of the lack of support for SSIS 2005/2008. I wonder how much will hurt customers this lack of SSIS solution integration between VS 2010 and SQL Server 2008?! Quoting an unofficial comment made on one of the forums, it seems that “there will be a separate add-on that will give limited functionality” [1] in working with SSIS, though is this the right approach?

Being used with the DTS packages functionality available on SQL Server 2000, I found it somehow awkward that Microsoft decided to separate SSIS from the SQL Server Management Studio, on one side I understand that the complexity of SSIS projects requires a IDE much like the one provided by Visual Studio, though for the developer used to SQL Server 2000 not sure if this approach was welcome. Of course, data export/import functionality is available using the’ SQL Server Import and Export Wizard’ though it’s not the same thing, and even if I find the ‘Execute Package Utility’ quite easy to use, I can’t say I’m a fan of it. I wonder which Microsoft’s plans are for the future…

It seems there are even more surprises from Microsoft that could come with SQL Server R2, for example "using SSIS package as a data source for SSRS report datasets" [2], even if the respective feature is a non-production feature, what happens with the customers that already built their solutions on it?! Of course, Microsoft doesn’t recommend the use of non-standard features as they might not be supported on upper versions, but you know developers, why reinvent the wheel when there is already some functionality for that purpose! 

I would expect that more such issues will be discovered once developers start to play with VS 2010 and the coming SQL Server 2008 R2.

References:
[1] Microsoft Connect. (2010). SSIS VS2010 project type. [Online] Available from: https://connect.microsoft.com/SQLServer/feedback/details/508552/ssis-vs2010-project-type (Accessed: 19 February 2010)
[2] Siddhumehta. (2010). SSIS Package not supported as a data source in SQL Server Reporting Services ( SSRS ) 2008 R2. [Online] Available from: http://siddhumehta.blogspot.com/2010/02/ssis-package-not-supported-as-data.html (Accessed: 19 February 2010)