Forgive me, i'm still very new and learning everyday. I'm writing a select statement in visual studio 05 under ms sql 2005. i have 4 fields in my result set:
transactions.accountnumber
max(statements.todate) AS LastStatementDate
transactions.postingdate
DATEDIFF(d,max(statements.lastdate),transactions.postingdate) AS Aging
From the DATEDIFF line, you can tell i'm trying to count how many days have past from when a statement goes out (statements.lastdate) to when a transaction was posted (transactions.postingdate)
My problem occurs when i get a negative days due to the fact that a transaction was placed before a statement goes out. In this case, what i want to do is to return the second latest statement date that is older than the posting date. This will give me a positive days count and not a negative.
For example, if the last statement date was 4/25/2007 and the posted date was 4/4/2007, i'll get a -21 for the aging days. I need to pull the latest statement that is dated before 4/4/2007 so i get a positive days count. What is the syntax to do this automatically?
Which function do i use? I've played with row_number, rank, case, and a few others. Any advice is appreciated.
Code Snippet
SELECT
transactions.accountnumber
max(statements.todate) AS LastStatementDate
transactions.postingdate
DATEDIFF(d,max(statements.lastdate),transactions.postingdate) AS Aging
FROM myTbl
WHERE DATEDIFF(d,max(statements.lastdate),transactions.postingdate) > -1 --or 0 if it has to be atleast 1 day old
GROUP BY transactions.accountnumber, transactions.postingdate
{just guessing on parts of that since I don't know your actual schema et al.}
Depending on the size your tables, could be a potential performance issue regarding index usage given that where clause.
HTH
|||It would be so much easier to help if you would post the table DDL and some sample data in the form of INSERT statements|||
dvan,
When posting this kind of question / probem, it is very helpful posting DDL, including constraints and indexes, sample data and expected results. The help should be in both ways.
- How are those tables ([statements] and [transactions]) related?
Try:
Code Snippet
select
t.accountnumber,
t.transaction_number,
t.postingdate,
max(s.todate) as LastStatementDate
datediff(day, max(s.lastdate), t.postingdate) as aging
from
dbo.transactions as t
left join
dbo.statements as s
on t.accountnumber = s.accountnumber
and s.lastdate = (
select max(s1.lastdate)
from statements as s1
where s1.accountnumber = t.accountnumber and s1.lastdate < t.postingdate
)
group by
t.accountnumber,
t.transaction_number,
t.postingdate,
go
AMB
|||
I'm still very new to SQL, so all I'm writing are only SELECT statements. Below is the actual select statement that I have written so far. My superiors are asking for the query to show total aging days from the most current date a statement was sent out (LastStatement) to the date a transaction was posted (ActualPostingDate), which yields a positive number. Those are working beautifully.
However, if a transaction was posted prior to the date the statement was sent out, it yields a negative. If it is a negative, we'd like to see the date of a statement that is most prior to the posting date.
So, let's say there are 3 dates of statements: 4/25/2007, 3/28/2007, & 4/15/2007. The posting date we're looking at is on the 4/19/2007.
My query will show a result set of: LastStatement = 4/25/2007 and Aging = -6
I'd like it to know how to look for the difference and show: LastStatement = 4/15/2007 and Aging = 3
Hope this makes sense. I think I'm looking for an IF...ELSE or CASE or something along those lines so that it works for all scenarios. Here's my query so far... sorry about the lack of info, I'm still learning.
Code Snippet
SELECT
CreditTransactions.AccountNumber,
MAX(StatementsHistory.ToDate) AS LastStatement,
CreditTransactions.ActualPostingDate,
DATEDIFF(d, MAX(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging
FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber
WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM')
GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate
|||
See if this gives you what you need:
Code Snippet
;with sh as (
SELECT AccountNumber, MAX(StatementsHistory.ToDate) AS LastStatement
FROM StatementsHistory
GROUP BY AccountNumber
), agingData as
(
SELECT
ct.AccountNumber,
sh.LastStatement,
ct.ActualPostingDate,
DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging
FROM CreditTransactions ct INNER JOIN sh
ON ct.AccountNumber = sh.AccountNumber
WHERE
(ct.TransactionType = 'T58') AND
(ct.Reversed = 'False') AND
(ct.ActualPostingDate >= '4/1/2007') AND
(ct.ActualPostingDate <= '4/30/2007 11:59:59 PM')
)
SELECT AccountNumber, LastStatement, ActualPostingDate, Aging
FROM agingData
WHERE Aging > -1
GROUP BY
AccountNumber,
ActualPostingDate
|||
I appreciate your time and effort greatly in writing your code for me DaleJ (and everyone else that's responded!), however, what you wrote basically avoids all the negative days results.
What I truly need it to do is when it sees the negative day, it will look into the other statements dates and pick the next lower date to compare it against the posting date so that after the DATEDIFF calculation, the result set has positive days and not negative.
I ran your code against an account that has a negative result, and your code returns no results due to the "WHERE Aging > -1" line.
I augmented your code a little bit cause I was getting errors and to make it easier for me to read/understand, here's what it looks like so far... i put '-30' in the "WHERE Aging >" line toward the end to show the negative results set for the account number i listed, otherwise at the orginal '-1', it would not produce a result set at all.
Code Snippet
;with sh as
(
SELECT AccountNumber, MAX(StatementsHistory.ToDate) AS LastStatement
FROM StatementsHistory
GROUP BY AccountNumber
),
AgingData as
(
SELECT
CreditTransactions.AccountNumber,
sh.LastStatement,
CreditTransactions.ActualPostingDate,
DATEDIFF(d, sh.LastStatement, CreditTransactions.ActualPostingDate) AS Aging
FROM CreditTransactions INNER JOIN sh
ON CreditTransactions.AccountNumber = sh.AccountNumber
WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:59 PM')
)
SELECT AccountNumber, LastStatement, ActualPostingDate, Aging
FROM AgingData
WHERE Aging > -30 and AccountNumber = 110774
GROUP BY AccountNumber,ActualPostingDate, LastStatement, Aging
The result set for the query above is: (example A)
AccountNumber LastStatement ActualPostingDate Aging
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:18:46.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:19:03.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:19:38.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:19:49.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:00.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:11.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:20.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:31.000 -21
0000110774 2007-04-25 00:00:00.000 2007-04-04 13:20:42.000 -21
There's 9 records because there's 9 dates of service that isn't showing, i believe a DISTINCT would change this to 1 record. Either way, the aging is still negative. If we would just query the statement dates and ordered by DESC for this account, we'd get the following:
The result set is: (example B)
2007-04-25 00:00:00.000
2007-03-23 00:00:00.000
2007-02-20 00:00:00.000
2007-01-19 00:00:00.000
2006-12-20 00:00:00.000
2006-11-17 00:00:00.000
2006-10-17 00:00:00.000
I would like the result set to not show 2007-04-25 00:00:00.000 because that date causes the DATEDIFF to create a negative. I would like the query (if the aging is a negative) to go and find the second date of 2007-03-23 00:00:00.000 and plug this into the result causing DATEDIFF to create a positive. In conclusion, i'd like the result set to look like this instead of example A above:
The result set IF IT WORKED LIKE I WANTED is: (example C)
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:18:46.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:03.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:38.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:49.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:00.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:11.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:20.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:31.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:42.000 12
Is this possible to do? I'd like it to this for all accounts should this scenario come up. I hope this clears things up a bit, I'm very new to SQL and am evolving everyday with it. Your time and help is very, very much appreciated.
|||
Hey dvang
See if this is closer:
Code Snippet
;with ct as
(
SELECT distinct c.AccountNumber,
convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate
FROM CreditTransactions c
WHERE
(c.TransactionType = 'T58') AND
(c.Reversed = 'False') AND
(c.ActualPostingDate >= '4/1/2007') AND
(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')
), sh as
(
SELECT s.AccountNumber, MAX(s.ToDate) AS LastStatement
FROM StatementsHistory s
INNER JOIN ct
ON s.AccountNumber = ct.AccountNumber
AND s.ToDate <= ct.ActualPostingDate
GROUP BY s.AccountNumber
)
SELECT ct.AccountNumber, sh.LastStatement, ct.ActualPostingDate,
DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging
FROM ct
INNER JOIN sh
ON ct.AccountNumber = sh.AccountNumber
|||
Hey DaleJ, thanks much! The secret was in the INNER JOIN syntax. Your new code was very deep and extensive, and I greatly appreciate the work you put into writing it for me. It taught me more than I was ready to know or learn! The entire code itself was still producing some negatives because in a few scenarios, we needed to look further back than just 1 statement date. For those instances we needed to choose the third highest statement date and not the second highest. Either way, you laid down a strong foundation for me to build on and I THANK YOU for that!
After reviewing, disecting, and testing your code piece by piece, I realized that all I had to do was incorporate the "AND s.ToDate <= ct.ActualPostingDate" logic into my joins and that alone would give me the proper result set I was seeking. That gave me the max date of statements that was less than the posting date, therefore yielding a positive aging days for every record. I've verified this against other live data and it's working out well so far. Please share any caveats if you know of any.
I never realized you could use operators such as "<", ">", "<>", etc. in the joins. I assumed we only used "=". Again, I'm extremely new to SQL, but am learning at an exponential rate.
I do have one request (and it goes out to all who read this), could someone point me to a webpage or site, or even explain to me how the abbreviations work. The code that DaleJ wrote for me has a lot of abbreviations. Below is a section of his code and I've underlined/bolded the abbreviations that doesn't make sense to me or where they're coming from. I'm kinda getting it, but I need to solidify my assumptions. It's confusing to me because the code works beautifully and I'm dumbfounded on why?!
Code Snippet
SELECT distinct c.AccountNumber,
convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate
FROM CreditTransactions c
WHERE
(c.TransactionType = 'T58') AND
(c.Reversed = 'False') AND
(c.ActualPostingDate >= '4/1/2007') AND
(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')
These abbreviations are throwing me off cause when I'm reading on msdn2.microsoft.com, their examples have lotsa those abbreviations and it would be such a great help in understanding the examples if I could follow along without wondering what they mean.
I started SQL on visual studio 2005, but seem to be migrating to the server management studio more and more. I've noticed some of the syntax cannot be understood by visual studio which results in some seriously funny-looking text parsing (I hope that makes sense).
|||
Hey dv
I'm not sure how you're still getting negative aging numbers.
That code should only be using any statement date that is on or before the posting date, which should produce 0 as the lowest aging number.
You could run in to situations where some of the credit transactions won't show up should they not have any statement date on or before their posting date.
The 'abbreviations' is an alias for referencing the table(s).
In the above snippet, the FROM CreditTransactions c tells the parser that any place it sees c. it should substitute CreditTransactions.
So, SELECT distinct c.AccountNumber, translates to SELECT distinct CreditTransactions.AccountNumber,
but the alias (abbreviation) requires less typing and makes it easier to read.
HTH
|||
I'm not sure where the negative is coming from either. In some scenarios, the negatives were being produced because there was no other statement dates prior to pick from, so the result set showed the only statement date there was for that account. The others however had prior dates that should have been displayed in the result set but did not? Let me try to break it down... we'll use account #16322 and your code, plus I added a WHERE line to specify the account number (in highlights).
the table ct coding produces this result set:
Code Snippet
SELECT distinct c.AccountNumber,
convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate
FROM CreditTransactions c
WHERE
(c.TransactionType = 'T58') AND
(c.Reversed = 'False') AND
(c.ActualPostingDate >= '4/1/2007') AND
(c.ActualPostingDate <= '4/30/2007 11:59:59 PM') AND
(c.AccountNumber = 16322)
AccountNumber | ActualPostingDate
0000016322 | 04/13/2007
0000016322 | 04/18/2007
0000016322 | 04/27/2007
the table sh coding produces this result set:
Code Snippet
;with ct as
(
SELECT distinct c.AccountNumber,
convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate
FROM CreditTransactions c
WHERE
(c.TransactionType = 'T58') AND
(c.Reversed = 'False') AND
(c.ActualPostingDate >= '4/1/2007') AND
(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')
)
SELECT s.AccountNumber, MAX(s.ToDate) AS LastStatement
FROM StatementsHistory s
INNER JOIN ct
ON s.AccountNumber = ct.AccountNumber
AND s.ToDate <= ct.ActualPostingDate
WHERE s.AccountNumber = 16322
GROUP BY s.AccountNumber
AccountNumber | LastStatementDate
0000016322 | 2007-04-25 00:00:00.000
so the actual select statement (or entire code) produces the final result set:
Code Snippet
;with ct as
(
SELECT distinct c.AccountNumber,
convert(varchar(10), c.ActualPostingDate, 101) as ActualPostingDate
FROM CreditTransactions c
WHERE
(c.TransactionType = 'T58') AND
(c.Reversed = 'False') AND
(c.ActualPostingDate >= '4/1/2007') AND
(c.ActualPostingDate <= '4/30/2007 11:59:59 PM')
), sh as
(
SELECT s.AccountNumber, MAX(s.ToDate) AS LastStatement
FROM StatementsHistory s
INNER JOIN ct
ON s.AccountNumber = ct.AccountNumber
AND s.ToDate <= ct.ActualPostingDate
GROUP BY s.AccountNumber
)
SELECT ct.AccountNumber, sh.LastStatement, ct.ActualPostingDate,
DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging
FROM ct
INNER JOIN sh
ON ct.AccountNumber = sh.AccountNumber
WHERE
(ct.AccountNumber = 16322)
AccountNumber | LastStatementDate | ActualPostingDate | Aging
0000016322 | 2007-04-25 00:00:00.000 | 4/13/2007 | -12
0000016322 | 2007-04-25 00:00:00.000 | 4/18/2007 | -7
0000016322 | 2007-04-25 00:00:00.000 | 4/27/2007 | 2
I hope I did this correctly each step as I broke it down. Am I not seeing it or does the final select statement not really checking to see that the statement date has to be lower than the posting date, hence, causing the negative aging in the first 2 records. The 3rd record has a positive.
The statment date for the first 2 records should have been 2007-03-26 00:00:00.000, which would generate positive aging days. I've tried to add "AND sh.LastStatement <= ct.ActualPostingDate" to the INNER JOIN line in the final select statement, but that produced a result set of just the 3rd record from above and ignoring the 1st and 2nd record?
I'm not trying to point out fault or error. My query is working fine from doing what I said in the above posts. I'm just trying to help break down the code so we can understand together why it didn't produce the result set we wanted from the beginning.
Also, THANK YOU VERY MUCH for explaining the abbreviations/aliases. That's makes perfect sense and will save me a lot of time in the future when writing codes.
|||
OK, good having some sampling of the data greatly helps 
The following should find the "on or before" statement date relative to each posting date.
Code Snippet
create table #StatementsHistory(AccountNumber int, ToDate datetime)
insert into #StatementsHistory values(0000110774, '2007-04-25')
insert into #StatementsHistory values(0000110774, '2007-03-23')
insert into #StatementsHistory values(0000110774, '2007-02-20')
insert into #StatementsHistory values(0000110774, '2007-01-19')
insert into #StatementsHistory values(0000110774, '2006-12-20')
insert into #StatementsHistory values(0000110774, '2006-11-17')
insert into #StatementsHistory values(0000110774, '2006-10-17')
create table #CreditTransactions(AccountNumber int, ActualPostingDate datetime,
TransactionType varchar(5), Reversed varchar(5))
insert into #CreditTransactions values(0000110774, '2007-04-04 13:18:46', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:19:03', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:19:38', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:19:49', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:00', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:11', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:20', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:31', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-04-04 13:20:42', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:18:46', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:19:03', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:19:38', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:19:49', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:00', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:11', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:20', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:31', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-03-14 13:20:42', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:18:46', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:19:03', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:19:38', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:19:49', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:00', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:11', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:20', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:31', 'T58', 'False')
insert into #CreditTransactions values(0000110774, '2007-02-15 13:20:42', 'T58', 'False')
;with ct as
(
SELECT distinct AccountNumber,
convert(varchar(10), ActualPostingDate, 101) as ActualPostingDate
FROM #CreditTransactions
WHERE
(TransactionType = 'T58') AND
(Reversed = 'False') AND
(ActualPostingDate >= '2/1/2007') AND
(ActualPostingDate <= '4/30/2007 11:59:59 PM')
), sh as
(
SELECT DISTINCT s.AccountNumber,
ct.ActualPostingDate,
max(s.ToDate) as LastStatement
from #StatementsHistory s
inner join ct
on s.AccountNumber = ct.AccountNumber
and s.ToDate <= ct.actualpostingdate
group by s.accountnumber, ct.ActualPostingDate
)
SELECT ct.AccountNumber,
ct.ActualPostingDate,
sh.LastStatement,
DATEDIFF(d, sh.LastStatement, ct.ActualPostingDate) AS Aging
FROM ct
INNER JOIN sh
on ct.AccountNumber = sh.AccountNumber
and ct.ActualPostingDate = sh.ActualPostingDate
|||hi dvang,
this is from your last post, actually you're in the right track here you just need to get the top 2 dates from the statementhistory and get the mininum from those two..
Code Snippet
--this is from you previous post
SELECT
CreditTransactions.AccountNumber,
MAX(StatementsHistory.ToDate) AS LastStatement,
CreditTransactions.ActualPostingDate,
DATEDIFF(d, MAX(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging
FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber
WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM')
GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate
-- the solution would then be
SELECT
CreditTransactions.AccountNumber,
MIN(StatementsHistory.ToDate) AS LastStatement, -- you should use min to get the 2nd highest
CreditTransactions.ActualPostingDate,
DATEDIFF(d, MIN(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging
FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber
WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM') AND
(StatementsHistory.ToDate IN (
SELECT TOP 2
a.ToDate
FROM StatementsHistory a
WHERE a.AccountNumber = StatementsHistory.AccountNumber
ORDER BY
a.ToDate DESC
)) -- this will get the top 2 dates for each account
GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate
ORDER BY
LastStatement
this would result to..
 dvang wrote: |
| |
| The result set IF IT WORKED LIKE I WANTED is: (example C) 0000110774 2007-03-23 00:00:00.000 2007-04-04 13:18:46.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:03.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:38.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:19:49.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:00.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:11.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:20.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:31.000 12
0000110774 2007-03-23 00:00:00.000 2007-04-04 13:20:42.000 12 |
|||oops sorry, i found a bug on my prev post when not filtering the actualpostingdate
here's another alternative, i'm not sure if this would fit your requirement
SELECT DISTINCT
CreditTransactions.AccountNumber,
MAX(StatementsHistory.ToDate) AS LastStatement,
CONVERT(varchar(10),CreditTransactions.ActualPostingDate,101) AS ActualPostingDate,
DATEDIFF(d, MAX(StatementsHistory.ToDate), CreditTransactions.ActualPostingDate) AS Aging
FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber
WHERE
(CreditTransactions.TransactionType = 'T58') AND
(CreditTransactions.Reversed = 'False') AND
--(CreditTransactions.ActualPostingDate >= '4/1/2007') AND
--(CreditTransactions.ActualPostingDate <= '4/30/2007 11:59:00 PM') AND
(StatementsHistory.ToDate <= CONVERT(varchar(10),CreditTransactions.ActualPostingDate,101))
GROUP BY
CreditTransactions.AccountNumber,
CreditTransactions.ActualPostingDate
ORDER BY
LastStatement|||
Forgive me for not replying sooner, I've been away on business. I just wanted to thank everyone that has contributed to helping me resolve this code. Everyone has been helpful, especially DaleJ.
It's nice to know I can always come back here when I run into SQL problems (which I will sooner or later) and have friendly people help out!
THANKS AGAIN!
end or holiday. But I like to get a solution
suigion: