Query

[Former Member]

Hi

I want query if user select Open it should display Open Sales Order , if user enters Closed it should display Closed Sales Order during that period. This i want through 1 query . Fields required are Order No , Date , Customer , Amount , Status.

Thanks

[Former Member · Accepted solution]

Try this query.

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal], T0.[DocStatus] FROM ORDR T0 WHERE (T0.[DocStatus]  = 'O' and  T0.[DocStatus]  = [%0]) OR ( T0.[DocStatus]  = 'C' and   T0.[DocStatus] = [%0] and  T0.[DocDate]  >= [%1] and  T0.[DocDate]  < [%2])

Krishnan

[Former Member]

Closed.

[Former Member]

Try this:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal], T0.[DocStatus]
FROM dbo.ORDR T0
INNER JOIN dbo.OCRD T1 ON T1.CardCode = T0.CardCode
INNER JOIN dbo.OCRG T2 ON T2.GroupCode = T1.GroupCode
WHERE T0.[DocStatus]  = '[%0]'  and  T0.[DocDate]  BETWEEN [%1] and [%2] AND T2.GroupName LIKE '[%3]%'

[Former Member]

Hi,

Try this:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal], T0.[DocStatus]
FROM dbo.ORDR T0 WHERE T0.[DocStatus]  = '[%0]'  and  T0.[DocDate]  >= [%1] and  T0.[DocDate]  < [%2]

or

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal], T0.[DocStatus]
FROM dbo.ORDR T0 WHERE T0.[DocStatus]  = '[%0]'  and  T0.[DocDate] BETWEEN [%1] and [%2]

Thanks,

Gordon

[Former Member]

Hi Gordon

If it is to be done on Customer Group then what should be the query

Thanks