Two Digit Dates in SQL – MM/DD/YYYY

When creating dates or numbers as strings it is sometimes required to have two digit numbers.

For example, instead of this: 3/1/2011

You want to see this: 03/01/2011

There is an easy way to do this using the Right() function and adding a string zero to the front of a number, and then take the right two characters.

Here are some examples:

Let’s say the Date is December 6, 2010, so the day is a single digit “6”

Running this query:


Select Convert(varchar(10), Day(GETDATE())) as OneDigit

Results = “6”

Add the right function with a string zero looks like this query:


Select Right(‘0’+Convert(varchar(10), Day(GETDATE())),2) as TwoDigit

Results = “06”

Another example without the date.


Right(‘0’+ Convert(varchar(3), ROW_NUMBER() over(Order by COLUMN_NAME)),2) as ColNumber


where TABLE_NAME = ‘Product’


Posted in Syndication, T-SQL | Tagged | Leave a comment

SSRS Top N and Bottom N reporting with Duplicates

Previously I wrote a blog on Top N and Bottom N reporting. There were a couple of gotcha I did not cover in that blog so I thought I would cover those here to answer some of the questions I have received. The other blog can be found here.

On a report you are trying to group the top N number of rows together and all others should be in a separate group. Let’s say you want the top 5 sales people based on amount.

In my other blog I showed how to use the Top N and Bottom N filters on groups. The major problem comes in when you have duplicate amounts at the cut off. For example if you want the top 5 sales employees to be in one group based on their sales amount, and three employees tie for the number 5 slot. This would cause more than 5 people to show in the top N group and those same people would show in the bottom N using the technique I showed in the previous blog.

So how do you fix this? You will need a different method to do this. You need to find the cut off amount and spilt the top from the bottom based on that amount.

Here is the table we will use on the report. Notice that positions 4, 5, and 6 have the same amount. If you set the filter on the report to top 5 you will get 6 rows in the top. The Bottom N method I showed in the previous blog will fail because the bottom group will not know about the extra rows in the top group. This will cause row 6 to show in both groups.


A better way to do this Top N and Bottom N grouping would be to find the cut off amount and filter the groups based on this.

Here is the main query pulling the data to show on the table.

SELECT RowNum, Name, Amount


Now you need to create a new data set and the query will look like below

