Transact-SQL - Joel Lipman .Com

Stored Procedure to List Distinct Values and Counts

: Joel Lipman; Transact-SQL; 26 October 2012; Hits: 7786

What?
Yes you could just run a SELECT DISTINCT query along with collation to make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns.

How?

: Joel Lipman; Transact-SQL; 10 October 2012; Hits: 8523

What?
This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on case-sensitivity.

Why?
We have a database with data in it. For a particular column we have setup default values, let's use the example "Data Not Yet Available". Unfortunately the end-user reported these default values sometimes list twice, especially when the case is different, eg. "Data not yet available". The final system (qlikview) was case-sensitive despite our server collation being case-insensitive.

Furthermore, we now have the task of finding all the variations of the default values which we found we could do with the built-in SOUNDEX function.

How?

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 9699

Why?
Hmm... I was writing a stored procedure that will scramble data given a table as a parameter. Because I only want to update a temporary table and not the original (source) table, I needed the following stored procedure (or part of).

What?
This will copy a given table into a temporary table all the while maintaining the structure and data.

Thinking inside of the box
I think everyone suggests the following (or at least the idea of):

copy raw

SELECT *
INTO #MyTempTable
FROM @GivenTable

SELECT *
INTO #MyTempTable
FROM @GivenTable

If @GivenTable is a parameter then the above will simply return an error. Also, if it was this easy, I wouldn't need to post this note on my website.

Category: Transact-SQL :: Article: 424

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 14618

What?
Some people have been suggesting you can use "CREATE OR REPLACE ... VIEW ... FUNCTION" but my SQL Server 2008 Management Studio doesn't like this and refuses to understand what I'm trying to do.

Why?
As this data seemed to be across various websites, I wanted a page which has all of them in one place. So here you go, I hope it's of some use. If it's wrong then just post a comment at the bottom of this page.

: Joel Lipman; Transact-SQL; 01 August 2012; Hits: 11894

This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which merely uses the same characters but switches their order randomly, so I've renamed it DataJumble as opposed to my article on Data-Scrambling.

Why?
We want to scramble sensitive data that we send to suppliers for support or analysis. With inspiration from: "Obfuscating your SQL Server Data" by John Magnabosco but tweaked for our purposes. NOTE that the following has only ever been run on development environments and I would not recommend running this on a production system as I have not tested the performance and database load.

copy raw

Before:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           John Smith           1990-03-21
           2           Fred Bloggs          1988-11-02
           3           Another User         1985-07-11
           4           Yet Another User     1977-06-25

       After:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           mStnoh iJh           1926-01-02
           2           lgreg BFdos          1969-03-14
           3           onrUest ehAr         1968-05-05
           4           otentre AhYe Usr     1964-08-08

Before:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 John Smith 1990-03-21
2 Fred Bloggs 1988-11-02
3 Another User 1985-07-11
4 Yet Another User 1977-06-25
After:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 mStnoh iJh 1926-01-02
2 lgreg BFdos 1969-03-14
3 onrUest ehAr 1968-05-05
4 otentre AhYe Usr 1964-08-08

Category: Transact-SQL :: Article: 422

: Joel Lipman; Transact-SQL; 11 October 2011; Hits: 10779

I have googled, binged and asked but to no avail. Some self-proclaimed IT experts in forums said "why would you want to do that?". Unhelpful Bellends. It's a bit like asking me "Why can't I turn on my computer" and me replying "why would you want to do that?".

Anyway, I was looking for something like the record separator in Oracle SQL*Plus where a row of data (blank or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following:

copy raw

SELECT
	DATENAME(dw, StartDate) AS 'Day'
FROM 
	Timetable
ORDER BY
	StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC

-- Yields
/*
Day              
---------------- 
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

SELECT
DATENAME(dw, StartDate) AS 'Day'
FROM
Timetable
ORDER BY
StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC
-- Yields
/*
Day
----------------
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

Category: Transact-SQL :: Article: 394

: Joel Lipman; Transact-SQL; 23 March 2011; Hits: 7750

My setup

Windows XP SP3
MS SQL Server Management Studio 2008
SQL Server 2008 R2
Regional Setting: London BST (British Summer Time)

: Joel Lipman; Transact-SQL; 14 January 2011; Hits: 14097

So I find myself searching the web for a way of producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database.

I'm calling this project "Reinventing the Wheel" because the request was a report that was similar to an MS Outlook calendar which will display any room bookings for that month. This is "Reinventing" because we used to use MS Outlook to store these kind of things until the company implemented a software to deal with room bookings and said we shouldn't use Outlook for this...

: Joel Lipman; Transact-SQL; 11 January 2011; Hits: 22773

Thought I'd add a note as I was getting confused with the built-in function "FormatDateTime()". The example is shown as:

copy raw

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

The other formats are:

copy raw

=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)

=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)

Unfortunately if you are using US dates and want the report to use a specific European date format and you spend as long as I did searching the web for a solution, then ignore all the above.

Another built-in function is the text-formatter

Category: Transact-SQL :: Article: 317

: Joel Lipman; Transact-SQL; 06 January 2011; Hits: 15115

Thought I'd put a note on this error. Bearing in mind that this is a general data type error and not just because I tried to convert a date in SQL format to a Month name.

copy raw

An error occurred during local report processing.
An error has occurred during report processing.
The Group expression used in grouping 'table1_month' returned a data type that is not valid.

An error occurred during local report processing.
An error has occurred during report processing.
The Group expression used in grouping 'table1_month' returned a data type that is not valid.

Category: Transact-SQL :: Article: 313

: Joel Lipman; Transact-SQL; 19 November 2010; Hits: 10063

Source: http://www.kodyaz.com/articles/sql-format-date-format-datetime-t-sql-convert-function.aspx

Error during processing - Converting Date/Time

: Joel Lipman; Transact-SQL; 13 July 2010; Hits: 13185

Well I find myself again the dummie of the Internet. I basically came across this error and STFW'd for ages following complex T-SQL Timestamp conversions but to no avail. Not saying that you shouldn't try their solutions but just check you haven't done this silly mistake.

I'm only guessing the same error would happen in Report Builder 2.0 which I've stopped using as my day job wants us to use Business Intelligence Development Studio for all our SQL Server Analysis Service (SSAS) and Reporting Service (SSRS) projects/solutions. The features and interface are very similar when developing reports though.

The error I'd get was

An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the dataset DataSet1.
Conversion failed when converting date and/or time from character string.