Aniversary from a dynamic Frequency

I needed to find the next occurrence from a start date on an incremental frequency ( Monthly, Quarterly, Biannual, Annually. ) Large volumes of data and performance implications meant that a round trip to .NET to loop through the data and process were not acceptable in this case.

</pre>
<pre>with frequencies as
 (
 SELECT 'M' Frequency, 1.0 multiplier
 UNION ALL
 SELECT 'Q' Frequency, 3.0 multiplier
 UNION ALL
 SELECT 'S' Frequency, 6.0 multiplier
 UNION ALL
 SELECT 'A' Frequency, 12.0 multiplier
 )

select top 10 Frequency,
 StartDate,
 DateAdd(m, CEILING(DATEDIFF(m, StartDate, getdate()) / multiplier) * multiplier, StartDate ) NextOccurance
 from
 ADB..ATable
 INNER JOIN frequencies FRQ
 on ADB..ATable.Frequency = FRQ.Frequency
 where StartDate is not null
<pre>
Advertisements

Days in a month

One thing which amazes me is the lack of some basic programmability in SQL especially around some core requirements of T-SQL. Dates is one of them.

To get the number of days in a month we get the first day in the month and the last. We then subtract from each other. One contains the 1st of this month and the other contains the 1st of next month. The minus includes one of the days but not the other.

The first day is found by taking todays day of the month, minusing it from 1 (-day of month + 1 ) and then adding it to today day of the month which equates to 1.

the last day of the month / 1st day of next month is found by adding 1 month onto the first day


declare @daysInMonth as int
declare @firstMonth as dateTime
declare @firstNextMonth as dateTime

set @firstMonth = DATEADD (d, 1 - DAY(GETDATE()), GETDATE())
set @firstNextMonth = DATEADD(m, 1, DATEADD (d, 1 - DAY(GETDATE()), GETDATE()))
set @daysInMonth =  DATEDIFF(day,@firstMonth, @firstNextMonth)
select @daysInMonth

.Net DateTime vs SQL Sever DateTime

Quite a nice way to handle “non null” date times being passed into SQL Server from .Net.

if( aClass.aDate == DateTime.MinValue )
{
aClass.aDate = (DateTime)SqlDateTime.MinValue;
}

There are other implementation of DateTime for Oracle and MySQL; the concept can be applied in these environments as well.

T-SQL Determine the next monthly occurance from a starting date

A sample script to determine the next monthly occurence from a start date


select
DateCreated,
dateadd(d,
 -- If the day of the end of the month > anniversary day then add the end of the month instead of anniversary
 case when datepart(d,datecreated)> datepart(d,dateadd(d,-1,dateadd(m,datediff(m,0,getdate())+1,0)))
 then datepart(d,dateadd(d,-1,dateadd(m,datediff(m,0,getdate())+1,0)))
 else datepart(d,datecreated)
 end
 -1,
 -- This month or next month if we are already past today
 dateadd(m,
 case when datepart(d,datecreated)
 then 1
 else 0
 end
 + datediff(m,0,getdate()),0)) CreatedAnniversary
from MyDB..MyTable

T-SQL Order records based upon a group

Quite often you want to find the latest record order of records based upon a group of data.  Using group you would have to then join back onto the table to filter the data further. Using partition and ronumber you can do this without having to group.


select

FieldA,

FieldB,

FieldC,

row_number() over( partition by FieldA,Field B order by FieldC ) As RowNumber

From Table

T-SQL Getting Quarter, Day Name and other date components from a date

I was looking for the day name and quater and stumbled accross the datename function.

SELECT DATENAME(year, GETDATE()) — 2011
SELECT DATENAME(quarter, GETDATE()) – 1
SELECT DATENAME(month, GETDATE()) – Jan
SELECT DATENAME(week, GETDATE()) — 5
SELECT DATENAME(day, GETDATE()) — Sunday
SELECT DATENAME(hour, GETDATE()) – 4
SELECT DATENAME(minute, GETDATE())
SELECT DATENAME(second, GETDATE())
SELECT DATENAME(millisecond, GETDATE())