With O as(

Select top (@N) t.RowNum, t.Amount, t.Name

From TopN t

Order by Amount desc


Select Top 1 Amount

From O

Order by Amount

I named this dataset MinTopAmount. This query is using a CTE to get the top N amounts. N is a parameter on the report, you can hard code this if desired. Then the outside query gets the lowest amount from that query. This gives you the lowest amount to show in the top N group. This query will only return single row and column with the cut off amount.

Now you need to set up the filters on the groups to filter based on the amount instead of Top or Bottom. These are two adjacent groups on a single table.


The Sum of the Amount value form the new dataset, which is named MinTopAmount in this example, should not sum anything because the query only returns one row due to the top 1 in the query with the CTE. The bottom group will have the exact same filter except it will use less than instead of greater than or equal to.

With the N set to 5 it will divide based on the amount at the fifth spot. If there is a tie, it won’t matter.

In this image you can see the bottom group in blue. Notice even though we selected Top 5 we get 6 rows in the top due to the tie, everyone else is in the bottom group.


If you have any issues let me know.

Posted in SSRS, Syndication | Tagged | 2 Comments

Standard Deviations with CTE in SQL

I was working with a client recently getting the number of standard deviations a student was off the average number of suspensions. I accomplished this with two Common Table Expressions, (CTE).

The first CTE gets the standard deviation and the average for the entire database. The Database holds all of the schools in the district and this CTE gives use the overall average.

The second CTE gets the number of suspensions summed up and group by student.

In the final query we bring the two CTE’s together by selected each column and performing the calculation to determine the number of standard deviations the student is off from the average (Mean).

That calculation is: (Student Count – Overall Average)/Standard Deviation

There are other ways to do this same thing. But by breaking it up into two CTE’s it make the maintenance and testing very easy. You can run the CTE’s separately to make sure each is returning the correct data before combining them.

Here is the query:


School as( — This CTE gets the Standard Deviation and Average of the Districts ISS

Select convert(numeric(18,3),stdev(f.InSchoolSuspension)) StdISS, AVG(convert(numeric(18,3),f.InSchoolSuspension)) AvgISS

from FactSchoolPerson f


Student as( — This CTE gets the ISS summed up by student

Select f.DimDWPersonKey, Sum(f.InSchoolSuspension) ISS

from FactSchoolPerson f

Group by f.DimDWPersonKey

)–Combine the two CTEs in this query and calculate the number of StdDevs Off the Mean

Select st.DimDWPersonKey, st.ISS, s.AvgISS , s.StdISS ,

((st.ISS – s.AvgISS)/ s.StdISS) StdOffISS

from student st Cross Join

School s

Order by st.DimDWPersonKey

Posted in Syndication, T-SQL | Tagged | Leave a comment

SSRS Report Models no Primary Keys

When creating report models you might run into bad database designs where the tables have no foreign keys and/or no primary keys. This can be resolved in the report model DSV. I have created two tables, one with first names, the second with last names. Each has an ID column which is the row that matches the row on the opposite table. There are no primary keys and no foreign keys on either table.


In visual studio I created a report model project and selected the database with these two tables. Then I created a DSV with these two tables.


After you create the DSV (Data Source View), right click on the DSV and select open.You will see the two tables in the DSV view.


Then I right clicked on the row to set a primary key. Remember this row must be unique.


Then I dragged the first ID to the second ID to create the foreign key. Remember, these must be matching data types.


Then I create the model and it has the relationships needed to create reports.


I created a report in report builder 2.0. Here you can see how the names match up correctly.


Posted in SSRS | Tagged | Leave a comment

SSRS – Creating Drill Through reports with MDX

If you are reporting from a Cube in SSRS and you want to drill through to another report you can set up actions just like any other drill through except one small change. The parameter you pass to the drill through report must be changed to Unique Name instead of value.


Go to the detailed report that you are drilling down to and right click on the datasets folder then select show hidden data sets so you can see the parameter dataset. In this query you will see the key under the parameter value. This is the unique value that the report needs to receive. Changing the parameter to unique name takes care of this issue.


Posted in mdx, SSAS, Syndication | Tagged , | 2 Comments

SSIS – Using Kill with SP_Who to Break locks


The dreaded table lock can occur and cause your SSIS packages to fail. A popular request I receive asks “How can I get rid of these table locks?” This blog will show you how to build a package that will kill any SPID’s that are running on your system that could be locking a table.

Note: Be careful using this technique, you could kill a critical process.

In this package you will have five variables.


objSpids = Holds the data from sp_Who2

strDatabase = Name of the database to look in Spids

strSpid = Current Spid in the for each loop

strSQLKill = Expression: “Kill ” + @[User::strSpid]

strSQLSPWho = Expression

“CREATE TABLE #sp_who2


Status VARCHAR(1000) NULL,





Command VARCHAR(1000) NULL,



LastBatch VARCHAR(1000) NULL,

ProgramName VARCHAR(1000) NULL,




INSERT INTO #sp_who2

EXEC sp_who2

SELECT cast(spid as varchar(10)) as spid

FROM #sp_who2

WHERE DBName = ‘”+ @[User::strDataBase] +”‘

and HostName is not null

and Status <> ‘BACKGROUND’

group by spid

DROP TABLE #sp_who2″

Notice the strSQLSPWho variable holds the query to create the table and put all of the SP_Who data into it. The database name comes from the strDatabase variable.

The first thing you will need to do is get the information from SP_who. This is done with an Execute SQL task. Set the SQL source type to variable and choose the strSQLSPWho variable as the source variable. Set the Results set to Full Results Set. In the Results set pane add a result set and set the name to 0 and the variable to objSpids.



Now you will need to loop through each row in the object variable with the Spids. The For Each Loop will do this. The Enumerator needs to be set to For Each ADO. Select the objSpids variable. Under variable mappings set the variable to strSpid and the index to 0.



Now drop an Execute SQL task in the For Each Loop. Set the SQL source type to variable and choose the strSQLKill variable as the source variable. Leave the Results set to None.


That is it for building the package. The next step is to test the package. Place a breakpoint on the For Each loop. Set this breakpoint to “Break at the beginning of every iteration of the loop”.


Start debugging the package and check the watch window or the locals window to for the value of the variables. To get these windows click on debug >Windows> Locals or Watch1.


Here is the watch window:


If the package is picking up Spids you don’t want you will need to adjust the where clause in strSPWho variable.

Posted in SSIS, Syndication | Tagged | 2 Comments

SSAS Trending KPI

KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is trending in right direction based on previous levels. If you want to show a measure has increased or decreased since last month, this can be accomplished by using the MDX function PrevMember. You want the trend to show at the month level only in excel. This way the users are not confused by extra trend graphics. This example will be using the adventure works cube.

Create a KPI in the cube and select the measure you want to compare.


The Goal will be a case statement to determine if the user is looking at the month level. If the user is not at the month level the goal will be null. If the user is at the month level then the goal will be a tuple of the previous month.

Case When ([Due Date].[Date].CurrentMember.level is [Due Date].[Date].[Month Name]) then

([Due Date].[Date].prevmember,[Measures].[Sales Amount Internet])

Else Null



The trend will check for the goal value, if the goal is null then the trend will be null. This ensures the trend only shows on the month level. If the goal is not null then the tuple for the previous month is compared to the KPI Value. Based on this comparison you will return a 1,0, or -1.


When Isempty(KPIGoal(“Monthly Internet Sales”)) Then Null

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) <

KPIValue(“Monthly Internet Sales”) Then 1

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) =

KPIValue(“Monthly Internet Sales”) Then 0

Else -1



This is what it looks like in excel.


Posted in SSAS, Syndication | Tagged | Leave a comment