Skip to Content
0
Oct 22, 2020 at 09:13 AM

SQL to to get Goods Reciept PO for the whole current week

42 Views

Hi Experts,

I have a requirement to get all the Goods Reciept POs that have the TaxDate that falls within the current week.

I have a UDT with all the From Dates and To dates that select the Week No. E.G.

Code | From Date | To Date 

202031 | 27/07/20 | 02/08/20

So the dates between To and From give me the Week Code.

I need to get all the Goods Reciept POs that the TaxDate falls between the to and from dates of the current week only.

This is what I have so far, but I can't see past how to get the current week code and only get the records that the week code is the same as...

SELECT
T2.[Code],
T1.[ItemCode], 
sum(T1.[Quantity]) as 'Quantity'
FROM 
OPDN T0 
INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN [dbo].[@DATE]  T2 ON T0.[U_Wk] = T2.[Code]
WHERE getdate() BETWEEN T2.U_From and T2.U_to
GROUP BY T1.[ItemCode], T2.[Code]
ORDER BY T1.[ItemCode] ASC