Re: Need Query Help

[Shawn Green]

On 6/22/2012 12:18 AM, Anupam Karmarkar wrote:

Thanks Rick for your reply,

Here i am asking about logic to perpare query or whole query itself.



A set-based approach to doing the basic task is to convert your set of 
start/stop times into duration values. The timediff() function mentioned 
already is a good way to do this.


CREATE TEMPORARY TABLE tmpHours SELECT EmployeeID, timediff(logouttime, 
logintime) as duration FROM sourcetable;


At this point, you have a temporary table of (EmployeeID, duration). It 
becomes very simple to write a summary query:


SELECT employeeid, sum(duration) as totalhours from tmpHours group by 
employeeid;


If you want to breakdown your final report by other values (by date, by 
week, by shift, etc) then you need to compute those and add them to the 
tmpHours table when you create it.


Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Need Query Help

[Anupam Karmarkar]

Thanks Rick for your reply,

Here i am asking about logic to perpare query or whole query itself.

--Anupam




 From: Rick James 
To: Anupam Karmarkar ; "mysql@lists.mysql.com" 
 
Sent: Wednesday, 20 June 2012 10:52 PM
Subject: RE: Need Query Help
 
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff
and SEC_TO_TIME()/3600

> -Original Message-
> From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
> Sent: Wednesday, June 20, 2012 2:39 AM
> To: mysql@lists.mysql.com
> Subject: Need Query Help
> 
> Hi All,
> 
> I need query help for following table struture, where we need to
> calculate login duration of that employee for give period.
> 
> Example table
> 
> 
> EmployeeID     LoginTime   LogoutTIme
> 
> 101             2012-05-01 10:00:00     2012-05-01 12:30:00
> 102             2012-04-31 23:00:00      2012-05-02 05:00:00
> 
> 103             2012-05-01 14:00:00  NULL
> 104             2012-05-02 00:10:00  2012-05-02 05:00:00
> 
> 
> I tried to fit all scenario in above table, Consider NULL as yet to
> logout
> 
> 
> How would i calcuate Employee and it Login duration for period say from
> 2012-05-01 08:00:00 to 2012-05-01 22:00:00
> 
> 
> --Anupam

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

RE: Need Query Help

[Rick James]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff
and SEC_TO_TIME()/3600

> -Original Message-
> From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
> Sent: Wednesday, June 20, 2012 2:39 AM
> To: mysql@lists.mysql.com
> Subject: Need Query Help
> 
> Hi All,
> 
> I need query help for following table struture, where we need to
> calculate login duration of that employee for give period.
> 
> Example table
> 
> 
> EmployeeID     LoginTime   LogoutTIme
> 
> 101             2012-05-01 10:00:00     2012-05-01 12:30:00
> 102             2012-04-31 23:00:00      2012-05-02 05:00:00
> 
> 103             2012-05-01 14:00:00  NULL
> 104             2012-05-02 00:10:00  2012-05-02 05:00:00
> 
> 
> I tried to fit all scenario in above table, Consider NULL as yet to
> logout
> 
> 
> How would i calcuate Employee and it Login duration for period say from
> 2012-05-01 08:00:00 to 2012-05-01 22:00:00
> 
> 
> --Anupam

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Need Query Help

[Anupam Karmarkar]

Hi All,

I need query help for following table struture, where we need to calculate 
login duration of that employee for give period.

Example table


EmployeeID     LoginTime   LogoutTIme

101             2012-05-01 10:00:00     2012-05-01 12:30:00
102             2012-04-31 23:00:00      2012-05-02 05:00:00  

103             2012-05-01 14:00:00  NULL    
104             2012-05-02 00:10:00  2012-05-02 05:00:00


I tried to fit all scenario in above table, Consider NULL as yet to logout


How would i calcuate Employee and it Login duration for period say from 
2012-05-01 08:00:00 to 2012-05-01 22:00:00


--Anupam

Re: I need Query Help

[Gabriel PREDA]

UPDATE `table1` SET `gender` = IF('f'=`gender`, 'm', 'f');
If you have NULL columns you might want to make another sublevel in IF to
leave it NULL !

I believe this should do it... you might also take into consideraion
removing the possibility of a NULL in the `gender` column... because it
allows the `gender` not to be specified... to be null !

--
Gabriel PREDA
Senior Web Developer

On 2/10/06, Veerabhadrarao Narra <[EMAIL PROTECTED]>
wrote:
>
>
> I have a table named table1 structure is
>
> ++---+--+-+-+---+
> | Field  | Type  | Null | Key | Default | Extra |
> ++---+--+-+-+---+
> | name   | varchar(50)   | NO   | PRI | |   |
> | gender | enum('f','m') | YES  | | NULL|   |
> ++---+--+-+-+---+
>
> And Values like
>
> +--++
> | name | gender |
> +--++
> | 1| m  |
> | 2| m  |
> | 3| m  |
> | 4| m  |
> | 5| m  |
> | 6| m  |
> | 7| m  |
> | 8| m  |
> | 9| m  |
> | 91   | f  |
> | 92   | f  |
> | 93   | f  |
> | 94   | f  |
> | 95   | f  |
> | 96   | f  |
> | 97   | f  |
> | 98   | f  |
> | 99   | f  |
> +--++
>
> Now i want to change the values in gender column
> from 'm' to 'f' as well as 'f' to 'm' in single UPDATE statement. How can
> we write this. And i have check constraint it accept only 'f' or 'm'.
> (Means name 1 to 9 gender have to change 'f' and 91 to 99 'm')
>

