Mir Abzal Ali Mir Abzal Ali - 1 month ago 11
MySQL Question

Select column datediff value use for another column in same select in mysql

In the select statement,

count_date
get with
datediff
function and I'm trying to multiply with
count_date*(t3.Rate/30) as payble
like below:

@var:= datediff(
(case when datediff(@todate, Max(t1.attnDate))!=1 then Max(t1.attnDate) else @todate end),
(case when datediff(Min(t1.attnDate), @fromdate)>1 then Min(t1.attnDate) else @fromdate end)
)+1 as count_date, @var*(t3.Rate/30) as payble


Output:

TraineeID count_date payble
31028 5 666.6667
31040 5 666.6667
31135 12 666.6667
31214 12 666.6667
31220 5 666.6667
31312 12 666.6667
31319 12 666.6667
31347 12 666.6667
31388 12 666.6667
31423 12 666.6667
31426 5 666.6667
31445 5 666.6667
31446 5 666.6667


Main Query:

SET @batch='Welding/SSTS-M/09/01';
SET @fromdate='2016-09-19';
SET @todate='2016-09-30';

SELECT t1.TraineeID,
datediff(
(case when datediff(@todate, Max(t1.attnDate))!=1 then Max(t1.attnDate) else @todate end),
(case when datediff(Min(t1.attnDate), @fromdate)>1 then Min(t1.attnDate) else @fromdate end)
)+1 as count_date

FROM tbl_attendance_processed t1
JOIN tbl_assigned t2 on t1.TraineeID=t2.TraineeID
LEFT JOIN tbl_foodlodging_rate t3 on t2.Round=t3.Round
LEFT JOIN tbl_trainee_status t4 on t1.TraineeID=t4.TraineeID
LEFT JOIN tbl_trade t5 ON t2.Trade=t5.ID
WHERE t2.BatchID=@batch and t1.attnDate>=@fromdate AND t1.attnDate<=@todate and t2.Reported=1 and t2.Status=1
GROUP by t1.TraineeID;

A J A J
Answer

I modified your query a bit. Try following.

SELECT tablea.*, tablea.count_date*(tablea.Rate/30) AS payble
FROM
(   SELECT t1.TraineeID,
        datediff(
            (case when datediff(@todate, Max(t1.attnDate))!=1 then Max(t1.attnDate) else @todate end),
            (case when datediff(Min(t1.attnDate), @fromdate)>1 then Min(t1.attnDate) else @fromdate end)
        )+1 as count_date, t3.Rate

    FROM tbl_attendance_processed t1
    JOIN tbl_assigned t2 on t1.TraineeID=t2.TraineeID
    LEFT JOIN tbl_foodlodging_rate t3 on t2.Round=t3.Round
    LEFT JOIN tbl_trainee_status t4 on t1.TraineeID=t4.TraineeID
    LEFT JOIN tbl_trade t5 ON t2.Trade=t5.ID
    WHERE t2.BatchID=@batch and t1.attnDate>=@fromdate AND t1.attnDate<=@todate and t2.Reported=1 and t2.Status=1
    GROUP by t1.TraineeID
) AS tablea;