Shantanu's Blog

How do I create a calendar from dates 2000-01-01 to 2010-12-31 using SQL commands?

DROP TABLE IF EXISTS _t_helper;
DROP TABLE IF EXISTS calendar_helper;
CREATE TABLE _t_helper (id TINYINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO _t_helper (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

DROP TABLE IF EXISTS calendar_helper;
CREATE TABLE calendar_helper (
dt DATE NOT NULL PRIMARY KEY,
yr MEDIUMINT UNSIGNED NOT NULL,
mon TINYINT UNSIGNED NOT NULL,
dom TINYINT UNSIGNED NOT NULL,
lbl CHAR(15) NOT NULL,
KEY(yr, mon, dom)
);

SET @counter := -1;
SET @from := '2000-01-01';
SET @to := '2010-12-31';
SET @thedate := @from;

INSERT INTO calendar_helper
SELECT
@thedate := (@from + INTERVAL @counter := @counter + 1 DAY),
YEAR(@thedate),
MONTH(@thedate),
DAY(@thedate),
DATE_FORMAT(@thedate, "%M %e")
FROM _t_helper t1, _t_helper t2, _t_helper t3, _t_helper t4
WHERE
@thedate < @to;

I have a large list of daily stats from various systems, i.e.

Table Stats
SystemID | tagID | dtStamp | Value
S001 : T100 : 2006-03-23 : 50
S001 : T101 : 2006-03-23 : 2
S001 : T100 : 2006-03-24 : 53
S001 : T101 : 2006-03-24 : 5
S001 : T100 : 2006-03-25 : 60
S001 : T101 : 2006-03-25 : 8
S001 : T100 : 2006-03-26 : 65
S001 : T101 : 2006-03-26 : 12
S002 : T100 : 2006-03-23 : 10
S002 : T101 : 2006-03-23 : 1
S002 : T100 : 2006-03-24 : 12
S002 : T101 : 2006-03-24 : 2

I need the date of the last record of data for each site for each tag. (i.e. the most recent data for each site)

I tried using max(dtStamp), but it found the biggest date in the table and tied it to every system/tag the same. i.e. it showed 2006-03-26 for S002/T101, even though I know I don't have data for that site on that day.

http://forums.mysql.com/read.php?10,79631,79631#msg-79631

I have been asked to write a query that lists all pairs of supplier numbers such that both suppliers are located in the same city. I have also been asked not to include any redundant or reversed pairs.

Here is a sample table:
+--------+---------+---------+----------+
| SUPPID | SNAME | SSTATUS | SCITY |
+--------+---------+---------+----------+
| S1 | Adams | 20 | Toronto |
| S2 | Barnes | 10 | Ottawa |
| S3 | Collins | 30 | Ottawa |
| S4 | Jones | 20 | Toronto |
| S5 | Emery | 30 | Kingston |
+--------+---------+---------+----------+

The Sample Return would be:
+--------+---------+
| SUPPID | SUPPID |
+--------+---------+
| S1 | S4 |
| S2 | S3 |
+--------+---------+

I have no idea how to tackle this query, any help would be appritiated.
We need a join of the table with itself, the join condition is obviously that the scity column is the same. Additionally, the suppid should differ so we don't get a row like S1 | S1.
Then we're left with reversed rows, to solve this, we use LEAST and GREATEST to have the smaller suppid always in the left column, and the bigger suppid in the right column. Finally, we select only distinct rows.

http://forums.mysql.com/read.php?10,79479,79479#msg-79479

Left Join and coalesce:
I have two tables as follows:

table name: finalexam
fields: studentid, module, marks

table name: contassessment
fields: studentid, module, assesstype, marks

there is a one-to-many relationship between finalexam and contassessment- One final exam module can have more than one assessment. The primary key in contassessment is (studentid, module, aseesstype).

Any select statement to select studentid, finalexam marks and continuos assessment marks summed up for each module?

http://forums.mysql.com/read.php?10,78999,78999#msg-78999

distinct row

I have table A:
============================
lang | id | sentence
--------------------
'de' | 1 | 'german word 1'
'de' | 2 | 'german word 2'
'en' | 1 | 'english word 1'
'en' | 2 | 'english word 2'
'en' | 3 | 'english word 3'
============================

I want with a query return
============================
lang | id | sentence
--------------------
'de' | 1 | 'german word 1'
'de' | 2 | 'german word 2'
'en' | 3 | 'english word 3'
============================

The meaning: For each id, if german sentence exists, return it, else return english, BUT NOT BOTH.

So, return DISTINCT but ONLY for id.
The query, until now:
SELECT * FROM A WHERE lang='en' OR lang='de' ORDER BY IF(lang='en',1,0);
Can you fix it please?

http://forums.mysql.com/read.php?10,78597,78597#msg-78597

Joining 3 tables:
I'm relatively familiar with SQL syntax but am not sure about the use of JOIN.

Here's what I have... I have a database of a collection of books. There are three tables: a primary table containing the majority of information about the books; a table containing a list of "subject ID's" versus "subject descriptions" (example, one record states that subject_id = 1 means "mystery"); and a table containing multiple records per book ID specifying which subjects that book belongs to.

Here's a basic, simple set of data to give the idea of what I have:

Table books
fields Book_id, Author, Title, Description
Table subjects
fields Subject_id, Subject_desc
Table sub_lookup
fields book_id, Subject_id

Let's say in books we have this data:
1,"MySQL Team","How to use MySQL","This book discusses the basics of MySQL."
2,"PHP.Net","Beginner's PHP","This book describes PHP."
3,"SQL Developers Committee","Buying SQL Servers","This book describes good MySQL servers for purchase."

In subjects:
1,"Computing"
2,"MySQL"
3,"PHP"
4,"Shopping"

In sub_lookup:
1,1
1,2
2,1
2,3
3,2
3,4

Now, I want to be able to search like this pseudocode: Show me every book from the books table, where its entry in the sub_lookup table indicates the book is about computing. So if I asked for all books that are of the Computing subject, I'd want back books #1 and #2. If I asked for all books about MySQL, I'd want back books #1 and #3. If I asked for books about shopping, I'd only want book #3. And so on.

Also, it'd be convenient to not have to do a query each time I get a subject ID out of the sub_lookup table just to get its plaintext equivelant.

I know you can use JOIN to accomplish things like this, but I'm not sure where to start with it.

Advice please?

http://forums.mysql.com/read.php?10,78552,78552#msg-78552

Finding conflicting event times:
I have an event table. This table stores basic info about calendar events. I am looking for a way to find calendar conflicts.

Example data
+-------+---------------------+---------------------+
| event | startdate | enddate |
+-------+---------------------+---------------------+
| 1 | 2006-03-24 13:00:00 | 2006-03-24 14:00:00 |
| 2 | 2006-03-25 13:00:00 | 2006-03-25 14:00:00 |
| 3 | 2006-03-26 10:00:00 | 2006-03-26 14:00:00 |
| 4 | 2006-03-26 08:00:00 | 2006-03-26 11:00:00 |
+-------+---------------------+---------------------+

I am looking for a query that will list events as conflicting or having overlapping times.
In the case above Event3 and Event4 overlap and occur in the same time bracket.

Any thoughts?

http://forums.devshed.com/mysql-help-4/finding-matching-conflicting-event-times-337399.html

Count desired rows:
I need to add the homescore and awayscore and count the number of times the total score is =<2 and the number of times the total score is =>3

+-------------+-------------+-------------+-------------+
| hometeam | awayteam | homescore | awayscore |
+-------------+-------------+-------------+-------------+
| sampdoria | fiorentina | 2 | 1 |
| fiorentina | sampdoria | 0 | 1 |
| ascoli | Milan | 0 | 3 |
+-------------+-------------+-------------+-------------+

Thanks for taking the time.

http://forums.devshed.com/mysql-help-4/count-desired-rows-337275.html

Derived Table:

I’m trying to extract distinct rows and the sum of a certain column of only those rows from a table. For example, given the MySQL statement:

select DECISION, count(distinct B_SSN, APP_DT), sum(REQUESTED_AMT) from table where APP_DT >= '20060322' and APP_DT <= '20060322' group by DECISION

With the table below:

DECISION – B_SSN – REQUESTED_AMT – APP_DT
PASS - 111111111 – 20000 - 20060322
PASS - 111111111 - 20000 - 20060322
PASS - 222222222 – 12000 - 20060322
PASS - 333333333 - 1200 - 20060322

Gives me this:
PASS – 3 – 53200

Whereas I’m looking for this:
PASS – 3 – 33200

Any tips or suggestions for getting SUM to act the way I want it to? Can it act that way? Am I going to have to retrieve all distinct rows and calculate this manually?

http://forums.mysql.com/read.php?10,78042,78042#msg-78042

Calculate time:
I am building a database that collects entry and exit of employees. Daily, the worker will log in when arriving at the workplace, log out for lunch, log in when returning from lunch, and log out to exit the workplace. There are just two tables in the db: tb_user and tb_logging_system.

Below, it is the structure of my tb_logging_system:

login id INT AUTO_INCREMENT (PK)
login_user_id INT NOT NULL (FK)
login_date_time_log_entry DATETIME NOT NULL
login_date_time_log_exit DATETIME NOT NULL

I have created the following SELECT to calculate the number of hours worked by each employee:

SELECT user_name,
TIMEDIFF(login_date_time_log_exit,login_date_time_log_entry) as Hours_Worked
FROM tb_user
INNER JOIN logging_system ON user_ID = login_user_id;

Sample data:

Name/ Entry / Exit / Hours_Worked
===========================================================
John / 2005-09-05 08:30:27 / 2005-09-05 12:19:33 / 03:49:06
John / 2005-09-05 13:29:35 / 2005-09-05 18:08:37 / 04:39:02
Mary / 2005-09-05 08:16:44 / 2005-09-05 12:40:09 / 04:23:25
Mary / 2005-09-05 14:22:00 / 2005-09-05 18:04:09 / 03:42:09

However, when I tried to implement a little further on this query, I didn’t achieve the results expected. The query is not grouping my results by employee or calculating the sum for the day of work. I have tried everything, including CAST( ) to convert the result of the TIMEDIFF to time; nevertheless, all my efforts have been unsuccessful. Below, it is the problematic query:

SELECT user_name,date(login_date_time_entry) as Date_Log,
SUM(TIMEDIFF(login_date_time_log_exit,login_date_time_log_entry)) as Hours_Worked_TOTAL
FROM tb_user
INNER JOIN logging_system ON user_ID = login_user_id
GROUP BY user_name, date(login_date_time_entry);


Sample data (what is being generated):

Employee / Date_Log / Hours_Worked_TOTAL
================================================
John / 2005-09-05 / 7
Mary / 2005-09-05 / 7


Below, it is the data that I am looking for:

Employee / Date_Log / Hours_Worked_TOTAL
================================================
John / 2005-09-05 / 8:28:08
Mary / 2005-09-05 / 8:05:34


I don’t understand what is happening. Can somebody help me?

http://forums.mysql.com/read.php?10,78016,78016#msg-78016

The "monthly" sum total

I thought this would be straight forward but I am having no luck with my query

SELECT SUM(Amount) AS total
FROM office
WHERE datepaid BETWEEN '2005-01-01' AND '2005-12-31'

I have several rows of data and one column that I want to create a summary for by month. For this I am looping 12 times and storing the data in an array.

However, my result is incorrect. If I go line by line and total up the year I get a greater number. Is there something obviously wrong in my query?

http://community.livejournal.com/mysql/90996.html

Table joins and date difference

I am trying to formulate a complex query to get some good info straight from SQL, rather than post-processing in XL.

I have a table as follows:
EventID Integer (key)
ActionID Integer, 1 = event on, 2 = event off
dtStamp Date

I want to get the duration of each Event.

I.e. I need, for each Event:
dtStamp(actionID = 2) - dtStamp(actionID = 1)

I was going to try:

>create view EventOnTime as select EventID, dtStamp as EventOnDtStamp from EventLog where ActionID = 1;

>create view EventOffTime as select EventID, dtStamp as EventOffDtStamp from EventLog where ActionID = 2;

>select eventOnTime.EventID, eventOnTime.EventOnDtStamp - eventOffTime.EventOffDtStamp as EventDuration where eventOnTime.EventID = eventOffTime.EventID;

... but I can't create views with this version of SQL (server version: 4.0.20-standard-log)

http://forums.mysql.com/read.php?10,75618,75618#msg-75618

Crosstab query

I use the following query:

SELECT category, SUM(amount) AS amount FROM table GROUP BY category;

so I get the following table:

| cateogory | amount |
| A | 10 |
| B | 5 |
| C | 16 |

but I actually would like to have a table like this:

| A | B | C |
| 10 | 5 | 16 |

the problem is, i don't know all the existing categories. (if i would know it i could use IF(...='A')).

how can I do this?

http://forums.mysql.com/read.php?10,75805,75805#msg-75805

How to lookup values in different sheets based on the sheet name?

By adding the following vlookup formula in the cell c2, excel will find the value in the respective sheet mentioned in the column A.

=VLOOKUP(B2,INDIRECT(A2&"!A:B"),2,FALSE)
~~~
Question: What if I want to print some other word in the column A instead of nse for e.g. nseindia?

=VLOOKUP(B2,INDIRECT(LEFT(A2,3)&"!A:B"),2,FALSE)

If the second sheet name is nse, you can still get the name by using the "LEFT" formula and selecting only the first 3 characters.
~~~
One can use the same technique to find out the maximum value in the column B on the said sheet, like this...
=MAX(INDIRECT(A2&"!B:B"))

When you get stuck while typing the vlookup formula, press Control+Shift+A after you've typed the first parenthesis of a function and Excel fills in the argument names.

For e.g.
=vlookup(
Ctrl + Shift + A

Similarly, Control+A pressed at that time will open the function wizard.

Gabbly Chat is an Ajax project that lets you chat with the visitors of any site in real time. If you add http://gabbly.com in front of a URL, the page will load, along with a small chat window where you can talk with other fellows. The concept is interesting: a webmaster could talk to the visitors of his site on any page, visitors could see other opinions about products, visitors can be guided, teachers can teach online!

http://gabbly.com

You can leave a message or ask me a question at gabbly chat

Business Week had an interview with Deep Nishar, Google's director of product management who also heads the company's wireless efforts worldwide. Excerpts:

How important strategically is the mobile business to Google?
For Google, it's extremely strategic. Our mission is, take the world's information and make it universally available and useful for our users. And we don't believe all the users will use PCs to access content. Especially in emerging markets where cell phone penetration is deeper than PC penetration, cell phones might become -- or, in some cases, have already become -- the primary means of accessing data.

What's your overarching mobile strategy?
The phone is not the PC. It's about creating the right experience for the mobile user, so they can find exactly what they want, quickly and efficiently. People search differently on mobile phones; they don't browse as much, as PC users do, for example.

An interview with Aroon Purie who talked about media yesterday, media today and, most importantly, media tomorrow.
_____

It is a tougher and tougher market. You're competing with television, with the Internet.

I think that the whole media is in a crisis. It's a paradoxical situation. You have an exploding media, which is expanding in every segment - television, internet and print. But I think also everybody is questioning their basic assumptions. Who is our target audience? How do people get information? When do they want this information? What are they willing to pay for information? Has news become a commodity? By information I mean news, opinions, analyses. These are the questions everybody is asking themselves because the consumer is getting information from many different sources in real time. There is no waiting any more. In my opinion, this question is more critical for newspapers. Today, newspapers no longer break stories. I don't get my F.I.R. (First Information Report) from newspapers any longer. I get this from the evening broadcast or morning broadcast, or even my mobile phone. Newspapers have to go beyond just delivering what has happened the night before which I already know about. I think that's the crisis they are facing - and they haven't, to my mind, been able to successfully meet that challenge.

http://www.exchange4media.com/impact_news.asp?news_id=20178§ion_id=30

We will learn how to open 8 pages test pdf file found here...
http://www.census.gov/ipc/prod/ib-9701.pdf

1) How do I print this file without any charts and font colors?
Type the entire URL into google.
If the file is indexed by google, you will see the "View as HTML" link.

2) What if the file is not listed in google search?
Type the file path after ?url= in the following path.

http://view.samurajdata.se/ps.php?url=

So the exact URL will be
http://view.samurajdata.se/ps.php?url=http://www.census.gov/ipc/prod/ib-9701.pdf
The utility will convert each page into a gif file that you can save, crop, rotate in powerpoint or word.

3) What if the file is private or not indexed by google?
send the file to your gmail account and then click on "View as HTML" in your inbox.

1) Select the rows you want to highlight.
2) From format menu choose Conditional formatting...
3) Type the formula =ODD(ROW())=ROW() in the formula is field.
4) Choose the "Format" button and then Patterns tab where you can choose a background color. Click on ok.

Most of the mobile phone users (out of the total 8 crore in India) don't have any internet access on their mobile phones due to the old handsets in use, high connectivity cost and usability issues (complicated keyboard, browser dilemma, small screen etc.)

But now we can type the command like this...

mo send shantanuo@yahoo.com very nice software

and send it as SMS to
9860609000

The message "very nice software" will be sent to shantanuo@yahoo.com if the user typed the command "mo" (stands for mobile) and "send". The SMS should be sent to the number 9860609000 The From: address obviously will not be the original sender's name but some obscure server name.

Most of the people who are currently deprived of sending email due to the reasons like old age, computer illiteracy, affordability, etc. will now be able to send email.

Happy SMSing!