Alternative to subquery (mysql)-CodePudding

I have tables that hold payroll header & detail info on a separate table. Schema of each below,

CREATE TABLE `Payroll` (
  `payId` int,
  `groupId` int,
  `startDate` date ,
  `endDate` date,
  `paymentDate` date
);

insert into Payroll values
(20,2,'2022-06-01','2022-06-30','2022-06-30'),
(21,2,'2022-07-01','2022-07-31','2022-07-31'),
(18,1,'2022-05-01','2022-05-31','2022-05-31'),
(19,1,'2022-07-01','2022-07-31','2022-07-31')
;

CREATE TABLE `PayrollItems` (
    `payId` int NOT NULL,
    `employeeId` int ,
    `payCategory` varchar(45) ,
    `value` decimal(15,4) NOT NULL  
);

insert into PayrollItems values
(20,12,'salary',200),
(20,12,'housing',500),
(20,13,'salary',400),
(20,14,'salary',1300),
(21,12,'salary',200),
(21,12,'housing',500),
(21,13,'salary',400),
(21,14,'salary',1300),
(18,13,'salary',400),
(18,13,'housing',1300),
(19,14,'salary',500),
(19,14,'housing',1200)
;

I am trying to get a query wherein given a payid i should get the previous payid details. Previous payid is identified by a combination of the paymentDate & groupId fields.

Therefore, for the data above, for payid 19 i should get records of payid 18 i.e each pay item value, as they both are of the same groupid, 1 , and paymentDate of payid 18 is prior to paymentDate of payid 19. There could be more records that have a paymentDate dated prior to payid 19, but only first record dated prior is required.

I tried,

SELECT 
    y.*
FROM
    Payroll ppi2
        JOIN
    PayrollItems prr3 ON (`prr3`.`payId` = `ppi2`.`payId`)
        LEFT JOIN
    (SELECT 
            prr3.employeeId,
            ppi2.paymentDate,
            ppi2.payId,
            ppi2.groupId,
            'Last months standard salary' AS Particulars,            
            MAX(CASE
                WHEN TRIM(prr3.payCategory) = 'salary' THEN value
                ELSE 0
            END) salary,
            MAX(CASE
                WHEN TRIM(prr3.payCategory) = 'housing' THEN value
                ELSE 0
            END) housing
    FROM
        Payroll ppi2
    JOIN PayrollItems prr3 ON (`prr3`.`payId` = `ppi2`.`payId`)
        AND ppi2.payId = 19
    GROUP BY ppi2.payId , prr3.employeeId , ppi2.paymentDate,ppi2.groupId
    ORDER BY ppi2.paymentDate DESC) AS y ON (y.groupId = ppi2.groupId)
        AND y.paymentDate < ppi2.paymentDate
GROUP BY y.payId,y.employeeId,y.paymentDate,y.groupId,y.Particulars;

but i am not getting any results.

Expected result,given payid = 19, would be,

payid   employeeid    housing   salary
 18        13            1300      400

Would there be another way of doing this ?

dbfiddle

CodePudding user response：

WITH
cte AS (
  SELECT t1.payid prev_payid
  FROM Payroll t1
  JOIN Payroll t2 USING (groupId)
  WHERE t2.payid = @payid
    AND t1.startDate < t2.startDate
  ORDER BY t1.startDate DESC LIMIT 1
  )
SELECT payId, 
       employeeId, 
       SUM(value * (payCategory = 'housing')) housing, 
       SUM(value * (payCategory = 'salary')) salary
FROM PayrollItems
CROSS JOIN cte
WHERE payid = prev_payid
GROUP BY 1, 2

https://dbfiddle.uk/1LAdyksH