A wonderfully crafted and beautifully designed system to help any and all designers when it comes to Grid Designs/Layouts. Thanks for the awesome resource Nathan!!

I am sure this is well known by most of you, but I am a noob! and want to help other noobs out there that are like me.

I needed to Create a new Cube today, and in the process I figured we needed an ‘UNKNOWN’ row for all those Unknown situations that could pop up in a Pivot table. Well, when you create a Cube, most of your Dimension and Fact tables are driven off of Keys that you set as Identity. Or at least this is how MS came to our shop and showed us how to do it.

Now, the problem here is Identity is READ-ONLY so our staging tables and Views could not properly add a ‘UNKNOWN’ with a regular INSERT when trying to force that Identity value to -1. Why negative one? So that you will ALWAYS know that this is either an UNKNOWN or N/A.

So, here is what we did within the initial load of one of the Dimension tables:

TRUNCATE TABLE PM_DWT.dbo.Dim_Partnership
SET IDENTITY_INSERT PM_DWT.dbo.Dim_Partnership ON
GO
INSERT INTO PM_DWT.dbo.Dim_Partnership (Partnership_Key,Partner_ID,Partnership_Name)
VALUES (-1,-1,'N/A')
GO
SET IDENTITY_INSERT PM_DWT.dbo.Dim_Partnership OFF
GO
DBCC CHECKIDENT (Dim_Partnership, RESEED, 0)
GO
INSERT INTO PM_DWT.dbo.Dim_Partnership (Partner_ID, Partnership_Name)
SELECT
Partner_ID
,Partner_Name
FROM PM_Cluster.dbo.v_Dim_Partnership
ORDER BY Partner_ID
GO

The above code will insert your first record for -1 and UNKNOWN. Then it will enable the Identity column again. What happens here though, is the Identity column see an entry, so it naturally wants to add one, so that your next value inserted will actually have 2. We want to force it to restart even though we inserted a record.

DBCC CHECKIDENT (Dim_Partnership, RESEED, 0)
GO

Will do just that…so that the main INSERT will now begin at 1.

These lines of code will get you the First and Last day of the current year.

SQL:

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),'First Day of the Year'
UNION
SELECT DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))),'Last Day of the Year'


Results:
2008-01-01 00:00:00.000 First Day of the Year
2008-12-31 00:00:00.000 Last Day of the Year

I can’t give you a full tutorial on all things Dates when it comes to SQL, but I can show you how I solved a date task for a project I am now finishing up. This project includes finding data between these dates:

First Day of Last Month
Current Day of Last Month - so today’s day - 28 - but of last month
Last Day of Last Month

First Day of Month
Current Day of Month - Today
Last Day of Month

First Day of Next Month
Current Day of Next Month - same, today’s day of the month for next month
Last Day of Next Month

Oh, I also needed to get this month, for last year with the three same time frames. First day of the current month of last year, last day of current month of last year, and today’s day of last year.

First off, it’s relatively easy to search the interweb and use the first thing that pops up and gives you the answer. But when I tried that, the code was long, ugly, and just wasn’t to easy to maintain. So, I started with the most simple of all:


SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101),'First Day of This Month Last Year'
UNION
SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,0,getdate())),101),'Today Last Year'
UNION
SELECT convert(varchar,DATEADD(YEAR,-1,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1)),101),'Last Day of This Month Last Year'
UNION
SELECT convert(varchar,DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101),'First Day of Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,-1,getdate()),101),'This Time Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),-1),101),'Last Day of Last Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0),101),'First Day of This Month'
UNION
SELECT convert(varchar,getdate(),101),'Today'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1),101),'Last Day of This Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),0),101),'First Day of Next Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,+1,getdate()),101),'This Time Next Month'
UNION
SELECT convert(varchar,DATEADD(MONTH,+1,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),-1)),101),'Last Day of Next Month'


Results:

01/01/2007 First Day of This Month Last Year
01/29/2007 Today Last Year
01/31/2007 Last Day of This Month Last Year
12/01/2007 First Day of Last Month
12/29/2007 This Time Last Month
12/31/2007 Last Day of Last Month
01/01/2008 First Day of This Month
01/29/2008 Today
01/31/2008 Last Day of This Month
02/01/2008 First Day of Next Month
02/29/2008 This Time Next Month
02/29/2008 Last Day of Next Month


I hope many will find this useful. Of course, you’ll most likely want to set variables with these…as your SQL could get very long and unreadable if you had to copy and paste these over and over for BETWEENs or some other similar date range.

** Please feel free to post…I’d like to know what others do, if there are more efficient ways, or newer ways. I am a relative Noob at SQL, so I know this can be done better.