October 31, 2011 at 4:54 am
Hello experts
i have a problem by filtering my results with the date
i have written the following code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[F_CALCULATION_QUANTITY]
(@ITEMCODE AS NVARCHAR(10),
@STARTDATE1 as DATETIME,
@ENDDATE1 AS DATETIME
)
RETURNS
NUMERIC(19,2)
AS
BEGIN
DECLARE
@RESULT1 AS NUMERIC(19,2),
@RESULT2 AS NUMERIC(19,2),
@RESULT AS NUMERIC(19,2)
SELECT @RESULT1=SUM(A.QUANTITY)
FROM INV1 A
JOIN OINV B ON A.DOCENTRY=B.DOCENTRY
WHERE A.ITEMCODE=@ITEMCODE
AND B.DOCDATE BETWEEN (@STARTDATE1) AND (@ENDDATE1)
SELECT @RESULT2=SUM(A.QUANTITY)
FROM RIN1 A
JOIN ORIN B ON A.DOCENTRY=B.DOCENTRY
WHERE A.ITEMCODE=@ITEMCODE
AND B.DOCDATE BETWEEN (@STARTDATE1) AND (@ENDDATE1)
SELECT @RESULT=ISNULL(@RESULT1,0)-ISNULL(@RESULT2,0)
RETURN @RESULT
END
and the query is the following one
DECLARE
@ITEMCODE AS NVARCHAR(10),
@STARTDATE1 as DATETIME,
@ENDDATE1 AS DATETIME
SET @STARTDATE1=(SELECT MAX(T0.DOCDATE) FROM oinv t0 WHERE T0.DOCDATE='2010-11-01')
set @ENDDATE1=(SELECT MAX(T0.DOCDATE) FROM oinv t0 WHERE T0.DOCDATE='2010-11-30')
SELECT
T1.ItemCode
, T1.Dscription
,DBO.F_CALCULATION_QUANTITY(@ITEMCODE,@STARTDATE1,@ENDDATE1)
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OSLP T3 ON T0.SLPCODE=T3.SLPCODE
WHERE
(T0.DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE1)
and (t2.cardcode ='80022')
and (T0.CANCELED= 'N')
group by t1.itemcode, T1.Dscription, t2.suppcatnum
the problem i have is that i want to filter my results accoring to the date provided by the user. i want it to be dynamic query.
by now, what i do is to edit the docdate in the function in order to get the desired results. this is not what i want.
could you please help me on this way in order to let the user to input the date?if i add the [%] in the query, it does not bring me the right results
October 31, 2011 at 7:03 am
What kind of front-end application are you using for this?
That's where user input takes place. How you do it depends on the type of application and how you build it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 11:26 am
1) this looks like it could be a VERY complex undertaking and thus is not really appropriate for a forum question. People here volunteer their time and involved questions like this are better done with a consultant. Perhaps someone will take the time to help.
2) Most importantly, PLEASE DO NOT USE A SCALAR UDF LIKE THIS!!! They are DEVASTATINGLY BAD!! There are many ways they will harm your performance. Make your query a set-based process (with intermediate temp tables if required) to get potentially orders of magnitude improvement in performance, reduced server load, better concurrency, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 1, 2011 at 11:33 am
You've shown us the function itself but not how you are using it. Give us the whole picture, not just little pieces of it.
November 2, 2011 at 12:23 am
hello to all!
thanks a lot for your help.i am using SAP Business One as a front end.
the reason i use this query with the scalar function is because i want to make a calculation on the back end and then to get the data in a single row.
in the beginning i was using union but the results for each item were displayed twice....thats what i want to get rid of
November 3, 2011 at 4:17 am
Let's start with small steps:
You have this in the caller routine:
[font="System"]@STARTDATE1 as DATETIME,
@ENDDATE1 AS DATETIME
SET @STARTDATE1=(SELECT MAX(T0.DOCDATE) FROM oinv t0 WHERE T0.DOCDATE='2010-11-01')
set @ENDDATE1=(SELECT MAX(T0.DOCDATE) FROM oinv t0 WHERE T0.DOCDATE='2010-11-30')[/font]
You may achieve the same result without killing the database:
SET @ENDDATE1 ='20101101' -- probably '2010-11-01' works as well
SET @ENDDATE1 ='20101131' -- unless you really want to stop at 30/11
If using SQL2008 you may wish to define as DATE if the time part is not important
Check the type of the DOCDATE column in the oinv table. If it is a datetime, your code or mine will produce datetime values of 2010-11-31 00:00:00.000 so this might miss on equality tests against say 2010-11-31 00:01:03.066
Once you realise you can have implicit typecasting of a string formatted as 'ddMMyyyy' into any date format, the answer to your question becomes easy:
Just format any start/end dates into a string before using them as parameters to a SELECT or an UDF.
But please heed previous posters' advice: DO NOT use this UDF.... for every value in every row return it will join and rejoin source tables a million ways.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply