Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for February 4th, 2008

Generating Dates between two date values

Posted by decipherinfosys on February 4, 2008

Last week, there was a requirement that was posed to us by one of our clients which needed to make use of the pipeline function. This client has a product that runs on both Oracle and SQL Server so a solution was needed for both the RDBMS. The requirement was that given two dates, the code should automatically generate all the dates that fall between those dates including the end date. If there are no days that fall between those 2 dates, only the end date should be displayed. This output was to be used in other SQLs so either this should be procedural code or if possible, it should be a function call that can then be used within other SQL statements.

We had blogged before on how to simulate pipeline functions in SQL Server. It can be done by either maintaining a table of numbers which is a very popular practice or can also be done by using a UDF in which we use a table variable to populate the results. This second technique is what we had demonstrated in that blog post. Another way of doing this is via a CTE using recursion. In this post, we will present the solution that we used for this client using a UDF – we went with this solution after noticing the performance benefits of this approach versus the others.  Let’s look at the UDF again:

@Nbr_Of_Rows int,
@Start_Nbr int
returns @table table
Row_Num int not null primary key,
Data_Value int
Seed the table with the initial value
insert @table values (1, @Start_Nbr)

Now, loop through and create the list
till the rowcount of the inserts is
more than 0
while @@ROWCOUNT > 0
insert into @table (Row_Num, Data_Value)
select t1.Row_Num + t2.Max_Row_Num, @Start_Nbr + t1.Row_Num + t2.Max_Row_Num – 1
from @table as t1
cross join
(select max (Row_Num) Max_Row_Num from @table) t2 /*derived table*/
where t1.Row_Num <= @Nbr_Of_Rows – t2.Max_Row_Num

Using this UDF, as we had demonstrated in that older post, one can generate any sequence of numbers after one passes in the number of records and the starting number from where we need the values from, example:

select * from dbo.UDF_GEN_SEQUENCE (9, 10);

Row_Num     Data_Value
———– ———–
1           10
2           11
3           12
4           13
5           14
6           15
7           16
8           17
9           18

In this example, we wanted 9 records with the data value starting from number 10.  So, now that we have this pipeline function, we can make use of it to generate the dates as well, example:

declare @start_date datetime, @end_date datetime, @i int
select  @start_date = ’01/01/2008′,
@end_date = ’01/10/2008′

select @i = datediff(dd, @start_date, @end_date)

select @start_date + data_value as date_progression
from dbo.UDF_GEN_SEQUENCE (@i, 1)

2008-01-02 00:00:00.000
2008-01-03 00:00:00.000
2008-01-04 00:00:00.000
2008-01-05 00:00:00.000
2008-01-06 00:00:00.000
2008-01-07 00:00:00.000
2008-01-08 00:00:00.000
2008-01-09 00:00:00.000
2008-01-10 00:00:00.000

Using this technique, we can then use this result set as part of other SQL statements as well.

Posted in Oracle, SQL Server | 1 Comment »