An infinite date table

Here is the creation script for a neat function that will generate a table full of every date in the given range.


-- =============================================
-- Author:  Aaron D. Wells
-- Create date: 9/23/2010
-- Description: Get a list of dates in a range
-- =============================================
CREATE FUNCTION fn_Dates (@fromDate date, @toDate date)
RETURNS @dateTable TABLE( [Date] Date NOT NULL, FiscalYear int NOT NULL, FiscalMonth int NOT NULL )
AS 
BEGIN
     WITH CTE_DatesTable([date])
     AS (
     SELECT @fromDate AS [date]
          UNION ALL
          SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable
          WHERE DATEADD(dd, 1, [date]) <= @toDate)  
          INSERT @dateTable
               SELECT [date],
                    CASE WHEN MONTH(date) > 9 THEN YEAR(date) + 1 ELSE YEAR(date) END FiscalYear,
                    CASE WHEN MONTH(date) > 9 THEN MONTH(date) - 9 ELSE MONTH(date) + 3 END FiscalMonth
               FROM CTE_DatesTable OPTION (MAXRECURSION 0)
     RETURN 
END
Using the function is pretty simple, just give it a starting and ending date. By the way, the FiscalYear column is used to calculate the fiscal year for the company I was working with when i wrote this. Your mileage may vary.

Comments

Post a Comment

Popular posts from this blog

Database Projects, SQL Unit Tests, and TeamCity

Image
Continuous Integration  is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses. Visual Studio has Database Projects   SQL Server Data Tools  to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests , which fills a huge gap if any  of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress. If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your data...
Read more

Building nice XML from SQL Server Tables

The XML functionality of SQL Server is vast, however, the best subset of that functionality that I've found may be demonstrated in the following example: Unfortunately, the example result is listed before the code. Join all the desired tables together Set the desired root element Set the path to an empty string to avoid an extra XML element at the root of each resulting row Provide an XPATH name with attributes for each of the desired columns (select * doesn't work well) Keep all the selected columns together by XPATH name or you'll split your xml attributes See my next blog entry for my best way to shred the xml
Read more

Not everyone needs to be a software developer...

...but everyone should learn to code. I have friends who are pharmacists, lawyers, police, firemen, military, mechanics, and musicians who also write code to help them out in their jobs. Software is a tool, a versatile and powerful tool, that can be applied to any occupation or industry. Learning to code is a 21st century skill that should be required in every middle school and high school in the country. I'm not alone in this belief. There are lots and lots and lots of organizations dedicated to helping people learn to code. Even the Prime Minister of Singapore posted a Sudoku Solver that he wrote while an electrical engineering student as encouragement and an example to the world that we need more people who can code. I think that code may have been more approachable when I was learning. The video games we had were blocky and you could understand how they could worked with a minimal amount of instruction or none at all; how many versions of breakout and lunar lander mu...
Read more