Jump to content

Please help me on union query

ztimer

By ztimer
in MySQL Help

 Share

Recommended Posts

ztimer Member

ztimer

Posted
Posted

Hi,

 

Messed on this for like 4 days now and everything seems hopeless. I have 2 tables "punch_log" and "schedules"

 

The layout for "punch_log"

 

|  id    |  user_id  |  date_time                    |  end_time                    |  duration  | Status  |

---------------------------------------------------------------------------------------------------------------------

| 232    |      22        |  2011-07-01 05:16:43  |  2011-07-01 12:47:36 |    27053    |      1    |

| 232    |      22        |  2011-07-02 05:16:43  |  2011-07-02 12:47:36 |    27053    |      1    |

| 232    |      22        |  2011-07-03 05:16:43  |  2011-07-03 12:47:36 |    27053    |      1    |

| 232    |      22        |  2011-07-04 05:16:43  |  2011-07-04 12:47:36 |    27053    |      1    |

| 232    |      22        |  2011-07-06 05:16:43  |  2011-07-06 12:47:36 |    27053    |      1    |

| 232    |      22        |  2011-07-08 05:16:43  |  2011-07-08 12:47:36 |    27053    |      1    |

| 232    |      22        |  2011-07-09 05:16:43  |  2011-07-09 12:47:36 |    27053    |      1    |

---------------------------------------------------------------------------------------------------------------------

 

 

The layout for "schedules"

 

|  ID  |        date      | user_id  | duration |

------------------------------------------------------

|    1  |  2011-07-01 |      22    |        8    |

|    2  |  2011-07-02 |      22    |        9    |

|    3  |  2011-07-03 |      22    |        2    |

|    4  |  2011-07-04 |      22    |        4    |

|    5  |  2011-07-05 |      22    |        5    |

|    6  |  2011-07-06 |      22    |        3    |

|    7  |  2011-07-07 |      22    |        6    |

|    8  |  2011-07-08 |      22    |        4    |

------------------------------------------------------

 

 

The idea is to make a mysql view where the two tables are combined by date but there is a problem

lets say the punch_log has dates 01.07 and 02.07 and 03.07 but the schedules has 02.07, 03.07 and 04.07

i want to get the result to show all of the info in table and leave some lines as null if there is no

duration but the date eighter in schedule or punch_log. I hope the below hand written table gives a idea

what im after.

 

|      date      | user_id  |          date_time            |          end_time            |  duration  |  Status  | schedule_duration  |

-------------------------------------------------------------------------------------------------------------------------------------------------------

| 2011-07-01 |    22      | 2011-07-01 05:16:43    | 2011-07-01 12:47:36  |    27053    |      1      |            8                  |

| 2011-07-02 |    22      | 2011-07-02 05:16:43    | 2011-07-02 12:47:36  |    27053    |      1      |            9                  |

| 2011-07-03 |    22      | 2011-07-03 05:16:43    | 2011-07-03 12:47:36  |    27053    |      1      |            2                  |

| 2011-07-04 |    22      | 2011-07-04 05:16:43    | 2011-07-04 12:47:36  |    27053    |      1      |            4                  |

| 2011-07-05 |    22      |                null                  |              null                  |        null      |    null    |            5                  |

| 2011-07-06 |    22      | 2011-07-06 05:16:43    | 2011-07-06 12:47:36  |    27053    |      1      |            3                  |

| 2011-07-07 |    22      |                null                  |              null                |        null      |    null    |            6                  |

| 2011-07-08 |    22      | 2011-07-08 05:16:43    | 2011-07-08 12:47:36  |    27053    |      1      |            4                  |

| 2011-07-09 |    22      | 2011-07-09 05:16:43    | 2011-07-09 12:47:36  |    27053    |      1      |            null                |

-------------------------------------------------------------------------------------------------------------------------------------------------------

 

