SQL, VB, MicroStrategy & Other Topics

To fill up my database with test data, I needed to generate dummy text, phone numbers, social security numbers etc. There are data generators out there, but i wanted to write my own, so here it is... ALTER FUNCTION dbo . fn_WORD_JUMBLER (       @Text VARCHAR ( MAX ) = '' ,       @DataType INT = 0 , -- 0 = random text, 1 = random number       @DataLenth INT = 10     ) RETURNS VARCHAR ( MAX ) AS BEGIN /* USAGE: SELECT dbo.fn_word_jumbler('ankeet Is Awesome!', DEFAULT, DEFAULT) SELECT dbo.fn_word_jumbler('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ', DEFAULT, DEFAULT) -- ALPHABETS SELECT dbo.fn_word_jumbler(DEFAULT, 0, DEFAULT) -- NUMBERS SELECT dbo.fn_word_jumbler(DEFAULT, 1, DEFAULT) ============================================================================= Created By: Ankeet Shah Purpose: Accepts an input and Returns the same text in reor...

Select * from tblTable for xml auto, elements

Creating Dynamic Filter Based on Tab Selection Use Report Studio to get the following end result. (Yesterday) (Month to Date) (Life to Date)   Create a Query Create a new query to be used solely to hold the tab values. Call it “TimeFrame Query” and create data items for every time frame that you’d like to appear. The expression definition should be a string encapsulated in single quotes. Create a String Variable Next let’s go ahead and create a string variable called “Tab Condition”. Define the expression as shown below: if (paramvalue('paramTabChoice') is null) then ('LTD') else (paramvalue('paramTabChoice')) This expression will set a default tab value for the report. Next, we will need to create a string value to correspond to every data item we created in the query. Note: The values of this variable must exactly match the expression definition of the data items we created. It is not necessary to use single quotes when ...

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.

I am back after a while! I have accepted a new position which means more responsibilities. I now lead a Business Intelligence initiative. This opportunity is exciting and interesting. I get to do what I love to do! So first task at hand was to be able to access data from the AS400 system for ETL to MS SQL 2008. Simple, right? Simple? I thought so, but got stumped for the first few minutes and thought many others might have the same issue! If you do not see IBMDA400, IBMDARLA and IBMDASQL as in the above screenshot, you are mssing some drivers. Issue at hand - cannot find proper drivers on MS SQL installation to connect to the AS400 system. What you need to do is install IBM Access iSeries for Windows software on your SQL Server box. http://www-03.ibm.com/systems/i/software/access/windows/index.html . After installing this software, logout and log back in. Once that is done follow these steps. 1. Navigate to SQL Server Installation > Server Objects > Linked Servers ...

A very simple way to select top X random rows from a table/query or select rows in a truly random order. The key is using a GUID. SELECT TOP X * FROM SomeTable ORDER B Y NewID () That Simple!

Here is a function that will easily let you convert from a CSV (you can modify it to be ;sv or -sv... you get the idea!) to a Table using SQL's Table-valued Functions. SQL Function: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE FUNCTION [dbo].[charlist_to_table] (@list ntext, @delimiter char(1) = N',') RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr varchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr varchar(4000), @leftover varchar(4000), @tmpval varchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = c...

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc...

CHECKSUM will calculate a hash value for the provided function parameters. CHECKSUM function when run multiple times for the same parameters, will result in same hash value. But it is not guaranteed that supplying different parameters will always result in a different hash. Therefore using CHECKSUM to uniquely identify each column of a table is not recommended (even with primary keys implemented on the table). I use this function to see if there were changes to any columns in a given row. Each of my tables have an additional column called ChecksumValue with INT datatype. Periodically (nightly in my case) I calculate Checksum for each row and compare it with the ChecksumValue column and if they turn out to be different, take appropriate action (send out email / restore original value etc). CREATE TABLE Example( FName varchar (50), LName varchar (50), ChecksumValue int ) -- Initially: UPDATE Example SET ChecksumValue = CHECKSUM (FName, LName) -- or CHECKSUM(FName) to track changes to ...

The title may not exactly explain with this post is about. I had the same problem while trying to search for a solution to this puzzle. Background: I work in debt collection industry. The collectors call debtors and collect money on the debts and based on the amount of money that they collect in a given month, they earn their commission. Sometimes towards the EoM, collectors are notoriously well known to be collecting any (good/bad) payments that they can, so to inflate their monthly numbers. And many a times the bad payment check will bounce as an NSF. But each of these NSFs cost big bucks to the Collection Agencies and the Debtor. Puzzle: One of the new analysis required was to find out which collector(s) had more than 10 NSF payments within any 5 day range. Example, between the range of April 1 and April 5, find all collectors that had more than 10 NSF payments or between the range of April 2 and April 6, find all collectors that had more than 10 NSF payments and so on. Solution: He...

I have been employed as a Systems Developer since last four years and during these four years many a times I have sought help from various blogs. I believe now that I have enough experience to blog about SQL and VB6 and thereby share my experiences with others in the community. Welcome to my blog - 'SQL, VB etc'!