Shantanu's Blog
Database Consultant
March 31, 2006
SQL Calendar dates
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;
March 30, 2006
MySQL Case Study - 115
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
MySQL Case Study - 114
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
March 28, 2006
MySQL Case Study - 113
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
March 27, 2006
MySQL Case Study - 112
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
March 26, 2006
MySQL Case Study - 111
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
March 24, 2006
MySQL Case Study - 110
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
MySQL Case Study - 109
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
MySQL Case Study - 108
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
MySQL Case Study - 107
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
March 23, 2006
MySQL Case Study - 106
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
MySQL Case Study - 105
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
MySQL Case Study - 104
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
March 22, 2006
exploring vlookup
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"))
March 21, 2006
Excel Formula shortcut
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.
March 12, 2006
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.comYou can leave a message or ask me a question at
gabbly chat
March 10, 2006
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.
Media Crisis
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
March 07, 2006
PDF file manipulation
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.
March 06, 2006
Highlight alternate rows in excel
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.
March 04, 2006
SMS to email
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!
Archives
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
March 2022
October 2022
November 2022
March 2023
April 2023
July 2023
September 2023
October 2023
November 2023
April 2024
May 2024
June 2024
August 2024
September 2024
October 2024