What i have so far. Its the best i have at the moment. :( Its not even close what it should be. I havent seen any usable

example on google eighter. Everywhere there is examples where id allways maches the id at the second table and the

data is only in 2 rows. no calculations ans so on.

 

SELECT

dates.date AS date,

  (SELECT schedules.duration FROM schedules WHERE schedules.date = dates.date AND schedules.user_id='44') AS schedule,

  (SELECT punch_log.date_time FROM punch_log WHERE punch_log.user_id ='44' AND DATE(punch_log.date_time) = dates.date GROUP BY dates.date) AS datentime,

  (SELECT SUM(round(punch_log.duration /60/60,2)) FROM punch_log WHERE punch_log.user_id ='44' AND punch_log.status = '1' AND DATE(punch_log.date_time) = dates.date) AS duration

FROM

  (SELECT DATE(punch_log.date_time) AS date FROM punch_log WHERE punch_log.user_id='44' UNION SELECT schedules.date AS date FROM schedules WHERE schedules.user_id='44') AS dates

LEFT OUTER JOIN schedules ON (dates.date = schedules.date)

WHERE

  schedules.date BETWEEN '2011-06-01' AND '2011-06-30'

GROUP BY

  dates.date

 

 

I used a date BETWEEN to limit the time to test the query.

 

PLEASE HELP. Im really stuck on this.

 

 

 

 

 

 

 

Link to comment
Share on other sites
ztimer Member

ztimer

Posted
  • Author
Posted

OK so i waited and waited for somebody to help and then it hit me

 

SELECT das.ID
    , das.date
    , das.user_id
    , das.worked
    , das.scheduled
    , das.timestart
    , das.timeend
    , schedules.comment
FROM
((
SELECT `schedules`.`ID` AS `ID`
     , `schedules`.`date` AS `date`
     , `schedules`.`user_id` AS `user_id`
     , `schedules`.`n1` AS `worked`
     , `schedules`.`duration` AS `scheduled`
     , `schedules`.`date_time` AS `timestart`
     , `schedules`.`end_time` AS `timeend`
     , `schedules`.`comment` AS `comment`
FROM
  `schedules`
GROUP BY
  `schedules`.`date`)
UNION
(
SELECT `punch_log`.`id` AS `ID`
     , cast(`punch_log`.`date_time` AS DATE) AS `date`
     , `punch_log`.`user_id` AS `user_id`
     , round(((`punch_log`.`duration` / 60) / 60), 2) AS `worked`
     , `punch_log`.`tags` AS `scheduled`
     , `punch_log`.`date_time` AS `timestart`
     , `punch_log`.`end_time` AS `timeend`
     , `punch_log`.`tags` AS `notes`
FROM
  `punch_log`
WHERE
  (`punch_log`.`status` = '1')
GROUP BY
  cast(`punch_log`.`date_time` AS DATE))) AS das
LEFT OUTER JOIN schedules ON (das.date = schedules.date)

 

Its still buggy as i need to get the group by date so i would see only a 1 date per user_id

Any ideas.??

Link to comment
Share on other sites
ztimer Member

ztimer

Posted
  • Author
Posted

Now i think i got my table ready. But there is still a problem. It will not group by date. How to group by date in a view. When i make another SELECT i get a error and in union i cant place group BY date it gives a error. Any ideas.

 

SELECT cast(`punch_log`.`date_time` AS DATE) AS `date`

    , cast(`punch_log`.`user_id` AS CHAR CHARSET utf8) AS `user_id`

    , round(((`punch_log`.`duration` / 60) / 60), 2) AS `worked_on_that_day`

    , `punch_log`.`tags` AS `scheduled_on_that_day`

    , `punch_log`.`tags` AS `comment`

    , cast(`punch_log`.`date_time` AS DATETIME) AS `date_time`

    , cast(`punch_log`.`end_time` AS DATETIME) AS `end_time`

FROM

  `punch_log`

WHERE

  (`punch_log`.`punch_type_id` = '1')

UNION ALL

SELECT cast(`schedules`.`date` AS DATE) AS `date`

    , cast(`schedules`.`user_id` AS CHAR CHARSET utf8) AS `user_id`

    , `schedules`.`n1` AS `worked_on_that_day`

    , `schedules`.`duration` AS `scheduled_on_that_day`

    , `schedules`.`comment` AS `comment`

    , cast(`schedules`.`n1` AS DATETIME) AS `date_time`

    , cast(`schedules`.`n1` AS DATETIME) AS `end_time`

FROM

  `schedules`

 

Well the biggest problem is behind be as a query to the view from php i can make it group and sum the results to get the result im after but im qurious is there a better way to have it done in the first place in the view.??

 

 

SELECT testview.`date`

    , testview.user_id

    , SUM(testview.worked_on_that_day) AS worked_on_that_day

    , SUM(testview.scheduled_on_that_day) AS scheduled_on_that_day

    , testview.`comment`

    , testview.date_time

    , testview.end_time

FROM

  testview

WHERE date BETWEEN '2011-06-01' AND '2011-06-30r' AND user_id = 44

GROUP BY `date`

 

RESULT WHAT I NEED :)