I need Query Help

[Veerabhadrarao Narra]

I have a table named table1 structure is

++---+--+-+-+---+
| Field  | Type  | Null | Key | Default | Extra |
++---+--+-+-+---+
| name   | varchar(50)   | NO   | PRI | |   |
| gender | enum('f','m') | YES  | | NULL|   |
++---+--+-+-+---+

And Values like

+--++
| name | gender |
+--++
| 1| m  |
| 2| m  |
| 3| m  |
| 4| m  |
| 5| m  |
| 6| m  |
| 7| m  |
| 8| m  |
| 9| m  |
| 91   | f  |
| 92   | f  |
| 93   | f  |
| 94   | f  |
| 95   | f  |
| 96   | f  |
| 97   | f  |
| 98   | f  |
| 99   | f  |
+--++

Now i want to change the values in gender column
from 'm' to 'f' as well as 'f' to 'm' in single UPDATE statement. How can
we write this. And i have check constraint it accept only 'f' or 'm'.
 (Means name 1 to 9 gender have to change 'f' and 91 to 99 'm')



-- 
Thanks & Regards,
veerabhadrarao narra,
+91-988-556-5556


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

FW: need query help

[Kenny]

I am currently running an online golf tournament on an NT server with an
Access database and asp pages. I have access to a Unix server that
supports asp script and mySQL database and I'd like to move my tour site
(for several reasons) but the SQL statements don't all work. The
following statement (in Access) sets the order of the leaderboard based
on total and puts in a Pos (position) number for each record. I then
loop thru the records and write them to a table on a web page.
posSQL = "Select M20.Start_no, M20.Player, M20.Ctry, " _
& "M20.Game_1, M20.Game_2, M20.Game_3, M20.Game_4, " _
& "M20.Total, M20.Par, M20.Money, (SELECT Count(Total) " _
& "FROM M20 B WHERE B.Start_no Like MP% and B.Total < M20.Total)+1 AS
Pos " & "FROM M20 WHERE (M20.Start_no Like MP%) and M20.Game_1 > 40 AND
" _ 
& "M20.Game_2  > 40 " _
& "AND M20.Game_3 > 40 AND M20.Game_4 > 40 ORDER BY M20.Total;"

 In the mySQL database with the same query I get the following error:
ADODB.Recordset.1 error '80004005' 
SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near 'SELECT
Count(Total) FROM M20 B WHERE B.Start_no Like 'mp%' and B.Total <
M20.Tot' at line 1
Does anyone have any suggestions or even a good sample query page that I
might find something?

Thanks,
Kenny 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

FW: need query help

[Kenny]

I am currently running an online golf tournament on an NT server with an
Access database and asp pages. I have access to a Unix server that
supports asp script and mySQL database and I'd like to move my tour site
(for several reasons) but the SQL statements don't all work. The
following statement (in Access) sets the order of the leaderboard based
on total and puts in a Pos (position) number for each record. I then
loop thru the records and write them to a table on a web page.
posSQL = "Select M20.Start_no, M20.Player, M20.Ctry, " _
& "M20.Game_1, M20.Game_2, M20.Game_3, M20.Game_4, " _
& "M20.Total, M20.Par, M20.Money, (SELECT Count(Total) " _
& "FROM M20 B WHERE B.Start_no Like MP% and B.Total < M20.Total)+1 AS
Pos " & "FROM M20 WHERE (M20.Start_no Like MP%) and M20.Game_1 > 40 AND
" _ 
& "M20.Game_2  > 40 " _
& "AND M20.Game_3 > 40 AND M20.Game_4 > 40 ORDER BY M20.Total;"

 In the mySQL database with the same query I get the following error:
ADODB.Recordset.1 error '80004005' 
SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near 'SELECT
Count(Total) FROM M20 B WHERE B.Start_no Like 'mp%' and B.Total <
M20.Tot' at line 1
Does anyone have any suggestions or even a good sample query page that I
might find something?

Thanks,
Kenny 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Need query help

[Quentin Bennett]

Hi,

You need to self-join payhistory along the lines of

select p1.* from payhistory p1, payhistory.p2 where
p1.payid = p2.payid and p1.paydate = max(p2.paydate)

Something like that, anyway - I know I had to fiddle around a bit to get a
similar think going.

Hope this help

Quentin

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 1 November 2001 4:22 p.m.
To: [EMAIL PROTECTED]
Subject: Need query help


Hello all,

I have 3 tables I need to join to extract certain data base on the acct_days
in
the active table '6' days.
QUERY:

