SQL, VB, MicroStrategy & Other Topics

Here is a function that will return first day of the current quarter. This can be used within MicroStrategy via ApplySimple functionality as pass through function. CREATE FUNCTION dbo.udf_GetFirstDayOfCurrentQuarter (@getdate as datetime ) RETURNS DateTime AS BEGIN RETURN CONVERT ( DATETIME , convert ( varchar (2), case ( month (@getdate)%3) WHEN 0 THEN ((( month (@getdate)/3) - 1) * 3) + 1 ELSE ( month (@getdate) - ( month (@getdate)%3)) + 1 END ) + '/1/' + convert ( Varchar (4), YEAR (@getdate)) ) END

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...

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 ...