I really appreciate if someone could give me a correct sql. Here in my scenario. I have two tables
1. Harvest Details
Harvest Details contains our detailed harvest in our farm for our cultured shimps and it contains the Pond No, Crop No, Harvest Date and Harvest Type, there are only two two "Partial Harvest" and "Final Termination" of the pond since we cannot harvest our farm in one shot due to the size. 10 hectares per pond.
Sample Harvest Details Table:
Harvest Details
t_pcodt_cropt_htypHarvest DateHarvest Type03PC0112213/09/2010Termination03PC0112126/05/2010Partial03PC0112111/06/2010Partial03PC0112104/09/2010Partial03PC0112103/09/2010Partial
in every pond it wil vary, sometime you will find only one line becuase this pond is still in the state of Partial Harvest and sometimes only 1 Partial + Final Terminal or any possible scenario like 3 Partial+Termination and so on.
The second table is the Production Order table where we are recording the material consumed on each pond from the start of pond up to the final termination of the pond. The field Actual Partial total contains the totals of all Item Group but that belongs to different dates within the date range of Harvest Date in my first table "Harvest Details".
I just excluded the Closing Date in my qrySample because I used a GROUP BY SQL that will sum up all ActualPartial used material but there is a filtering for that date from and to behind this grid result.
qrySamplePondCropItem GroupItem DescItem Grp DescCurrencyActual Partial03PC0112258 FERTILIZER- UREA N46% "SABIFertilizerSAR100003PC0112322 MOLASSES (REFINED - CANE)Grow out Raw MaterialsSAR285003PC0112322 HYDRATED LIME - 20KG/BAGGrow out Raw MaterialsSAR300003PC0112322 DAP 18-46-0(DIAMMONIUM PHOSPHGrow out Raw MaterialsSAR003PC0112322 COMPOST(A-GRADE) ORGANICGrow out Raw MaterialsSAR003PC0112801 Standard Starter-3Feed Mill Finished Prod. ItemSAR2987503PC0112801 Standard Starter-1Feed Mill Finished Prod. ItemSAR003PC0112801 Standard Grower-1Feed Mill Finished Prod. ItemSAR40675003PC0112801 Natural FeedFeed Mill Finished Prod. ItemSAR0
Now i need an SQL Subquires that can filter total Actual Qty based on this result. From this result, they can identify how much total they have from the 1 first Partial Harvest down to the last final termination. The Partial Harvest and Final Termination should dynamically adjust based on the records in "Harvest Details". If the scenario is only 1 Partial and 1 Termination, the column should display only that 2 columns not up to 5 partial.
DESIRED RESULT.
PondCropItem GroupItem Grp DescSub ItemItem DescPartial 1 Actual QtyPartial 2 Actual QtyPartial 3 Actual QtyPartial 4 Actual QtyPartial 5 Actual QtyTotal Actual Qty03PC0112258Fertilizer2580100001FERTILIZER- UREA N46% "SABI200000020003PC0112322Grow out Raw Materials3221101002COMPOST(A-GRADE) ORGANIC00000003PC0112322Grow out Raw Materials3221101003DAP 18-46-0(DIAMMONIUM PHOSPH00000003PC0112322Grow out Raw Materials3221101004HYDRATED LIME - 20KG/BAG602003400060003PC0112322Grow out Raw Materials3221301001MOLASSES (REFINED - CANE)570000057003PC0112801Feed Mill Finished Prod. Item FM010001Standard Starter-100000003PC0112801Feed Mill Finished Prod. Item FM010003Standard Starter-359750000597503PC0112801Feed Mill Finished Prod. Item FM020001Standard Grower-142200622532025008045003PC0112801Feed Mill Finished Prod. Item FM300000Natural Feed000000
1. Harvest Details
Harvest Details contains our detailed harvest in our farm for our cultured shimps and it contains the Pond No, Crop No, Harvest Date and Harvest Type, there are only two two "Partial Harvest" and "Final Termination" of the pond since we cannot harvest our farm in one shot due to the size. 10 hectares per pond.
Sample Harvest Details Table:
Harvest Details
t_pcodt_cropt_htypHarvest DateHarvest Type03PC0112213/09/2010Termination03PC0112126/05/2010Partial03PC0112111/06/2010Partial03PC0112104/09/2010Partial03PC0112103/09/2010Partial
in every pond it wil vary, sometime you will find only one line becuase this pond is still in the state of Partial Harvest and sometimes only 1 Partial + Final Terminal or any possible scenario like 3 Partial+Termination and so on.
The second table is the Production Order table where we are recording the material consumed on each pond from the start of pond up to the final termination of the pond. The field Actual Partial total contains the totals of all Item Group but that belongs to different dates within the date range of Harvest Date in my first table "Harvest Details".
I just excluded the Closing Date in my qrySample because I used a GROUP BY SQL that will sum up all ActualPartial used material but there is a filtering for that date from and to behind this grid result.
qrySamplePondCropItem GroupItem DescItem Grp DescCurrencyActual Partial03PC0112258 FERTILIZER- UREA N46% "SABIFertilizerSAR100003PC0112322 MOLASSES (REFINED - CANE)Grow out Raw MaterialsSAR285003PC0112322 HYDRATED LIME - 20KG/BAGGrow out Raw MaterialsSAR300003PC0112322 DAP 18-46-0(DIAMMONIUM PHOSPHGrow out Raw MaterialsSAR003PC0112322 COMPOST(A-GRADE) ORGANICGrow out Raw MaterialsSAR003PC0112801 Standard Starter-3Feed Mill Finished Prod. ItemSAR2987503PC0112801 Standard Starter-1Feed Mill Finished Prod. ItemSAR003PC0112801 Standard Grower-1Feed Mill Finished Prod. ItemSAR40675003PC0112801 Natural FeedFeed Mill Finished Prod. ItemSAR0
Now i need an SQL Subquires that can filter total Actual Qty based on this result. From this result, they can identify how much total they have from the 1 first Partial Harvest down to the last final termination. The Partial Harvest and Final Termination should dynamically adjust based on the records in "Harvest Details". If the scenario is only 1 Partial and 1 Termination, the column should display only that 2 columns not up to 5 partial.
DESIRED RESULT.
PondCropItem GroupItem Grp DescSub ItemItem DescPartial 1 Actual QtyPartial 2 Actual QtyPartial 3 Actual QtyPartial 4 Actual QtyPartial 5 Actual QtyTotal Actual Qty03PC0112258Fertilizer2580100001FERTILIZER- UREA N46% "SABI200000020003PC0112322Grow out Raw Materials3221101002COMPOST(A-GRADE) ORGANIC00000003PC0112322Grow out Raw Materials3221101003DAP 18-46-0(DIAMMONIUM PHOSPH00000003PC0112322Grow out Raw Materials3221101004HYDRATED LIME - 20KG/BAG602003400060003PC0112322Grow out Raw Materials3221301001MOLASSES (REFINED - CANE)570000057003PC0112801Feed Mill Finished Prod. Item FM010001Standard Starter-100000003PC0112801Feed Mill Finished Prod. Item FM010003Standard Starter-359750000597503PC0112801Feed Mill Finished Prod. Item FM020001Standard Grower-142200622532025008045003PC0112801Feed Mill Finished Prod. Item FM300000Natural Feed000000
