Kindly Help me to solve this problem

[tilakrajsingh]

Hi
I am creating a stock statement for a particular period using SQL 2000 store procedure with Crystal report.

I am having tables :

Product -> with field1: pr_item
field2 r_opening (stock as on
year starting
date)
........
grnote >- with field1 :wt_received (all receipts)
............
issue-> with field1: wt_issue (all issues)
........

Now i want to print a stock statement for each item as follows :

i.e. a Stock statement for period of 01-june-03 to 30-sep-03
===========================================================
Item Code | name | opening balance|
-------------------------
|transaction date|recd |issue | closing Bal
-------------------------------------------------------
xxxxxxxx | xxxxxx 000000.00
-------------------------
xxxxxxx|dd/mm/yy|00.00|00.00 | 0000.00
===========================================================
=> Opening balance = Pr_opening + total receipt (from year start date to 31-may-03) - total issue (from year start date to 31-may-03)

=> Closing balance = Calculated Opening + total receipt (01-june-03 to 30-Sep-03)

I have done so far :-

i am combining both the tables in a stored procedure with the help of UNION. all parameters (i.e. sdate,edate, sItem, eItem etc) are defined in crystal report.

report is coming fine but i am not finding a way to calculate the Opening balance for a item for a specific period...

Kindly help me to get rid of this problem....
I will be greatfull.
Thanks
Tilak Raj

 

[IdoMillet]

First, your overall approach (UNION) to solving this is fine.

Your question is not very clear. Which period do you need the starting balance for? If it's a single date, you can use a subselect that uses the same approach to compute an ending balance using all transactions before that date.

Alternatively, you can use a subreport using the same stored procedure but restricting it to transactions before that date.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[tilakrajsingh]

Hi IdoMillet

Thanks for your valued reply which i was expecting.

Let me explore my query:

1. Our financial year starts from 01-Apr-03 to 31-mar-03.
2. The product table having a field as OpeningQty which contains the closing balance of that item as on 31-mar-03.
3. I want to print a stock statement for the period of 01-Jun-03 to 31-Sep-03. I mean, for any specific date i specified in my both date parameter.

4. for this report, Opening balance will be the sum of
OpeningQty (from Product table) + total receipt upto 31- May-03 (from GRNOTE table) - total issue upto 31-May-03
(from ISSUE table).
5. The SP i am using is combing all the data from Product,
GRNOTE & ISSUE using UNION operator.
6. It returns all the data from all the tables for 01-Apr-
03 to last feeded date (no filteration is being used
here).

7. All the parameters are set in crystal report, i mean the date parameters and product parameters (from sDate to eDate & sItem to eItem) etc. When i run crystal report, it filters records as per given inputs e.g. 01-June-03 to 30-Sep-03 and item ABC to XYZ. In Crystal report, the field of Opening Balance, is having only the value that is selected from Product table (this is the problem point). Whereas i need the Opening Balance as per above point # 4.

I am not able to understand how to calculate opening balance till 31-May-03, before the date filter applies on records.

I hope the above points may clear my requirements.

If any points i missed to mention here, please revert back to me on tilakrajsingh@hotmail.com

I will be waiting your valued solution.

Regards
Tilak Raj

 

[tilakrajsingh]

Here is my SP :-

*********
create procedure StockVouchers
as

SELECT akki.GRNHead.GRN_ID as VoucherId, akki.Document.Doc_Type as DocType, akki.GRNHead.GRN_Type as VoucherType, akki.GRNHead.GRN_Unit as VoucherUnit, akki.GRNHead.GRN_VNCD as VoucherVNCD, akki.GRNHead.GRN_Date as VoucherDate,
akki.GRNDetail.GRN_ProdID as Product , akki.GRNDetail.GRN_WtAcc as Weight, akki.GRNDetail.GRN_QtyAcc as Qty, akki.GRNDetail.GRN_UnitVal as Rate, akki.UOM.UOM_Type as PS,
akki.Product.Prod_Type as PRType, akki.Product.Prod_Cat as PRCat, akki.Product.Prod_Name as PRName , akki.Product.Prod_UOM as UOM, akki.Company.Co_Name as CompanyName, akki.Company.Ac_SDate as SDate, akki.Company.Ac_EDate as EDate,
akki.Opening.
FROM akki.GRNHead INNER JOIN
akki.GRNDetail ON akki.GRNHead.GRN_ID = akki.GRNDetail.GRN_ID INNER JOIN
akki.Product ON akki.GRNDetail.GRN_ProdID = akki.Product.Prod_Code INNER JOIN
akki.UOM ON akki.Product.Prod_UOM = akki.UOM.UOM_Code INNER JOIN
akki.Document ON akki.GRNHead.GRN_Type = akki.Document.Doc_ID INNER JOIN
akki.Company ON akki.GRNHead.GRN_Unit = akki.Company.Co_Id inner join
akki.Opening on akki.GRNDetail.GRN_ProdID = akki.Opening.Pr_cd

union

SELECT akki.IssueHead.Is_Id, akki.Document.Doc_Type, akki.IssueHead.Is_Type, akki.IssueHead.Is_Unit, akki.IssueHead.Cl_Id, akki.IssueHead.Is_Date, akki.IssueDetail.Prod_Id,
akki.IssueDetail.WtIs, akki.IssueDetail.QtyIs, akki.IssueDetail.UnitRate, akki.UOM.UOM_Type,
akki.Product.Prod_Type, akki.Product.Prod_Cat, akki.Product.Prod_Name, akki.Product.Prod_UOM, akki.Company.Co_Name, akki.Company.Ac_SDate, akki.Company.Ac_EDate
FROM akki.Document INNER JOIN
akki.Company INNER JOIN
akki.IssueHead INNER JOIN
akki.IssueDetail ON akki.IssueHead.Is_Id = akki.IssueDetail.Is_Id ON akki.Company.Co_Id = akki.IssueHead.Is_Unit ON
akki.Document.Doc_ID = akki.IssueHead.Is_Type INNER JOIN
akki.Product ON akki.IssueDetail.Prod_Id = akki.Product.Prod_Code INNER JOIN
akki.UOM ON akki.Product.Prod_UOM = akki.UOM.UOM_Code

****************
This returns all rows from Product, Grnote, Issue.

Can you please send some tips how to calculate the Opening using the same SP.

Tilak Raj

 

[tilakrajsingh]

Can any one help me??? please???