Archive

Sql Date Format - Month and Year Only

Published
by
rs
on August 30, 2006
in SQL and Technical
. 3 Comments

i remember there is one time where i have to edit an old module where in the date field in sql is not a date data type but instead, it is a char type… they did that because they wanted to store only the month and year of the date in sql… at first it wasn’t a big deal after all until i have to return the data with a given date range… now that’s a problem… my problem…

since the date is in char type, it won’t be easy to select the data with a given range..
and so i came up with this solution… this is how i make a date range with char data type..

say we have to select all datas with these given date range: from 04/2003 to 03/2006

my query would be:

select field1, field2, dateField
from   table1
where  (cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003) or
       (cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006) or
       (cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4)
       as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006

now lets disect the code…
for the first where condition:
(cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003)
this will return all the data with the date range from 04/2003 to 12/2003…

for the second where condition:
(cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006)
this will return all the data with the date range from 01/2006 to 03/2006..

for the third and last where condition:
(cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4) as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006
this will return all the data with the date from 01/2004 to 12/2004 and from 01/2005 to 12/2005

if you don’t know how to use the substring function in sql… just google it… there are lots of threads and forums out there…

a smarter way to do this is convert the varchar type date into a real date format… in that way, you can easily make a date range because it is now in date type…
say you have “08/2006″ as august 2006, all you have to do is insert a “01/” on the left side to make it “01/08/2006″.. after that you can now convert it to date format… since you only need the month and year, the value of the day does not matter…

to insert the “01/” in the left side of your varchar date and convert it to date format:

convert(datetime, ‘01/’ + dateField,103)
*101 means the date format will be dd/mm/yy

now every time you need to the month and year.. use the code month() to return only the value of month and year() for the value of year…

sample:
say we have “30/03/1984″ in dateField

select month(dateField)
from sampleTbl

this will return “03″… and if you use the year() function.. it will return “1984″

another way is to use a contiguous string format… just convert your varchar date into this format ‘200608′ (means agust 2006).. then use <,>,= operators to create a range… just use substring() function to arrange your data into this format…

Prayers Do Come True

Published
by
rs
on August 29, 2006
in Uncategorized
. 4 Comments

There are three essential parts in prayer. one is you pray, second is you believe, third is you act. All of these are needed for your prayer to be answered.

You pray, its too obvious this one is needed in prayer. duh?!? but of course you have to know what’s for you and what’s not for you”. You know what i mean!!!

You believe, I remember in one of spongebob episodes wherein spongebob said: “If you believe in yourself and with a tiny pinch of magic, all of your dreams can come true.” Hehe, that’s not my point. What i mean is believe that God will answer your prayer, as in really believe in Him, not even a single doubt in your heart. Kind of like nothing to worry about. I know it’s hard to do but its a must. Once you worry, you already doubted the power of God, and if you have a doubt on God, then you should have not pray at all.

You act, another essential thing in prayer. Mercy is in God; deeds are in men. What you pray for won’t pop-up in just a blink of an eye. Of course you have to strive for it, do what ever it is you need and have to do.

with all these stuffs… your prayers will be answered… 100% sure… if you think you’ve done all of these and your prayer hasn’t been answered yet… then check the second part.. maybe there’s too little doubt in your heart.. too little to be seen… and remember, you can’t fool God so don’t even think of faking it…

Old Days

Published
by
rs
on August 28, 2006
in Uncategorized
. 1 Comment

I remember last week when i got a free time and ended up reading my friendster profile specifically my friend’s testimonials to me, it was a really great way to spend a dead afternoon. A great time really! I really felt good reading it. I started to realize how I became part of their lives, friends who are real, friends from college and high school. I never thought it was that fun reading it. It makes my heart beat faster, memories run through my brain, old good days. Everyone’s laughing, foolin’ around, and in short everybody’s happy. Different images flashes in the air as i read every one of it, oh how i really miss the old days back from high school and college. I never thought growing up is this hard. I just left school for about 4 months and I already miss everything, sigh! When will the old times happen again?

Comment

Published
by
rs
on August 27, 2006
in Uncategorized
. 0 Comments

I just recently started my technical blog in wordpress.com. I’m just a newbie in my job so you could count my blog post in your fingers. I post evry problems that i encountered in my career as a software developer. I also explained evry details how i manage to solve it. After a few postings, i started to get tired of doing it regularly, but not until this single email from wordpress tellin’ me that i got my first comment flashes in my screen. My heart beats so fast and i don’t know why, maybe so eager to know what kind of comment would it be. Then there it is, a simple thank you message from someone who lives in amsterdam i think, saying that she also had encountered the same problem as i had and thanks to me that i posted a solution for it. Well it really put a big smile on my face, i never thought that I could help someone on their career through my blog. Now I’m gonna’ do my tech blog regularly thinkin’ that somehow I’m helpin’ somebody in their career on the other part of the world.

check out my tech blog at http://richardsantos.wordpress.com/technical/

SQL isZero() Solution For Divide By Zero Error

Published
by
rs
on August 4, 2006
in SQL and Technical
. 22 Comments

I had a query once that contains a divide operation….

SELECT   x, y, ROUND(x/y) as quotient
FROM     dataTbl

It always returns a correct value…
but divide by zero error occurs whenever
there is a zero(0) not NULL value in y column…
now, what i want is instead of having
an error, i want it to return a zero(0) value also…
so I wonder if there is a function isZero(), I can simply
change the y value from 0 to NULL to avoid such error…
I was thinking of a code like this: isZero(y, NULL)…
unfortunately, there is no such functions…
I googled for the correct syntax and I came up with
the: NULLIF(fieldName, condition) function…

SELECT   x, y, ROUND(x/NULLIF(y, 0)) as quotient
FROM     dataTbl

by using the query above, divide by zero error is impossible…
what happen is SQL returns a NULL in y column if its value is zero(0)…
though the error was avoided… the result still does not satisfy me…
it only returns a NULL value in quotient field instead of zero(0)…
well, the solution is simple:

SELECT   x, y, ISNULL(ROUND(x/NULLIF(y, 0)), 0) as quotient
FROM     dataTbl

by using the ISNULL(fieldName, replacementValue) function,
It sort of like reversed the effect of NULLIF function…
what happen in this function is, if the value IS NULL
then replace it with what ever the replacementValue is…
in my query, it replaces the NULL value by zero(0)…

now that’s satisfying…. divide by zero error was avoided
and quotient field is never NULL….

SQL Server Joins And Group By (Performance Tuning)

Published
by
rs
on August 2, 2006
in SQL and Technical
. 1 Comment

I was given a certain module where in it performs a simple computation (the SUM function). What I’m tryin’ to do is add all the data from a specific fields and group by a certain field from another table. but the problem is the table containing the data to be calculated is three tables apart from the table containing the field needed for the group by function and four fields are needed for groupings.

to make it clear, here’s the query sample:


SELECT  a.field1, a.field2, a.field3, SUM(d.field2+d.field3) as totalFld
FROM    table1 a
        LEFT OUTER JOIN table2 b
        ON a.field4= b.field1
        LEFT OUTER JOIN table3 c
        ON b.field2= c.field1
        LEFT OUTER JOIN table4 d
        ON c.field2= d.field1
GROUP BY a.field1, a.field2, a.field3

By running the query above, it took me more than five minutes before it returns the data.

And so i started investigating and found out that the reason for slow performance is because I am joining the tables on the field that is not an index. So what I did is I added an indexes to the columns in each table used to join the tables. I’ve learned that for maximum performance, the indexes on the columns to be joined should have the same data type and it should be a clustered key for optimum performance.

Another thing is the group by function. I’ve found out that this also affects the performance. As the number of groupings increases the performance decreases.

to create an index using t-sql:

CREATE CLUSTERED INDEX indexName on tableName(fieldName1, fieldName2,….)

to delete an index using t-sql:

DROP INDEX tableName.indexName

String Concatenation (Memory Buffer)

Published
by
rs
on July 20, 2006
in Technical
. 0 Comments

While working with a certain web application, I just found my self trapped on a string concatination problem. What I’m tryin’ to do is concatenate 2 strings and assigning the output to the first string. My code goes something like this:

PSEUDO CODE

var myString=”";

for(i=1;myQuery.recordCount;i++){
     myString = myString & myQuery.record[i];
}

note:
Let say myQuery is a SQL query which returns
all the fields and its value from a certain table

Looks simple huh? but the problem is, as the record count increases the performance decreases.
say myQuery returns 20 columns and 60,000 rows, using the code above will makes you wait forever. This kind of problem had never been totally solved until the .Net stringbuilder exist.
Since I am not using .Net, I have to find my own way to fix this problem.

What happen behind the code is that, the code creates a new myString object containing the result of the concatenation and discards the old myString. The value of myString is now then temporarily saved in a single memory buffer. So if the loop is repeated several times, the value of myString goes larger and for every 1 loop, the code retrieves that large data from a memory and then saves it again and again and again. And as the data inside the myString goes larger, the performance slows down.

I came with this idea wherein instead of saving the data in a single buffer, why not divide the data and saves it into different memory buffer, in that case, the size of the data in a single buffer will never be too large for it to slow down the performance of the system.

here’s the new code:


PSEUDO CODE

var myString=”";
var x=0;
var j=1;
var myString[1]=”",….myString[20];

for(i=1;i=20;i++){
     x=myQuery.recordCount*i/20;
     for(j;myQuery.recordCount;j++){
          myString[i]=myString[i] & myQuery.record[j];
          if(j==x){
               break;
          }
     }
}

myString = myString[1] & myStrinf[2]……..myString[20]

This code will divide the data into 20 parts and assign each data to 20 different variables.
Once the looping is done, all the data will be set into a single variable called myString.

Using these codes, it changed my waiting time from 15mins to less than a minute.

SQL Union All

Published
by
rs
on July 20, 2006
in SQL and Technical
. 0 Comments

I was working in this certain module last week wherein I will create a web app page that will generate monthly reports. Everything was fine and I manage to do it with an ease until the boss checks my work, On the report generated, there was a certain field column wherein NULL values are present, though some has a value, still it is impossible because that field is required in the form, and so the 1st problem occurs

In that module, what I did is I created a views in sql wherein it contains the computations of the data selected from a certain table, and I also added a field named “officerFld” which will contain an officer’s name selected from a certain field from another table.

I started to investigate the problem and found out that the officer’s name is stored in two different tables wherein I only selected from one of those tables. So I started searching for a way on how to select those officer’s name from the second table and insert it on the “officerFld” on the views that i’ve created, in that case, all the NULL values will be replaced by the officer’s name from the second table and all the officer’s name from the first table will still remain in the “officerFld” found on the views that i created.

Thanks to kiko(my older brother), who gave me the idea to use the UNION ALL command in sql. Well, I’m just starting to learn sql so don’t expect me to know all the commands. But now I’m starting to acquire knowledge so that’s what i did, i used the UNION ALL command in sql to combine the results of my two queries together and I used LEFT OUTER JOIN to combine it to my views.

Coldfusion

Published
by
rs
on July 11, 2006
in Uncategorized
. 0 Comments

I work at a company where coldfusion is the main tool in web applications development. Though I am using ASP before I was hired, I still have to be focus on coldfusion so I have no choice but to learn it starting from the basics. First I decided to study the idea of how does coldfusion connects to the database, and I was amaze on how easy it is compare to other language. All you have to do is open the coldfusion administrator and and go to ODBC page and you are now ready to add a path to your database. See image below.

cfusion0
this is the ODBC page where you can choose what kind of database you will add

cfusion1
this is the create ODBC page where you will add your data source

cfusion2
this is the edit ODBC page where you can set and edit the properties of your data source

After you finished adding your data source, you can now add a coldfusion tag to your HTML to create your query. see code below.

  <CFQUERY NAME=”GetRecords” DATASOURCE=”YourDataSourceName”>
      SELECT lastName, firstName
      FROM TableName
      ORDER BY lastName, firstName
  </CFQUERY>

now to display the output, simply use the cfoutput tag. see code below.

   <CFOUTPUT QUERY=”GetRecords”>
      #GetRecords.CurrentRow#) #lastName#, #firstName#<BR>
   </CFOUTPUT>

# is equal to the % of ASP
GetRecords.CurrentRow - number of current record being displayed

sample output:

1) Abraham, Martin
2) Banks, Michelle
3) Jackson, Michael

see how easy and fast it is to access your database using coldfusion? much easy than ASP. But still ASP is the best for me. I don’t know if coldfusion has any limitations yet, I’m just starting to learn it though.

Lichtenstein inspired pop art!

Published
by
rs
on July 11, 2006
in Arts
. 9 Comments

Roy Lichtenstein inspired pop art! a cool digital art inspired by Roy Lichtenstein style using photoshop. some kind of like turning your picture into cartoons. It’s easy to do but requires a lot of patience. just visit melissaclifton.com for the tutorials. I’ve created my own lichtenstein inspired digital art, Juz check it out!


LICHTENSTEIN INSPIRED POP ART