SELECT CONCAT(m.fname,' ',m.lname) AS name,
m.email,m.zip,p.paytype,p.event,p.paydate
FROM members m 
LEFT JOIN payhistory p ON p.memid = m.memid
LEFT JOIN active a ON a.memid = m.memid
WHERE a.acct_days = '6'
GROUP BY p.memid ORDER BY p.paydate DESC

QUERY RESULTS:
+-+---+---+-+---
++
| name| email | zip   | paytype | event
| paydate|
+-+---+---+-+---
++
| Charge Schwartz | [EMAIL PROTECTED] | 33308 | Charge  | Quarterly New
| 2001-10-30 |
| Check Schwartz  | [EMAIL PROTECTED] | 33308 | Check   | Quarterly New
| 2001-10-30 |
+-+---+---+-+---
++


But what I need it to do is pull the most recent date and related payhistory
data in those columns that match the memid from the members table that only
has
the 6 days left on their account.

RESULTS DESIRED:
+-+---+---+-+---
++
| name| email | zip   | paytype | event
| paydate|
+-+---+---+-+---
++
| Charge Schwartz | [EMAIL PROTECTED] | 33308 | Check   | Quarterly New
| 2001-10-31 |
| Check Schwartz  | [EMAIL PROTECTED] | 33308 | Charge  | Quarterly New
| 2001-10-31 |
+-+---+---+-+---
++

Below are snips for the tables being joined. Any help would be much
appreciated.
Been at this for almost good part of the day! :)

TIA

-
active table
+---+---+
| memid | acct_days |
+---+---+
|21 | 6 |
|22 | 6 |
+---+---+
payhistory
+---+---+++-+
| payid | memid | event  | paydate| paytype |
+---+---+++-+
|83 |21 | Quarterly New  | 2001-10-30 | Charge  |
|84 |22 | Quarterly New  | 2001-10-30 | Check   |
|85 |21 | Quarterly Renew| 2001-10-31 | Check   |
|86 |22 | Quarterly Renew| 2001-10-31 | Charge  |
+---+---+++-+
members
+---+-+---+---+
| memid | name| email | zip   |
+---+-+---+---+
|21 | Charge Schwartz | [EMAIL PROTECTED] | 33308 |
|22 | Check Schwartz  | [EMAIL PROTECTED] | 33308 |
+---+-+---+---+

mysql database 
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225)686-2002
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended 
recipient, you are asked to respect that confidentiality and not 
disclose, copy or make use of its contents. If received in error 
you are asked to destroy this email and contact the sender immediately. 
Your assistance is appreciated.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Need query help

[mickalo]

Hello all,

I have 3 tables I need to join to extract certain data base on the acct_days in
the active table '6' days.
QUERY:

SELECT CONCAT(m.fname,' ',m.lname) AS name,
m.email,m.zip,p.paytype,p.event,p.paydate
FROM members m 
LEFT JOIN payhistory p ON p.memid = m.memid
LEFT JOIN active a ON a.memid = m.memid
WHERE a.acct_days = '6'
GROUP BY p.memid ORDER BY p.paydate DESC

QUERY RESULTS:
+-+---+---+-+---++
| name| email | zip   | paytype | event | paydate  
|  |
+-+---+---+-+---++
| Charge Schwartz | [EMAIL PROTECTED] | 33308 | Charge  | Quarterly New | 
|2001-10-30 |
| Check Schwartz  | [EMAIL PROTECTED] | 33308 | Check   | Quarterly New | 
|2001-10-30 |
+-+---+---+-+---++


But what I need it to do is pull the most recent date and related payhistory
data in those columns that match the memid from the members table that only has
the 6 days left on their account.

RESULTS DESIRED:
+-+---+---+-+---++
| name| email | zip   | paytype | event | paydate  
|  |
+-+---+---+-+---++
| Charge Schwartz | [EMAIL PROTECTED] | 33308 | Check   | Quarterly New | 
|2001-10-31 |
| Check Schwartz  | [EMAIL PROTECTED] | 33308 | Charge  | Quarterly New | 
|2001-10-31 |
+-+---+---+-+---++

Below are snips for the tables being joined. Any help would be much appreciated.
Been at this for almost good part of the day! :)

TIA

-
active table
+---+---+
| memid | acct_days |
+---+---+
|21 | 6 |
|22 | 6 |
+---+---+
payhistory
+---+---+++-+
| payid | memid | event  | paydate| paytype |
+---+---+++-+
|83 |21 | Quarterly New  | 2001-10-30 | Charge  |
|84 |22 | Quarterly New  | 2001-10-30 | Check   |
|85 |21 | Quarterly Renew| 2001-10-31 | Check   |
|86 |22 | Quarterly Renew| 2001-10-31 | Charge  |
+---+---+++-+
members
+---+-+---+---+
| memid | name| email | zip   |
+---+-+---+---+
|21 | Charge Schwartz | [EMAIL PROTECTED] | 33308 |
|22 | Check Schwartz  | [EMAIL PROTECTED] | 33308 |
+---+-+---+---+

mysql database 
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225)686-2002
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php