Help ordering IN clause using passed order

I am trying to make the IN clause of a stored procedure return the rows in
the order in which the IN clause values were specified. What I have is a
table that can be sorted on a number of columns yet I want to pull back a
subset of rows. I have the set of rows needed but I am unable to return the
rows in the correct order using the IN clause.
For example:
SELECT
T.ID,
T.Name
FROM
MyTable
WHERE
T.ID IN ('1,3,2')
Notice that the ID order is 1,3,2. I want the rows in that order without
having to use an Order By on the correct column. That would require that I
a) use dynamic SQL just to use the correct order by column or b) provide the
same query a bunch of times just changing the sorting. I would prefer to not
do either.
Is this possible in SQL Server?One option is to use a CASE expression like:
ORDER BY CASE id WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
END ;
For a general option, use CHARINDEX or PATINDEX function like:
ORDER BY CHARINDEX( ',' + @.list + ',', ',' + id + ',' ) ;
Anith|||Tim Menninger wrote:
> I am trying to make the IN clause of a stored procedure return the rows in
> the order in which the IN clause values were specified. What I have is a
> table that can be sorted on a number of columns yet I want to pull back a
> subset of rows. I have the set of rows needed but I am unable to return th
e
> rows in the correct order using the IN clause.
> For example:
> SELECT
> T.ID,
> T.Name
> FROM
> MyTable
> WHERE
> T.ID IN ('1,3,2')
> Notice that the ID order is 1,3,2. I want the rows in that order without
> having to use an Order By on the correct column. That would require that I
> a) use dynamic SQL just to use the correct order by column or b) provide t
he
> same query a bunch of times just changing the sorting. I would prefer to n
ot
> do either.
> Is this possible in SQL Server?
You should know that you cannot reliably order any query without using
ORDER BY. Try:
DECLARE @.in VARCHAR(100)
SET @.in = '1,3,2'
SELECT T.id, T.name
FROM MyTable
WHERE CHARINDEX(','+CAST(id AS VARCHAR)+',',','+@.in+',')>0
ORDER BY CHARINDEX(','+CAST(id AS VARCHAR)+',',','+@.in+',');
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I'd try to modify Erland Sommarskog's UDF iter_charlist_to_table that
parses a comma-separated string, found at
http://www.sommarskog.se/arrays-in-sql.html
CREATE FUNCTION iter_charlist_to_int_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
value int,
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos,
@.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (value, nstr) VALUES(cast(@.tmpval as int),
@.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(value, nstr) VALUES (cast(ltrim(rtrim(@.leftover)) as
int), ltrim(rtrim(@.leftover)))
RETURN
END
create table #t(i int)
insert into #t values(1)
insert into #t values(2)
insert into #t values(3)
insert into #t values(4)
insert into #t values(5)
select #t.i from #t, dbo.iter_charlist_to_int_table('1,3,2', ',') t
where #t.i=t.value
order by t.listpos
i
--
1
3
2
(3 row(s) affected)|||Your IN clause has only one member. You are confusing IN (1,3,2) and IN
('1,3,2'). You probably need to use dynamic SQL anyway to get your query
working the way you want it.
For example, with this:
create table fred
(
ID varchar(2),
Name varchar(100)
)
go
insert into fred(ID,Name)
select '1','Jim' union
select '2','Tom' union
select '3','Appleby'
select id,name from fred where id in ('1,2')
The select doesn't return anything. If ID were an int, you would get a
syntax error in the select statement
"Tim Menninger" <tmenninger@.comcast.net> wrote in message
news:e77bacLMGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am trying to make the IN clause of a stored procedure return the rows in
>the order in which the IN clause values were specified. What I have is a
>table that can be sorted on a number of columns yet I want to pull back a
>subset of rows. I have the set of rows needed but I am unable to return the
>rows in the correct order using the IN clause.
> For example:
> SELECT
> T.ID,
> T.Name
> FROM
> MyTable
> WHERE
> T.ID IN ('1,3,2')
> Notice that the ID order is 1,3,2. I want the rows in that order without
> having to use an Order By on the correct column. That would require that I
> a) use dynamic SQL just to use the correct order by column or b) provide
> the same query a bunch of times just changing the sorting. I would prefer
> to not do either.
> Is this possible in SQL Server?
>|||Someone was asleep in RDBMS 101 class! What is the definition of a
table? It models a set of rows. By definition a set has no ordering.
This is what you should have learned the first w in class.
Do this in the front end, where all formatting and presentation is done
in a tiered architecture (w #2) or with an ORDER BY clause to
convert from a tale to a cursor.sql

Help on elapse time to return data ??

Dear all,
I have build an ASP.net application which calls different store procedure.
When my customer request data from store procedure, I would like to display
on the page, the time it takes to return data.
How to do that ?
regards
sergeSomething like that
create proc myproc1
as
--here is your body's code
--usage
declare @.dt datetime
set @.dt=getdate()
exec myproc1
select datediff(s,@.dt,getdate())
drop proc myproc1
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
> Dear all,
> I have build an ASP.net application which calls different store procedure.
> When my customer request data from store procedure, I would like to
> display
> on the page, the time it takes to return data.
> How to do that ?
> regards
> serge|||To add to Uri's response, you can also calculate the elapsed time in your
application. C# example:
DateTime startTime = DateTime.Now;
//execute query
TimeSpan duration = DateTime.Now.Subtract(startTime);
Response.Write(duration.ToString();
Hope this helps.
Dan Guzman
SQL Server MVP
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
> Dear all,
> I have build an ASP.net application which calls different store procedure.
> When my customer request data from store procedure, I would like to
> display
> on the page, the time it takes to return data.
> How to do that ?
> regards
> serge|||Thnaks dan, I will do that
regards
serge
"Dan Guzman" wrote:

> To add to Uri's response, you can also calculate the elapsed time in your
> application. C# example:
> DateTime startTime = DateTime.Now;
> //execute query
> TimeSpan duration = DateTime.Now.Subtract(startTime);
> Response.Write(duration.ToString();
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:0A3D7622-0901-420D-A5F1-CCF7127BE43C@.microsoft.com...
>
>

Help needed for Transaction Support in SQL server 2005

Hi,

I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.

my data layer methods somewhat looks like

public void Save(order value)
{
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required))
{
int orderId = SaveOrderMaster(value);
value.OrderId = orderid;
int childId = SaveOrderDetails(value);
//complete the transaction
transactionScope.Complete();
}
}

here
1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.

2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".

My Problem:
Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.

The problem may occur if your code calls the second procedure before the first procedure is done (or before the row has been added to the parent table). This can happen, because in your code you have not ensured the second procedure to call after the first procedure is completed. So either you can implement this logic in your code, or you can make use of trigger functionality in SQL. For example you can use an INSERT trigger on the parent table to automatically do the same thing as your second procedure did:

CREATE TRIGGER trg_InsOrdDetails ON Orders FOR INSERT
AS
--do your insert here, you can also call the second procedure

go

For more information about SQL trigger, you can refer to this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||Jay

But those calls are from Businss Layer Dll which written in .NET. So when SaveOrderMaster(value) call returns it should update the record in database. because SaveOrderMaster(value) calls one stored procedure that returns the id.

so call is flowing in the following order:

UI calls BLL.Save(value) -->
BLL calls DAL.SaveOrderMaster(value) -->
DAL calls Stored Procedure and set the newly created id in value object.
BLL Calls DAL.SaveOrderDetails(values) -->
DAL calls stored procedure with the details and Id (created in SaveOrderMaster call).
and I am getting the foreignKey error while making call to SaveOrderDetails().

But I am not able to find the source of error. as this is not repeatative behaviour, some time it ouccurs after 5000 BLL.Save() calls.

I also cant use triggers here, as order detail data is huge. and i dont want to call my pass so many data to stored procedure in one go. (aka. business and design requirement -:) )

any help in this regard is highly appriciated?|||

When you have the error, what's the orderId in the parent table, and does it look like what you'd expect? Also, are you using identity(), or Scope_identity to get the key? If you're not using scope_identity(), then that could be your problem.

|||I am using the Scope_identity to retrive the value for the Order Table identity column.
and if i remove the Transaction, My SaveOrder method save the Master table entry while some time I am getting error while saving order details.|||

The TransactionScope class performs none atomic transactions which is not legal so pass your code to T-SQL transaction block and your problem will go away. Hope this helps.

http://www.codeproject.com/database/sqlservertransactions.asp

http://msdn2.microsoft.com/en-us/library/ms190295.aspx

help needed for 2 tables

hi everyone, i've added 2 tables one is on adding, the other one is on returning. but when i return, the dropdownlist keeps displaying those which are returned already. it shouldnt display because i've already returned.

here are the codes that i add,

"INSERT INTO record VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + toName + "', '" + toRemarks + "', '" + purpose + "', '" + timeEntered + "', '" + dateEntered + "', 'Out')"

here are the codes that i return

"INSERT INTO returnRecord VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + timeEntered + "', 'In')"

here are the codes that i wanna display in the dropdownlist which i have set the status = IN. means they have returned already so it should display.

this is wad i tried what its wrong -->

