Skip to Content
0

SQL Stored Procedure: Add Currency to GL Balance report

Jan 19 at 05:42 PM

112

avatar image

Experts,

I'm fairly new to using SQL commands to generate Crystal Reports. I have a Cash GL Balance Report where I just want to add the Currency and Exchange Rate for that day.

My issue is that it does not show a column with the rate....

Any help would be greatly appreciated,

Marli

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


WITH qry as 
(
	SELECT T1.[Account]
		  ,t2.AcctName
		  ,T1.[RefDate] RefDate
		  ,SUM(T1.[Debit])De
		  ,SUM(T1.[Credit])Cr
		  ,T2.ActCurr
	FROM   OJDT T0
		   INNER JOIN JDT1 T1
				ON  T0.TransId = T1.TransId
		   INNER JOIN oact t2
				ON  t1.Account = t2.AcctCode
					AND t2.Finanse = 'Y'
					AND t2.FrozenFor = 'N'
					AND not t2.AcctName like '%Petty Cash%'
	GROUP BY
		   T1.[Account]
		  ,T1.[RefDate]
		  ,t2.AcctName
		  ,T2.ActCurr
),
currencies as
(
	SELECT getdate() as tempdatecol, cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today',


T1.[Currency], T1.[Rate], T0.[CurrName], T1.[RateDate] 
FROM OCRN T0  RIGHT OUTER JOIN ORTT T1 
ON T0.[CurrCode] = T1.[Currency]


WHERE T1.[RateDate] = CONVERT(date,GETDATE())


)




	SELECT 'Inguran_SandBox' as company
		  ,c.Account AS Accountcode
		  ,c.AcctName
		  ,sum(de) - sum(cr) AS Closing
		  ,c.ActCurr
	FROM   qry c
	LEFT JOIN currencies ON currencies.CurrName = C.ActCurr
	GROUP BY c.Account, c.AcctName, c.ActCurr
	ORDER BY 
	  case
		when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
		else c.AcctName
	  end
	  


	 


END
1-001.png (13.1 kB)
SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Marli Schutte Jan 22 at 04:26 PM
0

Nagarajan,

I appreciate it. This still gives me NULL values on the Currency columns, see screenshot.

As a work around, I have created the 2 commands as separate Stored Procedures and that works in Crystal with a few Formulas. It does not seem to slow down report performance.

Thank for your help,

Marli


Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jan 19 at 11:42 PM
1

Hi,

Try this query to get currency and exchange rate,

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

WITH qry as

( SELECT T1.[Account] ,t2.AcctName ,T1.[RefDate] RefDate ,SUM(T1.[Debit])De ,SUM(T1.[Credit])Cr ,T2.ActCurr FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN oact t2 ON t1.Account = t2.AcctCode AND t2.Finanse = 'Y' AND t2.FrozenFor = 'N' AND not t2.AcctName like '%Petty Cash%' GROUP BY T1.[Account] ,T1.[RefDate] ,t2.AcctName ,T2.ActCurr ), currencies as

( SELECT getdate() as tempdatecol, cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today', T1.[Currency], T1.[Rate], T0.[CurrName], T1.[RateDate] FROM OCRN T0 RIGHT OUTER JOIN ORTT T1 ON T0.[CurrCode] = T1.[Currency] WHERE T1.[RateDate] = CONVERT(date,GETDATE()) )

SELECT 'Inguran_SandBox' as company ,c.Account AS Accountcode ,c.AcctName ,sum(de) - sum(cr) AS Closing ,c.ActCurr ,Currencies.Currency ,Currencies.Rate ,Currencies.RateDate FROM qry c LEFT JOIN currencies ON currencies.CurrName = C.ActCurr

GROUP BY c.Account, c.AcctName, c.ActCurr,Currencies.Currency,Currencies.Rate,Currencies.RateDate

ORDER BY case when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName else c.AcctName

end

END

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jan 23 at 02:04 PM
1

Hi,

Try this query,

WITH qry as ( SELECT T1.[Account] ,t2.AcctName ,T1.[RefDate] RefDate ,SUM(T1.[Debit])De ,SUM(T1.[Credit])Cr ,T2.ActCurr FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN oact t2 ON t1.Account = t2.AcctCode AND t2.Finanse = 'Y' AND t2.FrozenFor = 'N' AND not t2.AcctName like '%Petty Cash%' GROUP BY T1.[Account] ,T1.[RefDate] ,t2.AcctName ,T2.ActCurr ), currencies as ( SELECT getdate() as tempdatecol, cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today', T1.[Currency], T1.[Rate], T0.[CurrName], T1.[RateDate] FROM OCRN T0 LEFT JOIN ORTT T1 ON T0.[CurrCode] = T1.[Currency] WHERE T1.[RateDate] = CONVERT(date,GETDATE()) ) SELECT 'Inguran_SandBox' as company ,c.Account AS Accountcode ,c.AcctName ,sum(de) - sum(cr) AS Closing ,c.ActCurr , Currencies.Currency ,Currencies.Rate ,Currencies.RateDate FROM qry c LEFT JOIN currencies ON currencies.Currency = C.ActCurr GROUP BY c.Account, c.AcctName, c.ActCurr,Currencies.Currency,Currencies.Rate,Currencies.RateDate ORDER BY case when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName else c.AcctName end

Make sure you have entered exchange rate for today.

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 23 at 01:58 PM
1

Hi Marle,

I know you have a workaround but to answer your original question....

In your output statement, I do not see a column for rate and I think you are joining the currencies table using the wrong field.

Using this as the output statement, comments on the altered lines :-

SELECT 'Inguran_SandBox' as company
  ,c.Account AS Accountcode
  ,c.AcctName
  ,sum(de) - sum(cr) AS Closing
  ,c.ActCurr
  ,currencies.Rate    /* column for currency rate */
FROM   qry c
LEFT JOIN currencies ON currencies.Currency = C.ActCurr /* join on currency code, not currency name */
GROUP BY c.Account, c.AcctName, c.ActCurr, currencies.Rate
ORDER BY 
  case
when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
else c.AcctName
  end

works for me! ;-)

HTH and best wishes

Dave


sql-180123.png (17.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Marli Schutte Jan 24 at 05:29 PM
0

David,

Thanks that worked! Appreciate your and Nagarajan's help!

Share
10 |10000 characters needed characters left characters exceeded