RE: Merging Databases
-Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Monday, May 11, 2009 3:30 PM To: MySQL General List Subject: Merging Databases Hi, I have a lot of databases that have the exact same tables and each table has the exact same column structure. I'm looking at merging two of these databases together into a single database (Company A bought Company B and wants the data from A combined into B now). I've been tossing around the idea of looking in database B at each table that would need to be merged and simply adding the last ID number to every ID number in database A's tables. For example, in table1 in B's data, the last ID number is 2000, could we simply add 2000 to every ID number in table1 in A's data? Could we then export (SELECT INTO OUTFILE) from A's data and import (LOAD DATA) into B's data? Has anyone done something like this before? Did you have problems? -- - Johnny Withers 601.209.4985 joh...@pixelated.net --- Why not create a view and just concatenate on an identifier? This way the data can be kept in the same forms. Or, if you do want to have it as one table, you can use a select insert statement to move from one to another. Build the select query first to get the data looking like you want it, then convert it when you think you are ready. Of course, backups are your friend in any case. :) Cheers, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Need to pivot rows into columns
From: Attila [mailto:attee...@gmail.com] Sent: Thursday, January 22, 2009 3:06 PM To: mysql@lists.mysql.com Subject: Need to pivot rows into columns ... I am using MySQL and have found some possibilities with SQL Server (Pivot, crosstab, etc) ... but the requirements call for MySQL. Is the above query possible? Thanks a lot! -- Attila Software Developer attee...@gmail.com Hello, Look at http://dev.mysql.com/tech-resources/articles/wizard/print_version.html and http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html?page=2 for ideas for crosstabs. Some tools like TOAD for MYSQL have some functionality in this direction too. :) Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SELECT with replacement chars
Hi how can i replace, in a SELECT query, the last 3 numbers with asterisks? from 0123456789 to 0123456*** My psudocode for this would be something like: Select CONCAT(left$(`field`,(LENGTH(a)-3),'***') From `table` But there might be more efficient ideas out there. This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access Rights ?
This problem is Chapter 16.6 of The MYSQL Cookbook, (second edition) that has made my(sql) life much easier. You might want to look around at http://artfulsoftware.com/ which has lots of examples and a great e-book resource. Both of these are fantastic resources for me, and they are probably my most used books in my mysql library. Good luck, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Sachin Gaikwad [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 2:07 PM To: mysql@lists.mysql.com Subject: Access Rights ? Hi all, I am newbie using MySQL. I want to log each client request to database server. I thought when server checks access rights of user(client) will be good place to log all access/read/update requests. If somebody knows where exactly this is done in source code, I will get a real kick start. Right now I am browsing through the source code through cscope to find out this. Any help in this regard is appreciated. Thanks, Sachin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Many to many to too many relation.
This e-mail, including any attachments, may be confidential, privileged or
otherwise legally protected. It is intended only for the addressee. If you
received this e-mail in error or from someone who was not authorized to send it
to you, do not disseminate, copy or otherwise use this e-mail or its
attachments. Please notify the sender immediately by reply e-mail and delete
the e-mail from your system.
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 30, 2008 3:30 AM
To: Weston, Craig (OFT)
Cc: mysql@lists.mysql.com
Subject: Re: Many to many to too many relation.
I tried reading this and it took much brain power to do for free. I
suggest breaking this down into the simplest possible example you can.
Ideally have an create table statement, and insert with a small amount
of data (as simple as possible), a simple psuedo code query and
example output of what you want returned.
On Mon, Sep 29, 2008 at 2:00 PM, Weston, Craig (OFT)
[EMAIL PROTECTED] wrote:
Hello everyone.
I have a complex situation. I have a list of related records (see
partial extract below) where I am trying to determine the unique
lowest
number for each relation. This number should always be an AB number,
since all the MA numbers will always relate to an AB number. I need
the
distinct, lowest values that relate to each other, not necessarily in
the same row.
..
Rob,
I agree this is pretty complex. I am having difficulty understanding
how to express what I am trying to accomplish. I appreciate your
willingness to consider further, (or at least watch me try to explain
this further, which is valuable all by itself)
--- table ---
CREATE TABLE `test` (`Field1` varchar(50) collate latin1_general_ci
default NULL, `Field2` varchar(50) collate latin1_general_ci default
NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
--
-- Records
--
INSERT INTO `test` VALUES ('AB100687', 'AB100633');
INSERT INTO `test` VALUES ('AB100687', 'AB100661');
INSERT INTO `test` VALUES ('AB100687', 'AB100680');
INSERT INTO `test` VALUES ('AB100687', 'AB100681');
INSERT INTO `test` VALUES ('AB100687', 'AB100682');
INSERT INTO `test` VALUES ('AB100687', 'MA003343');
INSERT INTO `test` VALUES ('AB100687', 'AB100688');
INSERT INTO `test` VALUES ('AB100687', 'AB100690');
INSERT INTO `test` VALUES ('AB100687', 'AB100722');
INSERT INTO `test` VALUES ('AB100687', 'AB100731');
INSERT INTO `test` VALUES ('AB100687', 'AB100792');
INSERT INTO `test` VALUES ('AB100688', 'AB100687');
INSERT INTO `test` VALUES ('AB100688', 'MA003340');
INSERT INTO `test` VALUES ('AB100689', 'MA003351');
INSERT INTO `test` VALUES ('AB100690', 'AB100687');
INSERT INTO `test` VALUES ('AB100690', 'MA003354');
INSERT INTO `test` VALUES ('AB100690', 'AB100731');
--- psudocode ---
Give me the alphanumerically lowest (or first) record of each
association.
Each record returned must be unique.
--- results I am trying for ---
AB100633
AB100689
--- Possibly useful commentary and thoughts ---
The problem I have is that the table lists all associations twice. So
records 'AB100687', 'AB100688' exist in relation as well as 'AB100688',
'AB100687'. Relational chains also exist, for example, 'AB100687',
'AB100633' are related, but there are a bunch of id's that are
associated with 'AB100687' but not with 'AB100633'. However since
'AB100633' is the earliest they all should relate to it.
MA numbers will always be discarded since they are always related to an
AB number, which will be lower.
--- Thank you ---
Thanks for your help. I know this is complex, and I am explaining it
poorly, and I am sorry I can't express myself any better. I just can't
comprehend it very easily. Thank you for reading this, even if you can't
help. I feel like I learned something just trying to explain this
situation.
Regards,
Craig
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Many to many to too many relation.
Wow, I do feel very lucky. I am only at the this is a cursor part.
I sincerely appreciate your help.
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 30, 2008 10:11 AM
To: Weston, Craig (OFT)
Cc: mysql@lists.mysql.com
Subject: Re: Many to many to too many relation.
Luck for you my vpn appears to be screwed up. Here is a stored proc I
whipped up:
Stored procedure:
DROP PROCEDURE IF EXISTS s;
delimiter //
CREATE PROCEDURE s ()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE odone INT DEFAULT 0;
DECLARE f1,f2 VARCHAR(50);
DECLARE maxgroup INT DEFAULT 0;
DECLARE s_groupid INT;
DECLARE cur1 CURSOR FOR SELECT DISTINCT Field1,Field2
FROM test.test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp(groupid int,value
varchar(50),
UNIQUE(groupid, value));
OPEN cur1;
REPEAT
FETCH cur1 INTO f1, f2;
IF NOT done THEN
set odone = done;
SELECT groupid INTO s_groupid FROM TEMP
WHERE value IN(f1,f2) limit 1;
set done = odone;
IF s_groupid IS NULL THEN
INSERT INTO temp
values(maxgroup, f1);
INSERT INTO temp
values(maxgroup, f2);
SET maxgroup = maxgroup+1;
ELSE
INSERT IGNORE INTO temp
values(s_groupid, f1);
INSERT IGNORE INTO temp
values(s_groupid, f2);
END IF;
END IF;
set s_groupid =null;
UNTIL done END REPEAT;
CLOSE cur1;
END;
//
delimiter ;
call s();
mysql call s();
Query OK, 0 rows affected (2.61 sec)
mysql select min(value) from temp group by groupid;
++
| min(value) |
++
| AB100633 |
| AB100689 |
++
2 rows in set (0.00 sec)
Which matches the expect below...
On Tue, Sep 30, 2008 at 5:16 AM, Weston, Craig (OFT)
[EMAIL PROTECTED] wrote:
This e-mail, including any attachments, may be confidential,
privileged or otherwise legally protected. It is intended only for the
addressee. If you received this e-mail in error or from someone who was
not authorized to send it to you, do not disseminate, copy or otherwise
use this e-mail or its attachments. Please notify the sender
immediately by reply e-mail and delete the e-mail from your system.
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 30, 2008 3:30 AM
To: Weston, Craig (OFT)
Cc: mysql@lists.mysql.com
Subject: Re: Many to many to too many relation.
I tried reading this and it took much brain power to do for free. I
suggest breaking this down into the simplest possible example you can.
Ideally have an create table statement, and insert with a small amount
of data (as simple as possible), a simple psuedo code query and
example output of what you want returned.
On Mon, Sep 29, 2008 at 2:00 PM, Weston, Craig (OFT)
[EMAIL PROTECTED] wrote:
Hello everyone.
I have a complex situation. I have a list of related records (see
partial extract below) where I am trying to determine the unique
lowest
number for each relation. This number should always be an AB number,
since all the MA numbers will always relate to an AB number. I need
the
distinct, lowest values that relate to each other, not necessarily in
the same row.
..
Rob,
I agree this is pretty complex. I am having difficulty understanding
how to express what I am trying to accomplish. I appreciate your
willingness to consider further, (or at least watch me try to explain
this further, which is valuable all by itself)
--- table ---
CREATE TABLE `test` (`Field1` varchar(50) collate latin1_general_ci
default NULL, `Field2` varchar(50) collate latin1_general_ci default
NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
--
-- Records
--
INSERT INTO `test` VALUES ('AB100687', 'AB100633');
INSERT INTO `test` VALUES ('AB100687', 'AB100661');
INSERT INTO `test` VALUES ('AB100687', 'AB100680');
INSERT INTO `test` VALUES ('AB100687', 'AB100681');
INSERT INTO `test` VALUES ('AB100687', 'AB100682');
INSERT INTO `test` VALUES ('AB100687', 'MA003343');
INSERT INTO `test` VALUES ('AB100687', 'AB100688');
INSERT INTO `test` VALUES ('AB100687', 'AB100690');
INSERT INTO `test` VALUES ('AB100687', 'AB100722
RE: Many to many to too many relation.
Rob, I tried this on the small test data, a larger data set and it appears to make my day. I sincerely appreciate your willingness to help me out. I do have some questions, mostly so I can understand how this works better: I was wondering what the lines: set odone = done; set done = odone; do? I mean, in context, I understand they make variables do their thing, but I wasn't sure why they were needed. Also The statement set s_groupid =null; is very understandible, I just wondered why it was needed, or conversely why the other variables were not set null at the end? Thank you. -Original Message- From: Rob Wultsch [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2008 10:11 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: Many to many to too many relation. Luck for you my vpn appears to be screwed up. Here is a stored proc I whipped up: Stored procedure: DROP PROCEDURE IF EXISTS s; delimiter // CREATE PROCEDURE s () BEGIN DECLARE done INT DEFAULT 0; DECLARE odone INT DEFAULT 0; DECLARE f1,f2 VARCHAR(50); DECLARE maxgroup INT DEFAULT 0; DECLARE s_groupid INT; DECLARE cur1 CURSOR FOR SELECT DISTINCT Field1,Field2 FROM test.test; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DROP TABLE IF EXISTS temp; CREATE TEMPORARY TABLE temp(groupid int,value varchar(50), UNIQUE(groupid, value)); OPEN cur1; REPEAT FETCH cur1 INTO f1, f2; IF NOT done THEN set odone = done; SELECT groupid INTO s_groupid FROM TEMP WHERE value IN(f1,f2) limit 1; set done = odone; IF s_groupid IS NULL THEN INSERT INTO temp values(maxgroup, f1); INSERT INTO temp values(maxgroup, f2); SET maxgroup = maxgroup+1; ELSE INSERT IGNORE INTO temp values(s_groupid, f1); INSERT IGNORE INTO temp values(s_groupid, f2); END IF; END IF; set s_groupid =null; UNTIL done END REPEAT; CLOSE cur1; END; // delimiter ; call s(); mysql call s(); Query OK, 0 rows affected (2.61 sec) mysql select min(value) from temp group by groupid; ++ | min(value) | ++ | AB100633 | | AB100689 | ++ 2 rows in set (0.00 sec) Which matches the expect below... On Tue, Sep 30, 2008 at 5:16 AM, Weston, Craig (OFT) [EMAIL PROTECTED] wrote: This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Rob Wultsch [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2008 3:30 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: Many to many to too many relation. I tried reading this and it took much brain power to do for free. I suggest breaking this down into the simplest possible example you can. Ideally have an create table statement, and insert with a small amount of data (as simple as possible), a simple psuedo code query and example output of what you want returned. On Mon, Sep 29, 2008 at 2:00 PM, Weston, Craig (OFT) [EMAIL PROTECTED] wrote: Hello everyone. I have a complex situation. I have a list of related records (see partial extract below) where I am trying to determine the unique lowest number for each relation. This number should always be an AB number, since all the MA numbers will always relate to an AB number. I need the distinct, lowest values that relate to each other, not necessarily in the same row. .. Rob, I agree this is pretty complex. I am having difficulty understanding how to express what I am trying to accomplish. I appreciate your willingness to consider further, (or at least watch me try to explain this further, which is valuable all by itself) --- table --- CREATE TABLE `test` (`Field1` varchar(50) collate latin1_general_ci default NULL, `Field2` varchar(50) collate latin1_general_ci default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -- Records
Many to many to too many relation.
Hello everyone. I have a complex situation. I have a list of related records (see partial extract below) where I am trying to determine the unique lowest number for each relation. This number should always be an AB number, since all the MA numbers will always relate to an AB number. I need the distinct, lowest values that relate to each other, not necessarily in the same row. What I have so far is: SELECT distinct if (test.Field1 test.Field2,test.Field1,test.Field2) AS `Unique Incident id` FROM test The problem is that the data is recursive and non-exclusive, by which I mean that the data will always be in both columns, and it is possible that more than one relation exists. In the case of more than 1 relation I still need the just lowest distinct value in the relation, no matter how many other members of the relation exist. Can anyone offer any suggestions? Here is some sample data to chew on. AB100674,MA003296 AB100675,MA003306 AB100676,MA003314 AB100677,MA003312 AB100678,MA003321 AB100679,MA003318 AB100680,MA003320 AB100680,AB100687 AB100681,MA003335 AB100681,AB100687 AB100682,MA003330 AB100682,AB100687 AB100683,MA003337 AB100684,MA003336 AB100685,MA003332 AB100686,MA003341 AB100687,AB100633 AB100687,AB100661 AB100687,AB100680 AB100687,AB100681 AB100687,AB100682 AB100687,MA003343 AB100687,AB100688 AB100687,AB100690 AB100687,AB100722 AB100687,AB100731 AB100687,AB100792 AB100688,AB100687 AB100688,MA003340 AB100689,MA003351 AB100690,AB100687 AB100690,MA003354 AB100690,AB100731 AB100691,MA003353 Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: Combining Multiple Tables
Use null (or custom text) to replace the missing columns A quick ex: Select a,b,c,null,d from table1 Union all Select d,'test valid',e,f,g from table 2 This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Tim Carty [mailto:[EMAIL PROTECTED] Sent: Friday, September 26, 2008 10:41 AM To: mysql@lists.mysql.com Subject: Combining Multiple Tables I would like to combine multiple tables into a single table. I can create a UNION between tables with similar columns cols A, B, and C from table_1 with cols A, B, and C from Table_2. What I can¹t figure out is how to add additionals columns (D, E, F) from table_2 if the columns don¹t exist in Table_1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database Creating
I would like to create my first mysql database. Is there a gui tool that makes this easy to do? Thanks, Andrew Try Toad - http://toadsoft.com/toadmysql/mysql_freeware.htm This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calendar creation
Hi there, Is there any simple way to create a calendar table? For example I want to create a reference calendar containing all the dates of several years so I can accurately represent even days where I have no data in my data set. Any ideas? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: Calendar creation
Thanks, everybody! Craig Weston nfrastructure Contractor NYS CIO/OFT Enterprise Helpdesk Management 518-486-4569 [EMAIL PROTECTED] -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, July 17, 2008 2:26 PM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: Calendar creation Craig, Is there any simple way to create a calendar table? See Make a calendar table at http://www.artfulsoftware.com/infotree/queries.php. PB Weston, Craig (OFT) wrote: Hi there, Is there any simple way to create a calendar table? For example I want to create a reference calendar containing all the dates of several years so I can accurately represent even days where I have no data in my data set. Any ideas? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.0/1558 - Release Date: 7/17/2008 9:56 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date list including dates not in the list
Hi there, I have a list of events that occur periodically: 1/1/2008Event 1/1/2008Event 1/1/2008Event 1/2/2008Event 1/2/2008Event 1/4/2008Event 1/4/2008Event I know how to count events per day, but on days (such as 1/3/2008 in the above example) where there is no event, how could I get the date to display? The query I am using is: SELECT from_unixtime(`mainDate`,%m/%d/%Y), Count(`ID`) FROM `main_data` WHERE `department` = '9' GROUP BY from_unixtime(`mainDate`,%m/%d/%Y) What I get with this query is: 1/1/20083 1/2/20082 1/4/20081 The result set that I want is: 1/1/20083 1/2/20082 1/3/20080 1/4/20081 Any ideas? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: [MYSQL]time of elapsed time
Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 But it still is all the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the right numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 8:53 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [MYSQL]time of elapsed time Hi Craig, Weston, Craig (OFT) wrote: Hello everyone. Once again, I am jousting at the windmill of time and date formulae within MYSQL. I seek to create a cross-tab or pivot table of the SUM of all times with a specific category, on a per-ticket basis. I have everything working except the math part. Even that is kind of working ok, but it is not adding up The math part is: IF(`status` = 'WIP',SUM(time_to_sec(`CLOCK_TIME`)),0) AS 'WIP' (the same formula with 'CLOSED','OPEN', etc lists all the various statuses available.) CLOCK_TIME is a varchar field that contains a 4 digit date counter and a timer, in the format of 0293 23:44 0001 00:29 0001 19:15 My hunch is this is the problem. You should split the field into two: one for the date counter, one for the time. time_to_secs() is probably returning zero for most of these. Now, I run the query and get results. Every ticket has more than one status. But, for each ticket, I get a single line that appears to have the entire ticket time (in seconds) in one field and the rest are zero. I think this tells me that the statement is working but that I am grouping them wrong? `key` is the ticket number. From `clock_data` group by `clock_data`.`key` So my result set looks like 17881375,0,0,0,0,15235975,0,0,0,0,0,0,0,0 18184639,21362513,0,0,0,0,0,0,0,0,0,0,0,0 Etc. Can anyone help me over this hill? I think I am writing the if statement incorrectly somehow but don't see a way out of the box yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [MYSQL]time of elapsed time
There were 2 changes - First was IF(`status` = 'WIP',SUM(time_to_sec(RIGHT(`CLOCK_TIME`),8)),0) AS 'WIP' ( I addded the RIGHT limit on the string) And second was grouping by KEY,STATUS Further experimentation makes it appear that I am getting the right number of seconds for the entire string, so I am playing without the RIGHT() modifier right now. Maybe making a table with the status's on separate rows and then querying against that to make the single line?? This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 12:02 PM To: mysql@lists.mysql.com Subject: Re: [MYSQL]time of elapsed time Weston, Craig (OFT) wrote: Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 But it still is all the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the right numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? What is the query used for that result? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL]time of elapsed time
Hello everyone. Once again, I am jousting at the windmill of time and date formulae within MYSQL. I seek to create a cross-tab or pivot table of the SUM of all times with a specific category, on a per-ticket basis. I have everything working except the math part. Even that is kind of working ok, but it is not adding up The math part is: IF(`status` = 'WIP',SUM(time_to_sec(`CLOCK_TIME`)),0) AS 'WIP' (the same formula with 'CLOSED','OPEN', etc lists all the various statuses available.) CLOCK_TIME is a varchar field that contains a 4 digit date counter and a timer, in the format of 0293 23:44 0001 00:29 0001 19:15 ... Now, I run the query and get results. Every ticket has more than one status. But, for each ticket, I get a single line that appears to have the entire ticket time (in seconds) in one field and the rest are zero. I think this tells me that the statement is working but that I am grouping them wrong? `key` is the ticket number. From `clock_data` group by `clock_data`.`key` So my result set looks like 17881375,0,0,0,0,15235975,0,0,0,0,0,0,0,0 18184639,21362513,0,0,0,0,0,0,0,0,0,0,0,0 Etc. Can anyone help me over this hill? I think I am writing the if statement incorrectly somehow but don't see a way out of the box yet. Thank you. This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: [mysql] Text = Tinytext = MediumText = LongText?
Thank you This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Sebastian Mendel [mailto:[EMAIL PROTECTED] Sent: Thursday, October 18, 2007 4:21 AM To: mysql@lists.mysql.com Subject: Re: [mysql] Text = Tinytext = MediumText = LongText? Sebastian Mendel schrieb: Weston, Craig (OFT) schrieb: Hello everyone, hopefully a quick question: is there a functional difference between the various text field sizes other than size? For example if I wanted to could I run a FULLTEXT index against a LongText field? no ay ... difference: no, FULLTEXT index: yes -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[mysql] Text = Tinytext = MediumText = LongText?
Hello everyone, hopefully a quick question: is there a functional difference between the various text field sizes other than size? For example if I wanted to could I run a FULLTEXT index against a LongText field? The collary is of course how good of an idea is this, assuming that MOST of the data isn't even long enough to fill a text field. Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: Count syntax
Beauford, you might find this article on cross joins interesting, it was something shown to me a few weeks ago that discusses this kind of function. http://dev.mysql.com/tech-resources/articles/wizard/print_version.html Cheers, craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 1:36 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax 1 means that 1 will be added to the sum if the condition tests, otherwise 0 will be added to the sum. the condition in this case is (supportertype = 'L') and will be applied to every row. On 9/28/07, Beauford [EMAIL PROTECTED] wrote: Thanks - it works, but what does the 1 and 0 do in this - SUM(IF(supportertype = 'L', 1, 0)) -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:00 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [mysql] duplicating lines
Hi, I have narrowed the query - I just want to return the FIRST instance of each ticket, ie the Source. Here is the table: CREATE TABLE `Thedata1` ( `Source` varchar(15) collate latin1_general_ci NOT NULL default '', `Close_Time` datetime NOT NULL default '-00-00 00:00:00', `start_Time` datetime default NULL, COMMENT 'correct time in Seconds' `start_Time2` datetime default NULL COMMENT 'Original Time in Seconds', `Original_Cycle_Time` decimal(11,4) default NULL, `workday` double(20,0) default NULL, PRIMARY KEY (`Source`,`start_Time2`,`Close_Time`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My best guess on how to do this would be a self join? select `a1`.`Source, If(`a1`.`workday` `a2`.`workday`, `a1`.`workday`,`a2`.`workday`) from `Thedata1` as `a1` inner join `Thedata1` as `a2` on `a1`.`Source` = `a2`.`table1` and `a1`.`workday` `a2`.`workday` There can be more than 2 duplicates, and occasionally no duplicates. I really am not sure why this isn't working. Can anyone offer an insight? Thank you, Craig -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Thursday, September 13, 2007 1:27 PM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [mysql] duplicating lines Hi Craig, would you mind posting the SHOW CREATE TABLE for the tables in question? I'm having trouble determining what is the primary key for your service ticket table... Thanks, -Jay Weston, Craig (OFT) wrote: Hello again, I am having a duplication of results problem. I believe my query (below) is giving me exactly what I deserve in writing it. What it returns to me is a row for each status. What I would most want would be a single row with the oldest status - IE the status datetime that happened earliest. What I am trying to do is determine when a service desk ticket first enters any one of these three categories. I am not using distinct on `thedata2`.`Source` as this does not effect the result set. I have 2 tables. One of the tables lists all the ticket information at time of the ticket being closed. The other has an entry referenced by ticket number for each time a ticket is touched or updated. So what I am trying to do is identify the last time it was touched with the appropriate status change. Does anyone have any idea what I could do to eliminate the duplicate with the oldest time? I am experimenting in the idea of a subquery but can't think of anything else. ??? Thanks, craig SELECT `thedata2`.`Source`, `thedata1`.`Status`, `thedata2`.`Priority`, `thedata1`.`start_Time`, `thedata1`.`Close_Time`, `thedata1`.`workday`'cycletime' FROM `thedata2` Inner Join `thedata1` ON `thedata1`.`Source` = `thedata2`.`SourceSR` WHERE (`thedata1`.`Status` like 'Resolved' OR `thedata1`.`Status` like 'Restored' OR `thedata1`.`Status` like 'Isolation') and `thedata2`.`Open_Time` BETWEEN '2007-02-01 00:00' AND '2007-08-31 23:59:59' And ((`thedata2`.`Priority` = 1 and `thedata1`.`workday` 14400) OR (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 86400) or (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 172800)) This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [mysql] duplicating lines
Thank you Jay and Baron for your help. This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, September 14, 2007 12:28 PM To: Weston, Craig (OFT) Cc: Jay Pipes; mysql@lists.mysql.com Subject: Re: [mysql] duplicating lines I think you want to use a GROUP BY query. Try this article: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row -per-group-in-sql/ Baron Weston, Craig (OFT) wrote: Hi, I have narrowed the query - I just want to return the FIRST instance of each ticket, ie the Source. Here is the table: CREATE TABLE `Thedata1` ( `Source` varchar(15) collate latin1_general_ci NOT NULL default '', `Close_Time` datetime NOT NULL default '-00-00 00:00:00', `start_Time` datetime default NULL, COMMENT 'correct time in Seconds' `start_Time2` datetime default NULL COMMENT 'Original Time in Seconds', `Original_Cycle_Time` decimal(11,4) default NULL, `workday` double(20,0) default NULL, PRIMARY KEY (`Source`,`start_Time2`,`Close_Time`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My best guess on how to do this would be a self join? select `a1`.`Source, If(`a1`.`workday` `a2`.`workday`, `a1`.`workday`,`a2`.`workday`) from `Thedata1` as `a1` inner join `Thedata1` as `a2` on `a1`.`Source` = `a2`.`table1` and `a1`.`workday` `a2`.`workday` There can be more than 2 duplicates, and occasionally no duplicates. I really am not sure why this isn't working. Can anyone offer an insight? Thank you, Craig -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Thursday, September 13, 2007 1:27 PM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [mysql] duplicating lines Hi Craig, would you mind posting the SHOW CREATE TABLE for the tables in question? I'm having trouble determining what is the primary key for your service ticket table... Thanks, -Jay Weston, Craig (OFT) wrote: Hello again, I am having a duplication of results problem. I believe my query (below) is giving me exactly what I deserve in writing it. What it returns to me is a row for each status. What I would most want would be a single row with the oldest status - IE the status datetime that happened earliest. What I am trying to do is determine when a service desk ticket first enters any one of these three categories. I am not using distinct on `thedata2`.`Source` as this does not effect the result set. I have 2 tables. One of the tables lists all the ticket information at time of the ticket being closed. The other has an entry referenced by ticket number for each time a ticket is touched or updated. So what I am trying to do is identify the last time it was touched with the appropriate status change. Does anyone have any idea what I could do to eliminate the duplicate with the oldest time? I am experimenting in the idea of a subquery but can't think of anything else. ??? Thanks, craig SELECT `thedata2`.`Source`, `thedata1`.`Status`, `thedata2`.`Priority`, `thedata1`.`start_Time`, `thedata1`.`Close_Time`, `thedata1`.`workday`'cycletime' FROM `thedata2` Inner Join `thedata1` ON `thedata1`.`Source` = `thedata2`.`SourceSR` WHERE (`thedata1`.`Status` like 'Resolved' OR `thedata1`.`Status` like 'Restored' OR `thedata1`.`Status` like 'Isolation') and `thedata2`.`Open_Time` BETWEEN '2007-02-01 00:00' AND '2007-08-31 23:59:59' And ((`thedata2`.`Priority` = 1 and `thedata1`.`workday` 14400) OR (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 86400) or (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 172800)) This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[mysql] duplicating lines
Hello again, I am having a duplication of results problem. I believe my query (below) is giving me exactly what I deserve in writing it. What it returns to me is a row for each status. What I would most want would be a single row with the oldest status - IE the status datetime that happened earliest. What I am trying to do is determine when a service desk ticket first enters any one of these three categories. I am not using distinct on `thedata2`.`Source` as this does not effect the result set. I have 2 tables. One of the tables lists all the ticket information at time of the ticket being closed. The other has an entry referenced by ticket number for each time a ticket is touched or updated. So what I am trying to do is identify the last time it was touched with the appropriate status change. Does anyone have any idea what I could do to eliminate the duplicate with the oldest time? I am experimenting in the idea of a subquery but can't think of anything else. ??? Thanks, craig SELECT `thedata2`.`Source`, `thedata1`.`Status`, `thedata2`.`Priority`, `thedata1`.`start_Time`, `thedata1`.`Close_Time`, `thedata1`.`workday`'cycletime' FROM `thedata2` Inner Join `thedata1` ON `thedata1`.`Source` = `thedata2`.`SourceSR` WHERE (`thedata1`.`Status` like 'Resolved' OR `thedata1`.`Status` like 'Restored' OR `thedata1`.`Status` like 'Isolation') and `thedata2`.`Open_Time` BETWEEN '2007-02-01 00:00' AND '2007-08-31 23:59:59' And ((`thedata2`.`Priority` = 1 and `thedata1`.`workday` 14400) OR (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 86400) or (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 172800)) This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: Does this MySQL client exist?
You could look at Navicat, a non-free interface, does SSH and so on. http://navicat.com/index.html This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Christoph Boget [mailto:[EMAIL PROTECTED] Sent: Thursday, September 13, 2007 2:06 PM To: mysql@lists.mysql.com Subject: Does this MySQL client exist? I did a search and couldn't find anything like what I'm looking for and though I doubt something like this does exist, I figured I'd ask anyway. Is there a client (not phpMyAdmin) that can connect to a server (that is running MySQL) using SSH and connect to the database that way? Right now, the only way we are allowed to access the actual server is by using either SSH or SFTP. The only way we can access the MySQL database on that server is either use phpMyAdmin (which I don't particularly care for; not to disparage the hard work of the developers, it's just a matter of personal preference) or use the command line. I'm hoping that there is client software out there that can do what I'm looking for. Does it exist? thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [MYSQL]Time formatting for cycle time.
Peter, Baron and all,
I think that I am almost there. Here's my query to return
cycle time in hours:minutes:seconds accounting for business hours. There
is some discussion as to when a ticket cycle time ends - for example if
a person works on a ticket at 10:00 at night and closes it, it should
end then, not at close of business that day.
Thank you for your help. This is more complex than I really
feel qualified for and you have really helped me.
Regards,
Craig
DELIMITER $$
DROP FUNCTION IF EXISTS `BizHoursTimeDiff` $$
CREATE [EMAIL PROTECTED] FUNCTION `BizHoursTimeDiff`( d1
DATETIME, d2 DATETIME ) RETURNS char(30) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE dow1, dow2, days, wknddays INT;
DECLARE tdiff CHAR(20);
SET dow1 = DAYOFWEEK(d1);
SET dow2 = DAYOFWEEK(d2);
set @dayEnd = (select time(`business_hours`.`Day_End`) from
`business_hours` limit 1);
SET @dayStart = (select time(`business_hours`.`Day_Start`) from
`business_hours` limit 1);
set @d1 = if (TIME(d1) @dayEnd,@dayEnd,d1);
set @d1 = if (TIME(d1)
@dayStart,@dayStart,d1);
#set @d2 = if (TIME(@d2) @dayStart,@dayStart,@d2);
#set @d2 = if @dayEnd,@dayEnd,@d2);
SET tdiff = TIMEDIFF( TIME(d2), TIME(d1) );
SET days = DATEDIFF(d2,d1);
SET wknddays = 2 * FLOOR( days / 7 ) +
IF( dow1 = 1 AND dow2 1,1,
IF( dow1 = 7 AND dow2 = 1, 1,
IF( dow1 1 AND dow1 dow2, 2,
IF( dow1 7 AND dow2 = 7, 1, 0 )
)
)
);
SET @tdiff = tdiff;
SET days = FLOOR(days - wkndDays) - (IF( ASCII(tdiff) = 45, 1, 0 ) +
(SELECT count(*) FROM `holidays` WHERE `holidays`.`date` BETWEEN d1 AND
d2 AND WEEKDAY(`Holidays`.`date`)5));
SET tdiff = IF( ASCII(tdiff) = 45, TIMEDIFF( '24:00:00',
SUBSTRING(tdiff,2)), TIMEDIFF( tdiff, '00:00:00' ));
SET @hr = days * left((@dayEnd - @dayStart),2) + left(tdiff,2);
SET @min = mid(tdiff,4,2);
SET @sec = mid(tdiff,7,2);
RETURN concat_ws(':',@hr,@min,@sec);
END $$
DELIMITER ;
This e-mail, including any attachments, may be confidential, privileged or
otherwise legally protected. It is intended only for the addressee. If you
received this e-mail in error or from someone who was not authorized to send it
to you, do not disseminate, copy or otherwise use this e-mail or its
attachments. Please notify the sender immediately by reply e-mail and delete
the e-mail from your system.
RE: [MYSQL]Time formatting for cycle time.
Wow, thanks. Lots to think about. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, August 27, 2007 10:18 PM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [MYSQL]Time formatting for cycle time. Craig, I am working on Martin Minka's date diff function as found at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful thing. However, I am trying to alter it or identify a similar function that instead of giving me the number of days between two dates it returns the number of workday hours:minutes between two datetimes, or some other date differential (such as an exact number of days between two dates with remainder) Here's a logically equivalent datediff calc, mebbe slightly simpler: SET @d1 = '2007-1-1'; SET @d2 = '2007-3-31'; SET @dow1 = DAYOFWEEK(@d1); SET @dow2 = DAYOFWEEK(@d2); SET @days = DATEDIFF(@d2,@d1); SET @wknddays = 2 * FLOOR( @days / 7 ) + IF( @dow1 = 1 AND @dow2 1, 1, IF( @dow1 = 7 AND @dow2 = 1, 1, IF( @dow1 1 AND @dow1 @dow2, 2, if( @dow1 7 AND @dow2 = 7, 1, 0 ) ) ) ); SELECT FLOOR(@[EMAIL PROTECTED]) AS BizDays; To include time in the difference, you could adopt as a return convention a string format like 'N days hh:mm:ss', where N is the date difference calculated above, minus one if the time portion of d1 is later than than that of d2. Something like this: SET @d1 = '2007-1-1 00:00:00'; SET @d2 = '2007-3-31 12:00:00'; SET @tdiff = TIMEDIFF( TIME(@d1), TIME(@d2) ); SET @dow1 = DAYOFWEEK(@d1); SET @dow2 = DAYOFWEEK(@d2); SET @days = DATEDIFF(@d2,@d1); SET @wknddays = 2 * FLOOR( @days / 7 ) + IF( @dow1 = 1 AND @dow2 1, 1, IF( @dow1 = 7 AND @dow2 = 1, 1, IF( @dow1 1 AND @dow1 @dow2, 2, IF( @dow1 7 AND @dow2 = 7, 1, 0 ) ) ) ); SET @days = FLOOR(@days - @wkndDays) - IF( @tdiff 0, 1, 0 ); SET @tdiff = IF( ASCII(@tdiff) = 45, SUBSTRING(@tdiff,2), TIMEDIFF( '24:00:00', @tdiff )); SELECT CONCAT( @days, ' days ', @tdiff ); PB - Weston, Craig (OFT) wrote: Hello all, I am working on Martin Minka's date diff function as found at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful thing. However, I am trying to alter it or identify a similar function that instead of giving me the number of days between two dates it returns the number of workday hours:minutes between two datetimes, or some other date differential (such as an exact number of days between two dates with remainder) I hate asking open ended questions, but can anyone give me any hints as to how to make this conversion? I may be able to figure it out... In a few weeks. My long term goal is to identify the business hours(minutes, seconds, whatever) between two dates, taking into account weekends, holidays, and business hours. Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.12.9/975 - Release Date: 8/26/2007 9:34 PM
RE: [MYSQL]Time formatting for cycle time.
Ok, So, What I have come up with (so far) as a variant of Baron's query: SET @d1 = '2007-2-1 00:00:00';# Start date SET @d2 = '2007-2-28 23:59:59';# End date SET @tdiff = TIMEDIFF( TIME(@d1), TIME(@d2) ); SET @dow1 = DAYOFWEEK(@d1); SET @dow2 = DAYOFWEEK(@d2); SET @days = DATEDIFF(@d2,@d1); SET @wknddays = 2 * FLOOR( @days / 7 ) + IF( @dow1 = 1 AND @dow2 1, 1, IF( @dow1 = 7 AND @dow2 = 1, 1, IF( @dow1 1 AND @dow1 @dow2, 2, IF( @dow1 7 AND @dow2 = 7, 1, 0 ) ) ) ); SET @days2 = FLOOR(@days - @wkndDays) - (IF( @tdiff 0, 1, 0 ) + (SELECT count(*) FROM `resource_data`.`holidays` WHERE `resource_data`.`holidays`.`date` BETWEEN @d1 AND @d2)); SET @tdiff = IF( ASCII(@tdiff) = 45, SUBSTRING(@tdiff,2), TIMEDIFF( '24:00:00', @tdiff )); SELECT CONCAT( @days2, ' days ', @tdiff ), @wknddays, @days, @days2, @tdiff, IF( @tdiff 0, 1, 0 ),#Test value 1 IF( @tdiff 0, 1, 0 ),#Test value 2 FLOOR(@days - @wkndDays), (SELECT count(*) FROM `holidays` WHERE `holidays`.`date` BETWEEN @d1 AND @d2); What I did was reverse the @tdiff equation to add a day for a positive @tdiff instead of subtracting it. My test month, February, has 2 holidays in it. The results: ++---+---+ | CONCAT( @days2, ' days ', @tdiff ) | @wknddays | @days | ++---+---+ | 17 days 23:59:59 | 8 | 27| ++---+---+ +++--+ @days2 | IF( @tdiff 0, 1, 0 ) | IF( @tdiff 0, 1, 0 ) | holidays | +++--+ 17 | 0 | 1 |2 | +++--+ This seems to account for the remainder better? Of course the original reason it was added was to take into account times that were earlier, which it does not seem to do? Thank you for your help! Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [MYSQL]Time formatting for cycle time.
Ok then, Now I have a result set so very close to where I want to be. The query thus far: mysql SET @d1 = '2007-02-01 18:24:04';# Start date SET @d2 = '2007-2-28 23:05:40';# End date set @wkldays = (select WorkDayDiff`(@d2,@d1)-1); Set @wkldays2 = if(@wkldays 0,1,0); set @Day_End = (select `business_hours`.`Day_End` from `resource_data`.`business_hours` limit 1); set @Day_Start = (select `business_hours`.`Day_Start` from `resource_data`.`business_hours` limit 1); Set @t1 = (IF((HOUR(@d1))@Day_Start,7,IF((HOUR(@d1))@Day_End ,18,HOUR(@d1+(IF((HOUR(@d1))@Day_Start,0,IF((HOUR(@d1))@Day_End ,0,MINUTE(@d1)))/60)+(IF((HOUR(@d1))@Day_Start,0,IF((HOUR(@d1))@Day_En d ,0,SECOND(@d1)))/3600); Set @t2 = (IF((HOUR(@d2))@Day_Start,7,IF((HOUR(@d2))@Day_End ,18,HOUR(@d2+(IF((HOUR(@d2))@Day_Start,0,IF((HOUR(@d2))@Day_End ,0,MINUTE(@d2)))/60)+(IF((HOUR(@d2))@Day_Start,0,IF((HOUR(@d2))@Day_En d ,0,SECOND(@d2)))/3600); set @tdif = IF(@t1 @t2,((22 - @t1) + (@t2 - 11)),@t2 - @t1); select @wkldays, @t1, @t2, @wkldays2, @tdif, ((@[EMAIL PROTECTED])*11)[EMAIL PROTECTED] 'hours'; With the results: +--+-+-+---+--- | @wkldays | @t1 | @t2 | @wkldays2 | @tdif +--+-+-+---+--- | 17 | 18 | 18 | 0 | 0 +--+-+-+---+--- ++ | hours | ++ | 187.00 | ++ My question now, is does anyone know how I could alter this query to get precision in the hours? The idea is to get at least to the minute resolution. I tried ((@[EMAIL PROTECTED])*11)[EMAIL PROTECTED] + 0., but I have few brain cells not on strike. Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL]Time formatting for cycle time.
Hello all, I am working on Martin Minka's date diff function as found at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful thing. However, I am trying to alter it or identify a similar function that instead of giving me the number of days between two dates it returns the number of workday hours:minutes between two datetimes, or some other date differential (such as an exact number of days between two dates with remainder) I hate asking open ended questions, but can anyone give me any hints as to how to make this conversion? I may be able to figure it out... In a few weeks. My long term goal is to identify the business hours(minutes, seconds, whatever) between two dates, taking into account weekends, holidays, and business hours. Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
[mysql] reverse join
Hi there, I have a situation where I have duplicate data within several tables, but they are all indexed in the same way. How do I pull out all the non-duplicate records? Would I have to create a temporary table, add one data set, then add the second where it doesn't equal the first, and then do the same with the third? Or is there a cleaner way? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: [mysql] reverse join
Thanks for your help. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 10:47 AM To: Weston, Craig (OFT); mysql@lists.mysql.com Subject: Re: [mysql] reverse join Craig How do I pull out all the non-duplicate records? With an exclusion join. To find non-duplicates with respect to one key... SELECT keycandidate FROM tbl t1 LEFT JOIN tbl t2 ON t1.keycandidate=t2.keycandidate WHERE t2.keycandidate IS NULL; To expand the comparison to multiple keys... SELECT key1, key2, ... FROM tbl t1 LEFT JOIN tbl t2 ON t1.key1=t2.key2 AND t1.key2=t2.key2 AND ... WHERE t2.key1 IS NULL; PB Weston, Craig (OFT) wrote: Hi there, I have a situation where I have duplicate data within several tables, but they are all indexed in the same way. How do I pull out all the non-duplicate records? Would I have to create a temporary table, add one data set, then add the second where it doesn't equal the first, and then do the same with the third? Or is there a cleaner way? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.11.2/931 - Release Date: 8/1/2007 4:53 PM
[MYSQL]networkdays and business hours
Hi there,
I am trying to compute cycle time between two dates in mysql. This is
a measurement of business hours between two datetimes, in hours. In
Excel, I can use the networkdays function to get partway there. What
I am trying to calculate is:
# hours between start time and resolved time, excluding weekend days,
non business hours (business hours are 0700 to 16:00) and holidays. I
have a list of holidays
Based on some of the queries I have looked at in
http://www.artfulsoftware.com/infotree/mysqlquerytree.php I have so far
an equation that will take into account the weekends, I think:
@dow1 := DAYOFWEEK('d1') AS dow1,
@dow2 := DAYOFWEEK('d2') AS dow2,
@days := DATEDIFF('d2','d1') AS Days,
@wknddays := 2 * FLOOR( @days / 7 ),
if(@dow1 = 1 AND @dow2 1, 1,if(@dow1 = 7 AND @dow2 = 1, 1,if(@dow1 1
AND @dow1 @dow2, 2,if(@dow1 7 AND @dow2 = 7, 1, 0 AS WkndDays,
@days - @wkndDays AS BizDays
At least this is close to the original as posted over at artful. I need
the number in hours instead of days however so I am exploring with
TIMEDIFF instead of datediff.
For further background, here is the equation in Excel:
=IF(C2=,,(IF(H2=1,((EndDT)-(D2)),(NETWORKDAYS(D2,EndDT,HolidayList)-
1)*(DayEnd-DayStart)+IF(NETWORKDAYS(D2,EndDT,HolidayList),MEDIAN(MOD(End
DT,1),DayEnd,DayStart),DayEnd)-MEDIAN(NETWORKDAYS(EndDT,D2,HolidayList)*
MOD(D2,1),DayEnd,DayStart))*24))
Note that the Excel equation takes into account conditions I haven't
mentioned above,specifically if data doesn't exist, and if the site name
is listed on a critical site list and thus gets 24 hour support.
Does anyone have any ideas to further this quest?
This e-mail, including any attachments, may be confidential, privileged or
otherwise legally protected. It is intended only for the addressee. If you
received this e-mail in error or from someone who was not authorized to send it
to you, do not disseminate, copy or otherwise use this e-mail or its
attachments. Please notify the sender immediately by reply e-mail and delete
the e-mail from your system.
[mysql] crosstabs
In Access, designing a crosstab query is fairly straight forward. You just choose crosstab in the query design form. Is there some simple way to replicate this functionality within mysql? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: [mysql] crosstabs
This is a good idea. Thanks. This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 31, 2007 3:56 PM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [mysql] crosstabs Hi, Weston, Craig (OFT) wrote: In Access, designing a crosstab query is fairly straight forward. You just choose crosstab in the query design form. Is there some simple way to replicate this functionality within mysql? The simplest way -- if you're used to Access -- is probably to use Access as the front-end and MySQL as the backend, through ODBC. Otherwise, http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cross referenced tables
Hi there, I have a situation where I am trying to cross reference entries based on (for example) date and cost center. So without specifying the cost center sites in advance, how could I get a table returned out of mysql like: This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. ___ -Date - Site 1 - site 2 - site 3 - --- -1/20/2007 - 4 - 3- null - -1/21/2007 - 4 - 4- 2 - Where the number is a count of tickets or something from each site. So I don't know in advance what the column headers are but I do have that information in a table, referenced by date. Does that make any sense? Any ideas how best to go about this type of construction? Thanks, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query against two data types
Hello all. I have a query I am tring to implement and I can't wrap my mind around it somehow. Can anyone help? . I have two tables within a database. Within the first table, I have a blob field that basically has a large amount of text. Within the second table, I have a list of strings (in this case application names). Is there some way that I can search the blob for any occurrence of any of the strings? (returning the string name)? There could be more than 1 match. Any ideas? Thank you for any help you may offer. Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
RE: Query against two data types
Baron, Thank you for your answer. The information you refered me to is very interesting and will take some time to study. However most of what it appears to cover is getting the search data into a table, which is already accomplished. Basically the query I am trying to come up with would be something like: SELECT `ticket`.`Work Log`, `server_list`.`application_name`, FROM `ticket`,`server_list` Where `ticket`.`Work Log` like `server_list`.`application_name` This is formatted like if I were searching for a single text string such as '%access%' (i.e. Where `ticket`.`Work Log` like '%access%') but I don't understand how to write the query to return values from within the other table. I don't need anything especially fancy, I just need to understand. The big hole in my understanding is that one table has a single list, so the other is large text block which could contain words within the single list. I just havent figured out how to deal with this yet. Again, I really appreciate your response previously, thank you. Regards, Craig -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, July 12, 2007 1:17 PM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: Query against two data types Hi, If at all possible, normalizing the data is far and away the best solution. Failing that, you can use regular expressions RLIKE REPLACE(col, ' ', '|') or INSTR(). Another solution is fulltext search, but it would have many limitations depending on what you really need to do. Otherwise, try the approaches given here: http://www.sommarskog.se/arrays-in-sql.html But prepare yourself for some deep thinking if you want to understand how they work, especially the one that works against a table of numbers; it's a little murky, though it is probably the best of the solutions. Weston, Craig (OFT) wrote: Hello all. I have a query I am tring to implement and I can't wrap my mind around it somehow. Can anyone help? . I have two tables within a database. Within the first table, I have a blob field that basically has a large amount of text. Within the second table, I have a list of strings (in this case application names). Is there some way that I can search the blob for any occurrence of any of the strings? (returning the string name)? There could be more than 1 match. Any ideas? Thank you for any help you may offer. Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
