datetime related error on some machines

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

datetime related error on some machines

Author

Topic

sharmasumit45
Starting Member

9 Posts

Posted - 2008-02-04 : 00:20:35

Hi All,

I need help regarding below sp.It works fine on some machines but on some it fails I think there may be the date related issues.Also can u help me in understanding this sp as I am not good at Sp

ALTER PROCEDURE spu_Report_Balance_Sheet
@branch_id int,
@period_end_date datetime
AS

DECLARE @dPeriodEndDateIn datetime,
@dNextMonth datetime,
@sYear char(4),
@sMonth char(2),
@dFirstDay datetime,
@dLastDay datetime,
@dPeriodEndDate datetime,
@d1stPeriodStartDate datetime,
@d1stPeriodEndDate datetime,
@dPrevPeriodEndDate datetime,
@sYearName varchar(20),
@iBranchID int,
@iCompanyID smallint,
@iSubBranchID integer,
@RootnodeID integer --

-- Empty temporary tables
SET NOCOUNT ON

TRUNCATE TABLE Report_Transaction -- Truncate is much faster than delete
TRUNCATE TABLE Report_TreePathNames

SELECT @iBranchID = ISNULL(@branch_id, 0)

SELECT @RootNodeID =
(
SELECT node_id
FROM structuretree
WHERE mapping_id IS NULL
AND account_id IS NULL
AND node_id <> 0
AND parent_node_id = 0
AND company_id = @ibranchid
)

SELECT @RootNodeID = ISNULL(@RootNodeid, 1)

-- EXECUTE spu_Report_BS_TreePathNames 1
EXECUTE spu_Report_BS_TreePathNames @rootnodeId

SELECT @dPeriodEndDateIn = ISNULL(@period_end_date, GETDATE())

EXEC spu_sub_branch_default @source_id=@iCompanyID, @sub_branch_id=@iSubBranchID OUTPUT

IF @iBranchID = 0 BEGIN
DECLARE cCompany CURSOR FAST_FORWARD FOR
SELECT company_id FROM Company
END ELSE BEGIN
DECLARE cCompany CURSOR FAST_FORWARD FOR
SELECT company_id FROM Company
WHERE company_id = @iBranchID
END

OPEN cCompany

-- Get the transactions for each company
FETCH NEXT FROM cCompany INTO @iCompanyID

WHILE @@FETCH_STATUS = 0 BEGIN
-- If Period end date is not found, default to end of input month
SELECT @dNextMonth = DATEADD(month, 1, @dPeriodEndDateIn)
SELECT @sYear = CONVERT(char(4), DATEPART(year, @dNextMonth))
SELECT @sMonth = CONVERT(char(2), DATEPART(month, @dNextMonth))
SELECT @dFirstDay = CONVERT(datetime, @sYear + '-' + @sMonth + '-01 23:59:59')
SELECT @dLastDay = DATEADD(day, -1, @dFirstDay)

SELECT @dPeriodEndDate = (SELECT ISNULL(MIN(period_end_date), @dLastDay)
FROM Period
WHERE company_id = @iCompanyID
AND period_end_date >= @dPeriodEndDateIn)

SELECT @sYearName = (SELECT ISNULL(year_name, '')
FROM Period
WHERE company_id = @iCompanyID
AND period_end_date = @dPeriodEndDate
AND sub_branch_id = @iSubBranchID)

-- If 1st Period End Date not found set it to the current period end date
SELECT @d1stPeriodEndDate = (SELECT ISNULL(MIN(period_end_date), @dPeriodEndDate)
FROM Period
WHERE company_id = @iCompanyID

AND year_name = @sYearName)

-- If previous period end date not found set it to end of previous month
SELECT @sYear = CONVERT(char(4), DATEPART(year, @dPeriodEndDate))
SELECT @sMonth = CONVERT(char(2), DATEPART(month, @dPeriodEndDate))

SELECT @dFirstDay = CONVERT(datetime, @sYear + '-' + @sMonth + '-01 23:59:59')
SELECT @dLastDay = DATEADD(day, -1, @dFirstDay)

SELECT @dPrevPeriodEndDate = (SELECT ISNULL(MAX(period_end_date), @dLastDay)
FROM Period
WHERE company_id = @iCompanyID
AND period_end_date < @d1stPeriodEndDate)

INSERT INTO Report_Transaction(
transdetail_id,
amount,
account_id,
account_name,
account_code,
extra_char1,
extra_datetime1,
document_date,
documenttype_id,
ledger_type,
account_type)
-- Balance Sheet details
SELECT TransDetail.transdetail_id,
round(TransDetail.amount,2),
Account.account_id,
Account.account_name,
Account.short_code,
Period.period_name,
Period.period_end_date,
Document.document_date,
Document.documenttype_id,
ISNULL(LedgerType.description, ''),
ISNULL(AccountType.description, '')
FROM Report_TreePathNames RTPN WITH (NOLOCK) -- No need to lock this table on select hard coded static table for report
JOIN Account Account
ON RTPN.account_id = Account.account_id
--1.6.9 AND Account.company_id = @iCompanyID
JOIN TransDetail TransDetail
ON Account.account_id = TransDetail.account_id
AND Transdetail.company_id = @iCompanyID -- 1.6.9
JOIN Period Period
ON TransDetail.period_id = Period.period_id
JOIN Document Document
ON TransDetail.document_id = Document.document_id
LEFT OUTER JOIN AccountType AccountType
ON Account.accounttype_id = AccountType.accounttype_id
LEFT OUTER JOIN Ledger Ledger
ON Account.ledger_id = Ledger.ledger_id
LEFT OUTER JOIN LedgerType LedgerType
ON Ledger.ledgertype_id = LedgerType.ledgertype_id
JOIN ElementExtras EE
ON EE.report_map_id = RTPN.report_map_id2
WHERE EE.element_id in (SELECT element_id from element where element_name in ('Balance Sheet','Profit And Loss'))
AND
(
(
Document.document_date <= @dPeriodEndDate
AND
TransDetail.spare NOT IN ('AGENT ADJ', 'COMM ADJ', 'BROK ADJ')
)
OR
(
TransDetail.ref_date <= @dPeriodEndDate
AND
TransDetail.spare IN ('AGENT ADJ', 'COMM ADJ', 'BROK ADJ')
)
)

INSERT INTO Report_Transaction(
transdetail_id,
amount,
account_id,
account_name,
account_code,
extra_char1,
extra_datetime1,
document_date,
documenttype_id,
ledger_type,
account_type)
SELECT 0,
0.0,
Account.account_id,
Account.account_name,
Account.short_code,
'',
@dPeriodEndDate,
null,
0,
ISNULL(LedgerType.description, ''),
ISNULL(AccountType.description, '')
FROM Report_TreePathNames RTPN WITH (NOLOCK) -- no need to lock this table
JOIN Account Account
ON RTPN.account_id = Account.account_id
--1.6.9 AND Account.company_id = @iCompanyID
LEFT OUTER JOIN Ledger Ledger
ON Account.ledger_id = Ledger.ledger_id
LEFT OUTER JOIN LedgerType LedgerType
ON Ledger.ledgertype_id = LedgerType.ledgertype_id
LEFT OUTER JOIN AccountType AccountType
ON Account.accounttype_id = AccountType.accounttype_id
WHERE RTPN.account_id NOT IN
(SELECT account_id
FROM Report_Transaction WITH (NOLOCK)) -- no need to lock this table

FETCH NEXT FROM cCompany INTO @iCompanyID

END

CLOSE cCompany
DEALLOCATE cCompany

-- Extract data
SET NOCOUNT OFF

-- Balance Sheet items
SELECT RTPN.element_name1,
RTPN.Report_Map_Id1,
RTPN.element_name2,
RTPN.Report_Map_Id2,
RTPN.element_name3,
RTPN.Report_Map_Id3,
RTPN.element_name4,
RTPN.Report_Map_Id4,
RTPN.element_name5,
RTPN.Report_Map_Id5,
RTPN.element_name6,
RTPN.Report_Map_Id6,
RTPN.element_name7,
RTPN.Report_Map_Id7,
RTPN.element_name8,
RTPN.Report_Map_Id8,
RTPN.element_name9,
RTPN.Report_Map_Id9,
RTPN.element_name10,
RTPN.Report_Map_Id10,
round(RT.Amount,2),
RT.account_name,
RT.account_code short_code,
RT.extra_char1 period_name,
RT.extra_datetime1 period_end_date,
RT.document_date,
RT.documenttype_id,
RT.transdetail_id,
RT.ledger_type,
RT.account_type
FROM Report_TreePathNames RTPN WITH (NOLOCK) -- no need to lock this table
JOIN Report_Transaction RT WITH (NOLOCK)
ON RTPN.account_id = RT.account_id
JOIN ElementExtras EE
ON EE.report_map_id = RTPN.report_map_id2
WHERE
EE.element_id IN (SELECT element_id FROM element WHERE element_name in ('Balance Sheet','Profit And Loss'))
ORDER BY 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 23

-- Empty temporary tables
SET NOCOUNT ON

TRUNCATE TABLE Report_Transaction --- Truncate is much faster than delete
TRUNCATE TABLE Report_TreePathNames

SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources