clintz

P114 million worth 3 letter word

clintz — Tue, 23 May 2006 03:38:00 GMT

This is what happens when your manager doesn't know anything about software development. My first project here in my new company was in a rush and had to be finished in 3 months, so after my supervisor explained to me the details I proceeded with the development at once. This new project of mine by the way was already at its 3rd phase when i started. I just replaced the old programmer after she resigns at the middle of the project development. During the development, my manager told me that the system must be launched in a month so i was freaked out coz i know this project has a 3-month development time. After i explained it to my manager, she told that the client was rushing her and she promised the client that the system (my project) will be done in a month. So i worked all night and day and sleeping only atmost 3 hours. And thank God I finished the System 2 days before the deadline. And the problem starts here. I told my manager that it must be tested thoroughly and have a test run for atleast ten days. But after the quality testing and fixing some bugs found, the system was deployed after doing some test runs on two days. After a few days, they've found some minor bugs and its not a big problem. Two months after, i've received no feedbacks or error reports on the system and think that the system was running smoothly when one day our client drop a line and told my manager that their profit loss not just a little but 114 million pesos starting when the day they used the system i've developed. After that, our client stopped the system from operating until the problem has been solved. After reviewing the business rules of the system, i've found out that one SP has an extra word "NOT" in the WHERE statement. So instead of getting the correct data, the reverse are the one which are computed and returned. When I told my supervisor about this, he laugh at me and told me that that 3 letter word killed your whole System. After that incident, my manager (accounting graduate) started listening to her developers and studied how the software development works. And I learned also that its not just the error that pops up that we should check, more importantly, we should check and check and check if the data that were produced was correct specialy when it has a financial concern..

After this incident, i was too carefull when modifying objects on database..

T-SQL: Calendar

clintz — Wed, 21 Dec 2005 22:27:00 GMT

/************************************************************
Name : Clintz_SP_Calendar
Purpose: hmmmmmm
Created: 12212005

************************************************************/
create procedure Clintz_SP_Calendar
@Month int = ''
, @Year int = ''

if @Month = ''
set @Month = month(getdate())
if @Year = ''
set @Year = year(getdate())

declare @tblCalendar table (WeekNum int, Sunday int, Monday int, Tuesday int, Wednesday int, Thursday int, Friday int, Saturday int)
declare @PassedDate datetime, @DayName varchar(50)
declare @LastDayOfMonth int, @WeekNum int, @LastMonthWeekNum int, @cntr int

insert into @tblCalendar (WeekNum)
select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6

set @PassedDate = convert(varchar, @Month) +'/1/' + convert(varchar, @Year)
set @LastDayOfMonth = day(dateadd(d, -1, dateadd(m, 1, @PassedDate)))
set @LastMonthWeekNum = datename(wk, dateadd(d, -1, @PassedDate))

set @cntr = 0

while @cntr < @LastDayOfMonth
begin
  set @DayName = datename(dw, dateadd(d, @cntr, @PassedDate))
  set @WeekNum = datename(wk, dateadd(d, @cntr, @PassedDate)) - @LastMonthWeekNum + 1
   if @WeekNum < 0
    set @WeekNum = datename(wk, dateadd(d, @cntr, @PassedDate))

  if @DayName = 'Sunday'
   begin
    update @tblCalendar
    set Sunday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Monday'
   begin
    update @tblCalendar
    set Monday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Tuesday'
   begin
    update @tblCalendar
    set Tuesday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Wednesday'
   begin
    update @tblCalendar
    set Wednesday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Thursday'
   begin
    update @tblCalendar
    set Thursday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Friday'
   begin
    update @tblCalendar
    set Friday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Saturday'
   begin
    update @tblCalendar
    set Saturday = @cntr + 1
    where WeekNum = @WeekNum
   end

set @cntr = @cntr + 1
end

select datename(m, @PassedDate) as [Month], datename(yyyy, @PassedDate) as [Year]

select isnull(convert(varchar, Sunday), '') as Sunday
  , isnull(convert(varchar, Monday), '') as Monday
  , isnull(convert(varchar, Tuesday), '') as Tuesday
  , isnull(convert(varchar, Wednesday), '') as Wednesday
  , isnull(convert(varchar, Thursday), '') as Thursday
  , isnull(convert(varchar, Friday), '') as Friday
  , isnull(convert(varchar, Saturday), '') as Saturday
from @tblCalendar
where (isnull(Sunday, 0) + isnull(Monday, 0) + isnull(Tuesday, 0) +
  isnull(Wednesday, 0) + isnull(Thursday, 0) + isnull(Friday, 0) +
  isnull(Saturday, 0)) > 0

sp_calendar 10, 1999

T-SQL: SP to Import Excel file to MSSQL

clintz — Sat, 03 Dec 2005 00:17:00 GMT

create procedure SP_ImportExcelFile(
@Source varchar(1000)
, @SourceSheet varchar(100)
, @DestinationTable varchar(100))
as

declare @retval int
--check if file exists
EXEC master..xp_fileexist @Source, @retval output

if @retval = 0
begin
print 'file does not exist.'
return
end

-- check if worksheet exists and if not use Sheet1
if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]'
else
set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''
set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)

exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @Source + ''', ' + @SourceSheet + ')')

T-SQL: Adding description to columns

clintz — Fri, 02 Dec 2005 00:39:00 GMT

use the sp_addextendedproperty SP to add description to a column.

sp_addextendedproperty
    [ @name = ] { 'property_name' }
    [ , [ @value = ] { 'value' }
        [ , [ @level0type = ] { 'level0_object_type' }
         , [ @level0name = ] { 'level0_object_name' }
            [ , [ @level1type = ] { 'level1_object_type' }
             , [ @level1name = ] { 'level1_object_name' }
                    [ , [ @level2type = ] { 'level2_object_type' }
                     , [ @level2name = ] { 'level2_object_name' }
                    ]
            ]
        ]
    ]

e.g.

EXEC sp_addextendedproperty 'MS_Description', '<column description>', 'user', dbo, 'table', '<table name enclosed with single quote>', 'column', <column name not enclosed with single quote>

you can use anyname on [value] but if you want to see the description on Enterprise Manager, use 'MS_Description'.

to view the column properties, use the fn_listextendedproperty function.

e.g.

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '<table name enclosed with single quote>', 'column', default)

BOL: Property Management

Word Tip: Review side by side

clintz — Fri, 25 Nov 2005 02:28:00 GMT

A new feature of Word allows you to review documents side-by-side. This is especially helpful if you have two copies of the same document which was modified by two different people. To do this, you must first have the documents open. Then, you need to go to Window | Compare Side by side with <name of document>. Word will then show the two documents literally side-by-side one another.

Word Tip: Uppercase, lowercase, or initial capitals

clintz — Fri, 25 Nov 2005 02:28:00 GMT

this is a heading

Sometimes we need to make a particular text all capitals or all small letters or sometimes, all the first letters of the enter paragraph capitalized. To do this, you need to highlight the text that you need to capitalize or “un-capitalize”. Once you’ve selected the text, press the <SHIFT> <F3> buttons. You will then see the text you highlighted change the case from all letters capitalized, to all letters not capitalized and to all the first letters capitalized.

Word Tip: Lines, I forgot my lines

clintz — Fri, 25 Nov 2005 02:26:00 GMT

Sometimes users need to add horizontal lines to their documents in order to separate items or divide a document into groups. In order to do this, we would type in a series of hyphens, underscores or equal characters. There is a shortcut to do this and it would give a much better line instead of just the underscore (_) or hyphen (-) character.

To do this, just type three hyphens (-) and press the <ENTER> key. This will result in a horizontal line similar to the first line below. To draw a horizontal line that is thicker than the first line in the sample below, just type three underscores (_) and press the <ENTER> key. This will result in the second line as seen below. Lastly, to draw a double line, just type three equal signs (=) and press the <ENTER> key. The result is the third line below.

Word Tip: Paragraphs and more paragraphs

clintz — Fri, 25 Nov 2005 02:23:00 GMT

This is my first paragraph. As I was writing this I had a lot of ideas in mind, each idea came up fast so I had to write it as another paragraph. That way, I can just write everything up and worry about how to organize the thought later

Oops, I want to make this my second paragraph. So I have to move this up, between the first paragraph and the second paragraph.

This is my third paragraph. There are still a lot of ideas that are in my mind, such as what are we going to eat.

This is my second paragraph and again, I have a lot of ideas coming in. I type everything and organize the flow later.

Now, I have so many paragraphs and I can’t decide on how to organize them to make one flow. This is where the problem begins because I have to copy and paste a lot of times to get everything in one cohesive flow.

Whenever you write memos, you would usually type the contents first and worry about the flow of thought later on after you’ve typed all the things that you wanted in the memo. The next step would be to organize the memo so that it has one cohesive message. Sometimes, you need to rearrange the paragraphs such that the fourth paragraph would be made the second paragraph or the last paragraph be made the first paragraph. What I usually do is to make use of <CTRL> <C> and <CTRL> <V> (copying and pasting). But there is a better way to do that without having to copy and paste paragraphs. All you need to do is to highlight the particular paragraph that you want to move and press the <SHIFT> <ALT> keys and the up arrow () to move the paragraph upwards or <SHIFT> <ALT> and the down arrow () to move the paragraph downwards.

Word Tip: Keyboard Shortcuts for increase/decrease font

clintz — Fri, 25 Nov 2005 02:23:00 GMT

The size of the font is big

The size of the font is small

Are you annoyed whenever you need to change the size of the font of a particular text? What you would do is to highlight the text, go to the toolbar and change the font size. Sometimes, you need to do this for a couple of times before you get the font size that you want. There is a shortcut to this, all you need to do is highlight the text, press the <CTRL> <SHIFT> button and the greater than character (>) to increase the font size. Alternatively, you can press <CTRL> <SHIFT> and the less than character (<) to decrease the font size.

Word Tip: <ALT> <X> Combination

clintz — Fri, 25 Nov 2005 02:19:00 GMT

00A9

00AE

2122

263A

00A5

00A3

20AC

Were you faced with a situation where you had to insert a particular symbol within the document. What we usually do is to go to the Insert | Symbol menu and select the symbol from there.

Now you can do the same but this time, all you need to do is to remember the codes that are relevant to your work. To display the symbol, key in the code first and then press the <ALT> <X> key. The code will be changed to the symbol equivalent. Above is a list of some of the codes, try pressing <ALT> <X> after each one and see for yourself.

Word Tip: Bullets, Bullets, Bullets

clintz — Fri, 25 Nov 2005 02:16:00 GMT

Bullet point 1
Bullet point 2
Related to bullet point 2
Bullet point 3

Did you ever get annoyed whenever you used bullets and you had to place a sub-item under one of the items. The bullet follows and you had to make a backspace and then tab all your way to the area where you want the sub-item to be in. You can simplify this method by just pressing <SHIFT> <ENTER> after one of the bulleted items. This would allow you to place sub-items under the bullet without having to contend with the actual bullet symbol. To resume the entering data with bullets, just press <ENTER> this would make the bullets appear again.

Word Tip: Display more options with the <SHIFT> Key

clintz — Fri, 25 Nov 2005 02:16:00 GMT

If you have multiple Word documents that are open, you can immediately save all the documents without going through each one of the documents and pressing the save button. All you need to do is go to one document that is open and make sure to press the <SHIFT> key before click the File menu. The Save, option will be converted to a Save All option, allowing you to save all the documents in just one go.

Word Tip: Faster way to create tables (and add rows too!)

clintz — Fri, 25 Nov 2005 02:14:00 GMT

Ever wondered if there was an easy way of creating tables? Instead of clicking the table icon and dragging the number of columns and rows that you want, you can make use of the “plus” and “dash” characters. To do this, just start with a plus then a dash then a plus. It should be something like this: +-+

After entering the plus, make sure you press the <ENTER> key. This will result in a one column row. To make a two column row you just need to add another dash and a plus so that it would look something like this: +-+-+. Pressing enter after would result in a two column row.

The dash corresponds to the number of columns that you need in your table. Just remember to add a plus at the end and press the <ENTER> key.

Word Tip: Calculations in Word tables

clintz — Fri, 25 Nov 2005 02:11:00 GMT

Ever needed to put tables in your documents? Worse, you needed to make computations on them too! Well you can actually can put computations on your tables in Word.

In the last column of the table (or wherever you need to perform the calculation) you need to put the formula. To do this, go to Table | Formula, put the formula as you would normally do in Excel (e.g. =SUM or =A1+B1). When using the table, you access the rows and columns as you would normally do in Excel (i.e. the first column on the first row is A1, the second column is B1 and so on). So, if you had a table having one rows and four columns and you wanted to add up the entire row, just put the formula as: =A1+B1+C1+D1.

If you change the values in one of the columns you do not need to enter the formula again, all you have to do is go the columns where the formula is, right click on it and select Update field. The field will be recomputed for you.

T-SQL: coalesce

clintz — Fri, 25 Nov 2005 01:48:00 GMT

instead of using mulriple if's like:

if @user = ''
begin
  if @age = ''
   begin
    select * from customers
   end
  else
   begin
    select * from customers where age = @age
   end
end
else
begin
  if @age = ''
   begin
    select * from customers where [name] = @user
   end
  else
   begin
    select * from customers where [name] = @user and age = @age
   end
end

you can use the COALESCE() like:

if @user = ''
set @user = null
if @age = ''
set @age = null

select * from customers where [name] = coalesce(@user, name) and age = coalesce(@age, age)