October 13, 2008 at 3:42 pm
I wrote a sp in SQL Server with 2 datetime parameters. Crystal won't accept it--I guess it wants a "date" parameter (little new to Crystal and am missing SSRS). So I've read to make the parameters in the proc to strings and then convert them to datetime in the proc. Only problem--I haven't figured out the right syntax.
Some of my errors:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Syntax error converting the varchar value '07/01/2008' to a column of data type int.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Code:
alter PROCEDURE [dbo].[Trans_History3_Crystal_Account_pr]
@Company AS INT
,@Accounts AS VARCHAR(100)
,@AccountingUnit as VARCHAR(100)
,@BegSubAcct as INT
,@EndSubAcct as INT
,@StartDate AS VARCHAR(11)
,@EndDate AS VARCHAR(11)
,@LRange as Bit
--,@Group as nvarchar(16)
,@NoBalAct as bit
set @StartDate = cast (@StartDate as datetime)
set @EndDate = cast(@EndDate as datetime)
I've tried SET DATEFORMAT and that hasn't worked either.
October 13, 2008 at 5:40 pm
Paraphrasing your extract of your stored procedure as:
CREATE PROCEDURE TDateTest
@StartDate AS VARCHAR(11),
@EndDate AS VARCHAR(11)
AS
DECLARE @SDate AS DATETIME
DECLARE @EDate AS DATETIME
set @SDate = cast (@StartDate as datetime)
set @EDate = cast(@EndDate as datetime)
set @StartDate = cast (@StartDate as datetime)
set @EndDate = cast(@EndDate as datetime)
SELECT @StartDate AS StartDate, @EndDate AS EndDate, @SDate AS SDATE, @EDate AS EDATE
Ran as:
TDateTest '07/01/2008','08/01/2008'
Results:
StartDate EndDate SDATE EDATE
----------- ----------- ----------------------- -----------------------
Jul 1 2008 Aug 1 2008 2008-07-01 00:00:00.000 2008-08-01 00:00:00.000
No errors -- also note the difference in the formats SDATE and EDATE are true datetime values.
There must be something else in your procedure to create the problem. If you post all of the SP maybe someone will find the error and help you.
October 14, 2008 at 7:34 am
I get the same results that you show. However, when I put it in my stored proc, I get no results and I should. Don't know if anyone wants to look through the proc but I've posted it below. I've tried everything on the 'Net and am beating my head against the wall. Surely people have come up with this before.
Crystal Reports reads your proc and parameters and types and doesn't like SQL Server's datetime type.
USE [trn]
GO
/****** Object: StoredProcedure [dbo].[Trans_History3_Crystal_Account_pr] Script Date: 10/13/2008 10:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[Trans_History3_Crystal_Account_pr]
@Company AS INT
,@Accounts AS VARCHAR(100)
,@AccountingUnit as VARCHAR(100)
,@BegSubAcct as INT
,@EndSubAcct as INT ,
@SDate AS VARCHAR(11)
,@EDate AS VARCHAR(11)
,@LRange as Bit
,@NoBalAct as bit
--1st test to get zero records ddr 9/24/08
--exec Trans_History3_Crystal_Account_pr 3000,'10010,10010','ALL',0,999, '01/01/2007','01/01/07',0,1
--exec Trans_History3_Crystal_Account_pr 9330,'10000,99999','ALL',0,999,'05/01/2008','07/31/2008',1,1
--NOTE: This code is separated by sorting acct_unit, accounts and then by accounts,acct_unit
--Accordingly, you need to change both sets of Crystal code for any enhancements/bugs ddr 8/15/08
AS
----***********NOTES
----used Running Balances code found on the Internet ddr 9/10/08
----This procedure is used to get beginning balance (based on parameters)
----transactions with date range and calculate and ending balance
----It's a basic general ledger for the clients
----************
--
--***LEAVE DECLARED
--PRINT @StartDate
--PRINT @EndDate
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
set @StartDate = cast (@SDate as datetime)
set @EndDate = cast(@EDate as datetime)
set @SDate = cast (@StartDate as datetime)
set @EDate = cast(@EndDate as datetime)
--SELECT @StartDate AS StartDate, @EndDate AS EndDate, @SDate AS SDATE, @EDate AS EDATE
--PRINT @StartDate
--PRINT @EndDate
DECLARE
@FiscalYearStart as Int,
@FiscalYearEnd as Int,
@FiscalMonthStart as Int,
@FiscalMonthEnd as Int,
@FirstOfStartMonth as datetime,
@StartDateFirstDay as datetime,
@i AS INT,
@AcctUnit1 as nvarchar(50),
@IDs as int,
@Delim varchar(1),
@FirstLen int,
@LastLen int,
@NumCommasAccounts int,
@StartAccount AS INT,
@EndAccount AS INT,
@Counter AS INT,
@rowcount as int,
@rowcount2 as int,
@rowcounttmpGL as int,
@rowcountGLBegBal as int,
--@msg as nvarchar(5),
--@msg2 as nvarchar(5),
@rowcount3 as int,
@Group as nvarchar(15)
set @Delim = ','
set @FirstLen = len(@Accounts)
set @NumCommasAccounts = 0
set @StartAccount = 0
set @EndAccount = 0
set @rowcount = 0
set @rowcount2 = 0
set @rowcount3 = 0
set @rowcounttmpGL = 0
set @rowcountGLBegBal = 0
set @Group = 'Account'
--SET @StartDate = @StartDate -1 -- to get 24 hours before midnight
--print 'got here'
SET @FiscalYearStart = YEAR(@StartDate)+1
set @FiscalYearEnd = YEAR(@EndDate)
set @FiscalMonthStart = MONTH(dateadd(Day,1,@StartDate))
SET @FiscalMonthEnd = MONTH(dateadd(Day,1,@EndDate))
SET @StartAccount = 0
set @EndAccount = 0
--ERROR CHECKING
--print @Group
if @StartDate > getdate() or @EndDate > (getdate() + 10000) --or @StartDate < '1970-1-01'or @EndDate < '1970-1-01'
begin
print 'Please check your date range. It appears to have an error.'
-- declare @PrintMsg nvarchar(50)
-- set @PrintMsg = 'Please check your date range. It appears to have an error.'
--it won't print but it at least stops processing.
return
end
----
------IF MONTH(@EndDate) = 1
------ BEGIN
------ SET @FiscalMonthPriortoEndDateMonth = 12
------ END
------ELSE
------ BEGIN
------ SET @FiscalMonthPriortoEndDateMonth = MONTH(@EndDate) -1
------ END
--
----TO get first of month of beginning date set to a variable
IF DAY (@StartDate) <> 1
BEGIN
SET @FirstOfStartMonth = dateadd(month, -1,DateAdd(Month,DateDiff(Month,0,@StartDate),0))
END
else
BEGIN
SET @FirstOfStartMonth = @StartDate
END
set @StartDateFirstDay = dateadd(month,0,DateAdd(Month,DateDiff(Month,0,@StartDate),0))
SET @Delim = ','
set @NumCommasAccounts = @FirstLen - LEN(Replace(@Accounts,@Delim,''))
--give default range based on no range, no commas and no account numbers input
--no account input - give them whole range
if @Accounts = '0' OR len(@Accounts) = 0
BEGIN
set @StartAccount = 10000
set @EndAccount = 99999
set @LRange = 1
END
--one account only, whether or not they clicked the Range checkbox
ELSE
BEGIN
--code based on user input
if @NumCommasAccounts >= 1 and len(@Accounts) > 0 and @Accounts <> '0'
begin
DECLARE @VALUES TABLE(Account nvarchar(4000))
declare @tmpAccountsRowNum table
(Acct int
,Range bit)
INSERT INTO @VALUES
select cast(param as int) from dbo.fn_MVParam(@Accounts,',')
SET @Counter = @@rowcount
CREATE TABLE #tmpAccounts
(RowNumber int IDENTITY(1,1)
,Account int
)
INSERT #tmpAccounts(Account)
Select DISTINCT Account
From @VALUES
IF @@ROWCOUNT = 2
SELECT @EndAccount = Account FROM #tmpAccounts WHERE RowNumber = 2
ELSE
SELECT @EndAccount = Account FROM #tmpAccounts WHERE RowNumber = 1
SELECT @StartAccount = Account FROM #tmpAccounts WHERE RowNumber = 1
-- print convert(nvarchar(5),@StartAccount)
--print convert(nvarchar(5),@EndAccount)
--range of 2 numbers
IF @NumCommasAccounts >= 1 and @LRange = 1 and @Counter = 2 -- gets rid of compile error-this should be empty, values already set
set @StartAccount = @StartAccount
--USE VALUES 'IN @tmpAccountsRowNum'
ELSE
BEGIN
set @StartAccount = 0
set @EndAccount = 0
SET @LRange = 0
END
END
else -- one account
IF ((@NumCommasAccounts -1)
and len(@Accounts) > 0) and @Accounts <> '0'
BEGIN
set @StartAccount = cast(@Accounts as int)
set @EndAccount = cast(@Accounts as int)
set @LRange = 1
END
end
--string of Accounting Units--put in table
DECLARE @VALUESAcctUnit TABLE(AcctUnit nvarchar(15))
if @AccountingUnit <> 'ALL' and len(@AccountingUnit) > 0
begin
INSERT INTO @VALUESAcctUnit
select cast(param as int) from dbo.fn_MVParam(@AccountingUnit,',')
END
--PRINT @StartAccount
--PRINT @EndAccount
--PRINT @LRange
--
--SELECT * FROM #tmpAccounts
----------------********************
------BEGINNING GL BALANCES FOR EACH MONTH FOR EACH FISCAL YEAR REQUIRED BY USER
------gets cumulative balances, debit and credit, by month and year
------based on time period, join this with transactions
------appears to me that you'll just need balances for month prior to ending month
------since it is cumulative and you'll need debittrans and credittrans
------selected below
------the view was built with Doug's help--vwLawsonGLAmounts, currently on dev-prod to speed
------up this query since GLAmounts is not well normalized.
------had to use the DB_AMOUNT_13 field to get an integer field to use for month
------get regular accounts first; memo accounts 2nd
----
DECLARE @tmpGLAmts TABLE(Company INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
DECLARE @tmpViewGL TABLE
(Company INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
DECLARE @tmpGLMemos TABLE(COMPANY INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
--
DECLARE @tmpGLMemoAmounts TABLE(COMPANY INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
DECLARE @tmp_GLAccounts table(Company int
,var_levels nvarchar(30)
,account int
,sub_account int
,acct_unit nvarchar(15)
,chart_name nvarchar(12)
,description nvarchar(30)
,account_desc nvarchar(60))
DECLARE @tmp_GLBegBalTrans TABLE
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,debittrans decimal(24,2)
,credittrans decimal(24,2)
,description nvarchar(30)
)
DECLARE @tmp_GLBegBal TABLE
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,DebitsBeginning decimal(24,2)
,CreditsBeginning decimal(24,2)
,TMONTH int
,account_desc nvarchar(150)
,debittrans decimal(24,2)
,credittrans decimal(24,2)
,description nvarchar(30)
)
DECLARE @tmp_CurrPeriodTrans table
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,posting_date datetime
,tranamount decimal(24,2)
,reference nvarchar(150)
,description nvarchar(150)
,update_date datetime
,control_group int
,system nvarchar(2)
)
DECLARE @tmp_CurrPeriodTrans2 table
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,posting_date datetime
,tranamount decimal(24,2)
,reference nvarchar(150)
,description nvarchar(150)
,update_date datetime
,control_group int
,system nvarchar(2)
)
DECLARE @tmp_Totals TABLE
(company int
,fiscal_year int
,TMONTH INT
,acct_unit nvarchar(100)
,account int
,sub_account int
,TotBegDebits decimal(24,2)
,TotBegCredits decimal(24,2)
,begbal decimal(24,2)
,totaldebits decimal(24,2)
,totalcredits decimal(24,2)
)
--do not turn into # tmp table as I have to DELETE with it and it has an identity column
DECLARE @tmpZeros Table
(RowNum int, BegBal decimal (24,2), totdebits decimal(18,2)
,totcredits decimal(18,2), fiscal_year int
, account int, acct_unit nvarchar(15)
, sub_account int)
--print (@Group)
--
--*************************sort by acct_unit or by account****************************
if rtrim(@Group) = 'Account' and right(rtrim(@Group),4) <> 'Unit'
BEGIN
--get ending balances at a month from the view built with Doug
--had to build views for both tables because they are not normalized and months are columns
--which doesn't work
--dbo.vwLawsonGLAmounts only has accounts less than 80000
--dbo.vwLawsonGLAmounts_Memo only has accounts 80000 and higher
--Print @AccountingUnit
--print convert(nvarchar(10),LEN(@AccountingUnit))
--print @StartAccount
--print @EndAccount
--print @LRange
--print @FiscalYearStart
--print @FiscalYearEnd
IF UPPER(@AccountingUnit) <> 'ALL' AND LEN(@AccountingUnit) > 0
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
,SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (SELECT AcctUnit from @VALUESAcctUnit)
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
--ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (SELECT AcctUnit from @VALUESAcctUnit)
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
--ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
-- select * from @tmpViewGL
END
else
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
,SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (SELECT AcctUnit from @VALUESAcctUnit)
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
-- ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (SELECT AcctUnit from @VALUESAcctUnit)
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
-- ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
-- SELECT * FROM @tmpViewGL
END
else -- ALL ACCOUNTING UNITS
--range of accounts
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
,SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
--ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
--ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
END
else -- get all accounting units, not a range
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
--ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD,
SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
--ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
END
INSERT INTO @tmpGLAmts
SELECT company, fiscal_year, account, sub_account, acct_unit
,TMONTH, SUM(DMTD) AS DMTD, SUM(CMTD) AS CMTD
FROM @tmpViewGL
WHERE TMONTH = @FiscalMonthStart
GROUP BY company,fiscal_year,account,acct_unit,sub_account,TMONTH
INSERT INTO @tmpGLMemoAmounts
SELECT company, fiscal_year, account, sub_account, acct_unit
,TMONTH, SUM(DMTD) AS DMTD, SUM(CMTD) AS CMTD
FROM @tmpGLMemos
WHERE TMONTH = @FiscalMonthStart
GROUP BY company,fiscal_year,TMONTH,account,acct_unit,sub_account
SELECT @rowcounttmpGL = count(*) from @tmpGLAmts
----select * from @tmpGLAmts
----select * from @tmpGLMemoAmounts
if LEN(@AccountingUnit) = 0
begin
print 'Must have accounting unit--can be "ALL"--for report to run'
-- IF OBJECT_ID('tempdb..#tmpAccounts') IS NOT NULL
-- DROP TABLE #tmpAccount
return(0)
end
--print @AccountingUnit
--print @StartAccount
--print @EndAccount
--print @LRange
----have to get all accounts for left outer join below even though they may not
----have beginning balances, they may have transactions and won't be available
----for the left side of the join without this
IF UPPER(@AccountingUnit) <> 'ALL' --AND LEN(@AccountingUnit) > 0
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
BEGIN
insert INTO @tmp_GLAccounts
select distinct m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl
on m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.acct_unit in (SELECT AcctUnit from @VALUESAcctUnit)
AND m.account between @StartAccount and @EndAccount
and m.sub_account >= @BegSubAcct and m.sub_account <= @EndSubAcct
END
ELSE
--if @StartAccount = 0 AND @EndAccount = 0 or @LRange = 0
BEGIN
insert INTO @tmp_GLAccounts
select distinct m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl on m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.acct_unit in (SELECT AcctUnit from @VALUESAcctUnit)
AND m.account in (select Account from #tmpAccounts)
and m.sub_account >= @BegSubAcct and m.sub_account <= @EndSubAcct
END
else
-- UPPER(@AccountingUnit) = 'ALL'
--Acct_unit code --for index
IF @StartAccount <> 0 AND @EndAccount <> 0
BEGIN
INSERT INTO @tmp_GLAccounts
select DISTINCT m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl
on m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.account between @StartAccount and @EndAccount
and m.sub_account >= @BegSubAcct
and m.sub_account <= @EndSubAcct
END
else --acct unit = ALL and select accounts
BEGIN
INSERT INTO @tmp_GLAccounts
select DISTINCT m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.var_levels = n.var_levels
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl on
m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.account in (select Account from #tmpAccounts)
and m.sub_account >= @BegSubAcct
and m.sub_account <= @EndSubAcct
END
------get beginning GL balances
DECLARE @tmp_GLBegAccounts TABLE
(company int
, fiscal_year int
, acct_unit nvarchar(100)
,account int, sub_account int
,account_desc nvarchar(60)
,description nvarchar(30)
, TMONTH int
,BegDebits decimal (24,2)
, BegCredits decimal (24,2)
)
------range or one account
insert into @tmp_GLBegAccounts
select glaa.company
,gla.fiscal_year
,glaa.acct_unit
,glaa.account
,glaa.sub_account
,glaa.account_desc
,glaa.description
,gla.TMONTH
,gla.DMTD as BegDebits
,gla.CMTD as BegCredits
from @tmp_GLAccounts glaa
left outer join @tmpGLAmts gla on glaa.company = gla.company
and glaa.acct_unit = gla.acct_unit
and glaa.account = gla.account
and glaa.sub_account = gla.sub_account
where glaa.company = @Company
and gla.fiscal_year >= @FiscalYearStart
and gla.fiscal_year <= @FiscalYearEnd
AND GLaa.ACCOUNT < 80000
UNION
select glaa.company
,gla.fiscal_year
,glaa.acct_unit
,glaa.account
,glaa.sub_account
,glaa.account_desc
,glaa.description
,gla.TMONTH
,gla.DMTD as BegDebits
,gla.CMTD as BegCredits
from @tmp_GLAccounts glaa
left outer join @tmpGLMemoAmounts gla on glaa.company = gla.company
and glaa.acct_unit = gla.acct_unit
and glaa.account = gla.account
and glaa.sub_account = gla.sub_account
where glaa.company = @Company
and gla.fiscal_year >= @FiscalYearStart
and gla.fiscal_year <= @FiscalYearEnd
AND GLaa.ACCOUNT > 79999
SELECT @rowcountGLBegBal= COUNT(*) FROM @tmp_GLAccounts
-- select * from @tmp_GLBegAccounts
IF @StartAccount = 0 AND @EndAccount = 0
begin
select *
INTO #tmp_GLTransPullFromTmp
from GLTrans glt
where glt.company = @Company
and glt.fiscal_year between @FiscalYearStart and @FiscalYearEnd
AND glt.account in (SELECT Account from #tmpAccounts)
and glt.sub_account between @BegSubAcct and @EndSubAcct
and (glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate) -- dateadd(dd,1,@EndDate) )
and glt.posting_date <> '1900-01-01'
and glt.status = 9
SELECT @rowcount = count(*) from #tmp_GLTransPullFromTmp
end
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
begin
select *
INTO #tmp_GLTransPullBegEnd
from GLTrans glt
where glt.company = @Company
and glt.fiscal_year between @FiscalYearStart and @FiscalYearEnd
and glt.account between @StartAccount and @EndAccount
and glt.sub_account between @BegSubAcct and @EndSubAcct
and (glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate) --dateadd(dd,1,@EndDate) )
and glt.posting_date <> '1900-01-01'
and glt.status = 9
SELECT @rowcount2 = count(*) from #tmp_GLTransPullBegEnd
END
----Transactions to add to beginning blances
----non sequential acct #s or one number
-----DECLARE @tmp_GLBegBalTrans TABLE above
------PRINT @StartAccount
------PRINT @EndAccount
------PRINT @StartDateFirstDay
------PRINT @FirstOfStartMonth
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
INSERT INTO @tmp_GLBegBalTrans
select gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,sum(case when (glt.tran_amount > 0 or glt.units_amount > 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate )
then glt.tran_amount+glt.units_amount else 0 end)
as debittrans
,sum(case when (glt.tran_amount < 0 or glt.units_amount < 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate )
then glt.tran_amount+glt.units_amount else 0 end)
as credittrans
,gla.description
from @tmp_GLBegAccounts gla
left outer join #tmp_GLTransPullBegEnd glt on gla.company = glt.company
and gla.fiscal_year = glt.fiscal_year
and gla.acct_unit = glt.acct_unit
and gla.account = glt.account
and gla.sub_account = glt.sub_account
where gla.fiscal_year between @FiscalYearStart and @FiscalYearEnd
--and gla.account between @StartAccount and @EndAccount
and gla.sub_account >= @BegSubAcct and gla.sub_account <= @EndSubAcct
group by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
order by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
--ELSE
IF @StartAccount = 0 AND @EndAccount = 0
begin
INSERT INTO @tmp_GLBegBalTrans
select gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,sum(case when (glt.tran_amount > 0 or glt.units_amount > 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate )AND
(glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate )
then glt.tran_amount+glt.units_amount else 0 end)
as debittrans
,sum(case when (glt.tran_amount < 0 or glt.units_amount < 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate ) AND
(glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate )
then glt.tran_amount+glt.units_amount else 0 end)
as credittrans
,gla.description
from @tmp_GLBegAccounts gla
left outer join #tmp_GLTransPullFromTmp glt on gla.company = glt.company
and gla.fiscal_year = glt.fiscal_year
and gla.acct_unit = glt.acct_unit
and gla.account = glt.account
and gla.sub_account = glt.sub_account
where gla.sub_account >= @BegSubAcct and gla.sub_account <= @EndSubAcct
and gla.fiscal_year >= @FiscalYearStart and gla.fiscal_year <= @FiscalYearEnd
--and gla.account in (SELECT Account from #tmpAccounts)
group by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
order by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
END
--
---- ----BEGINNING BALANCES--adds GLTrans to month end totals if they start on a day other
---- -- than the first day of the month--just the range of GLTrans from the beginning
---- -- of the month to before the beginning date
---- ----add debittrans TO total transaction debits on the report
---- ----add BegCredits to total transaction credits on the report
---- ----DECLARE @tmp_GLBegBal TABLE above
----
INSERT INTO @tmp_GLBegBal
select glt.company
,glt.fiscal_year
,glt.acct_unit
,glt.account
,glt.sub_account
,gla.BegDebits as DebitsBeginning
,gla.BegCredits as CreditsBeginning
,gla.TMONTH
,gla.account_desc
,sum(glt.debittrans) as debittrans
,sum(glt.credittrans) AS credittrans
,glt.description
from @tmp_GLBegBalTrans glt
INNER join @tmp_GLBegAccounts gla on
glt.company = gla.company
and glt.fiscal_year = gla.fiscal_year
and glt.acct_unit = gla.acct_unit
and glt.account = gla.account
and glt.sub_account = gla.sub_account
group by glt.company,glt.fiscal_year,gla.TMONTH
,glt.account,glt.acct_unit,glt.sub_account,gla.account_desc
, gla.BegDebits, gla.BegCredits,glt.description
order by glt.company,glt.fiscal_year,gla.TMONTH,glt.account,glt.acct_unit
,glt.sub_account,gla.account_desc,glt.description
SELECT @rowcount3 = count(*) from @tmp_GLBegBalTrans
------CURRENT PERIOD TRANSACTIONS between date period they selected-includes those dates as beginning
------and ending
------DECLARE @tmp_CurrPeriodTrans table above
--select * from @tmp_GLBegAccounts
--select * from @tmp_GLBegBal
--
if @rowcounttmpGL = 0 and @rowcountGLBegBal = 0 and @rowcount = 0 and @rowcount2 = 0
and @rowcount3 = 0 and @NoBalAct = 1
BEGIN
PRINT 'There are no records'
IF OBJECT_ID('tempdb..#tmpAccounts') IS NOT NULL
DROP TABLE #tmpAccounts
IF OBJECT_ID('tempdb..#tmpGLTransPullBegEnd') IS NOT NULL
DROP TABLE #tmpGLTransPullBegEnd
IF OBJECT_ID('tempdb..#tmp_GLTransPullFromTmp') IS NOT NULL
DROP TABLE #tmp_GLTransPullFromTmp
return(0)
END
---- code to handle the dates to take care of the midnight thing
--IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
INSERT INTO @tmp_CurrPeriodTrans
SELECT glt.company
,glt.fiscal_year
,glt.acct_unit
,glt.account
,glt.sub_account
,isnull(glt.posting_date,'') as posting_date
,glt.tran_amount+glt.units_amount as tranamount
,glt.reference
,glt.description
,glt.update_date
,glt.Control_Group
,glt.system
from #tmp_GLTransPullBegEnd glt
where glt.posting_date between dateadd(dd,-1,@StartDate) and dateadd(dd,1,@EndDate)
--AND glt.account between @StartAccount and @EndAccount
order by glt.company,glt.fiscal_year
,glt.account,glt.acct_unit,glt.sub_account
-- else
-- INSERT INTO @tmp_CurrPeriodTrans
-- select glt.company
-- ,glt.fiscal_year
-- ,glt.acct_unit
-- ,glt.account
-- ,glt.sub_account
-- ,isnull(glt.posting_date,'') as posting_date
-- ,glt.tran_amount+glt.units_amount as tranamount
-- ,glt.reference
-- ,glt.description
-- ,glt.update_date
-- ,glt.Control_Group
-- ,glt.system
-- from #tmp_GLTransPullFromTmp glt
-- where glt.posting_date between dateadd(dd,-1,@StartDate) and dateadd(dd,1,@EndDate)
-- --AND glt.account in (SELECT Account from #tmpAccounts)
-- order by glt.company,glt.fiscal_year
-- ,glt.account,glt.acct_unit,glt.sub_account
---- select * from @tmp_CurrPeriodTrans
--
----
-- ------Get company, name, date period, account#/sub, acct_unit,
-- -----date and details of transactions
DECLARE @tmp_Details TABLE
(RowNum INT IDENTITY (1,1) NOT NULL
,company int NOT NULL
,fiscal_year int NOT NULL
,acct_unit nvarchar(100) NOT NULL
,account int NOT NULL
,sub_account int NOT NULL
,posting_date datetime NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH INT NOT NULL
,account_Desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(30) NOT NULL
,update_date datetime NOT NULL
,debits decimal(18,2) not NULL
,credits decimal(18,2) not NULL
,BegBal MONEY not NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,JE int NOT NULL
,StartDate datetime NOT NULL
,EndDate datetime NOT NULL)
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
INSERT INTO @tmp_Details
SELECT gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,isnull(cpt.posting_date,'') as posting_date
,isnull(cpt.reference,'') as reference
,isnull(cpt.system,'') as SystemCode
,(gla.DebitsBeginning + gla.debittrans) as TotBegDebits
,gla.CreditsBeginning + gla.credittrans as TotBegCredits
,gla.TMONTH
,gla.account_desc
,gla.description as acct_unitname
,isnull( cpt.update_date, '') as update_date
,CASE
when cpt.tran_amount > 0 then cpt.tran_amount else 0 end as debits
,CASE
when cpt.tran_amount < 0 then cpt.tran_amount else 0 end as credits
,0 as BegBal
,0 as RunBal
,isnull(cpt.description,'') as TransDescr
,isnull(cpt.Control_Group,0) as JE
,@StartDate as StartDate
,@EndDate as EndDate
FROM @tmp_GLBegBal gla
LEFT OUTER JOIN #tmp_GLTransPullBegEnd cpt ON
gla.company = cpt.company
AND gla.fiscal_year = cpt.fiscal_year
AND gla.acct_unit = cpt.acct_unit
AND gla.account = cpt.account
AND gla.sub_account = cpt.sub_account
else
INSERT INTO @tmp_Details
SELECT gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,isnull(cpt.posting_date,'') as posting_date
,isnull(cpt.reference,'') as reference
,isnull(cpt.system,'') as SystemCode
,gla.DebitsBeginning + gla.debittrans as TotBegDebits
,gla.CreditsBeginning + gla.credittrans as TotBegCredits
,gla.TMONTH
,gla.account_desc
,gla.description as acct_unitname
,isnull( cpt.update_date, '') as update_date
,CASE
when cpt.tran_amount > 0 then cpt.tran_amount else 0 end as debits
,CASE
when cpt.tran_amount < 0 then cpt.tran_amount else 0 end as credits
,0 as BegBal
,0 as RunBal
,isnull(cpt.description,'') as TransDescr
,isnull(cpt.Control_Group,'') as JE
,@StartDate as StartDate
,@EndDate as EndDate
FROM @tmp_GLBegBal gla
LEFT OUTER JOIN #tmp_GLTransPullFromTmp cpt ON
gla.company = cpt.company
AND gla.fiscal_year = cpt.fiscal_year
AND gla.acct_unit = cpt.acct_unit
AND gla.account = cpt.account
AND gla.sub_account = cpt.sub_account
----select * from @tmp_Details
--
--
IF OBJECT_ID('tempdb..#tmpAccounts') IS NOT NULL
DROP TABLE #tmpAccounts
IF OBJECT_ID('tempdb..#tmpGLTransPullBegEnd') IS NOT NULL
DROP TABLE #tmpGLTransPullBegEnd
IF OBJECT_ID('tempdb..#tmp_GLTransPullFromTmp') IS NOT NULL
DROP TABLE #tmp_GLTransPullFromTmp
--
-- --cursor to test numbers to Lawson-check period on both
-- --TO TEST AGAINST LAWSON NUMBERS
---- insert into @tmp_Totals
---- select company
---- ,fiscal_year
---- ,TMONTH
---- ,acct_unit
---- ,account
---- ,sub_account
---- ,TotBegDebits
---- ,TotBegCredits
---- ,TotBegDebits+TotBegCredits as begbal
---- ,sum(debits) as totaldebits
---- ,sum(credits) as totalcredits
---- FROM @tmp_Details
---- GROUP BY company
---- ,fiscal_year
---- ,TMONTH
---- ,account
---- ,acct_unit
---- ,sub_account
---- ,TotBegDebits
---- ,TotBegCredits
---- ORDER BY company
---- ,fiscal_year
---- ,TMONTH
---- ,account
---- ,acct_unit
---- ,sub_account
----
---- select * from @tmp_Totals
----
--have to create new table for each user
--found this on a forum when my dynamic sql wasn't working
-- ddr 9/30/08
CREATE TABLE #nisse (RowNum INT IDENTITY(1,1) NOT NULL
,Company int NOT NULL
,Name nvarchar(50)
,Fiscal_year int NOT NULL
,Acct_unit nvarchar(15) NOT NULL
,Account int NOT NULL
,sub_account int NOT NULL
,posting_date datetime NOT NULL
,update_date datetime NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,debits decimal (18,2) NOT NULL
,credits decimal (18,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME NOT NULL
,JE int NOT NULL)
ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date
on #nisse (Account, Acct_unit, sub_account,posting_date)
insert into #nisse
(company
,Name
,account
,acct_unit
,sub_account
,fiscal_year
,posting_date
,update_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
)
Select company
,''
,account
,acct_unit
,sub_account
,fiscal_year
,posting_date
,Update_date
-- ,case
-- when convert(varchar,posting_date,101) = '01/01/1900'
-- then ''''
-- else + convert(varchar,posting_date,101)
-- end as posting_date
-- ,case
-- when convert(varchar,update_date,101) = '01/01/1900'
-- then ''''
-- else + convert(varchar,update_date,101)
-- end as update_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
FROM @tmp_Details
--
-- --if user doesn't want to see zero balance/activity accounts, we're deleting them
-- --ddr 9/12/08
-- --this table declared above
DECLARE @tmpZero Table
(BegBal decimal (24,2), totdebits decimal(18,2)
,totcredits decimal(18,2), fiscal_year int
, account int, acct_unit nvarchar(15)
, sub_account int)
--select * from #nisse
--declare @count1 int
--declare @count2 int
--declare @count3 int, @count4 int
--
--set @count1 = 0
--set @count2 = 0
--set @count3 = 0
--set @count4 = 0
if @NoBalAct = 1
BEGIN
INSERT INTO @tmpZero --(BegBal, totdebits, totcredits)
SELECT SUM(TotBegDebits+TotBegCredits) AS BegBal, sum(debits) as totdebits,
sum(credits) as totcredits, fiscal_year,account,acct_unit,sub_account
from @tmp_Details
group by fiscal_year,account,acct_unit,sub_account
order by fiscal_year,account,acct_unit,sub_account
--select @count1 = @@ROWCOUNT
--print convert(nvarchar(5),@count1) + 'count1'
--select * from @tmpZero
DELETE FROM @tmpZero
WHERE BegBal+totdebits+totcredits <> 0
--select @count2 = @@ROWCOUNT
--print convert(nvarchar(5),@count2) + 'count2'
--select * from @tmpZero
IF @@ROWCOUNT = 0
begin
set @NoBalAct = 0
end
--select * from #nisse
if @NoBalAct = 1
DELETE
FROM #nisse
WHERE #nisse.fiscal_year+#nisse.acct_unit+#nisse.account+#nisse.sub_account In
(Select fiscal_year+acct_unit+account+sub_account from @tmpZero)
--select @count3 = @@ROWCOUNT
--print convert(nvarchar(5),@count3) + 'count3'
END
---select * from #nisse
--select @count4 = @@ROWCOUNT
--print convert(nvarchar(5),@count4) + 'count4'
--set the Beginning Balances on each line based on Beginning Debits and Beginning Credits
UPDATE #nisse
SET BegBal = TotBegDebits + TotBegCredits
FROM #nisse --WITH (INDEX(IX_TransHist_AccountID_Date),TABLOCKX)
--EXEC @sql
update #nisse
set name = case when company = 9330 then 'Southwestern Kansas'
when company = 9400 then 'Southern Colorado'
when company = 9430 then 'Mountain Plains'
when company = 9480 then 'Premier Participations'
when company = 9530 then 'New Mexico'
when company = 9400 then 'Southern Colorado'
else 'AFG' end
FROM #nisse
--THE @PrevRunBal resets the previous running balance when account, sub, or acct_unit
-- changes ddr 9/8/08
DECLARE @PrevRunBal2 MONEY
SET @PrevRunBal2 = 0
DECLARE @PrevAcct2 int
SET @PrevAcct2 = ''
DECLARE @PrevSubAcct2 smallint
SET @PrevSubAcct2 = ''
DECLARE @PrevAcctUnit2 nvarchar(15)
SET @PrevAcctUnit2 = ''
--set the beginning balances for Running Balance field ddr 9/11/08
UPDATE #nisse
SET RunBal = BegBal
FROM #nisse --WITH (INDEX(IX_TransHist_AccountID_Date),TABLOCKX)
--EXEC @sql
--update Running Balance based on whether it needs to include the Beginning Balance
--or not (if account, sub_account or acct_unit changes) ddr 9/10/08
UPDATE #nisse
SET @PrevRunBal2 = RunBal = Case
WHEN Account = @PrevAcct2
and acct_unit = @PrevAcctUnit2
and Sub_Account = @PrevSubAcct2
THEN @PrevRunBal2
+ debits + credits
ELSE BegBal + debits + credits
END,
@PrevAcct2 = account,
@PrevSubAcct2 = sub_account,
@PrevAcctUnit2 = acct_unit
FROM #nisse
SELECT account,acct_unit, sub_account, posting_date, Update_date,
startdate, enddate,
company, SystemCode, JE,
TransDescr, acct_unitname,debits, credits, BegBal, RunBal,
reference, convert(nvarchar(8),Account) as AcctSort
FROM #nisse
ORDER BY Company
, account
,acct_unit
,sub_account
,posting_date
END
November 12, 2008 at 9:58 am
Suggestion:
Try different connection methods: ODBC, OLE DB, SQL Native Client...
Also, try changing data type to char then convert back to date or datetime as needed in the report.
-Rob
November 12, 2008 at 10:16 am
Thanks for all the suggestions. I changed it to a varchar in Crystal and just convert it to a datetime in the proc. It works although there's some string manipulation in the Crystal formulas but not a big deal. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply