General SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesGeneral SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old October 13th, 2011, 04:50 AM
mauzzz mauzzz is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2011
Posts: 1 mauzzz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 41 m
Reputation Power: 0
Help to build a mysql query

Hi,

Firstly I would like to apologize for the Title name, I just had no idea how to name it correctly.
My problem is: I have two tables, a table with data and the second a log table. I can't change the tables specs because the software we bought is using this tables and changes will brake something. This data is not from real world, the real data is something else but the structure and the sample is correctly chosen.

Data table has a structure like this:
id | name | status | path |
a1 | worker1 | alfa | loc1.txt |
a2 | worker2 | beta | loc2.txt |
a3 | worker3 | alfa | loc3.txt |
b1 | security1 | test1 | loc8.txt |
a4 | worker3 | delta | loc4.txt |
c4 | wireless7 | comm | loc9.txt |
a5 | worker1 | sigmna | loc5.txt |
a6 | worker1 | gama | loc6.txt |


LOG table has a structure like this:
lid | ldate | ltime |
a1 | 2011-10-10 | 10:19:25 |
a2 | 2011-10-11 | 08:23:18 |
a6 | 2011-10-10 | 14:19:25 |
a3 | 2011-10-10 | 10:19:25 |
a1 | 2011-10-11 | 10:22:13 |
a1 | 2011-10-13 | 18:19:25 |
a4 | 2011-10-11 | 10:19:25 |
a5 | 2011-10-11 | 10:22:48 |
a6 | 2011-10-12 | 10:19:25 |

What I have to do is randomly select ID from data table with this conditions:
- select all where id should start with "a%"
- a selected worker must not repeat with the same status in next 24 hours.
- a selected worker could be repeated with different status, but not before 4 hours
- limit by 80 results, in this results the same worker must not repeated as we are building a list


What I currently have is:

Code:
SELECT A.id, A.name, A.status, A.path 
FROM data A LEFT OUTER JOIN log B ON A.id=B.lid AND
((str_to_date(CONCAT(B.ldate,' ',B.ltime),'%Y-%m-%d %H:%i:%s') < NOW() - INTERVAL 24 HOUR)) 
WHERE A.id like "a%" order by A.name limit 1,80


This query currently removes everything that should't be repeated in 24 hours, but if I remove that I can't correctly add the condition, that a worker with different status can be selected but not before 4 hours. I am stuck for a couple of days now....help appreciated.



Thanks

Reply With Quote
  #2  
Old June 24th, 2012, 02:58 PM
bitsmed bitsmed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2012
Posts: 11 bitsmed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 59 m 53 sec
Reputation Power: 0
I don't think this could be done, as to your rules:

Quote:
Originally Posted by mauzzz
What I have to do is randomly select ID from data table with this conditions:
- select all where id should start with "a%"
- a selected worker must not repeat with the same status in next 24 hours.
- a selected worker could be repeated with different status, but not before 4 hours
- limit by 80 results, in this results the same worker must not repeated as we are building a list


Mainly because you want id's, but only one per name.
Other from that, I think (with some tweaking) you might be able to build your list with this.

This sql will give you only the names that comply to your rules:

Code:
select a.name
  from data as a
       left outer join log as b
                    on b.lid=a.id
       left outer join data as c
                    on c.name=a.name
       left outer join log as d
                    on d.lid=c.id
                   and (!(d.lid=b.lid
                   and    d.ldate=b.ldate
                   and    d.ltime=b.ltime
                         )
                       )
 where a.id like 'a%'
 group by a.name
 having sum(case when a.status=c.status
                  and timediff(str_to_date(concat(d.ldate,' ',d.ltime),'%Y-%m-%d %H:%i:%s')
                              ,str_to_date(concat(b.ldate,' ',b.ltime),'%Y-%m-%d %H:%i:%s')
                              )
                      between time('-24:00:00') and time('24:00:00')
                 then 1
                 else 0
            end
           )=0
    and sum(case when timediff(str_to_date(concat(d.ldate,' ',d.ltime),'%Y-%m-%d %H:%i:%s')
                              ,str_to_date(concat(b.ldate,' ',b.ltime),'%Y-%m-%d %H:%i:%s')
                              )
                      between time('-4:00:00') and time('4:00:00')
                 then 1
                 else 0
            end
           )=0
 order by a.name
 limit 0,80


This sql will show you, what actually is compared (column "greenrule" will be greater than zero if same worker and status is repeated within 24 hours ; column "orangerule" will be greater than zero if same worker switched "job" within 4 hours):
Code:
select *
      ,case when a.status=c.status
             and timediff(str_to_date(concat(d.ldate,' ',d.ltime),'%Y-%m-%d %H:%i:%s')
                         ,str_to_date(concat(b.ldate,' ',b.ltime),'%Y-%m-%d %H:%i:%s')
                         )
                 between time('-24:00:00') and time('24:00:00')
            then 1
            else 0
       end as greenrule
      ,case when timediff(str_to_date(concat(d.ldate,' ',d.ltime),'%Y-%m-%d %H:%i:%s')
                         ,str_to_date(concat(b.ldate,' ',b.ltime),'%Y-%m-%d %H:%i:%s')
                         )
                 between time('-4:00:00') and time('4:00:00')
            then 1
            else 0
       end as orangerule
  from data as a
       left outer join log as b
                    on b.lid=a.id
       left outer join data as c
                    on c.name=a.name
       left outer join log as d
                    on d.lid=c.id
                   and (!(d.lid=b.lid
                   and    d.ldate=b.ldate
                   and    d.ltime=b.ltime
                         )
                       )
 where a.id like 'a%'
 order by a.name
         ,a.status
         ,b.ldate
         ,b.ltime
         ,c.status
         ,d.ldate
         ,d.ltime


Edit: just realized, this thread is old (and it seems this forum is dead) - Sorry

Reply With Quote
  #3  
Old November 14th, 2012, 02:22 AM
KernWillia KernWillia is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 3 KernWillia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m 15 sec
Reputation Power: 0
Mainly because you want id's, but only one per name.URLURLURLURLURLURL

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesGeneral SQL Development > Help to build a mysql query


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap