InputBox in Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

InputBox in Query

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-07-08 : 11:30:43

Hi,

I have a query which I would like to include an inputbox in, so that the user can enter a date value. How do I do this ?

My query looks like this :

INSERT INTO INVENTRY([STOCKLIST CODE], [WAREHOUSE CODE], [PROCESS YYYYMM], [INVENTORY TYPE], [PRODUCT CODE], [OPENING BALANCE QTY.], [OPENING BAL. VALUE],[RECEIPT QUANTITY],[RECEIPT VALUE],[FREE RECEIPT QUANTITY],[FREE RECEIPT VALUE],[RETURN QUANTITY],[RETURN VALUE],[ADJUSTMENT QUANTITY],[ADJUSTMENT VALUE],[ALLOCATED QUANTITY],[ALLOCATED VALUE],[CLOSING QUANTITY],[CLOSING VALUE])

SELECT '5555550101'
,LocnCode
,ProcessDate
,'S'
,ProductCode
,ISNULL(OpenQty,0.00)
,ISNULL(OpeningBalValue,0.00)
,ISNULL(RecQty,0.00)
,ISNULL(RecValue,0.00)
,0.00
,0.00
,0.00
,0.00
,ISNULL(AdjQty,0.00)
,ISNULL(AdjValue,0.00)
,ISNULL(AllocQty,0.00)
,ISNULL(AllocValue,0.00)
,ISNULL(ClosingQty,0.00)
,ISNULL(ClosingValue,0.00)

FROM
(

SELECT
LTRIM(STR(YEAR(I12.DOCDATE)) + REPLACE(STR(MONTH(I12.DOCDATE), 2),' ', '0')) As ProcessDate12
,I11.[CP PRODUCT CODE] as ProductCode11
,SUM(I12.TRXQTY*I11.[UNITS/CASE]*-1) as AllocQty
,SUM(I12.EXTDCOST/71.70) as AllocValue
FROM IV30300 I12
RIGHT OUTER JOIN [ITEM_MAPPING] I11
ON I11.[KIRK ITEM NUMBER] = I12.ITEMNMBR
and I12.DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'
AND I12.DOCTYPE = '6'
GROUP BY I11.[CP PRODUCT CODE], LTRIM(STR(YEAR(I12.DOCDATE)) + REPLACE(STR(MONTH(I12.DOCDATE), 2),' ', '0'))
)D12
RIGHT OUTER JOIN
(
SELECT
LEFT(I3.LOCNCODE,6) as LocnCode
,'200712' As ProcessDate
,I2.[CP PRODUCT CODE] as ProductCode
,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/71.70 As OpeningBalValue
,SUM(I3.QTYONHND*I2.[UNITS/CASE]) As ClosingQty
,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/71.70 As ClosingValue

FROM IV00101 I1
JOIN [ITEM_MAPPING] I2
ON I1.ITEMNMBR = I2.[KIRK ITEM NUMBER]
JOIN IV00102 I3
ON I1.ITEMNMBR = I3.ITEMNMBR
AND I3.LOCNCODE = 'MGD-MAIN'

GROUP BY I2.[CP PRODUCT CODE], LEFT(I3.LOCNCODE,6)
)D1
ON D12.ProductCode11 = D1.ProductCode
LEFT JOIN
(
SELECT
B1.ITEMNMBR as Item
,SUM(B1.NETQTY*B2.[UNITS/CASE]) as OpenQty
FROM INV_BALANCES B1
INNER JOIN [ITEM_MAPPING] B2
ON B1.ITEMNMBR = B2.[CP PRODUCT CODE]
GROUP BY B1.ITEMNMBR
)D13
ON D1.ProductCode = D13.Item

LEFT JOIN
(
SELECT
I6.[CP PRODUCT CODE] as ProductCode2
,LTRIM(STR(YEAR(I5.DOCDATE)) + REPLACE(STR(MONTH(I5.DOCDATE), 2),' ', '0')) As ProcessDate2
,SUM(I5.TRXQTY*I6.[UNITS/CASE]) as AdjQty
,SUM(I5.EXTDCOST/71.70) as AdjValue
FROM IV30300 I5
RIGHT OUTER JOIN [ITEM_MAPPING] I6
ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR
AND DOCTYPE = '1'
AND DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'
or DOCTYPE = '2'
AND DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'

GROUP BY I6.[CP PRODUCT CODE],LTRIM(STR(YEAR(I5.DOCDATE)) + REPLACE(STR(MONTH(I5.DOCDATE), 2),' ', '0'))
)D2
ON D1.ProductCode = D2.ProductCode2
and D1.ProcessDate = D2.ProcessDate2

LEFT OUTER JOIN
(
SELECT
I9.[CP PRODUCT CODE] as ProdCode
FROM [ITEM_MAPPING] I9
LEFT OUTER JOIN IV30300 I10
ON I9.[KIRK ITEM NUMBER] = I10.ITEMNMBR
GROUP BY I9.[CP PRODUCT CODE]
)D4
ON D12.ProductCode11 = D4.ProdCode
LEFT OUTER JOIN
(
SELECT
LTRIM(STR(YEAR(P1.DOCDATE)) + REPLACE(STR(MONTH(P1.DOCDATE), 2),' ', '0')) as ProcessDate7
,I7.[CP PRODUCT CODE] as ProductCode8
,P1.TRXLOCTN as Loc
,SUM(P1.TRXQTY * I7.[UNITS/CASE]) as RecQty
,SUM(P1.TRXQTY * I7.[CASE PRICE]) as RecValue

FROM IV30300 P1
RIGHT OUTER JOIN [ITEM_MAPPING] I7
ON P1.ITEMNMBR = I7.[KIRK ITEM NUMBER]
AND P1.DOCTYPE = '4'
AND TRXLOCTN = 'MGD-MAIN'
AND P1.DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'
GROUP BY I7.[CP PRODUCT CODE], P1.TRXLOCTN, LTRIM(STR(YEAR(P1.DOCDATE)) + REPLACE(STR(MONTH(P1.DOCDATE), 2),' ', '0'))
)D3
ON D4.ProdCode = D3.ProductCode8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 13:24:12

just add a parameter to hold the date value from input box and pass it to query. Also include code at your front end to get value from input box and pass it down to query paramter.

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-07-08 : 13:35:52

I'm not sure what you mean you say to include it in my front end. This is a query which a user is going to run regularly, but I don't want them to modify the query, so there isn't a front end.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 13:58:17

quote:
Originally posted by tracy5436

I'm not sure what you mean you say to include it in my front end. This is a query which a user is going to run regularly, but I don't want them to modify the query, so there isn't a front end.

Then where are you having the input box for user to enter date? If you're trying to do this in sql please note that this is not possible in sql. you can make sql prompt for user input. this needs to be done in some kind of front end application and value needs to grabbed and passed onto query from there.

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-07-09 : 16:52:25

I have created a macro in Excel which will create the inputbox. However, how do I pass this value into SQL ? I have tried using an ado connection and including the query in the macro, but the query is too long. Is there another way of doing this ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources