CREATE PROCEDURE P_SEARCH_KIND_SALE
AS
CREATE TABLE #temp_table
(
small_type_id int,
kind_name char(10),
quantity float,
amount float,
work_date char(12),
sale_cost float,
gain float
)
INSERT INTO #temp_table
SELECT
b.small_type_id,b.name AS kind_name,
a.quantity,a.amount,convert(char(12),a.word_date,102) AS work_date,
(c.cost_price*a.quantity) AS sale_cost,
(a.amount-c.cost_price*a.quantity) AS gain
FROM pos_sale a,small_type b,goods_info c
WHERE a.goods_id=c.goods_id
AND c.small_type=b.small_type_id
SELECT work_date, small_type_id ,kind_name,
sum( quantity) AS sum_quantity , sum(amount) AS sum_amount , sum(sale_cost) AS sum_sale_cost , sum( gain ) AS sum_gain
FROM #temp_table
GROUP BY work_date, small_type_id ,kind_name ORDER BY work_date
查询商品销售的存储过程：
CREATE PROCEDURE P_SEARCH_GOODS_SALE
AS
CREATE TABLE #temp_table
(
goods_id int,
bar_id int,
goods_name char(20),
guige char(4),
quantity float,
amount float,
work_date char(12),
sale_cost float,
gain float,
gain_rate float
)
INSERT INTO #temp_table
SELECT
a.goods_id,a.bar_id,a.name AS goods_name,
a.guige,a.quantity,a.amount,convert(char(12),a.word_date,102) AS work_date,
(c.cost_price*a.quantity) AS sale_cost,
(a.amount-c.cost_price*a.quantity) AS gain,
((a.amount-c.cost_price*a.quantity)/(c.cost_price*a.quantity)) AS gain_rate
FORM pos_sale a,large_type b,goods_info c
WHERE a.goods_id=c.goods_id
AND c.large_type=b.large_type_id
SELECT work_date, goods_id , bar_id, goods_name , guige ,
sum( quantity) AS sum_quantity , sum(amount) AS sum_amount , sum(sale_cost) AS sum_sale_cose , sum( gain ) AS sum_gain,gain_rate*100 AS gain_rate
FORM #temp_table
GROUP BY work_date,goods_id, goods_name , guige,gain_rate,bar_id
ORDER BY work_date
库存调整存储过程
CREATE PROCEDURE P_STORE_ADJUST
@goods_id int,
@adjust_quantity float,
@adjust_readon int,
@work_person int
AS
DECLARE @price money
DECLARE @quantity float
DECLARE @amount money
SELECT @quantity= QUANTITY,@amount=AMOUNT FROM STORE_DETAIL WHERE GOODS_ID=@goods_id
SELECT @price=cost_price FROM GOODS_INFO WHERE GOODS_ID=@goods_id
UPDATE STORE_DETAIL
SET
QUANTITY=QUANTITY-@adjust_quantity, AMOUNT=AMOUNT-@price*@adjust_quantity
WHERE goods_id =@goods_id
INSERT STORE_ADJUST
VALUES(@goods_id,@quantity,@amount,@adjust_quantity,convert(money,@price*@adjust_quantity),@adjust_readon,@work_pe
