May 2, 2012 at 10:20 am
I have been asked to write a report listing the records where the balance on a client account is not zero, and where no money has been posted into or out of that clients account in the last year.
Entries into and out of the clients account are stored on the Ac_Client_Ledger_Transactions table. I think my query is selecting the most recent posting date where the posting date is more than one year old. But what I really want to do is select the most recent posting date where there have been no postings in the last year.
Can anyone advise the correct way of writing the WHERE clause so that only client details are returned where there have been no postings on the Ac_Client_Ledger_Transactions table in the last year?
Thanks
SELECT Matters.FeeEarnerRef, Entities.Code, Matters.Number, Entities.Name, Matters.Description, Matters.Created, Matters.Client_Ac_Balance, max(TimeTransactions.TransactionDate) as Last_Time_Posting_date, max(Ac_Client_Ledger_Transactions.PostedDate) as Last_Financial_Posting_date
FROM Ac_Client_Ledger_Transactions INNER JOIN (TimeTransactions INNER JOIN (Matters INNER JOIN Entities ON Matters.EntityRef = Entities.Code) ON (TimeTransactions.MatterNoRef = Matters.Number) AND (TimeTransactions.EntityRef = Matters.EntityRef)) ON (Ac_Client_Ledger_Transactions.Matter_No = Matters.Number) AND (Ac_Client_Ledger_Transactions.Client_Code = Matters.EntityRef)
WHERE (((Matters.Client_Ac_Balance)<>0) and (Ac_Client_Ledger_Transactions.PostedDate <='2011-04-20'))
GROUP BY matters.feeearnerref, entities.code, matters.number, entities.name, matters.description, matters.created, matters.client_ac_balance
May 2, 2012 at 10:36 am
Something like this?
(max(Ac_Client_Ledger_Transactions.PostedDate) <='2011-04-20')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 10:38 am
It would help if you could post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT INTO statements) for the table(s) involved (also, not real data, just something the will represent the problem domain), expected results based on the sample data.
May 2, 2012 at 1:27 pm
Conceptually you want to do something like
SELECT
*
FROM YourTable
WHERE YOUR ClientCode IS NOT IN
(
SELECT ClientCode FROM YourTable WHERE PostedDate > DATEADD(yy,-1,getdate())
)
The MAX statement should give similar results. I would venture a guess that it performs better.
May 2, 2012 at 1:31 pm
Try this:
SELECT Matters.FeeEarnerRef,
Entities.Code,
Matters.Number,
Entities.Name,
Matters.Description,
Matters.Created,
Matters.Client_Ac_Balance,
MAX(TimeTransactions.TransactionDate) AS Last_Time_Posting_date,
MAX(Ac_Client_Ledger_Transactions.PostedDate) AS Last_Financial_Posting_date
FROM Ac_Client_Ledger_Transactions
INNER JOIN (TimeTransactions
INNER JOIN (Matters
INNER JOIN Entities
ON Matters.EntityRef = Entities.Code)
ON (TimeTransactions.MatterNoRef = Matters.Number)
AND (TimeTransactions.EntityRef = Matters.EntityRef))
ON (Ac_Client_Ledger_Transactions.Matter_No = Matters.Number)
AND (Ac_Client_Ledger_Transactions.Client_Code = Matters.EntityRef)
WHERE Matters.Client_Ac_Balance <> 0
GROUP BY matters.feeearnerref,
entities.code,
matters.number,
entities.name,
matters.description,
matters.created,
matters.client_ac_balance
HAVING MAX(Ac_Client_Ledger_Transactions.PostedDate) <= '2011-04-20';
The "Having" clause allows you to do "Where clause" type stuff on aggregates, like Max(), Min(), Avg(), and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 1:38 pm
The "Having" clause allows you to do "Where clause" type stuff on aggregates, like Max(), Min(), Avg(), and so on.
sheesh in looking at my post I could have been a bit more forthcoming with information about how to do that. Thanks for stepping in to clarify Gus.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 1:40 pm
Sean Lange (5/2/2012)
The "Having" clause allows you to do "Where clause" type stuff on aggregates, like Max(), Min(), Avg(), and so on.
sheesh in looking at my post I could have been a bit more forthcoming with information about how to do that. Thanks for stepping in to clarify Gus.
Yes, clarifying Gus is important. 😀
I figured you were aiming towards a Having clause, but got excited and forgot to mention that detail.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 3, 2012 at 6:39 am
Thanks very much for your help - I'm fairly new to SQL so I didn't know there is such a thing as 'HAVING', and it does the job perfectly.
Thanks again
May 3, 2012 at 7:15 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply