Simply Coder

Green Environment Q&A site proposal

2010-08-05T23:29:00.000-07:00

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!

Android Eco 1.3 - new bright design

2010-08-04T23:22:00.001-07:00

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

direct link

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

direct link

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

direct link

Android Eco by max.gontar@gmail.com is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
Based on a work at www.panda.org.

Please give your feedbacks!

Press Release for Ecology :)

2010-07-30T20:37:00.000-07:00

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!

Saving energy color scheme on OLED (Nexus One)

2010-07-27T22:15:00.000-07:00

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.

Android ecoTips - Tips to save the Nature

2010-07-26T22:59:00.000-07:00

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

Please give your feedbacks!!!

Android. Green. Ecology. Lamp.

2010-06-30T23:55:00.000-07:00

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

Please give your feedbacks!!!

Fun with SQL (games, painting, puzzles)

2010-01-24T01:00:00.000-08:00

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

Lionel Clarke 3D Maze Game

CraigD

Battleship (MS SQL 2008 GEOGRAPHY and GEOMETRY)

Adam Haines - Tic Tac Toe T-SQL Style

Kevin Duan - A Sudoku solution with set based T-SQL utilizing binary operators

Steve Wint - SQL Sudoku

Steve Wint - T-SQL Checkers

mySQLgame (online game)

Drawing with SQL

Roji Thomas - T-SQL Drawing

Graeme Job - T-SQL Mandelbrot

Dean Swift - Mandelbrot Fractals Using Stored Procedures

Gregory Stark - Mandelbrot in Postgres with recursive queries

SQL Puzzles

A bunch of puzzles from Lionel Clarke blog: 1|2|3|4|5|6|7|8|9

And of course Joe Celko's Puzzles

Some SQL Techniques

2010-01-24T00:37:00.000-08:00

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

Simply Coder

Green Environment Q&A site proposal

Android Eco 1.3 - new bright design

Press Release for Ecology :)

Saving energy color scheme on OLED (Nexus One)

Android ecoTips - Tips to save the Nature

Android. Green. Ecology. Lamp.

Fun with SQL (games, painting, puzzles)

SQL Games

Drawing with SQL

SQL Puzzles

SQL Easter eggs

Songs about SQL

Other

Some SQL Techniques