July 17, 2015 at 8:49 am
I am somewhat new to SQL Server and am trying to add a customized query to a report I am building. I have to take the current Accounting Period and roll it back to an older accounting period. I have tried the query a few different ways but can not get it to work right. The issue is in the date manipulation in the where statement. Specifically, in the "where (ACCTPERIOD = (Left(ACCTPERIOD,4) -1) * 100 + 12)" area. As long as this is not used, data is returned. If you insert this statement in the query, it succeeds but does not return data. Below are two ways that I have been trying to get this to work. Does anyone have any tips or pointers they can give me for this issue?
Use DATA85
select GLAccum.Endbal, GLAccum.Base, GLAccum.Division, GLAccum.DeptNo, (Left(GLAccum.ACCTPERIOD,4) -1) * 100 + 12 "PriorAcctPeriod"
from GLAccum
INNER JOIN GLChart
On GLAccum.GLCHARTKEY=GLChart.GLCHARTKEY
where (ACCTPERIOD = (Left(ACCTPERIOD,4) -1) * 100 + 12) and TYPE='x' and GroupSequence = '3';
&
Use DATA85
select GLAccum.Endbal, GLAccum.Base, GLAccum.Division, GLAccum.DeptNo, (Left(GLAccum.ACCTPERIOD,4) -1) * 100 + 12 "PriorAcctPeriod"
from GLAccum
INNER JOIN GLChart
On GLAccum.GLCHARTKEY=GLChart.GLCHARTKEY
where ACCTPERIOD = Cast((Left(ACCTPERIOD,4) -1) * 100 + 12 as VARCHAR(10)) and TYPE='x' and GroupSequence = '3';
July 17, 2015 at 9:13 am
brandonarrigo (7/17/2015)
I am somewhat new to SQL Server and am trying to add a customized query to a report I am building. I have to take the current Accounting Period and roll it back to an older accounting period. I have tried the query a few different ways but can not get it to work right. The issue is in the date manipulation in the where statement. Specifically, in the "where (ACCTPERIOD = (Left(ACCTPERIOD,4) -1) * 100 + 12)" area. As long as this is not used, data is returned. If you insert this statement in the query, it succeeds but does not return data. Below are two ways that I have been trying to get this to work. Does anyone have any tips or pointers they can give me for this issue?Use DATA85
select GLAccum.Endbal, GLAccum.Base, GLAccum.Division, GLAccum.DeptNo, (Left(GLAccum.ACCTPERIOD,4) -1) * 100 + 12 "PriorAcctPeriod"
from GLAccum
INNER JOIN GLChart
On GLAccum.GLCHARTKEY=GLChart.GLCHARTKEY
where (ACCTPERIOD = (Left(ACCTPERIOD,4) -1) * 100 + 12) and TYPE='x' and GroupSequence = '3';
&
Use DATA85
select GLAccum.Endbal, GLAccum.Base, GLAccum.Division, GLAccum.DeptNo, (Left(GLAccum.ACCTPERIOD,4) -1) * 100 + 12 "PriorAcctPeriod"
from GLAccum
INNER JOIN GLChart
On GLAccum.GLCHARTKEY=GLChart.GLCHARTKEY
where ACCTPERIOD = Cast((Left(ACCTPERIOD,4) -1) * 100 + 12 as VARCHAR(10)) and TYPE='x' and GroupSequence = '3';
Based on the above, not really much we can do. It would help if you could post the DDL for the tables (CREATE TABLE statements), some sample data for the tables, and the expected results based on the sample data.
Need help with this, please read the first article I reference below in my signature block regarding asking for help.
July 17, 2015 at 9:19 am
Considering the ACCTPERIOD has a value of 16456874
Which would be the correct result?
164412 (first four digits minus one with a two digit suffix of 12)
16440012 (first four digits minus one with a four digit suffix of 0012)
1972800 (first four digits minus one multiplied by 1200)
184128 (first four digits minus one multiplied by 112)
July 17, 2015 at 9:35 am
GLAccum table:
CREATE TABLE [dbo].[GLAccum](
[LOCATION] [varchar](2) NOT NULL,
[GLACCUMKEY] [int] IDENTITY(1,1) NOT NULL,
[ACCTPERIOD] [decimal](7, 0) NULL,
[GLCHARTKEY] [int] NULL,
[BASE] [varchar](15) NULL,
[DIVISION] [varchar](2) NULL,
[DEPTNO] [varchar](5) NULL,
[BEGBAL] [decimal](13, 2) NULL,
[ENDBAL] [decimal](13, 2) NULL,
[ACTIVITYAMT] [decimal](13, 2) NULL,
[TOTALDEBITAMT] [decimal](13, 2) NULL,
[TOTALCREDITAMT] [decimal](13, 2) NULL,
[CRE_DATE] [datetime] NULL,
[CNG_DATE] [datetime] NULL,
[CNG_TIME] [int] NULL,
[CNG_BY] [varchar](5) NULL,
CONSTRAINT [GLACUM_PermKey] PRIMARY KEY CLUSTERED
GLChart:
CREATE TABLE [dbo].[GLChart](
[LOCATION] [varchar](2) NOT NULL,
[GLCHARTKEY] [int] IDENTITY(1,1) NOT NULL,
[BASE] [varchar](15) NULL,
[TYPE] [varchar](1) NULL,
[GroupKey] [int] NULL,
[GROUPSEQUENCE] [varchar](2) NULL,
[SumKey] [int] NULL,
[SUMCODE] [varchar](3) NULL,
[CATEGORY] [varchar](1) NULL,
[DESCRIPTION] [varchar](30) NULL,
[BALANCE] [decimal](13, 2) NULL,
[INTRACOFLAG] [tinyint] NULL,
[INTRACOOFFSET] [varchar](10) NULL,
[ALLOWSUBFLAG] [tinyint] NULL,
[INTERFACECODE] [varchar](20) NULL,
[CASHFLOWFACTOR] [varchar](1) NULL,
[STATUS] [varchar](1) NULL,
[CRE_DATE] [datetime] NULL,
[CNG_DATE] [datetime] NULL,
[CNG_TIME] [int] NULL,
[CNG_BY] [varchar](5) NULL,
CONSTRAINT [GLCHAR_PERMKEY] PRIMARY KEY CLUSTERED
What I am trying to do is read the AccountingPeriod in GLAccum and then manipulate it to the prior years last accounting period. For example, if the accountingperiod in GLAccum is 201507, I want to manipulate it to be 201412.
Let me put it this way:
If GLChar.Type = 'X' and GLChar.GroupSequence = '3'
Then show GLAccum.EndBal
from the date manipulated to show the prior years last accounting period.
Data Returned should look like this: 0.003799-091201201012
July 17, 2015 at 9:55 am
The problem is that you're comparing the same column and same row expecting it to have different values.
What you need is a self join. Something like this:
select GLAccum.Endbal, GLAccum.Base, GLAccum.Division, GLAccum.DeptNo, (Left(GLAccum.ACCTPERIOD,4) -1) * 100 + 12 "PriorAcctPeriod"
from GLAccum
INNER JOIN GLChart On GLAccum.GLCHARTKEY=GLChart.GLCHARTKEY
INNER JOIN GLAccum prev On GLAccum.ACCTPERIOD= (prev.ACCTPERIOD - (88 + (prev.ACCTPERIOD % 100))) -- 100 (previous year) - 12(last period) = 88 (This is added to the current period to substract the exact number needed without conversions)
where TYPE='x' and GroupSequence = '3';
I changed your formula, I hope that you get what I did.
July 17, 2015 at 10:17 am
I do understand what you are doing there. I did not realize that was a possibility though. I am going to run this through the mill and I will let you know what I find. Thanks so much for the help. We are migrating from an archaic database(Btrieve) that did not have that large of an online community. One thing I am liking most about SQL is just that, a large online community.
July 17, 2015 at 1:38 pm
That did work! Running the query in studio manager returned exactly what it was supposed to. Unfortunately, I now have an issue with Crystal Reports passing that query and returning the correct data. But that will be another issue entirely. This issue on the other hand looks to be right now. Thanks again for the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply