Pivoting table and view to create a custom view in My SQL

Question

I am trying to create a a view for a kind of master time sheet output that the office can view. I already have a view that outputs the information for how long a user worked on a date that is then filtered for each user to view their times on their page.

I am no expert at this I am still learning so I also understand that the DB is not optimal, but my main focus is figuring out what I am doing wrong with my query for the pivoted table. The dates are always getting updated and my plan is to allow the user to filter the output via PHP and maybe limit data to the last 90 days in this view.

The idea is that it will output as below

           | 09/09/2020 | 10/09/2020 | 11/09/2020 | 12/09/2020 | 13/09/2020 
---------: | :--------- | :--------- | :--------- | :--------- | :---------
employee 1 | 09:45:00   | 09:45:00   | 09:45:00   | 09:45:00   | 09:45:00   
employee 2 | 09:45:00   | 09:45:00   | 09:45:00   | 09:45:00   | 09:45:00

SQL Query I am trying.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(s.date = "', `date`,'", `value`,"")) AS ',date)
              ) INTO @sql
FROM daily_hours;


SET @sql = CONCAT('SELECT s.uid,  ', @sql, ' 
                  FROM daily_hours s
                 GROUP BY s.uid
                 ORDER BY s.uid');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;

Test DB Export

-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Sep 22, 2020 at 04:11 AM
-- Server version: 8.0.21
-- PHP Version: 7.3.21

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `timeclock`
--

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

--
-- Table structure for table `clockrecords`
--

DROP TABLE IF EXISTS `clockrecords`;
CREATE TABLE IF NOT EXISTS `clockrecords` (
  `ClockRef` int NOT NULL AUTO_INCREMENT,
  `UID` int NOT NULL,
  `FirstName` varchar(25) NOT NULL,
  `LastName` varchar(25) NOT NULL,
  `ClockDirection` enum('In','Out') NOT NULL,
  `ClockStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Location` enum('Unknown','Location1','Location2','Location3','Manual') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'Unknown',
  PRIMARY KEY (`ClockRef`)
) ENGINE=MyISAM AUTO_INCREMENT=81 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `clockrecords`
--

INSERT INTO `clockrecords` (`ClockRef`, `UID`, `FirstName`, `LastName`, `ClockDirection`, `ClockStamp`, `Location`) VALUES
(31, 1, 'Jane', 'Smith', 'In', '2020-09-12 06:30:00', 'Manual'),
(32, 1, 'Jane', 'Smith', 'Out', '2020-09-12 11:45:00', 'Manual'),
(35, 1, 'Jane', 'Smith', 'In', '2020-09-12 12:30:00', 'Manual'),
(36, 1, 'Jane', 'Smith', 'Out', '2020-09-12 17:00:00', 'Manual'),
(37, 1, 'Jane', 'Smith', 'In', '2020-09-13 06:30:00', 'Manual'),
(38, 1, 'Jane', 'Smith', 'Out', '2020-09-13 11:45:00', 'Manual'),
(39, 1, 'Jane', 'Smith', 'In', '2020-09-13 12:30:00', 'Manual'),
(40, 1, 'Jane', 'Smith', 'Out', '2020-09-13 17:00:00', 'Manual'),
(41, 1, 'Jane', 'Smith', 'In', '2020-09-14 06:30:00', 'Manual'),
(42, 1, 'Jane', 'Smith', 'Out', '2020-09-14 11:45:00', 'Manual'),
(43, 1, 'Jane', 'Smith', 'In', '2020-09-14 12:30:00', 'Manual'),
(44, 1, 'Jane', 'Smith', 'Out', '2020-09-14 17:00:00', 'Manual'),
(45, 1, 'Jane', 'Smith', 'In', '2020-09-15 06:30:00', 'Manual'),
(46, 1, 'Jane', 'Smith', 'Out', '2020-09-15 11:45:00', 'Manual'),
(47, 1, 'Jane', 'Smith', 'In', '2020-09-15 12:30:00', 'Manual'),
(48, 1, 'Jane', 'Smith', 'Out', '2020-09-15 17:00:00', 'Manual'),
(49, 1, 'Jane', 'Smith', 'In', '2020-09-09 07:30:00', 'Manual'),
(50, 1, 'Jane', 'Smith', 'Out', '2020-09-09 11:45:00', 'Manual'),
(51, 1, 'Jane', 'Smith', 'In', '2020-09-09 12:30:00', 'Manual'),
(52, 1, 'Jane', 'Smith', 'Out', '2020-09-09 17:00:00', 'Manual'),
(53, 1, 'Jane', 'Smith', 'In', '2020-09-10 06:30:00', 'Manual'),
(54, 1, 'Jane', 'Smith', 'Out', '2020-09-10 11:45:00', 'Manual'),
(55, 1, 'Jane', 'Smith', 'In', '2020-09-10 12:30:00', 'Manual'),
(56, 1, 'Jane', 'Smith', 'Out', '2020-09-10 17:00:00', 'Manual'),
(57, 3, 'John', 'Smith', 'In', '2020-09-12 06:30:00', 'Manual'),
(58, 3, 'John', 'Smith', 'Out', '2020-09-12 11:45:00', 'Manual'),
(59, 3, 'John', 'Smith', 'In', '2020-09-12 12:30:00', 'Manual'),
(60, 3, 'John', 'Smith', 'Out', '2020-09-12 17:00:00', 'Manual'),
(61, 3, 'John', 'Smith', 'In', '2020-09-13 06:30:00', 'Manual'),
(62, 3, 'John', 'Smith', 'Out', '2020-09-13 11:45:00', 'Manual'),
(63, 3, 'John', 'Smith', 'In', '2020-09-13 12:30:00', 'Manual'),
(64, 3, 'John', 'Smith', 'Out', '2020-09-13 17:00:00', 'Manual'),
(65, 3, 'John', 'Smith', 'In', '2020-09-14 06:30:00', 'Manual'),
(66, 3, 'John', 'Smith', 'Out', '2020-09-14 11:45:00', 'Manual'),
(67, 3, 'John', 'Smith', 'In', '2020-09-14 12:30:00', 'Manual'),
(68, 3, 'John', 'Smith', 'Out', '2020-09-14 17:00:00', 'Manual'),
(69, 3, 'John', 'Smith', 'In', '2020-09-15 06:30:00', 'Manual'),
(70, 3, 'John', 'Smith', 'Out', '2020-09-15 11:45:00', 'Manual'),
(71, 3, 'John', 'Smith', 'In', '2020-09-15 12:30:00', 'Manual'),
(72, 3, 'John', 'Smith', 'Out', '2020-09-15 17:00:00', 'Manual'),
(73, 3, 'John', 'Smith', 'In', '2020-09-09 06:30:00', 'Manual'),
(74, 3, 'John', 'Smith', 'Out', '2020-09-09 11:45:00', 'Manual'),
(75, 3, 'John', 'Smith', 'In', '2020-09-09 12:30:00', 'Manual'),
(76, 3, 'John', 'Smith', 'Out', '2020-09-09 17:00:00', 'Manual'),
(77, 3, 'John', 'Smith', 'In', '2020-09-10 06:30:00', 'Manual'),
(78, 3, 'John', 'Smith', 'Out', '2020-09-10 11:45:00', 'Manual'),
(79, 3, 'John', 'Smith', 'In', '2020-09-10 12:30:00', 'Manual'),
(80, 3, 'John', 'Smith', 'Out', '2020-09-10 17:00:00', 'Manual');

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

--
-- Stand-in structure for view `daily_hours`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `daily_hours`;
CREATE TABLE IF NOT EXISTS `daily_hours` (
`uid` int
,`date` varchar(10)
,`TotalTime` time
);

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

--
-- Table structure for table `employees`
--

DROP TABLE IF EXISTS `employees`;
CREATE TABLE IF NOT EXISTS `employees` (
  `UID` int NOT NULL AUTO_INCREMENT,
  `Active` enum('Yes','No') NOT NULL DEFAULT 'Yes',
  `FirstName` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `LastName` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Pin` int NOT NULL,
  `Admin` enum('Yes','No') NOT NULL DEFAULT 'No',
  PRIMARY KEY (`UID`),
  UNIQUE KEY `Pin` (`Pin`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`UID`, `Active`, `FirstName`, `LastName`, `Pin`, `Admin`) VALUES
(1, 'Yes', 'Jane', 'Smith', 1234, 'Yes'),
(3, 'Yes', 'John', 'Smith', 4321, 'No');

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

--
-- Stand-in structure for view `shiftlengths`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `shiftlengths`;
CREATE TABLE IF NOT EXISTS `shiftlengths` (
`uid` int
,`length` time
,`date` varchar(10)
);

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

--
-- Stand-in structure for view `shiftlist`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `shiftlist`;
CREATE TABLE IF NOT EXISTS `shiftlist` (
`uid` int
,`in_time` timestamp
,`out_time` timestamp
);

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

--
-- Structure for view `daily_hours`
--
DROP TABLE IF EXISTS `daily_hours`;

DROP VIEW IF EXISTS `daily_hours`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `daily_hours`  AS  select `shiftlengths`.`uid` AS `uid`,`shiftlengths`.`date` AS `date`,sec_to_time(sum(time_to_sec(`shiftlengths`.`length`))) AS `TotalTime` from `shiftlengths` group by `shiftlengths`.`date`,`shiftlengths`.`uid` ;

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

--
-- Structure for view `shiftlengths`
--
DROP TABLE IF EXISTS `shiftlengths`;

DROP VIEW IF EXISTS `shiftlengths`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `shiftlengths`  AS  select `shiftlist`.`uid` AS `uid`,timediff(`shiftlist`.`out_time`,`shiftlist`.`in_time`) AS `length`,date_format(`shiftlist`.`in_time`,'%d-%m-%Y') AS `date` from `shiftlist` ;

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

--
-- Structure for view `shiftlist`
--
DROP TABLE IF EXISTS `shiftlist`;

DROP VIEW IF EXISTS `shiftlist`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `shiftlist`  AS  select `o`.`UID` AS `uid`,(select max(`i`.`ClockStamp`) from `clockrecords` `i` where ((`i`.`ClockDirection` = 'In') and (`i`.`UID` = `o`.`UID`) and (`i`.`ClockStamp` < `o`.`ClockStamp`))) AS `in_time`,`o`.`ClockStamp` AS `out_time` from `clockrecords` `o` where (`o`.`ClockDirection` = 'Out') order by `o`.`ClockStamp` ;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I have already taken a look at

https://codingsight.com/pivot-tables-in-mysql/

MySQL pivot row into dynamic number of columns

Consider handling issues of data display in application code — Strawberry, Sep 22 '20 at 06:14
@Strawberry -- If I was to do this on the PHP side how would it be possible to match up the hours to the date column? the View method seems like it would be the most versatile but I could be wrong, Like I say I am new to this setup. — Sam, Sep 22 '20 at 16:24
Queries against views in mysql have limited access to underlying indexes, rendering their utility questionable (in my view). — Strawberry, Sep 22 '20 at 16:35

Pivoting table and view to create a custom view in My SQL

0 Answers0