How to dynamically define quarterly dates in Coldfusion

You know those reports that upper management request from time to time? The ones that give you nightmares? Yeah those. Well sometimes they will request quarterly reports so I wanted to share an easy way to define quarterly dates.

So we start with our default variables:

<cfset quarter = 1>
<cfset year = "#year(now())#">
<cfset startmonth = 1>

And here are the functions that create the dates:

<cffunction name="QuarterFirstDate" returnType="date">
    <cfargument name="quarternumber" required="yes" type="numeric">
    <cfargument name="yr" type="numeric" default="#year(now())#">
    <cfargument name="startmonth" type="numeric" default="1">
    <cfset firstDate = DateAdd("m",startmonth-1,CreateDate(yr, ((quarternumber-1)*3)+1, "1"))>
    <cfreturn firstDate>
</cffunction>

<cffunction name="QuarterLastDate" returnType="date">
    <cfargument name="quarternumber" required="yes" type="numeric">
    <cfargument name="yr" type="numeric"  default="#year(now())#">
    <cfargument name="startmonth" type="numeric" default="1">
    <cfset lastDate = DateAdd("m",startmonth-1,CreateDate(yr, quarternumber*3, DaysInMonth(CreateDate(yr, quarternumber*3, "1"))))>
    <cfreturn lastDate>
</cffunction>


And here is how I define my returned variables to be used in my code:

<cfset startDate = #DateFormat(QuarterFirstDate(quarter, year, startmonth))#>
<cfset endDate = #DateFormat(QuarterLastDate(quarter, year, startmonth))#>

To change the quarters, simply change the default quarter variable define at the top of the page:

<cfset quarter = 1> = 01-Jan-14 - 31-Mar-14
<cfset quarter = 2>  = 01-Apr-14 - 30-Jun-14
<cfset quarter = 3>  = 01-Jul-14 - 30-Sep-14
<cfset quarter = 4>  01-Oct-14 - 31-Dec-14


nJoy!

How to return a set number of rows in SQL using a variable

You're gonna love me for this one :)

I'll make it very simple too.

So I am sure you've use the TOP clause to retreive a certain number of rows from a query, right?


Here is an example of how you can set the value of TOP as a variable based on, let's say...user input.

CREATE PROCEDURE [dbo].[testRowCount]
    (
        @total int=0  input variable. This can be based on anything like user input, etc.
    )

AS
DECLARE @top INT what you will use as the TOP variable
SET @top = @total  here you set the value for TOP based on the input

And here is how you use in in the select statement

SELECT TOP(@top) *
            FROM  table
WHERE....



You're welcome :)

Stored Proc Clean Up - Getting rid of procs you don't use

If you are like me, then you HATE clutter. I am a minimalist - yep even when coding. I hate to see stored procs laying around that I:

1. Don't know what they do OR
2. Don't know if they are even in use

Of course, you don't want to start deleting procs randomly (that's a no-no)

So here are a few snippets to help you with your SQL house keeping:

This one tells you all of the procs that have run since the last restart.

SELECT p.name, last_execution_time, s.type_desc, create_date, modify_date
 FROM sys.dm_exec_procedure_stats AS s
INNER JOIN sys.procedures AS p
ON s.[object_id] = p.[object_id]
ORDER BY p.name;

And this one shows procs that have NOT been run since last restart:

SELECT p.*
FROM sys.procedures AS p
LEFT JOIN sys.dm_exec_procedure_stats AS s
ON s.[object_id] = p.[object_id]
WHERE s.object_id IS null
order by name


Happy Cleaning!