"select a.manifestNo from record a, returnRecord r where a.status = 'Out' And r.status = 'In'"

my dropdownlist cant retrieve those records that are already returned. i suspect is the select statement that is wrong. but i cant figure out how. please help me!

regards, ethan

Instead of using inline SQL you really should use stored procedures, if you rewrite these as stored procedures you will be able to test them more effectively in sql management studio. Are you trying to sleect manifestNo from both tables, i think the error lies around returnecord r - you are specifiying the table but the select.

Help Needed - ISNULL when the field is already null..

Hello,
I just noticed today, after a long time, that if you do a
SELECT * FROM Table WHERE Field = Field
And that Field is null, it will return NOTHING at all...
Problem is, in my search stored procedures, I always use:
SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
So if the value is null, it simply avoid the check.
But now I'm screwed, for the first time I have to do this on fields that CAN
be null... and well, it returns NOTHING.
of course, WHERE Field IS NULL works like a charm, but I need this to be
working so if it's null, it skips the filter, if not, it does it.
Any help would be appreciated.Forgot to add...
the algorithm should be...
If null then don't check at all, return null and not null
but if not null, then must be it and only it...
I'm saying this, because I tried ISNULL(Field, 'foo') = ISNULL(@.Field,
'foo') and that, well, will only return rows with null values :-(
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> a crit dans le
message de news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Try,
...
where colA = @.par1 or @.par1 is null
...
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Nicolas LeBlanc" wrote:

> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>
>|||Read this article, which contains lots of info about doing this and similar
operations:
www.sommarskog.se/dyn-search.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||How about :
CREATE TABLE [test] (
[col1] [int] IDENTITY (1, 1) NOT NULL ,
[col2] [datetime] NULL
) ON [PRIMARY]
GO
col1 col2
-- ---
1 2005-02-17 22:15:22.263
2 2005-02-15 22:15:24.920
3 2005-02-14 22:16:23.857
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
declare @.d datetime
set @.d = null -- try this one or the next
set @.d = '2005-02-17 22:15:22.263'
SELECT *
FROM test
WHERE
(col2 = @.d)
UNION ALL
SELECT *
FROM test
WHERE
(@.d IS NULL and col2 IS NULL)
Check execution plans to see which ones are optimized the best:
David Gugick
Imceda Software
www.imceda.com|||Based on your needs, you must use one of the two following statements:
Where (Field = @.Field)
Where @.Field is Null OR (Field = @.Field)
Where (@.Field is Null and Field is Null) OR (Field = @.Field)
Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
There is also an option in SQL-Server that will make the equality (Null =
Null) return True instead of False but I don't suggest using it; otherwise
you will have a continuous moving target.
S. L.
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
> CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Personally -- if I can get away with it security-wise -- I would rather use
dynamic SQL to solve these kinds of issues...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uYbp28dFFHA.2052@.TK2MSFTNGP09.phx.gbl...
> How about :
> CREATE TABLE [test] (
> [col1] [int] IDENTITY (1, 1) NOT NULL ,
> [col2] [datetime] NULL
> ) ON [PRIMARY]
> GO
> col1 col2
> -- ---
> 1 2005-02-17 22:15:22.263
> 2 2005-02-15 22:15:24.920
> 3 2005-02-14 22:16:23.857
> 4 NULL
> 5 NULL
> 6 NULL
> 7 NULL
> 8 NULL
> declare @.d datetime
> set @.d = null -- try this one or the next
> set @.d = '2005-02-17 22:15:22.263'
> SELECT *
> FROM test
> WHERE
> (col2 = @.d)
> UNION ALL
> SELECT *
> FROM test
> WHERE
> (@.d IS NULL and col2 IS NULL)
>
>
> Check execution plans to see which ones are optimized the best:
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Seems about right. I wonder why I didn't think of that one before...
I sure won't go into dynamic sql, no way!
Merci !
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> a
crit dans le message de news:Ozl8y$dFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Based on your needs, you must use one of the two following statements:
> Where (Field = @.Field)
> Where @.Field is Null OR (Field = @.Field)
> Where (@.Field is Null and Field is Null) OR (Field = @.Field)
> Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
> There is also an option in SQL-Server that will make the equality (Null =
> Null) return True instead of False but I don't suggest using it; otherwise
> you will have a continuous moving target.
> S. L.
> "Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
> news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
>|||If there is only one predicate, then the simplest solution would be:
If @.Field IS NULL
SELECT ... FROM Table
Else
SELECT ... FROM Table WHERE Field = @.Field
Of course with more predicates this approach becomes unwieldly.
Gert-Jan
Nicolas LeBlanc wrote:
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.

Help me with this SELECT FROM OPENXML

The following script doesnt return any records. Why ?
----
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
----You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
>

Help me with this SELECT FROM OPENXML

The following script doesnt return any records. Why ?
declare @.t varchar(1000)
DECLARE @.XMLDocPointer1 INT
EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
NAME=''SAGAR'' /></EMP>'
select @.t
BEGIN TRANSACTION
select * FROM
OPENXML(@.XMLDocPointer1,'/EMP/EE')
WITH (EID VARCHAR(2), ENAME VARCHAR(30))
EXEC sp_xml_removedocument @.XMLDocPointer1
COMMIT
You must assign value to @.t variable before sp_xml_preparedocument
call.
On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:
> The following script doesnt return any records. Why ?
> ----
> declare @.t varchar(1000)
> DECLARE @.XMLDocPointer1 INT
> EXEC sp_xml_preparedocument @.XMLDocPointer1 OUTPUT, @.t
> set @.t = '<EMP><EE EID=''1'' NAME=''ANAND'' /><EE EID=''2''
> NAME=''SAGAR'' /></EMP>'
> select @.t
> BEGIN TRANSACTION
> select * FROM
> OPENXML(@.XMLDocPointer1,'/EMP/EE')
> WITH (EID VARCHAR(2), ENAME VARCHAR(30))
> EXEC sp_xml_removedocument @.XMLDocPointer1
> COMMIT
> ----
|||Hello gapokrif@.gmail.com,
Well spotted
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> You must assign value to @.t variable before sp_xml_preparedocument
> call.
> On Jul 16, 1:04 pm, Sagar <anandsa...@.gmail.com> wrote:

help me return the second highest date based on another field.

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!