March 22, 2009 at 8:53 am
Hi there, I was wondering if someone could help with a report I am trying to build. I am new to SQL and I am having some problems with the results of the query. Your help would be greatly appreciated.
What I am trying to do:
Basically, I need to create a stock movement report with the following information:
Item Code, Item Description, Supplier Code, (Opening Stock as at @fromdate), (Goods In between @fromdate and @todate), (Goods Out between @fromdate and @todate), (Closing Stock as at @todate)
The problem I am having
The query runs fine, but the figure are not being reported corrected. The report is showing incorrect figures outside the date range.
The Stored procedure I have written for the crystal report
============================================================================
ALTER proc [dbo].[sp_STKMOVE]
@FromDate Date,
@ToDate date,
@Supplier varchar(200)
as
select distinct SM.STKCODE,DES1,SUPP1,isnull((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and TRANDATE 0.0),0) 'Tot_In',isnull((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and convert(varchar,TRANDATE,103) between convert(varchar,@FromDate,103) and convert(varchar,@ToDate,103) and QTY< 0.0),0) 'Tot_In -1',isnull((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and TRANDATE<=@ToDate),0) 'Tot_In -3',ACNAME
from ACOCMP1.STKMOVE SM,ACOCMP1.STKHEAD SH,ACOCMP1.PURDDAT P
where SM.STKCODE=SH.STKCODE and SUPP1=ACNO and SUPP1=@Supplier and ((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and TRANDATE 0)
order by SM.STKCODE
====================================================================
March 23, 2009 at 6:53 am
I just copied-n-paste your sql into sql management studio to check for syntax--> several syntax errors came up. Also, I noticed that there are no Joins in your From clause. Consequently, I'm surprised the stored procedure could run; also, if it does actually run, then I'm not surprised that the results are not right ... probably due to the lack of joins.
My comments may not be all that helpful, but they focus on some basic issues that you should check out first.
March 23, 2009 at 7:04 am
I think the OP does have joins in his Code however they are using the old SQL syntax.
OP If you are new to SQL it is highly recommended that you learn the correct syntax for writing INNER JOINS rather than using the WHERE clause syntax for your code.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 23, 2009 at 8:07 am
Thanks for you replies : peterzeke, Christopher Stobbs
I take on board all you have suggested, is there any recommendations you can make regarding the script. I need to correct is possible issues as a matter of urgency.
Any help you can give me would be great.
Thanks again
March 23, 2009 at 8:32 am
faisul (3/23/2009)
Thanks for you replies : peterzeke, Christopher StobbsI take on board all you have suggested, is there any recommendations you can make regarding the script. I need to correct is possible issues as a matter of urgency.
Any help you can give me would be great.
Thanks again
Your original script still is in error. The code you have given doesn't even pass syntax checks - we can't help with providing solutions to your query errors, if the query you give us isn't even valid! We can guess what it should be, but if you've missed a couple of characters here and there, who's to say you haven't missed an entire line of code!
Can you post the EXACT code for the procedure.
Also some test data will help us help you even more. We don't need all the data, just enough to provide a test case illustrating your issue - you never know, just putting this together might shed a little light on your problem.
Kev
March 30, 2009 at 4:03 pm
Hi Guys,
I have taken you advise and tried to re-write the stored procedure myself. I found it a good experience, and learned some code while doing so.
Here is the re-written script:
===================================================
CREATE proc [ACOCMP1].[sp_advstkmove]
@FromDate DATETIME,
@ToDate Datetime,
@Supplier varchar(200)
AS
/* This is the main query*/
SELECT
SM.STKCODE,SH.DES1,SH.CASETYPE,SC.CASEQTY,PD.ACNAME,
isnull((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and TRANDATE<=@fromDate),0) 'Opening Stock',
isnull((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and qty<0 and TRANDATE between @FromDate and @ToDate ),0) 'Total Out',
isnull((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and qty>0 and TRANDATE between @FromDate and @ToDate ),0) 'Total In',
isnull((Select sum(QTY) from ACOCMP1.STKMOVE where STKCODE=SM.STKCODE and TRANDATE<=@ToDate),0) 'Closing Stock'
FROM ACOCMP1.STKMOVE SM INNER JOIN ACOCMP1.STKHEAD SH ON SM.STKCODE=SH.STKCODE INNER JOIN ACOCMP1.STKCASE SC ON SH.CASETYPE=SC.CASEDES
INNER JOIN ACOCMP1.PURDDAT PD ON SH.SUPP1=PD.ACNO
WHERE SH.SUPP1=@supplier AND SM.TRANDATE <= @fromdate
GROUP BY SM.STKCODE, SH.DES1, SH.CASETYPE,SC.CASEQTY, PD.ACNAME
ORDER BY SM.STKCODE
GO
===========================================
The script and report seems to be working fine now. Thanks for all your suggestions and help. If you have any comments to make on the re-written script e.g. way to improve it, please let me know.
:-):-):-):-):-):-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply