How to add vairables to give results from last month, two month ago etc.

[neilos]

Hi All,

I need to be able to amend this query that gives results for all purchase orders from one month ago, two month ago, three months ago etc. Currently it retuns only last month.
Is it possible to have a user input where you enter how many months ago you want to view?
I.E. enter 1 for 1 month ago, 2 for 2 months ago...

SELECT 
OPCH.DocNum, OPCH.DocDate, OPCH.CardCode, OPCH.CardName, OPCH.DocTotal, OPCH.VatSum, (OPCH.DocTotal-  OPCH.VatSum) as 'Nett' 

FROM OPCH OPCH 

WHERE 
OPCH.U_CDL ='y' 
and 
OPCH.DocDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) 
and 
OPCH.DocDate <= DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
 
order by OPCH.DocNum asc

I've tried;

OPCH.DocDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())[%0], 0) and 
OPCH.DocDate <= DATEADD(MONTH, DATEDIFF(MONTH, [%0], GETDATE())[%0], [%0]) 

But the input changes to a date automatically and obviuously fails.

Thanks.

[LoHa · Accepted solution]

Hi Neilos,

try this

/**SELECT FROM [PCH1] T0 **/
DECLARE @Month AS INT
/* WHERE */
SET @Month = /* T0.Quantity */ [%0]

SELECT 
OPCH.DocNum, OPCH.DocDate, OPCH.CardCode, OPCH.CardName, OPCH.DocTotal, OPCH.VatSum, (OPCH.DocTotal-  OPCH.VatSum) as 'Nett' 

FROM OPCH OPCH 

WHERE 
OPCH.U_CDL ='y' 
and 
OPCH.DocDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - @Month, 0) 
and 
OPCH.DocDate <= DATEADD(MONTH, DATEDIFF(MONTH, - @Month, GETDATE()) - @Month, - @Month)
 
order by OPCH.DocNum asc

regards Lothar

[neilos · Accepted solution]

@LoHa That's working well, thankyou.

One question, as you've commented a select from PCH1, the query doesn't use anything from the PO lines but the query doesn't work without the comments.
How does it make a difference?

"The variable name '@Month' has already been declared."

[LoHa]

Hi,

fine that it works.

The comments are neccessary for B1. You choose a table and a field in it.

The trick here is that the Name shown in the parameter-question window is used as in the gui from B1.

With it you can declare a variable at the beginning if the query and not suing [%0] on different points.

Here some more examples

/*Buchungsdatum*/
/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.T_RefDate */ '[%1]'

-----
/*Fälligkeitsdatum*/
/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_DueDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.T_DueDate */ '[%1]'

-----
/*Belegdatum*/
/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_TaxDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.T_TaxDate */ '[%1]'

----------------------
/**SELECT FROM [OHEM] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.StartDate */ '[%0]'

/**SELECT FROM [OHEM] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.termDate */ '[%1]'

For us we made an UDT with UDF's only to Name the Parameters as we want, because the Name of the UDF's is shown in the window

regards Lothar