Query with multiple filter

[Former Member]

Hi All,

I need to write a query example

SELECT 'INV', T0.DocDate, T0.CardCode, T0.CardName, T0.DocTotal FROM OINV T0

WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]'

UNION

SELECT 'CN', T1.DocDate, T1.CardCode, T1.CardName, T1.DocTotal FROM ORIN T1

WHERE T1.DocDate BETWEEN '[%2]' AND '[%3]'

But Actually i want to filter the Date is same. How can the query can help only filter one time only mean between date1 and date2, and my query can filter all the invoice and CN between date1 and date2

Please advised.

Thanks

[Former Member]

Samantha,

I would suggest prompting for the dates into SQL variables first (see SAP Note# 730960 for details), then you can use the those in both of your WHERE clauses.

Ken

[vijay_kumar1]

Hi

If you want to use the same criteria for date, you could use this query.

/SELECT FROM [dbo].[OINV] T8/

declare @docdate as datetime

/* WHERE */

set @docdate = /* T8.DocDate */ '[%0]'

/SELECT FROM [dbo].[ORDR] T9/

declare @docdate1 as datetime

/* WHERE */

set @docdate1 = /* T9.DocDate */ '[%1]'

SELECT 'INV', T0.DocDate, T0.CardCode, T0.CardName, T0.DocTotal FROM OINV T0

WHERE T0.DocDate BETWEEN @docdate AND @docdate1

UNION

SELECT 'CN', T1.DocDate, T1.CardCode, T1.CardName, T1.DocTotal FROM ORIN T1

WHERE T1.DocDate BETWEEN @docdate AND @docdate1

Regards,

Vijay Kumar

SAP Business One Forums Team

[Former Member]

Thanks alot. It's work. But how i give point?

[vijay_kumar1]

Hi Samantha,

Select the thread as " Answered" and you should be able to give points.

Regards,

Vijay Kumar

SAP Business One Forums Team