Hey everybody,
I want to create a Query Report which shows the total of each item fo each calendar week. As timestamp the to_time field from the Job Card Time Log doctype should be used and the completed_qty field from the same table. As there is no item field in this table and it is not possible to add one it is necessary to get the regarding item from the Job Card doctype.
I created an SQL for that but it doe not work properly. The connection of both files does not work I can create a working report for the Job Card Time Log document, but the item names are missing then. Or I can create a report from the Job Card document, but then I need to use another date field from the job card which is not comrotable, because sometimes in our production the job card is finished at different dates. That means it will be better to get the real data from the Tim Log Table.
Does anybody of you have an idea how this can be done?
SELECT
YEAR(modified) AS year,
WEEK(modified, 1) AS week_number,
production_item,
SUM(total_completed_qty) AS total_quantity
FROM
tabJob Card
WHERE
YEAR(modified) = 2024
GROUP BY
year, week_number, production_item
ORDER BY
year, week_number, production_item;
This code works with all information from the Job card doctype - but the time stamp is maybe not the right one, because maybe this job card was finished during different calendarweeks. I want the real result for each calendar week.
So I tried to find a sollution to use the time stamp from the time Stamp table in the Job Card document. But all I can realize is the following code which is working, but the item field is missing, because I have to idea how to combine the Job Card doctype and the results from the Job Card Time Log table.
SELECT
YEAR(to_time) AS year,
WEEK(to_time, 1) AS week_number,
SUM(completed_qty) AS total_quantity
FROM
tabJob Card Time Log
WHERE
YEAR(to_time) = 2023
GROUP BY
year, week_number
ORDER BY
year, week_number;
I tried the following code, but it is not working. The way I tred to combine both sources is wrong and I have no idea how to solve this.
SELECT
jc.production_item,
WEEK(jctl.to_time, 1) AS calendar_week,
SUM(jctl.completed_qty) AS total_completed_qty
FROM
tabJob Card
AS jc
JOIN
tabJob Card Time Log
AS jctl ON jc.name = jctl.parent
WHERE
YEAR(jctl.to_time) = 2024
GROUP BY
jc.production_item,
calendar_week
ORDER BY
calendar_week,
jc.production_item;
Does anybody did something like that and can help me to find a sollution?
Best regards
Anja