Skip to Content
0
Former Member
Nov 29, 2009 at 04:40 PM

Prompt for Date Values in Query

17 Views

I have the following query but instead of setting the variables I would like to be prompted upon running.

I tried using the [%0] but couldn't get it to work. Any suggestions would be appreciated.

declare @StartDate DateTime

declare @EndDate DateTime

set @StartDate = '20091101'

set @EndDate = '20091130'

select substring(rdr1.ITEMCODE,1,3) as BRAND, ROUND(sum(rdr1.linetotal),2) AS ORDERTOTAL, isnull(ROUND(sum(dln1.linetotal),2),0) AS SHIPTOTAL, isnull(ROUND(sum(rdr1.linetotal),2) - ROUND(sum(dln1.linetotal),2),0) AS TOTALDIF, ROUND(sum(rdr1.grssprofit),2) AS ORDERGP, isnull(ROUND(sum(dln1.grssprofit),2),0) AS SHIPGP, isnull(ROUND(sum(rdr1.grssprofit),2) - ROUND(sum(dln1.grssprofit),2),0) AS GPDIF, isnull(ROUND((sum(dln1.linetotal) / sum(rdr1.linetotal)*100),2),0) AS FILLRATE

from rdr1

LEFT OUTER join dln1

on (rdr1.docentry = dln1.baseentry and rdr1.itemcode = dln1.itemcode) join ordr on ordr.docentry = rdr1.docentry join oitm on oitm.itemcode = rdr1.itemcode

where rdr1.linestatus = 'C' And ordr.canceled = 'N' And ordr.docstatus = 'C' And oitm.u_status = '01' And

rdr1.docdate >= @StartDate And rdr1.docdate <= @EndDate

GROUP BY substring(RDR1.ITEMCODE,1,3) ORDER BY substring(RDR1.ITEMCODE,1,3)