Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the following script:
Tabella1°Qtà:
SQL select
max(POQ.ITMREF_0) as [Article],
max(POQ.QTYSTU_0) as [Qty 1°arr.],
min(POQ.EXTRCPDAT_0) as [1° Receiving Date ],
max(POQ.RCPQTYSTU_0) as [1° Qty Ric. For.]
//max(POQ.YDATARCVPR_0) as [1° Date Receiving Conf.]
from xxxx.yyyy.PORDERQ POQ
where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000
group by POQ.ITMREF_0
I would load only 1 row per POQ.ITMREF_0 and only the data of the row with the min date POQ.EXTRCPDAT_0
How can I set the script?
Many thanks
Andrea
You can modify your query to include a subquery that selects the minimum date for each item, and then use that in the main query to filter the rows. Here's an example of how you can modify your query:
SQL select
max(POQ.ITMREF_0) as [Article],
max(POQ.QTYSTU_0) as [Qty 1°arr.],
min(POQ.EXTRCPDAT_0) as [1° Receiving Date ],
max(POQ.RCPQTYSTU_0) as [1° Qty Ric. For.]
//max(POQ.YDATARCVPR_0) as [1° Date Receiving Conf.]
from xxxx.yyyy.PORDERQ POQ
INNER JOIN (SELECT ITMREF_0, min(EXTRCPDAT_0) as min_date from xxxx.yyyy.PORDERQ where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000 group by ITMREF_0) subquery
ON POQ.ITMREF_0 = subquery.ITMREF_0 AND POQ.EXTRCPDAT_0 = subquery.min_date
where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000
group by POQ.ITMREF_0
The subquery selects the minimum date for each item and join it with the main query, filtering only the rows with the min date of each item.
Please note that the query might require some adjustments depending on the specific structure and contents of the xxxx.yyyy.PORDERQ table.
You can modify your query to include a subquery that selects the minimum date for each item, and then use that in the main query to filter the rows. Here's an example of how you can modify your query:
SQL select
max(POQ.ITMREF_0) as [Article],
max(POQ.QTYSTU_0) as [Qty 1°arr.],
min(POQ.EXTRCPDAT_0) as [1° Receiving Date ],
max(POQ.RCPQTYSTU_0) as [1° Qty Ric. For.]
//max(POQ.YDATARCVPR_0) as [1° Date Receiving Conf.]
from xxxx.yyyy.PORDERQ POQ
INNER JOIN (SELECT ITMREF_0, min(EXTRCPDAT_0) as min_date from xxxx.yyyy.PORDERQ where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000 group by ITMREF_0) subquery
ON POQ.ITMREF_0 = subquery.ITMREF_0 AND POQ.EXTRCPDAT_0 = subquery.min_date
where POQ.QTYSTU_0-POQ.RCPQTYSTU_0 >0 and POQ.RCPCLEFLG_0 = 1 and year(POQ.YDATARCVPR_0)<>2000
group by POQ.ITMREF_0
The subquery selects the minimum date for each item and join it with the main query, filtering only the rows with the min date of each item.
Please note that the query might require some adjustments depending on the specific structure and contents of the xxxx.yyyy.PORDERQ table.
Hi jcmachado,
many thanks for your reply.
I tested your subquery, it seems working!
Best regards
Andrea