Returning a value for several different dates -- single query

Wednesday, January 02, 2013 11:28 PM

0
Hi Ladies & Gents,

T-SQL Question here for ya ;)

I'm working with Reporting Services and I've come across a problem that must be handled in the TSQL.

I have a large table that contains a PERIOD_END_DATE field and a NET BALANCE field, among many others. What I'm trying to get is a query which will return several fields from this table: 'Starting Balance', 'Change this Period', 'Ending Balance'.

The 'Starting Balance' will always equal the prior periods ending balance.

The 'Change this Period' will equal the current periods ending balance - the prior periods ending balance

The 'Ending Balance' will be...just the current periods ending balance.

PERIOD_END_DATE       CREDIT_SUM       DEBIT_SUM        NET_BALANCE
6/30/2005 0:00                 -100                       100                        0
5/31/2005 0:00                 -100                       100                        0
9/30/2005 0:00                 -100                       100                        0
8/31/2002 0:00                 -100                       100                        0

I think I should be incorporating something into the query like:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) -- 'First Day of Current Month

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) --'Last Day of the Current Month

Really the GETDATE() will be replaced by a @Paramter the user will select before they run the report.

Any idea what this query would look like? I've been thinking about it all afternoon and SQL isn't my foray. :-( There can be multiple of the same PERIOD_END_DATES but for different departments/divisions/ledger types.

Craig
- Edited by determinism Wednesday, January 02, 2013 11:32 PM
- Reply
- Quote

All Replies

Thursday, January 03, 2013 2:33 AM

Moderator

1
Your query will depend on your SQL Server version. In SQL Server 2012 the solution is simpler - check LEAD and LAG functions in BOL. For SQL 2008 and 2005 you will need to use CROSS APPLY to get the previous balance.

If you post your SQL Server version, DDL of your table, insert statements and the desired result, I can then write the query for you. Or just use the hints above to write that query yourself.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
- Edited by Naomi NMicrosoft Community Contributor, Moderator Thursday, January 03, 2013 2:33 AM
- Reply
- Quote

Thursday, January 03, 2013 10:17 PM

Thanks Naomi. I'm on my own here at this current engagement, I've gotten by in the 4-5 months by myself but this is really stumping me.

SQL Server Version: 2008 R2

Details:

Microsoft SQL Server Management Studio      10.50.2500.0
Microsoft Analysis Services Client Tools      10.50.2500.0
Microsoft Data Access Components (MDAC)      6.1.7600.16385
Microsoft MSXML      3.0 6.0
Microsoft Internet Explorer      8.0.7600.16385
Microsoft .NET Framework      2.0.50727.4984
Operating System      6.1.7600

DDL:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CHART_OF_ACCOUNTS_DETAIL](
	[ImportDate] [smalldatetime] NULL,
	[CHART_OF_ACCOUNTS_DETAIL_ID] [varchar](254) NULL,
	[ACCOUNT_NUMBER] [varchar](254) NULL,
	[COAD_ID] [varchar](254) NULL,
	[CREDIT_SUM] [numeric](13, 2) NULL,
	[DEBIT_SUM] [numeric](13, 2) NULL,
	[DEPARTMENT] [varchar](254) NULL,
	[DIVISION] [varchar](254) NULL,
	[DIVISION_CODE] [int] NULL,
	[GL_ACCT_NUMBER] [varchar](254) NULL,
	[JOURNAL_SOURCE_CODE] [varchar](254) NULL,
	[LEDGER_TYPE_CODE] [varchar](254) NULL,
	[MAJOR_ACCOUNT] [varchar](254) NULL,
	[NET_BALANCE] [numeric](13, 2) NULL,
	[NORMAL_BALANCE_FLAG] [varchar](254) NULL,
	[PERIOD_END_DATE] [smalldatetime] NULL,
	[POST_DATE] [smalldatetime] NULL,
	[YR] [varchar](254) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Insert Statements:

138k+ Records but here's a good spread. (Mock Data)

INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.55020.25*13118','20.55020.25','20.55020.25*13118','0.00','561.64','25','20','20','20.55020.25','AP','SE','55020','561.64','D','Nov 30 2003 12:00AM','Nov 30 2003 12:00AM','03')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.80800.20*16162','20.80800.20','20.80800.20*16162','0.00','4.55','20','20','20','20.80800.20','AP','OE','80800','4.55','D','Mar 31 2012 12:00AM','Mar 31 2012 12:00AM','12')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','30.60100.20*13270','30.60100.20','30.60100.20*13270','0.00','24392.97','20','30','30','30.60100.20','JE','CE','60100','24392.97','D','Apr 30 2004 12:00AM','Apr 30 2004 12:00AM','04')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','40.21303.00*14701','40.21303.00','40.21303.00*14701','-31018.50','0.00','00','40','40','40.21303.00','SA','CL','21303','-31018.50','C','Mar 31 2008 12:00AM','Mar 31 2008 12:00AM','08')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.49015.27*15584','20.49015.27','20.49015.27*15584','-335.18','0.00','27','20','20','20.49015.27','CL','IN','49015','-335.18','C','Aug 31 2010 12:00AM','Aug 31 2010 12:00AM','10')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.40000.10*13696','20.40000.10','20.40000.10*13696','-3538.10','1952.10','10','20','20','20.40000.10','SA','IN','40000','-1586.00','C','Jun 30 2005 12:00AM','Jun 30 2005 12:00AM','05')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.40000.24*13666','20.40000.24','20.40000.24*13666','-3378.30','2967.80','24','20','20','20.40000.24','SA','IN','40000','-410.50','C','May 31 2005 12:00AM','May 31 2005 12:00AM','05')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.21200.00*16376','20.21200.00','20.21200.00*16376','-7420.11','7654.11','00','20','20','20.21200.00','SA','CL','21200','234.00','C','Oct 31 2012 12:00AM','Oct 31 2012 12:00AM','12')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','30.91100.20*13362','30.91100.20','30.91100.20*13362','0.00','88.42','20','30','30','30.91100.20','CL','AE','91100','88.42','D','Jul 31 2004 12:00AM','Jul 31 2004 12:00AM','04')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.84300.50*13574','20.84300.50','20.84300.50*13574','0.00','393.34','50','20','20','20.84300.50','CL','OE','84300','393.34','D','Feb 28 2005 12:00AM','Feb 28 2005 12:00AM','05')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.66004.24*13911','20.66004.24','20.66004.24*13911','0.00','7584.97','24','20','20','20.66004.24','JE','CE','66004','7584.97','D','Jan 31 2006 12:00AM','Jan 31 2006 12:00AM','06')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.83401.27*13788','20.83401.27','20.83401.27*13788','0.00','14.20','27','20','20','20.83401.27','CL','OE','83401','14.20','D','Sep 30 2005 12:00AM','Sep 30 2005 12:00AM','05')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.21201.00*15461','20.21201.00','20.21201.00*15461','-462351.14','284786.92','00','20','20','20.21201.00','SA','CL','21201','-177564.22','C','Apr 30 2010 12:00AM','Apr 30 2010 12:00AM','10')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.60050.22*13180','20.60050.22','20.60050.22*13180','0.00','303.64','22','20','20','20.60050.22','JE','IN','60050','303.64','D','Jan 31 2004 12:00AM','Jan 31 2004 12:00AM','04')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.21534.00*12662','20.21534.00','20.21534.00*12662','-70.00','6322.40','00','20','20','20.21534.00','SA','CL','21534','6252.40','C','Aug 31 2002 12:00AM','Aug 31 2002 12:00AM','02')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.84100.10*15157','20.84100.10','20.84100.10*15157','0.00','70.44','10','20','20','20.84100.10','CL','OE','84100','70.44','D','Jun 30 2009 12:00AM','Jun 30 2009 12:00AM','09')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.49055.25*13027','20.49055.25','20.49055.25*13027','-86.94','0.00','25','20','20','20.49055.25','CL','IN','49055','-86.94','C','Aug 31 2003 12:00AM','Aug 31 2003 12:00AM','03')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.81300.24*13788','20.81300.24','20.81300.24*13788','-100.00','634.30','24','20','20','20.81300.24','AP','CE','81300','534.30','D','Sep 30 2005 12:00AM','Sep 30 2005 12:00AM','05')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.84100.24*15127','20.84100.24','20.84100.24*15127','0.00','104.33','24','20','20','20.84100.24','CL','OE','84100','104.33','D','May 31 2009 12:00AM','May 31 2009 12:00AM','09')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','40.53000.00*16131','40.53000.00','40.53000.00*16131','-5.05','5.05','00','40','40','40.53000.00','AP','SE','53000','0.00','D','Feb 29 2012 12:00AM','Feb 29 2012 12:00AM','12')
INSERT CHART_OF_ACCOUNTS_DETAIL(ImportDate,CHART_OF_ACCOUNTS_DETAIL_ID,ACCOUNT_NUMBER,COAD_ID,CREDIT_SUM,DEBIT_SUM,DEPARTMENT,DIVISION,DIVISION_CODE,GL_ACCT_NUMBER,JOURNAL_SOURCE_CODE,LEDGER_TYPE_CODE,MAJOR_ACCOUNT,NET_BALANCE,NORMAL_BALANCE_FLAG,PERIOD_END_DATE,POST_DATE,YR)       VALUES('Nov 28 2012 10:27AM','20.84500.23*15188','20.84500.23','20.84500.23*15188','0.00','2952.57','23','20','20','20.84500.23','AP','OE','84500','2952.57','D','Jul 31 2009 12:00AM','Jul 31 2009 12:00AM','09')

Here is my current query with what fields I am pulling currently, I had to bring in some descriptive fields from 2-3 other tables:

select
	CHART_OF_ACCOUNTS_DETAIL.MAJOR_ACCOUNT
, CHART_OF_ACCOUNTS_MASTER.[DESCRIPTION] as 'Acct Descrip' -- from CHART_OF_ACCOUNTS_MASTER
, LEDGER_TYPE_CODE
, LEDGER_TYPE_CODES.TYPE_DESCRIPTION -- from dbo.LEDGER_TYPE_CODES
, CHART_OF_ACCOUNTS_DETAIL.DEPARTMENT 'Department Number'
, CHART_OF_ACCOUNTS_DETAIL.DIVISION 'Division Number'
, CHART_OF_ACCOUNTS_DETAIL.PERIOD_END_DATE
, CHART_OF_ACCOUNTS_DETAIL.NET_BALANCE
, Departments.department as 'Department Name' --from dbo.Departments
from dbo.CHART_OF_ACCOUNTS_DETAIL FULL JOIN
dbo.Departments ON dbo.CHART_OF_ACCOUNTS_DETAIL.DEPARTMENT = dbo.Departments.deptno INNER JOIN
dbo.LEDGER_TYPE_CODES ON dbo.CHART_OF_ACCOUNTS_DETAIL.LEDGER_TYPE_CODE = dbo.LEDGER_TYPE_CODES.LEDGER_TYPE_CODES_ID INNER JOIN
dbo.CHART_OF_ACCOUNTS_MASTER ON dbo.CHART_OF_ACCOUNTS_DETAIL.ACCOUNT_NUMBER = dbo.CHART_OF_ACCOUNTS_MASTER.CHART_OF_ACCOUNTS_MASTER_ID

Edited by determinism Thursday, January 03, 2013 10:18 PM

Thursday, January 03, 2013 11:43 PM

Moderator

Check this code (assuming you want the balances for each department / division):

SELECT Det.MAJOR_ACCOUNT
	--, CHART_OF_ACCOUNTS_MASTER.[DESCRIPTION] as 'Acct Descrip' -- from CHART_OF_ACCOUNTS_MASTER
	--, LEDGER_TYPE_CODE
	--, LEDGER_TYPE_CODES.TYPE_DESCRIPTION -- from dbo.LEDGER_TYPE_CODES
	,Det.DEPARTMENT 'Department Number'
	,Det.DIVISION 'Division Number'
	,Det.PERIOD_END_DATE
	,COALESCE(Prev.NET_BALANCE,0) AS [Starting Balance]
	,Det.NET_BALANCE - COALESCE(Prev.NET_BALANCE, 0) AS [Change this Period]
	,Det.NET_BALANCE AS [Ending Balance]
--, Departments.department as 'Department Name' --from dbo.Departments
FROM dbo.CHART_OF_ACCOUNTS_DETAIL Det
OUTER APPLY (
	SELECT TOP (1) Net_Balance
	FROM dbo.CHART_OF_ACCOUNTS_DETAIL Det2
	WHERE Det2.PERIOD_END_DATE < Det.PERIOD_END_DATE
	AND Det2.DEPARTMENT = Det.DEPARTMENT
	AND Det2.DIVISION_CODE = Det.DIVISION_CODE
	ORDER BY PERIOD_END_DATE DESC
	) Prev
ORDER BY Det.PERIOD_END_DATE

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Friday, January 04, 2013 10:47 PM

Very cool work with the cross apply -- am I able to use a second cross apply to grab another three columns showing the same periods data but for last year? If so, I think I might be able to pull that off now that I see how you're doing it. Naomi for MVP.

One thing though... it looks like sometimes the prior periods ending balance equals the starting balance of the following period, but not always:

MAJOR_ACCOUNT	Department Number	Division Number	PERIOD_END_DATE	Starting Balance	Change this Period	Ending Balance
55020	0	10	4/30/2012 0:00	853.59	-98.74	754.85
55020	0	10	5/31/2012 0:00	754.85	-128.97	625.88
55020	0	10	6/30/2012 0:00	335.02	1128.58	1463.6
55020	0	10	7/31/2012 0:00	1289.53	-612.4	677.13
55020	0	10	8/31/2012 0:00	677.13	966.59	1643.72
55020	0	10	9/30/2012 0:00	1643.72	-3060.47	-1416.75
55020	0	10	10/31/2012 0:00	663.85	-295.09	368.76

Returning a value for several different dates -- single query

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?