SQL, VB, MicroStrategy & Other Topics

First Day of Current Month: =cdate( month(today) & "/1/" & year(today) ) Last Day of Current Month: =cdate( month(today) & "/" & day(dateadd("d", -1, dateadd("m", 1, cdate(month(today) & "/1/" & year(today))))) & "/" & year(today) ) First Day of Next Month: =cdate( month(dateadd("m", 1, today)) & "/1 /" & year(dateadd("m", 1, today)) )

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

Use the following code to validate Social Security Number (USA). The rules used for validation are based on SSA website, Wikipedia and HowStuffWorks.com. There may be more rules to the validation. If you find any, keep me posted! 'njoy Validating! SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Ankeet -- Create date: 6/19/2008 -- Description: Validates SSN -- ============================================= ALTER FUNCTION ValidateSSN ( @SSN varchar (11) ) RETURNS varchar (255) AS BEGIN /* USAGE: SELECT DBO.ValidateSSN(777992322) BASED ON: http://people.howstuffworks.com/social-security-number2.htm http://en.wikipedia.org/wiki/Social_Security_number http://www.socialsecurity.gov/employer/stateweb.htm http://en.wikipedia.org/wiki/Individual_Taxpayer_Identification_Number The Administration publishes the last group number used for each area number.[17]. Since group numbers are allocated in a regular (if unusual) pattern, it i...

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

Being used to DTS 2000, I was very thrilled to see Foreach Loop Container in action with SSIS 2005. It took me some time to figure out how to make this correctly work for my scenario, but once done it worked like a charm! Mission: To be able to import all .csv files from a particular location to a SQL table, move the file to an archive folder. All of these files had same file structure, but different file names. Plan: I am assuming that you are familiar with creating and opening an SSIS Package. 1. Drag and drop the Foreach Loop Container (Found under Control Flow Items in Toolbox) in ControlFlow area. 2. Add 5 new variables in the Variables pane (Right click anywhere in Control Flow area and click on Variables) Name / Data Type varFileName / String varSourceFolder / String varFilePath / String varArchiveFolder / String varArchivePath / String 3. Now its time to change the variables' properties in the Properties pane. Change the EvaluateAsExpression property to True for varFilePat...

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