-------------------------------

1.06.2011 44 6,28 6,5 (null) 1.06.2011 6:36:47 1.06.2011 12:53:26

3.06.2011 44 6,08 6,5 (null) 3.06.2011 6:39:01 3.06.2011 12:43:35

7.06.2011 44 6,26 6,5 (null) 7.06.2011 6:26:19 7.06.2011 12:42:05

8.06.2011 44 7,37 6,5 (null) 8.06.2011 6:36:19 8.06.2011 13:58:42

9.06.2011 44 (null) 7,5 Arsti juures. Tunnid teeb järgi 23.06. (null) (null)

10.06.2011 44 8,43 7,5 (null) 10.06.2011 5:39:53 10.06.2011 14:05:26

13.06.2011 44 8,54 8 (null) 13.06.2011 5:36:00 13.06.2011 14:08:34

14.06.2011 44 8,46 8 (null) 14.06.2011 5:36:38 14.06.2011 14:04:22

15.06.2011 44 7,78 8 (null) 15.06.2011 5:44:27 15.06.2011 9:04:42

16.06.2011 44 7,99 8 (null) 16.06.2011 5:46:28 16.06.2011 13:46:00

17.06.2011 44 8,25 7 (null) 17.06.2011 5:46:16 17.06.2011 14:01:03

18.06.2011 44 8,84 (null) (null) 18.06.2011 5:20:27 18.06.2011 14:10:51

20.06.2011 44 8,19 7,5 (null) 20.06.2011 5:45:56 20.06.2011 13:57:11

21.06.2011 44 7,32 7,5 (null) 21.06.2011 5:44:39 21.06.2011 13:04:07

22.06.2011 44 6,44 4 (null) 22.06.2011 5:37:00 22.06.2011 12:03:35

23.06.2011 44 5,33 (null) (null) 23.06.2011 6:00:47 23.06.2011 11:20:43

27.06.2011 44 8,16 6,5 (null) 27.06.2011 5:51:38 27.06.2011 14:01:31

28.06.2011 44 8,28 6,5 (null) 28.06.2011 5:46:16 28.06.2011 14:03:15

29.06.2011 44 8,44 6,5 (null) 29.06.2011 5:39:53 29.06.2011 14:06:00

30.06.2011 44 6,93 6,5 (null) 30.06.2011 5:50:42 30.06.2011 12:46:15

 

 

Link to comment
Share on other sites
ztimer Member

ztimer

Posted
  • Author
Posted

Well the current problem is nicely described at the last post.?

Is there a way to group by date in making a mysql table view or not. Or is there a better soluton to get the table.

I mean like a faster way. If no grouping is done in the view then i must group it in a query loop for every user and it makes

the whole page load quite slowly. Thats why i was interested to get the view ready to have the data allready sorted and so on.

 

 

TLDR -- what's the current problem?

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.