Shantanu's Blog

Database Consultant

April 27, 2006


Power Point

Desktop Computer & 17" monitor use anything between 140-330 watts. Thus the power saved by switching computers off can keep only 2 light bulbs on!

Microwave uses 10 to 50 times more power than a computer where as AC uses 4 to 10 times more power. So if you are keen on saving power, avoid using Microwave, AC and washing machine. See the chart here...

1) Switch off the monitor (NOT the computer) when the computer is not in use.

2) Set the Power settings on your computer to automatically go into Sleep/Standby mode after 15 minutes or so of inactivity. * Screen saver doesn’t save power *

a) Right click anywhere on the desktop, choose properties
b) Choose Screen Saver Tab and click on settings
c) Choose the appropriate power scheme.
d) Change the "power button" setting on "advanced" tab to standby if you think that you might inadvertently press the power button instead of Sleep button on the keyboard.

3) Use laptop computers. They use a lot less energy than desktops. (just 45 watts! less power than a ceiling fan!)

4) If you use a desktop, use a trendy, space saver, flat screen and sleek "LCD monitor". It uses 50% less energy than traditional, old CRT's.

The first rule is VERY important. Don't switch off the computer when you can just switch off the monitor. The CPU will keep using power, but not more than the power consumed by a light bulb!

i) This trick will save you a lot of time that the computer takes to start and shut down.
ii) You will also save your time (and dialing configuration) to get connected to the net.
iii) You can keep the frequently used applications like IE and word open. (You can even keep the frequently used sites in IE and files in word open!)

iv) Last but not the least (and the most important reason) the temporary memory will not be reset if you don't switch off the computer. It means the ignored words in spell check, recently used websites and cookies passed on to your computer are preserved. It saves a lot of time and hassle.

Inputs from Yahoo Answers

April 26, 2006


Online spreadsheets reviewed

I had written about website that allows us to host csv and excel files online.

Thinkfree's online service was reviewed as well, but I do not recommend it anymore since it takes a lot of time to load the Java Runtime.

Free services:
1) Cost: free
2) Advantage: Excellent charts,
3) Sharing: One click sharing with public
4) Remark: Good for personal use and web designers
1) Cost: free
2) Advantage: autosave, old versions,
3) Sharing: public sharing with or without password
4) Remark: Good for office use
1) Cost: free
2) Advantage: Easy copy paste from excel, create charts
3) Sharing: Allow public sharing
4) Remark: Good for non-critical data

Paid services:
1) Cost: 1 sheet free, $10 per month for 10 sheets.
2) Advantage: Excellent Sort and import options. Create calendar or Map.
3) Sharing: Useful for a group upto 5. No Public sharing.
4) Remark: Good for limited number of sheets / users
1) Cost: 30 days free trial, $10 per month for 3 sheets.
2) Advantage: It supports excellent group by, sort and import functions just like excel.
3) Sharing: Useful for a group upto 5. No Public sharing.
4) Remark: It looks and feels like Database management software rather than online spreadsheet.

April 09, 2006


MySQL Case Study - 119

Pivot + CrossTab

I have a table in the form

year | season | hits

2004 | Summer | 42
2004 | Autumn | 43
2005 | Spring | 51
2005 | Summer | 52
2005 | Autumn | 53
2006 | Spring | 61

I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form

year | Spring | Summer | Autumn
2004 | ------ | ----42 | ----43
2005 | ----51 | ----52 | ----53
2006 | ----61 | ------ | ------

Could anyone suggest a mysql query to generate the required output?

Sorry about bad formatting of query but this board doesn't seem to accept bbcode.,81701,81701#msg-81701

April 08, 2006


Kasparov No Longer Number One

Having ruled the chess world for over 20 years as the world's top player, and holding the record for being the world's number one ranked player 23 times, GM Garry Kasparov finally drops from the top spot due to inactivity in FIDE's April 2006 Rating List. Taking his place at the number one spot is the current FIDE Chess Champion GM Veselin Topalov with a rating of 2804.

At his peak, in July 1999, GM Kasparov reached and incredible rating of 2851 which has yet to be toppled by anyone else. However, Topalov and GM Viswanathan Anand are within possible reach of breaking this mark if they continue their amazing performances. Could Topalov and Anand become big rivals as we had with Karpov and Kasparov?

April 07, 2006


MySQL Case Study - 118

I'm having a hard time with a query and I thought someone might have a good time taking a crack at it.

I have a table that we'll call Options.

it looks like this.

style int
sequence int
optionCode varchar
optionDesc tinytext

There are no keys in this table. (i didn't make it and I can't augment the schema).

Here's a sample of what the table might look like

276........15............AAA.............lah de dah de do
333........08............BBB.............lah de dah de do
277........15............BBB.............lah de dah de do
250........15............CCC.............lah de dah de do
280........15............DDD.............lah de dah de do

What I want to do is ultimately choose the style ID that has all of it's option codes within this list ('AAA', 'BBB', 'CCC', 'DDD') Right now (since I made this fake table) I know that style I want is 276.

276 shows that it has all 4 of those option codes spread out on 4 different rows. Other styles have *SOME* of those option codes, but not all of them.

How would I select that single style that contains All the values in that list without selecting the styles that conatin SOME of those values.,81353,81353#msg-81353


MySQL Case Study - 117

Calculate Percentage:

I'm very new to MySQL and have a query I'm trying to achieve, if anyone can point me in the right direction, I would greatly appreciate it. I have a table created to store print job logs from a print server:

Table = printLogs

logNum mediumint(8)
logDateTime datetime
docNum tinyint(3)
docName varchar(200)
owner varchar(25)
printerName varchar(25)
printerPort varchar(50)
sizeInBytes varchar(50)
pagesPrinted smallint(10)

What I'm trying to do is find a query that, based on a 'printerName', will display 'owner' and percentage derived from the total number of pages printed to that 'printerName' for a given date range.

Something like this:

| owner | Percentage of total jobs |
| Mary | 50% |
| John | 30% |
| Kim | 10% |
| Sue | 10% |

Each row of the table is a different print job and can be one of about 20 different owners who have printed any number of pages to any of 10 printers. The main reason for the query is to be to determine which group/owner uses each printer the most.

Thanks for ANY suggestions,,81298,81298#msg-81298


MySQL Case Study - 116

I'm a bit stomped with something I would like to do in mysql, but not sure how I should do it.
Perhaps it's not even possible, I'll try to explain.
I have two tables, Rents and Vechicules.
In the Rents table I have these fields, CustomerId, startdate, enddate, vechicleId as int, date, date, int resp.
The Vechicules table contains the fields VechiculeId and licenseplate.

So there's a link from the Rents to the vechicules table with vechiculeId.
With this i could see which customer has which car in a certain start to end date,
but also, because there's a carpooling scheme and insurances we can have 2 or more customers renting the same car.

And I want to be able to search in the Rents table with the Customer id, a start date and an endate and return all the rows where that certain customer is the sole renter.
No with the carpool we would have in the Rents table:
2 - 04/04/06 - 04/11/06 - 6
3 - 04/04/06 - 04/31/06 - 6

So customers 2 and 3 are carpooling from 04/04 to 04/11 and then after that customer 3 is the sole renter from 04/15 until 04/31.

Long dragged out question, can someone help out with this query? I really don't know whether it even possible with mysql.,81050,81050#msg-81050


June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021  

This page is powered by Blogger. Isn't yours?