Need help with SQL select

[RickyC1]

All,

Have an account balance table with fiscal year and period. Need to create a trail balance file from this table. All I have to do is add all debits then all credits for the account from the beginning of time to the specified month, there is the problem.

Cause the there is a (fiscal year) and (period) field I have to interrogate both on the same record so see if I want to include the data. How do I do this? With an inner select?

An example of what I have so far:
SELECT ACCOUNT_ID, sum(DEBIT_AMOUNT) as DebitSum,
sum(CREDIT_AMOUNT) as CreditSum, sum(DEBIT_AMOUNT) - sum(CREDIT_AMOUNT) as Result
From ACCOUNT_BALANCE
Where (ACCOUNT_ID = '253556891')
And (Acct_Year = '2006' And Acct_Period <= '4') And
Group by Account_id

 

[PHV]

WHERE ACCOUNT_ID = '253556891'
AND (Acct_Year < '2006' OR Acct_Period <= '4')
GROUP BY Account_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RickyC1]

Not sure how this is going to come over but here is the data I'm working with. As you can see there is a Yr and period field. I want all records except year Not 2006 and Not > Period 4. Everything except the first record. The user will have the ability to choose which peiod and year they want. I will have to exlude everything above that period if it exists. In this example it does and that is the first record in the data example

Acct# Yr Period Debit Credit
1000-01 2006 5 .00 81795.44
1000-01 2006 4 769034.63 728476.69
1000-01 2006 3 931966.77 931542.90
1000-01 2006 2 699973.91 741520.54
1000-01 2006 1 538303.38 540212.49
1000-01 2004 12 666876.70 670913.02
1000-01 2004 11 735706.24 730321.81
1000-01 2004 10 743586.01 711158.08
1000-01 2004 9 893528.47 816399.94
1000-01 2004 8 698851.04 805554.22
1000-01 2004 7 803661.48 736104.37
1000-01 2004 6 744332.87 735055.81
1000-01 2004 5 773636.23 803059.00
1000-01 2004 4 643444.48 607026.40
1000-01 2004 3 644240.73 641810.35
1000-01 2004 2 .00 47499.59
1000-01 2004 1 111025.90 .00
1000-01 2003 12 .00 190870.75

Here is what I'm working on but is not working. I can easly do this in the vb program which will call this sql but would like to do it within the sql, just think its more elegent programming.

SELECT ACCOUNT_ID, ACCT_YEAR, ACCT_PERIOD, DEBIT_AMOUNT, CREDIT_AMOUNT
From ACCOUNT_BALANCE
Where ((ACCOUNT_ID = '1000-0102')
And (Acct_Year = '2006' And Acct_Period ='4'))
Or ((ACCOUNT_ID = '1000-0102')
And (Acct_Year <= '2006' And Acct_Period !> '4'))
Order by acct_year desc, acct_period desc
--Group by Account_id

 

[RickyC1]

Do I need a subquery for this cause I need to interigate two fields on the same record? What is the general rule with two fields (or more) detemine if the record belongs in in the result set.

 

[PHV]

Have you even read my previous post ?
Another way:
SELECT ACCOUNT_ID, ACCT_YEAR, ACCT_PERIOD, DEBIT_AMOUNT, CREDIT_AMOUNT
From ACCOUNT_BALANCE
Where ACCOUNT_ID='1000-0102'
And ((Acct_Year='2006' And Acct_Period<='4') Or Acct_Year<'2006')
Order by acct_year desc, acct_period desc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RickyC1]

I glaced at it and it look like something I had already tried. It did the trick, thanks very much. Will keep testing to see if it works all variations of the data.