DevPinoy.org
A Filipino Developers Community
clintz' blog » Creating Dummy Data
   
Creating Dummy Data
Testing is one of the "must" in software development process.
Once, our client lost almost 14 million pesos in a month because of using an application that was developed in a hurry and tested only for 2 days. I feel sorry for them because i was the one who developed that application. I warned them though, that they must have a test run for atleast a month or two.
Anyway, when testing some application, you will need a dummy data.The most common dummy data that were used are Integer/Float, String and Date.

To create a random integer or float, you can use the RAND() function (from BOL: RAND() function returns a random float value from 0 through 1), but sometimes I get a value more than 1.
So, if you want to generate a random integer which is less than n (n is any integer), all you have to do is multiply the result of RAND() function to n.
So when you run,
    select rand() * 100
the result is always < 100.
When you want to generate a random number between two numbers, you can can create a query something like this

    declare @min int, @max int
    select @min = 10, @max = 20

    select (((@max + 1) - @min) * RAND()) + @min

To create a random character you can use CHAR() function.
This query will generate a random 10-letter string (capital letters)

    declare @min int, @max int, @nRand int, @cRand varchar(10)
    select @min = 65, @max = 90 -- (97 - 122 --> small letters)

    declare @i int
    set @i = 0
    set @cRand = ''
    while @i < 10
        begin
            select @nRand = (((@max + 1) - @min) * RAND()) + @min
            select @cRand = @cRand + char(@nRand)
            set @i = @i + 1
        end

    select @cRand


At first, i find it hard on generating a random date. When you try
    
    select cast(0 as datetime) -- it will return 1900-01-01
    
If that's the case, then all you have to do to generate a random date is generate a random number.
In Sql server, i think the full range of dates it can handle is from 01/01/1900    to 12/31/9999.
The average days in a year is 365.2422.

    select cast(365.2422 as datetime) -- will return 1901-01-01
    select cast(365.2422 * 100 as datetime) -- will return 2000-01-01
    select cast(365.2422 * 100 + 13 as datetime) -- will return 2000-01-14

Anyway, if you want to generate a random date in between two dates, you can try this script:

    declare @nRand int
    declare @Jan float, @Feb float, @Mar float, @Apr float
        , @May float, @Jun float, @Jul float, @Aug float
        , @Sep float, @Oct float, @Nov float, @Dec float
    
    select @Jan  = 31
    , @Feb =  @Jan + 28.2422
    , @Mar =  @Feb + 31
    , @Apr =  @Mar + 30
    , @May =  @Apr + 31
    , @Jun =  @May + 30
    , @Jul =  @Jun + 31
    , @Aug =  @Jul + 31
    , @Sep = @Aug + 30
    , @Oct = @Sep + 31
    , @Nov = @Oct + 30
    , @Dec = @Nov + 31
    
    -- select @Jan jan, @Feb feb, @Mar mar, @Apr apr
    --     , @May may, @Jun jun, @Jul jul, @Aug aug
    --     , @Sep sep, @Oct oct, @Nov nov, @Dec [dec]
    
    --to generate a random date between Aug 2005 and Feb 2005:
   select @nRand = ((@Aug + 1) - @Feb) * RAND() + @Feb
   select convert(varchar, cast((365.2422 * 105) - 31 + @nRand as datetime), 101)

There's a lot of way to create a random data in sql server. But those are some of the basic ways i know.
Posted 08-31-2006 2:55 PM by clintz
Filed under: , ,

Comments

trashVin wrote re: Creating Dummy Data
on 08-31-2006 12:30 AM

im just curious how your company reacted to the 14million waste....

giniedpooh wrote re: Creating Dummy Data
on 01-08-2009 12:28 AM

wow that's a lot of money... 14 million wasted???

Testing a system is a process, a procedure.. there's unit testing done by the Developers and there's also tests done by QA or Test Analysts (3rd party people not involved in the dev team) who will review the Software Requirement System. Then if errors or problems were encountered it will be logged for dev team to fix. Test Analyst think about scenarios... the what ifffssss??? to be able to know if the system will run well upon deployment... because still there's User's Acceptance Test.. then it will be up to them if the deployment is a success. Job well done is not over yet because sometimes clients still ask for support :-)


Copyright DevPinoy 2005-2008