The Zig-Zag SQL query

Once I was asked to solve a very peculiar problem. The problem was to find the employee_id and the corresponding current month salary and previous month salary in a single row. The table looks like this:

employee_id	job_id	department_id	date_id	amount_credited
206	AC_ACCOUNT	110	20080731	8500
206	AC_ACCOUNT	110	20080431	8600
100	AD_PRES	90	20081131	26000
101	AD_VP	90	20080731	19000
102	AD_VP	90	20081231	20000
102	AD_VP	90	20080631	18000
121	ST_MAN	50	20080431	8500
102	AD_VP	90	20080831	20000
118	PU_CLERK	30	20081131	2700
118	PU_CLERK	30	20080431	2800
121	ST_MAN	50	20080531	8200
206	AC_ACCOUNT	110	20080831	8600
206	AC_ACCOUNT	110	20080631	8400
100	AD_PRES	90	20080531	24000
100	AD_PRES	90	20080431	27000
100	AD_PRES	90	20080331	26000
100	AD_PRES	90	20080231	25000

…………………………………………………………………

……………………………………………………………………

………………………………………………………………

…………………………………………………

…………………………………………………………..

P.S: Don't be astonished by seeing the date_id. It is made like that to ease the understanding. The date_id is not possible in all cases, but assume them to be correct for simplicity and making life easier.

Here, amount_credited, is the current month salary.

How to go about this SQL puzzle? I did it like this:

======================================================================

select aa.EMPLOYEE_ID,aa.DATE_ID, aa.AMOUNT_CREDITED "THIS_MONTH", bb.AMOUNT_CREDITED "LAST_MONTH"

from table_given aa, table_given bb

where

aa.EMPLOYEE_ID = bb.EMPLOYEE_ID AND

aa.DATE_ID=bb.date_id+100

UNION ALL

select distinct aa.EMPLOYEE_ID,aa.DATE_ID, aa.AMOUNT_CREDITED "THIS_MONTH", null "LAST_MONTH"

from table_given aa, table_given bb

where

aa.DATE_ID-100 NOT IN (Select distinct DATE_ID from new_fact_hr)

order by 1,2;

======================================================================

In this I have taken into account the relationship among date_id for formulating this query. The date_id for each month differs by 100.

e.g. 20080331

2008

03

31

Year Month Day

P.S: Don't be astonished by seeing the date_id. It is made like that to ease the understanding. The date_id is not possible in all cases, but assume them to be correct for simplicity and making life easier.

This query can be run on any database, which follows SQL standards. The zig-zag output produced is as follow:

employee_id	date_id	This Month	Last Month
100	20080131	24000
100	20080231	25000	24000
100	20080331	26000	25000
100	20080431	27000	26000
100	20080531	24000	27000
100	20080631	25000	24000
100	20080731	26000	25000
100	20080831	27000	26000
100	20080931	24000	27000
100	20081031	25000	24000
100	20081131	26000	25000
100	20081231	27000	26000
101	20080131	17000
101	20080231	18000	17000
101	20080331	19000	18000
101	20080431	20000	19000
………………….	………………………………….	…………………….	……………….
………………….	………………………………….	…………………….	……………….
………………….	………………………………….	…………………….	……………….
………………….	………………………………….	…………………….	……………….
………………….	………………………………….	…………………….	……………….

However, this query can be done more effectively with a shorter query if we use ORACLE database. It has many proprietary SQL commands, which is much advanced than SQL standards. I leave this up to you to try your hand on. Hint: Use Oracle SQL for Analytics (e.g. LAG, PARTITION OVER etc.). Good Luck to you.

Perl Vs Python Fight

I was confused as to what to start with? Perl or Python. Though I had done Perl before but wanna give a chance to Python too, if it outperforms Perl. I browsed through the web, and compiled the following pros and cons for both of them:

1) Python tends to focus more on code aesthetics than performance; as a result, Python OpenGL developers continue to struggle with overcoming Python's array handling performance issues.

2) Perl is much simpler to port to/from C/C++/Java

3) Perl has a much larger repository of open source libraries

4) Perl has much stronger/faster string handling (dynamic shader programs)

5) Perl simplifies embedding values within text blocks

6) Every function in Python can easily be performed in Perl; the converse is not true, particularly in terms of string handling.

7) Python has no defined global constants

8) Python has no forward referencing/prototypes

9) Python has no simple method of retrieving a dictionary/hash element's key

10) Python's print has no global method for enabling unbuffered I/O

11) Python's formatted print requires lists to be first converted to tuples

12) Python's print has no means to suppress both trailing new lines and spaces

13) Python has no ifdef method (short of reformatting) to block out prototype code

After going through these comparisons I decided to stick with Perl only, and learn Python to find the above truth only.