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

[neilos]

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

[marco_ramirez]

I think the getdate () clause should not be here, instead it uses parameters to enter the date values from - to. Example:

Where T1. [ItemCode]> = [% 0] and T2. [Code] <= [% 1]

Regards

[neilos]

Hi Marco,

Thanks for the Reply.
I would ideally like this to get the data without any additional input from the user other than clicking go.
Which is why I am using the getdate() clause instead of a date range.