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 with Visitors of a Site

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

March 10, 2006

 

Google Mobile

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  

This page is powered by Blogger. Isn't yours?