Solved

sql query  help needed!!

Posted on 2003-11-12
4
1,031 Views
Last Modified: 2012-08-13
Hello All,

In the following query i m selecting a few columns and making a sum of hours...
but b'coz of the groupby clause for datetime_from..i m not able to get the
absent days in the hours_booked table.(where sum should be 0)...i want the days
which are not present in the table with the sum value as 0.

How i can do this?? the database i m using is MySql.

SELECT  DATE_FORMAT(datetime_from,'%M-%Y') AS monthOfYear ,  
DATE_FORMAT(datetime_from,'%W') AS dayOfMonth ,  
DATE_FORMAT(datetime_from,'%Y-%m-%d') AS date ,  
(SUM(UNIX_TIMESTAMP(datetime_to)-UNIX_TIMESTAMP(datetime_from))/3600) AS hours  
FROM hours_booked
WHERE UPPER(owner) = UPPER('something')
AND DATE_FORMAT(datetime_from,'%M-%Y')='May-2003'
GROUP BY DATE_FORMAT(datetime_from,'%Y-%M-%d')  
ORDER BY DATE_FORMAT(datetime_from,'%Y-%m-%d') DESC

Please guide.
Thanks and Regards
aks
0
Comment
Question by:aks143
  • 3
4 Comments
 
LVL 1

Expert Comment

by:spike3382
Comment Utility
There's no real easy way to do this in mysql as far as I know, but wait a couple days and maybe someone will come up with more elegant answer than these, but I'll take a stab:

1) This might be easier in the language you're connecting to the DB with (like VB or PHP).  Checking for out of sequence dates, and inserting the zeros as neccessary.  It's at least worth considering.  In general I'm 100% behind moving as much computation into the database, as they're extremely powerful tools, but I just don't think that mysql can handle this as gracefully as one would want.

2) To do it in the DB, I think the easiest way might be to create a table called something like tbl_aux_dates.  This table can have just one field called, for example, auxdate.  You can populate the table with one of each of the possible dates (so it would have one entry for every possible day), and then do an outer join between hours_booked and tbl_aux_dates and make everything in your where clause and group by refer to the auxdate field.  So, the query would look approximately like this:

SELECT  DATE_FORMAT(A.auxdate,'%M-%Y') AS monthOfYear ,  
DATE_FORMAT(A.auxdate,'%W') AS dayOfMonth ,  
DATE_FORMAT(A.auxdate,'%Y-%m-%d') AS date ,  
IFNULL(((SUM(UNIX_TIMESTAMP(datetime_to)-UNIX_TIMESTAMP(datetime_from))/3600)), 0) AS hours  
FROM tbl_aux_dates as A LEFT OUTER JOIN hours_booked as B
ON (A.auxdate=B.datetime_from)
WHERE UPPER(B.owner) = UPPER('something')
AND DATE_FORMAT(A.auxdate,'%M-%Y')='May-2003'
GROUP BY DATE_FORMAT(A.auxdate,'%Y-%M-%d')  
ORDER BY DATE_FORMAT(A.auxdate,'%Y-%m-%d') DESC

We have to use left outer join to make sure that even rows in tbl_aux_date that don't have corresponding rows in hours_booked are displayed.  More info on left outer join can be found here:
http://www.mysql.com/doc/en/JOIN.html

And the IFNULL() is used in case the datetime_to is NULL, as should be if there isn't a corresponding row in hours_booked.  This is the one thing I'm not 100% sure on.  I'm 99% sure that it will work, but there's a lot of null values floating around there.  If it doesn't work, try out some different control flow functions.  You can find them here:
http://www.mysql.com/doc/en/Date_and_time_functions.html

I hope that helps... and makes some sort of sense.  I'm not sure if I explained it very well.  And like I said, someone could have a solution that's much better than this one.  This assumes that when you say that the dates don't appear in the main table, they really don't appear anywhere in the table.  As in, if a date has no hours booked, then it has no entry whatsoever in the table.  Good luck!
-Matt
0
 
LVL 1

Expert Comment

by:spike3382
Comment Utility
Sorry, the line that starts IFNULL should have the aliases in it as well, so it would actually be

IFNULL(((SUM(UNIX_TIMESTAMP(B.datetime_to)-UNIX_TIMESTAMP(B.datetime_from))/3600)), 0) AS hours  
0
 

Author Comment

by:aks143
Comment Utility
Hii´spike3382,

Thanks for your answer. I think the second option is more appropriate. I was also thinking of the same, but the idea of having all the possible dates..somehow didn't suits me. I mean to say, the hours_booked table has data coming in every day...either my aux_date table should have all the dates for the given year...OR all the absent dates in the hours_booked table. But, what is the flexible way to create such kind of aux_date table?? How can i insert in my aux table..all the days in a year.

Regards
aks
0
 
LVL 1

Accepted Solution

by:
spike3382 earned 75 total points
Comment Utility
Right... and that's the problem with mySQl at the moment.  There is no flexible way to do it without relying on third party software.  Off the top of my head, probably the best way to do it would be to connect to the database with PHP or VB or whatever the language is that you're using to talk to the database, and then to get the UNIX timestamp for the current date, and write a loop that adds that as a date into the mysql database, and loops backwards 356 times subtracting 86,400 seconds (the number of seconds in a day) from the timestamp and adding that as a day, and then another one that loops forwards 356 times adding 86,400 seconds every time and adding that as a possible day.  That'd give you 2 years of dates to work with.  Of course you can up the iterations of the loop in either direction to give you more days.   Never having had to do this myself, and depending on what language you're using, there could be an easier way, although I think that's about as elegant as you're going to get with MySQL in its current state.

Hope that helps,
Matt
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now