asked on

Detail to summary report using stored procedure

Hi All,

I have a detail report using SP, which is working fine.
I try to convert to make it a summary report using SP.
But the result is wrong.

USE [GRM_TRADING]
GO
/****** Object:  StoredProcedure [dbo].[ERV_SP_Query_Data_Mutasi_Stok]    Script Date: 6/28/2018 5:33:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ERV_SP_Open_Data_Mutasi_Stok]
       
    	-- Add the parameters for the stored procedure here
		@Year CHAR(4) = ''
		, @GdgCode VARCHAR(9) = ''
		, @BarangCode VARCHAR(40) = ''
		, @TipeTransaksi VARCHAR(5) = ''
		, @JenisBarang VARCHAR(5) = ''
		, @NoTransaksi VARCHAR(30) = ''
	
	WITH ENCRYPTION
	 
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_MUTASI
		AS
		(
		SELECT
		B.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[TglTransaksi]	
		, RTRIM(A.NoTransaksi) AS NoTransaksi
		, RTRIM(A.[BarangCode]) AS [BarangCode]
		, RTRIM(B.[BarCode]) AS [Kode Vendor]
		, RTRIM(B.FullName) AS BarangName	
		, A.CostNoSeq
		, A.NoSeq	
		--, (CASE WHEN QtyTransaksi > 0 THEN QtyTransaksi ELSE 0 END ) / B.Ratio1 AS Debet
		--, (CASE WHEN QtyTransaksi < 0 THEN QtyTransaksi ELSE 0 END) / Ratio1 AS Kredit
			, (CASE WHEN QtyTransaksi > 0 THEN QtyTransaksi ELSE 0 END )  AS Debet
		, (CASE WHEN QtyTransaksi < 0 THEN QtyTransaksi ELSE 0 END) AS Kredit
		, A.HPP
		, NilaiKurs 
		, A.HPPRp
		, A.SumberModal
		, B.Satuan1
		, D.NoTransaksi AS NoTransaksiOpname
		, D.Qty AS QtyTransaksiOpname	 
				
		FROM ERV_VIEW_Mutasi_Stok A

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

		LEFT JOIN Pemakaian C 
		ON A.NoTransaksi = C.NoTransaksi 
		AND A.BarangCode  = C.BarangCode 
		AND A.GdgCode = C.GdgCode 

		LEFT JOIN Opname D
		ON C.NoTransaksiOpname = D.NoTransaksi 
		AND C.GdgCode = D.GdgCode 
		AND C.BarangCode = D.BarangCode 
		AND C.GdgCode = D.GdgCode 

		WHERE 1=1
	    AND YEAR(A.TglTransaksi) = IIF(@Year = '', YEAR(A.TglTransaksi), @Year)
		AND A.GdgCode = IIF(@GdgCode = '', A.GdgCode, @GdgCode)
		AND A.BarangCode = IIF(@BarangCode = '', A.BarangCode, @BarangCode)
		AND A.TipeTransaksi = IIF(@TipeTransaksi = '', A.TipeTransaksi, @TipeTransaksi)
		AND B.JenisCode = IIF(@JenisBarang = '', B.JenisCode, @JenisBarang)

		AND 
		(
		A.NoTransaksi = IIF(@NoTransaksi = '', A.NoTransaksi, @NoTransaksi)
		OR
		A.SumberModal = IIF(@NoTransaksi = '', A.SumberModal, @NoTransaksi)
		)
		)

		SELECT
		A.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[BarangCode]
		, A.[Kode Vendor]
		, A.BarangName		
		, CONVERT(VARCHAR(20), A.[TglTransaksi], 111) AS [TglTransaksi]
		, A.NoTransaksi			
		, A.Debet		
		, A.Kredit			
		, SUM(A.Debet + A.Kredit) OVER(PARTITION BY A.GdgCode, A.BarangCode ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi, A.CostNoSeq, A.NoSeq  ROWS UNBOUNDED PRECEDING) AS [Saldo Akhir]
		, A.HPP
		, A.NilaiKurs 
		, A.HPPRp
		, RTRIM(A.SumberModal) AS SumberModal
		, A.Satuan1
		, RTRIM(A.NoTransaksiOpname) AS SumberModal
		, ISNULL(A.QtyTransaksiOpname, 0) AS QtyTransaksiOpname

		FROM CTE_MUTASI A
		
		ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi,  A.CostNoSeq, A.NoSeq
	 

	
END

Open in new window

USE [GRM_TRADING]
GO
/****** Object:  StoredProcedure [dbo].[ERV_SP_Query_Data_Mutasi_Stok]    Script Date: 6/28/2018 5:33:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ERV_SP_Open_Data_Mutasi_Stok_Summary]
       
    	-- Add the parameters for the stored procedure here
		@Year CHAR(4) = ''
		, @GdgCode VARCHAR(9) = ''
		, @BarangCode VARCHAR(40) = ''
		, @TipeTransaksi VARCHAR(5) = ''
		, @JenisBarang VARCHAR(5) = ''
		, @NoTransaksi VARCHAR(30) = ''
	
	WITH ENCRYPTION
	 
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_MUTASI
		AS
		(
		SELECT
		B.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[TglTransaksi]	
		, RTRIM(A.NoTransaksi) AS NoTransaksi
		, RTRIM(A.[BarangCode]) AS [BarangCode]
		, RTRIM(B.[BarCode]) AS [Kode Vendor]
		, RTRIM(B.FullName) AS BarangName	
		, A.CostNoSeq
		, A.NoSeq	
		--, (CASE WHEN QtyTransaksi > 0 THEN QtyTransaksi ELSE 0 END ) / B.Ratio1 AS Debet
		--, (CASE WHEN QtyTransaksi < 0 THEN QtyTransaksi ELSE 0 END) / Ratio1 AS Kredit
			, (CASE WHEN QtyTransaksi > 0 THEN QtyTransaksi ELSE 0 END )  AS Debet
		, (CASE WHEN QtyTransaksi < 0 THEN QtyTransaksi ELSE 0 END) AS Kredit
		, A.HPP
		, NilaiKurs 
		, A.HPPRp
	    , B.Satuan1
		, D.NoTransaksi AS NoTransaksiOpname
		, CASE D.SatuanCode 
		  WHEN B.Satuan THEN D.Qty 
		  WHEN B.Satuan1 THEN D.Qty * B.Ratio1
		  WHEN B.Satuan2 THEN D.Qty * B.Ratio2
		  END AS QtyTransaksiOpname	 
				
		FROM ERV_VIEW_Mutasi_Stok A

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

		LEFT JOIN Pemakaian C 
		ON A.NoTransaksi = C.NoTransaksi 
		AND A.BarangCode  = C.BarangCode 
		AND A.GdgCode = C.GdgCode 

		LEFT JOIN Opname D
		ON C.NoTransaksiOpname = D.NoTransaksi 
		AND C.GdgCode = D.GdgCode 
		AND C.BarangCode = D.BarangCode 
		AND C.GdgCode = D.GdgCode 

		WHERE 1=1
	    AND YEAR(A.TglTransaksi) = IIF(@Year = '', YEAR(A.TglTransaksi), @Year)
		AND A.GdgCode = IIF(@GdgCode = '', A.GdgCode, @GdgCode)
		AND A.BarangCode = IIF(@BarangCode = '', A.BarangCode, @BarangCode)
		AND A.TipeTransaksi = IIF(@TipeTransaksi = '', A.TipeTransaksi, @TipeTransaksi)
		AND B.JenisCode = IIF(@JenisBarang = '', B.JenisCode, @JenisBarang)

		AND 
		(
		A.NoTransaksi = IIF(@NoTransaksi = '', A.NoTransaksi, @NoTransaksi)
		OR
		A.SumberModal = IIF(@NoTransaksi = '', A.SumberModal, @NoTransaksi)
		)
		)

		SELECT
		A.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[BarangCode]
		, A.[Kode Vendor]
		, A.BarangName		
		, CONVERT(VARCHAR(20), A.[TglTransaksi], 111) AS [TglTransaksi]
		, A.NoTransaksi		
		, A.CostNoSeq	
		, A.NoSeq
		, A.Debet		
		, A.Kredit					
		, A.HPP
		, A.NilaiKurs 
		, A.HPPRp
		, A.Satuan1 
		, RTRIM(A.NoTransaksiOpname) AS NoTransaksiOpname
		, A.QtyTransaksiOpname
		
		INTO #TEMP

		FROM CTE_MUTASI A

		ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi, A.CostNoSeq, A.NoSeq
	 
		
		SELECT
		A.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[BarangCode]
		, A.[Kode Vendor]
		, A.BarangName		
		, A.TglTransaksi
		, A.NoTransaksi		
		, A.CostNoSeq	
		, SUM(A.Debet) AS Debet
		, SUM(A.Kredit) AS Kredit		
		, SUM(A.Debet + A.Kredit) OVER(PARTITION BY A.GdgCode, A.BarangCode 
		  ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi, A.CostNoSeq ROWS UNBOUNDED PRECEDING
		  ) 
		  AS [Saldo Akhir]
		, SUM(A.HPP) AS HPP
		, SUM(A.HPPRp) AS HPPRp
		, A.Satuan1 
		, ISNULL(A.NoTransaksiOpname, 0) AS NoTransaksiOpname
		, ISNULL(A.QtyTransaksiOpname, 0) AS QtyTransaksiOpname

		FROM #TEMP A

	    GROUP BY 

		A.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[BarangCode]
		, A.[Kode Vendor]
		, A.BarangName		
		, A.TglTransaksi
		, A.NoTransaksi		
		, A.CostNoSeq
		, A.Satuan1 
		, A.NoTransaksiOpname
		, A.QtyTransaksiOpname 
			
		ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi, A.CostNoSeq

		DROP TABLE #TEMP
	
END

Open in new window

What's wrong with the code.

Thank you.

Jim Horn

>What's wrong with the code.
Dude. Mind readers we ain't, and experts here cannot connect to your data sources and execute queries and find out for yourselves, which means if the queries execute and not throw an error, but the return set does not match the detail return set, then we're very limited in helping you.

There can be 50 shades of 'is wrong', so please spell that out in greater detail for us.

Please execute your queries, and if an error is thrown click on the error message, watch the cursor jump to the offending line, and tell us what line that is.

emi_sastra

ASKER

Hi Jim,

I get error.

Msg 8120, Level 16, State 1, Procedure ERV_SP_Open_Data_Mutasi_Stok_Summary, Line 117
Column '#TEMP.Debet' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure ERV_SP_Open_Data_Mutasi_Stok_Summary, Line 117
Column '#TEMP.Kredit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thank you.

Jim Horn

Ok, that's a start. Looking at your SELECT clause I don't see any aggregates such as SUM, COUNT, MIN, MAX, etc. so what's the purpose of having the GROUP BY? You should be able to eliminate the entire GROUP BY, and do a SELECT DISTINCT if needed.

emi_sastra

ASKER

I've use group clause already.

SELECT
		A.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[BarangCode]
		, A.[Kode Vendor]
		, A.BarangName		
		, A.TglTransaksi
		, A.NoTransaksi		
		, A.CostNoSeq	
		, SUM(A.Debet) AS Debet
		, SUM(A.Kredit) AS Kredit		
		, SUM(A.Debet + A.Kredit) OVER(PARTITION BY A.GdgCode, A.BarangCode 
		  ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi, A.CostNoSeq ROWS UNBOUNDED PRECEDING
		  ) 
		  AS [Saldo Akhir]
		, SUM(A.HPP) AS HPP
		, SUM(A.HPPRp) AS HPPRp
		, A.Satuan1 
		, ISNULL(A.NoTransaksiOpname, 0) AS NoTransaksiOpname
		, ISNULL(A.QtyTransaksiOpname, 0) AS QtyTransaksiOpname

		FROM #TEMP A

	    GROUP BY 

		A.JenisCode
		, A.TipeTransaksi
		, A.[GdgCode]
		, A.[BarangCode]
		, A.[Kode Vendor]
		, A.BarangName		
		, A.TglTransaksi
		, A.NoTransaksi		
		, A.CostNoSeq
		, A.Satuan1 
		, A.NoTransaksiOpname
		, A.QtyTransaksiOpname 
			
		ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi, A.CostNoSeq

		DROP TABLE #TEMP

Open in new window

The error I believe at :

, SUM(A.Debet + A.Kredit) OVER(PARTITION BY A.GdgCode, A.BarangCode
              ORDER BY A.GdgCode, A.BarangCode, A.TglTransaksi, A.CostNoSeq ROWS UNBOUNDED PRECEDING
              )
              AS [Saldo Akhir]

But I don't know the solution.

Thank you.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.