میلاد علوی
شنبه 11 مهر 1394, 22:08 عصر
با سلام
مشکلی که من دارم اینه که مقدار وارده کالا رو دارم مقدار صادرشم دارم میخوام موجودی کالارو بگیرم نمیتونم
به این شکل :
USE [Sherkat1]
GO
/****** Object: StoredProcedure [dbo].[KardexAnbar] Script Date: 2015/10/03 10:07:45 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[KardexAnbar]
@CodeAnbar bigint,
@CodeGhafase bigint,
@CodeGoruhAnbar bigint,
@CodeKala bigint
as
set nocount on;
SELECT TOP (2147483647)ID, TarikhSanad AS تاريخ, Sharh AS شرح, SaatSanad,
COALESCE((SELECT MeghdareKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID ) ,0)as VoroudKala,
COALESCE ((SELECT GheymatVahedKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID),0)as GheymatVahedVoroudKala,
COALESCE ((SELECT MeghdareKala * GheymatVahedKala AS Expr1 FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID) ,0)as MablaghVoroudKala,
COALESCE((SELECT MeghdareKala FROM Sanad WHEre HavaleKhoroujAnbar = 1 AND ID = Sanad_1.ID) ,0)as KhoroujKala,
COALESCE ((SELECT GheymatVahedKala FROM Sanad WHERE HavaleKhoroujAnbar = 1 AND ID = Sanad_1.ID),0)as GheymatVahedKhoroujKala,
COALESCE ((SELECT MeghdareKala * GheymatVahedKala AS Expr1 FROM Sanad WHERE HavaleKhoroujAnbar = 1 AND ID = Sanad_1.ID) ,0)as MablaghKhoroujKala,
INNER JOIN sys KardexAnbar1 t add x char(1) NOT NULL CONSTRAINT abc DEFAULT 1
--COALESCE(COALESCE((SELECT MeghdareKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID <= Sanad_1.ID ),0)-COALESCE((SELECT MeghdareKala FROM Sanad WHEre HavaleKhoroujAnbar = 1 AND ID <= Sanad_1.ID),0),0) as MojoudiKala
--COALESCE ((SELECT GheymatVahedKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID),0)as GheymatVahedMojoudiKala
from Sanad as sanad_1 where
CodeAnbar=@CodeAnbar and CodeGhafase=@CodeGhafase and CodeGoruhAnbar=@CodeGoruhAnbar and CodeKala=@CodeKala and Code <> 0 AND (HavaleVoroudAnbar <> 0 OR HavaleKhoroujAnbar <> 0)
ORDER BY تاريخ, SaatSanad, ID
[KardexAnbar] '101','101' ,'105', '101'
میخوام VoroudKala کالارو طبق Id از KhoroujKala کم کنم
لازم بذکر است که بدلیل تغییرات قیمت بصورت میانگین باید سطر به سطر محاسبه انجام شود
اینم عکس چیزی که میخوام:
135666
مشکلی که من دارم اینه که مقدار وارده کالا رو دارم مقدار صادرشم دارم میخوام موجودی کالارو بگیرم نمیتونم
به این شکل :
USE [Sherkat1]
GO
/****** Object: StoredProcedure [dbo].[KardexAnbar] Script Date: 2015/10/03 10:07:45 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[KardexAnbar]
@CodeAnbar bigint,
@CodeGhafase bigint,
@CodeGoruhAnbar bigint,
@CodeKala bigint
as
set nocount on;
SELECT TOP (2147483647)ID, TarikhSanad AS تاريخ, Sharh AS شرح, SaatSanad,
COALESCE((SELECT MeghdareKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID ) ,0)as VoroudKala,
COALESCE ((SELECT GheymatVahedKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID),0)as GheymatVahedVoroudKala,
COALESCE ((SELECT MeghdareKala * GheymatVahedKala AS Expr1 FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID) ,0)as MablaghVoroudKala,
COALESCE((SELECT MeghdareKala FROM Sanad WHEre HavaleKhoroujAnbar = 1 AND ID = Sanad_1.ID) ,0)as KhoroujKala,
COALESCE ((SELECT GheymatVahedKala FROM Sanad WHERE HavaleKhoroujAnbar = 1 AND ID = Sanad_1.ID),0)as GheymatVahedKhoroujKala,
COALESCE ((SELECT MeghdareKala * GheymatVahedKala AS Expr1 FROM Sanad WHERE HavaleKhoroujAnbar = 1 AND ID = Sanad_1.ID) ,0)as MablaghKhoroujKala,
INNER JOIN sys KardexAnbar1 t add x char(1) NOT NULL CONSTRAINT abc DEFAULT 1
--COALESCE(COALESCE((SELECT MeghdareKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID <= Sanad_1.ID ),0)-COALESCE((SELECT MeghdareKala FROM Sanad WHEre HavaleKhoroujAnbar = 1 AND ID <= Sanad_1.ID),0),0) as MojoudiKala
--COALESCE ((SELECT GheymatVahedKala FROM Sanad WHERE HavaleVoroudAnbar = 1 AND ID = Sanad_1.ID),0)as GheymatVahedMojoudiKala
from Sanad as sanad_1 where
CodeAnbar=@CodeAnbar and CodeGhafase=@CodeGhafase and CodeGoruhAnbar=@CodeGoruhAnbar and CodeKala=@CodeKala and Code <> 0 AND (HavaleVoroudAnbar <> 0 OR HavaleKhoroujAnbar <> 0)
ORDER BY تاريخ, SaatSanad, ID
[KardexAnbar] '101','101' ,'105', '101'
میخوام VoroudKala کالارو طبق Id از KhoroujKala کم کنم
لازم بذکر است که بدلیل تغییرات قیمت بصورت میانگین باید سطر به سطر محاسبه انجام شود
اینم عکس چیزی که میخوام:
135666