Skip to Content
0
Former Member
Mar 12, 2010 at 03:48 PM

How to do this in Query Generator?

13 Views

Dear Experts,

Please check my following query I've written in Query Generator.

/* select from dbo.OCRD t0 */
declare @BP nvarchar(20)
set @BP=/* t0.CardName */ '[%0]'

declare @Dt1 datetime
declare @Dt2 datetime

set @Dt1=/* Start Date */ [%1]
set Dt2=[%2]


select distinct t1.po, Max(t1.supplier) Supplier,Max(t1.process) Process, Max(t1.OrderNo) OrderNo,Max(t1.Date) Date,
isnull((select sum(t2.Quantity) from dbo.ir t2 where t2.po=t1.po and t2.TType<0),0) Issued,
isnull((select sum(t3.Quantity) from dbo.ir t3 where t3.po=t1.po and t3.TType>0),0) Received,
isnull((select sum(t2.Quantity) from dbo.ir t2 where t2.po=t1.po and t2.TType<0),0)-isnull((select sum(t3.Quantity) from dbo.ir t3 where t3.po=t1.po and t3.TType>0),0) Balance

from dbo.ir t1 where t1.Supplier=@BP and (t1.Date>=@Dt1 and t1.Date<=@Dt2) group by t1.po

The Query works well. But for the the lines

set @Dt1=/* Start Date */ [%1]
set Dt2=[%2]

it displays BP Code as the prompt next the text boxes meant for inputting dates. I would like to display 'Start Date' and 'End Date' respectively for Dt1 and Dt2.

Please explain me how to go about it.

Thanks in advance.

Regards

Anand