I am greatful if any one can Help!!!! me

[tilakrajsingh]

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

I have :
Table : Product -> with field: pr_item
pr_opening (stock as on
year starting
date)
........
Table : grnote >- with field :wt_received (all receipts)
............
table : issue-> with field : wt_issue (all issues)
........

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

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.

Tilak Raj

 

[zhavic]

Can you post there query you have now ?
We can that add to it than you want ( Opening balance etc. )

For example:

If you have query like this:

SELECT some_columns
FROM some_tables
JOIN Product ON some_condition
-- there can be joined table with 'total receipt' for each product
INNER JOIN
( SELECT SUM( wt_received ) AS receipt, some_join_column FROM grnote GROUP BY some_join_column ) AS total_receipts ON total receipts.some_join_column = Products.some_join_column
-- there can be added subquery for 'total issue' and than in the select list it can be simly 'Product.Pr_opening + total receipts.receipt - ...'


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[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