asked on

Summary of cost

Hi All,

I have detail data below :

NoTransaksi                    TglTransaksi            GdgCode   QtyTransaksi          ItemUnitCost                            NilaiKurs             NoTransaksiBeli                PPN  JenisCode
------------------------------ ----------------------- --------- --------------------- --------------------------------------- --------------------- ------------------------------ ---- ---------
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       120.00                14850.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       100.00                12976.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       100.00                12976.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       100.00                12976.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       120.00                14850.000000                            1.00                  NULL                           T    BG  
SJ OLT 20160731                2016-07-31 00:00:00.000 OLT       -48.00                14850.000000                            1.00                  GR OLT 20160731                T    BG  
SJ OLT 20160731                2016-07-31 00:00:00.000 OLT       -90.00                14850.000000                            1.00                  GR OLT 20160731                T    BG  
SJ OLT 20160731                2016-07-31 00:00:00.000 OLT       -22.00                12976.000000                            1.00                  GR OLT 20160731                T    BG  
SJ OLT 20160731                2016-07-31 00:00:00.000 OLT       -47.00                12976.000000                            1.00                  GR OLT 20160731                T    BG  
SJ OLT 20160731                2016-07-31 00:00:00.000 OLT       -52.00                12976.000000                            1.00                  GR OLT 20160731                T    BG

Open in new window

I use below query to get it:

SELECT
NoTransaksi
, TglTransaksi
, GdgCode
, QtyTransaksi
, ItemUnitCost
, NilaiKurs
, NoTransaksiBeli
, PPN
, JenisCode

FROM TMSTOKBARANGDETIL A

INNER JOIN TMBARANG B
ON A.BarangCode = B.BarangCode

WHERE GdgCode = 'OLT'
AND B.JenisCode = 'BG'
AND CONVERT(VARCHAR(6), A.TglTransaksi, 112) = '201607'

Open in new window

The problem is I get Cost = 0 for below code :

USE [GRM_TRADING]
GO
/****** Object:  StoredProcedure [dbo].[ERV_SP_Laporan_Stok_Summary]    Script Date: 7/2/2018 5:51:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ERV_SP_Laporan_Stok_Summary] 
	-- Add the parameters for the stored procedure here
	@Year CHAR(4) = ''
	, @GdgCode VARCHAR(9) = '' 
	, @JenisCode VARCHAR(5) = '' 
	, @BarangCode VARCHAR(30) = ''

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
		; WITH CTE_TERIMA
AS
(

	SELECT
	A.BarangCode
	, A.GdgCode
	, A.NoTransaksi
	, A.TglTransaksi
	, A.PPN
	, SUM(A.QtyTransaksi) AS SisaStok 
	, A.ItemUnitCost
	, A.NilaiKurs

	FROM TMSTOKBARANGDETIL A	 

	INNER JOIN TMBARANG B
	ON A.BarangCode = B.BarangCode

	WHERE YEAR(TglTransaksi) = IIF(@Year = '', YEAR(TglTransaksi), @Year)	 
	AND B.JenisCode = IIF(@JenisCode = '', B.JenisCode, @JenisCode)	 
	AND A.BarangCode = IIF(@BarangCode = '', A.BarangCode, @BarangCode)	  
	 
	AND A.QtyTransaksi > 0

	GROUP BY 
	A.BarangCode
	, A.GdgCode
	, A.NoTransaksi
	, A.TglTransaksi
	, A.PPN
	, A.ItemUnitCost
	, A.NilaiKurs	 
	) 

, CTE_KELUAR
AS
(
	SELECT
	A.BarangCode
	, A.GdgCode
	, A.NoTransaksiBeli AS NoTransaksi 
	, A.PPN
	, SUM(A.QtyTransaksi) AS SisaStok  

	FROM TMSTOKBARANGDETIL A	 

	INNER JOIN TMBARANG B
	ON A.BarangCode = B.BarangCode

	WHERE YEAR(TglTransaksi) = IIF(@Year = '', YEAR(TglTransaksi), @Year)
	AND B.JenisCode = IIF(@JenisCode = '', B.JenisCode, @JenisCode)	 
	AND A.BarangCode = IIF(@BarangCode = '', A.BarangCode, @BarangCode)	  
	 
	AND A.QtyTransaksi < 0

	GROUP BY 
	A.BarangCode
	, A.GdgCode
	, A.NoTransaksiBeli 
	, A.PPN 
	)


	SELECT 
	C.JenisCode
	, A.GdgCode
	, CONVERT(VARCHAR(6), A.TglTransaksi, 112) AS BulanTahun
	, SUM((A.SisaStok + ISNULL(B.SisaStok, 0)) * A.NilaiKurs) AS Cost

	FROM CTE_TERIMA A
	
	LEFT JOIN CTE_KELUAR B 
	ON A.BarangCode = B.BarangCode 
	AND A.GdgCode = B.GdgCode 
	AND A.PPN = B.PPN 
	AND A.NoTransaksi = B.NoTransaksi 

	INNER JOIN TMBARANG C
	ON A.BarangCode = C.BarangCode
	
	WHERE 1=1

	GROUP BY 
	C.JenisCode
	, A.GdgCode 
	, CONVERT(VARCHAR(6), A.TglTransaksi, 112) 

END

Open in new window

with result :

JenisCode	GdgCode	BulanTahun	Cost
BG  	OLT      	201607	0.00

Open in new window

I do not see the problem at the code.

What's wrong ?

Thank you.

Raja Jegan R

In your procedure, there is no need to calculate the SUM for positive and negative values and then SUM it up, it can be done in a single shot..
Try this query, this should bring you the correct result set..

declare 	@Year CHAR(4) = ''
	, @GdgCode VARCHAR(9) = '' 
	, @JenisCode VARCHAR(5) = '' 
	, @BarangCode VARCHAR(30) = ''

SELECT B.JenisCode
, A.GdgCode
, CONVERT(VARCHAR(6), A.TglTransaksi, 112) TglTransaksi
, SUM(ISNULL(A.QtyTransaksi,0)) * A.NilaiKurs AS Cost 
FROM TMSTOKBARANGDETIL A	 
INNER JOIN TMBARANG B
ON A.BarangCode = B.BarangCode
WHERE YEAR(TglTransaksi) = IIF(@Year = '', YEAR(TglTransaksi), @Year)	 
AND B.JenisCode = IIF(@JenisCode = '', B.JenisCode, @JenisCode)	 
AND A.BarangCode = IIF(@BarangCode = '', A.BarangCode, @BarangCode)	  
GROUP BY B.JenisCode
, A.GdgCode
, CONVERT(VARCHAR(6), A.TglTransaksi, 112)

Open in new window

emi_sastra

ASKER

Hi Raja,

I revised : , SUM(ISNULL(A.QtyTransaksi,0) * A.ItemUnitCost * A.NilaiKurs) AS Cost

And add , SUM(ISNULL(A.QtyTransaksi,0)) AS TotalQty

What if I want to trace the source of stock :

1. NoTransaksi is the source of stock of stock In.
2. NoTransaksiBeli is the source of stock of stock Out.

Please look at :
1. CTE_TERIMA (Stock In) which is use NoTransaksi
2. CTE_KELUAR (Stock Out) which is use NoTransaksiBeli

Thank you.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi Raja,

It works.

Thank you very much for your help.

Raja Jegan R

Welcome..