Thursday, August 5, 2010

Green Environment Q&A site proposal

I've took a shot and register a Green Environment Q&A site proposal on Area51:
Green environment
Proposed Q&A site for people interested in stuff recycling, products composting, trash utilization, alternative energy, sustainable economy, wild nature saving, green lifestyle etc. I know there are similar proposals but this one is more general thus may grow faster.


If you feel interested, please go there and follow!

Wednesday, August 4, 2010

Android Eco 1.3 - new bright design




From feedbacks I've learned design should be brighter and more readable.
Hope this 1.3 version will be more like that.

ecoFootprint


Calculate your personal ecological footprint, textual information taken from the Wildlife Fund site
- offline
- short 19 q and a full 42 q
- english & metric unit systems
- send userbar
- ways to improve
- ecological design
- Creative Commons License



ecoLamp


Calculate energy and environmental benefits of replacing light bulbs
- offline
- info from Wikipedia
- send userbar
- ecological design
- Creative Commons License
- Chinese translation by http://goapk.com



ecoTips


Widget, displays tips from Wildlife Fund site
- offline
- forward advice
- ecological design
- Creative Commons License
widget, click on Recycle for next tip
widget, click on text for tips list
list short click - move tip to widget
list long click - send advice





Please give your feedbacks!

Friday, July 30, 2010

Press Release for Ecology :)

Singapore Today Online
Be Superman, Save the world with these earth-friendly apps
by May Chua (2010/7/30)
ecoFootprint (Android)
Android users can try ecoFootprint. After taking the quick test (19 questions) or full test (42 questions), you will find out how many Earths the world would need if everyone lived like you, and what you can do to minimise your footprint.

...
ecoTips (Android)
Here's an app that lets you walk the talk. ecoTips presents tips on greener living from the Wildlife Fund site, divided into five categories - home, travel, office, market and garden. You can use this app to educate those around you by sending tips to them via social media, email, Bluetooth or SMS.


Well, it's like a nice word to me :)

And it's an online newspaper, no wood was cut down for printing!

Tuesday, July 27, 2010

Saving energy color scheme on OLED (Nexus One)

Just to repeat my question from SO:



To sum-up:
Ironic Sans: Ow My Eyes
they say it's not working for simple LCD
http://hackaday.com:
...using only the red pixels dropped the current use down to 35% of what the full color display was pulling...
WikiPedia - AMOLED:
For example, our measurement shows that a commercial QVGA OLED display consumes 3 and 0.7 Watts showing black text on a white background and white text on a black background, respectively.

Monday, July 26, 2010

Android ecoTips - Tips to save the Nature

From WikiPedia:
The World Wide Fund for Nature (WWF) is an international non-governmental organization working on issues regarding the conservation, research and restoration of the environment, formerly named the World Wildlife Fund, which remains its official name in the United States and Canada. It is the world's largest independent conservation organization with over 5 million supporters worldwide, working in more than 90 countries, supporting around 1300 conservation and environmental projects around the world.
...
The group says its mission is "to halt and reverse the destruction of our environment".


So it's really great org, they do the right stuff for human survival.
Also there is a list of tips on their website.

Since all text there is under Creative Commons license, I've made this small Android widget to display those tips. Enjoy!





Features:
  • works offline
  • forward tip text over email, twitter, SMS etc
  • ecological energy-saving design
  • Ukrainian and English localization
  • Creative Commons license

qrcode

Please give your feedbacks!!!

Wednesday, June 30, 2010

Android. Green. Ecology. Lamp.

Several years ago I've decided to upgrade my house lamps from light bulbs to CFL, about 7 items. I knew it will improve my electricity bill, but I never done any calculations.

Lately I was more interested in environment saving, and that brought me to idea of app which will calculate all advantages of energy saving lamps.

By some accident (maybe not-without-StackOverflow) I was gifted with N1. Thank you Google!

With ability to create ecoLamp it would be a crime not to try.


Features:
  • works offline
  • lamp types description with photos and text from Wikipedia
  • calculates energy saving, equivalent coal, gas and oil, CO2 emission, lamps waste and money saving
  • generates userbar which you can add to email, Picasa etc

  • settings for electricity tariff and measurement units
  • Ukrainian and English localisation

qrcode

Please give your feedbacks!!!

Sunday, January 24, 2010

Fun with SQL (games, painting, puzzles)

Several days ago my question on StackOverflow was deleted by system: Fun with SQL (games, painting, puzzles). Since it was not a real question and would be deleted again if I repost it I decided to post it here on my own territory : )

If you think SQL is boring or dumb or limited, try this:

SQL Games

Drawing with SQL

SQL Puzzles

SQL Easter eggs

Songs about SQL

Other

Some SQL Techniques

1. Hierarchical tree formatting SELECT using CTE (MS SQL 2005)
Say you have some table with hierarchical tree structure (departments on example) and you need to output it in CheckBoxList or in Lable this way:
     Main Department
Department 1
Department 2
SubDepartment 1
Department 3

Then you can use such query:

WITH Hierarchy(DepID, Name, ParentID, Sort, Type) AS
(
-- First we will take the highest Department (Type = 1)
SELECT DepID, Name, ParentID,
-- We will need this field for correct sorting
Name + CONVERT(VARCHAR(MAX), DepID) AS Sort, 1 AS Type
FROM Departments WHERE Type = 1
UNION ALL
-- Now we will take the other records in recursion
SELECT SubDep.DepID, SubDep.Name, SubDep.ParentID,
CONVERT(VARCHAR(MAX), Sort) + SubDep.Name
+ CONVERT(VARCHAR(MAX), SubDep.DepID) AS Sort,
ParentDep.Type + 1
FROM Departments SubDep
INNER JOIN Hierarchy ParentDep ON SubDep.ParentID = ParentDep.DepID
)
-- Final select
SELECT DepID,
-- Now we need to put some spaces (or any other symbols) to make it
-- look-like hierarchy
REPLICATE(' ', Type - 1) + Name AS DepartmentName, ParentID, Sort
FROM Hierarchy
UNION
-- Default value
SELECT -1 AS DepID, 'None' AS DepartmentName, -2, ' ' AS Sort
-- Important to sort by this field to preserve correct Parent-Child hierarchy
ORDER BY Sort ASC

Other samples

Using stored procedure:
Working with hierarchical data in SQL Server databases
Plain select for limited nesting level:
More Trees & Hierarchies in SQL
Another one solution using CTE:
SQL Organization Chart

2. Last Date selection with grouping - using RANK() OVER
Imagine some Events table with ID, User, Date and Description columns. You need to select all last Events for each User. There is no guarantee that Event with higher ID has nearest Date.

What you can do is play around with INNER SELECT, MAX, GROUPING like this:

SELECT E.UserName, E.Description, E.Date
FROM Events E
INNER JOIN
(
SELECT UserName, MAX(Date) AS MaxDate FROM Events
GROUP BY UserName
) AS EG ON E.Date = EG.MaxDate

But I prefer use RANK OVER:

SELECT EG.UserName, EG.Description, EG.Date FROM
(
SELECT RANK() OVER(PARTITION BY UserName ORDER BY Date DESC) AS N,
E.UserName, E.Description, E.Date
FROM Events E
) AS EG WHERE EG.N = 1

It's more complicated, but it seems to be more correct for me.

3. Paging using TOP and NOT IN

DECLARE @RowNumber INT, @RecordsPerPage INT, @PageNumber INT
SELECT @RecordsPerPage = 6, @PageNumber = 7
SELECT TOP(@RecordsPerPage) * FROM [TableName]
WHERE ID NOT IN
(
SELECT TOP((@PageNumber-1)*@RecordsPerPage) ID
FROM [TableName]
ORDER BY Date ASC
)
ORDER BY Date ASC

4. Set variable values in dynamic SQL with REPLACE

Instead of ugly

SET @SELECT_SQL = 'SELECT * FROM [TableName]
WHERE Date < ' + CAST(@Date, VARCHAR) + ' AND Flag = ' + @Flag

It's more easy, safe and readable to use REPLACE:

DECLARE @VAR_SQL VARCHAR(3000), @SELECT_SQL VARCHAR(3000)
DECLARE @Id INT
SET @Id = 3
DECLARE @Flag VARCHAR(1)
SET @Flag = 'X'
DECLARE @Date DATETIME
SET @Date = GETDATE()
SET @VAR_SQL =
'DECLARE @Date DATETIME
SET @Date = CAST(:Date AS DATETIME)
'
SET @SELECT_SQL = 'SELECT * FROM [TableName]
WHERE Id > :Id AND Flag = :Flag AND Date < @Date'

SET @SELECT_SQL =
REPLACE(@SELECT_SQL, ':Flag', QUOTENAME(CONVERT(VARCHAR, @Flag),''''))
SET @SELECT_SQL = REPLACE(@SELECT_SQL, ':Id', CONVERT(VARCHAR, @Id))
SET @VAR_SQL =
REPLACE(@VAR_SQL, ':Date', QUOTENAME(CONVERT(VARCHAR, @Date),''''))

PRINT(@VAR_SQL + @SELECT_SQL)
EXEC(@VAR_SQL + @SELECT_SQL)

5. DROP before CREATE
There are some good practices for writing stored procedures or functions, one of them is to include IF EXISTS ... DROP block in procedure creation script.

IF EXISTS
(
SELECT 1 FROM sysobjects
WHERE id = OBJECT_ID(N'[ProcedureName]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [ProcedureName]
GO

IF EXISTS
(
SELECT 1 FROM sysobjects
WHERE id = OBJECT_ID(N'[ScalarFunctionName]')
AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1
)
DROP FUNCTION [ScalarFunctionName]
GO

IF EXISTS
(
SELECT 1 FROM sysobjects
WHERE id = OBJECT_ID(N'[TableFunctionName]')
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1
)
DROP FUNCTION [TableFunctionName]
GO

--Talking about temporary tables:
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT, DATESTART DATETIME, DATEEND DATETIME)

6. Lot of dynamic sql, temp tables, and others on Erland Sommarskog's home page