Help on using CASE together with UPDATE

Hi
I need some help on how to update some fields with a value based on the
value in another field. I have tried to do this with a CASE statement, but I
haven't really been able to get anywhere near something that works...
If I run the select statement -
"select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
a.zipcode, z.cityname, a.title from user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' ",
then it gives the records I want to update. What I then want to do, is to
update the field u.userdepartment with a value based on the value of the
field "title". Eg. when the field title has the value "IT" then I'd like to
set userdepartment = 2959028.
I'd think that I can use something like ...userdepartment = CASE title = 'IT' then 2959028... but apparently I need some guidedance on how to do
this.
Can any of you help with this?
Regards
Steenuntested code follows:
Does this select statement return what you are looking for?
SELECT user, title,
userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
If so, this might be the update statement that you are looking for:
UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
--
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Try Something on these lines:
UPDATE user
SET userdepartment = CASE title
WHEN 'IT' THEN 2959028
WHEN 'HR' THEN 2959029
ELSE NULL
END
From address a
INNER JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' and u.addressidentold = a.addressident
-- Note: code not tested ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Hi
Thanks to both of you - by "combining" your examples I got it working.
Keith - the second line of your example should be
...userdepartment = CASE title When 'IT' then 29... then it works...
It's always a joy to use this newsgroup - no matter what stupid and simple
question being asked, there're always a lot of helpfull answers to us less
"sql-skilled" people......
Thanks
Steen
.
Keith Kratochvil wrote:
> untested code follows:
> Does this select statement return what you are looking for?
> SELECT user, title,
> userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
> If so, this might be the update statement that you are looking for:
> UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I need some help on how to update some fields with a value based on
>> the value in another field. I have tried to do this with a CASE
>> statement, but I haven't really been able to get anywhere near
>> something that works...
>> If I run the select statement -
>> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
>> a.zipcode, z.cityname, a.title from user u
>> JOIN address a on u.addressidentold = a.addressident
>> JOIN Zipcode z ON a.zipcode=z.zipcode
>> where u.userinit='spe' ",
>> then it gives the records I want to update. What I then want to do,
>> is to update the field u.userdepartment with a value based on the
>> value of the field "title". Eg. when the field title has the value
>> "IT" then I'd like to set userdepartment = 2959028.
>> I'd think that I can use something like ...userdepartment = CASE
>> title = 'IT' then 2959028... but apparently I need some guidedance
>> on how to do this.
>> Can any of you help with this?
>> Regards
>> Steen|||There are two ways to do CASE. This is the other method. My revised
example should work correctly:
CASE WHEN title = 'IT' THEN 2959028
WHEN title = 'MARKETING' THEN 1
WHEN title = '...' THEN 2
ELSE NULL END
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Of9LDvGrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks to both of you - by "combining" your examples I got it working.
> Keith - the second line of your example should be
> ...userdepartment = CASE title When 'IT' then 29... then it works...
> It's always a joy to use this newsgroup - no matter what stupid and simple
> question being asked, there're always a lot of helpfull answers to us less
> "sql-skilled" people......
> Thanks
> Steen
> .
> Keith Kratochvil wrote:
> > untested code follows:
> >
> > Does this select statement return what you are looking for?
> >
> > SELECT user, title,
> > userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> > If so, this might be the update statement that you are looking for:
> > UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I need some help on how to update some fields with a value based on
> >> the value in another field. I have tried to do this with a CASE
> >> statement, but I haven't really been able to get anywhere near
> >> something that works...
> >>
> >> If I run the select statement -
> >>
> >> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> >> a.zipcode, z.cityname, a.title from user u
> >> JOIN address a on u.addressidentold = a.addressident
> >> JOIN Zipcode z ON a.zipcode=z.zipcode
> >> where u.userinit='spe' ",
> >>
> >> then it gives the records I want to update. What I then want to do,
> >> is to update the field u.userdepartment with a value based on the
> >> value of the field "title". Eg. when the field title has the value
> >> "IT" then I'd like to set userdepartment = 2959028.
> >> I'd think that I can use something like ...userdepartment = CASE
> >> title = 'IT' then 2959028... but apparently I need some guidedance
> >> on how to do this.
> >>
> >> Can any of you help with this?
> >>
> >> Regards
> >> Steen
>

Help on using CASE together with UPDATE

Hi
I need some help on how to update some fields with a value based on the
value in another field. I have tried to do this with a CASE statement, but I
haven't really been able to get anywhere near something that works...
If I run the select statement -
"select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
a.zipcode, z.cityname, a.title from user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' ",
then it gives the records I want to update. What I then want to do, is to
update the field u.userdepartment with a value based on the value of the
field "title". Eg. when the field title has the value "IT" then I'd like to
set userdepartment = 2959028.
I'd think that I can use something like ...userdepartment = CASE title =
'IT' then 2959028... but apparently I need some guidedance on how to do
this.
Can any of you help with this?
Regards
Steen
untested code follows:
Does this select statement return what you are looking for?
SELECT user, title,
userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
If so, this might be the update statement that you are looking for:
UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title =
> 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>
|||Try Something on these lines:
UPDATE user
SET userdepartment =
CASE title
WHEN 'IT' THEN 2959028
WHEN 'HR' THEN 2959029
ELSE NULL
END
From address a
INNER JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' and u.addressidentold = a.addressident
-- Note: code not tested ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title =
> 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>
|||Hi
Thanks to both of you - by "combining" your examples I got it working.
Keith - the second line of your example should be
....userdepartment = CASE title When 'IT' then 29... then it works...
It's always a joy to use this newsgroup - no matter what stupid and simple
question being asked, there're always a lot of helpfull answers to us less
"sql-skilled" people......
Thanks
Steen
..
Keith Kratochvil wrote:[vbcol=seagreen]
> untested code follows:
> Does this select statement return what you are looking for?
> SELECT user, title,
> userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
> If so, this might be the update statement that you are looking for:
> UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
|||There are two ways to do CASE. This is the other method. My revised
example should work correctly:
CASE WHEN title = 'IT' THEN 2959028
WHEN title = 'MARKETING' THEN 1
WHEN title = '...' THEN 2
ELSE NULL END
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Of9LDvGrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks to both of you - by "combining" your examples I got it working.
> Keith - the second line of your example should be
> ...userdepartment = CASE title When 'IT' then 29... then it works...
> It's always a joy to use this newsgroup - no matter what stupid and simple
> question being asked, there're always a lot of helpfull answers to us less
> "sql-skilled" people......
> Thanks
> Steen
> .
> Keith Kratochvil wrote:
>

Help on Security of reports acccess

Dear all,
If I need to give acces to a particular folder and report type based on
groups of user's where this can be configured ?
For instance typically a user belonging to group MAINTENANCE will be able to
use reports only from maintenance folder and groups of QUALITY reports from
quality folder.
The yshould only use the report and not have access to datasource even
seeing them
Do I use the adminstrativ part of report server or should I create
authorisation and credential rights inside the Web.Config file of my report
server '
thnaks for help
regards
sergeIf you are using Report Manager to deliver the reports the security can be
configured right from there. Browse to the Report Manager web page
(http://myserver/reports). If your account has admin privileges then you
can edit the security, found under Properties for each folder. I am using
this to deliver reports for Accounting, Warehouse, and Transportation from
the same server, and each group sees only the reports I want them to see.
They also don't have access to the data source.
Just make sure that all groups have access to the home folder of Report
Manager, otherwise they won't get too far.
-Greg

help on query

I need advice on how to approach this. To simplify with a sample, below is a
list of various calculations based from ITEMS table:
ITEM_NO FIELD_CALCULATION
123 cost*qty
111 cost-discount
222 sales*discount
333 cost-freight
etc...
There are 20 additional line items with different calculations.

From my SP, how do I incorporate the above to say:
SELECT ??
INTO #tmp1
FROM GL_ACCOUNT GL,
ITEMS I
WHERE GL.ITEM_NO = I.ITEM_NO

TIA!
BobHi

You could try something like:

SELECT I.Item_No,
CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
WHEN 111 THEN GL.cost-GL.discount
WHEN 222 THEN GL.sales*GL.discount
WHEN 333 THEN GL.cost-GL.freight
END AS [Calculation]
FROM GL_ACCOUNT GL,
JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO

John

"B" <no_spam@.no_spam.com> wrote in message
news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>I need advice on how to approach this. To simplify with a sample, below is
>a
> list of various calculations based from ITEMS table:
> ITEM_NO FIELD_CALCULATION
> 123 cost*qty
> 111 cost-discount
> 222 sales*discount
> 333 cost-freight
> etc...
> There are 20 additional line items with different calculations.
> From my SP, how do I incorporate the above to say:
> SELECT ??
> INTO #tmp1
> FROM GL_ACCOUNT GL,
> ITEMS I
> WHERE GL.ITEM_NO = I.ITEM_NO
> TIA!
> Bob
>|||I was hoping to use the table created as a source without having to hardcode
since it will be used by other SP.

Thank you for your time.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
> Hi
> You could try something like:
> SELECT I.Item_No,
> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
> WHEN 111 THEN GL.cost-GL.discount
> WHEN 222 THEN GL.sales*GL.discount
> WHEN 333 THEN GL.cost-GL.freight
> END AS [Calculation]
> FROM GL_ACCOUNT GL,
> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
> John
> "B" <no_spam@.no_spam.com> wrote in message
> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
> >I need advice on how to approach this. To simplify with a sample, below
is
> >a
> > list of various calculations based from ITEMS table:
> > ITEM_NO FIELD_CALCULATION
> > 123 cost*qty
> > 111 cost-discount
> > 222 sales*discount
> > 333 cost-freight
> > etc...
> > There are 20 additional line items with different calculations.
> > From my SP, how do I incorporate the above to say:
> > SELECT ??
> > INTO #tmp1
> > FROM GL_ACCOUNT GL,
> > ITEMS I
> > WHERE GL.ITEM_NO = I.ITEM_NO
> > TIA!
> > Bob|||Hi

Creating a view would overcome that problem.

John

"B" <no_spam@.no_spam.com> wrote in message
news:JMSdnZXT2LmwK8rfRVn-iQ@.rcn.net...
>I was hoping to use the table created as a source without having to
>hardcode
> since it will be used by other SP.
> Thank you for your time.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
>> Hi
>>
>> You could try something like:
>>
>> SELECT I.Item_No,
>> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
>> WHEN 111 THEN GL.cost-GL.discount
>> WHEN 222 THEN GL.sales*GL.discount
>> WHEN 333 THEN GL.cost-GL.freight
>> END AS [Calculation]
>> FROM GL_ACCOUNT GL,
>> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
>>
>> John
>>
>> "B" <no_spam@.no_spam.com> wrote in message
>> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>> >I need advice on how to approach this. To simplify with a sample, below
> is
>> >a
>> > list of various calculations based from ITEMS table:
>> > ITEM_NO FIELD_CALCULATION
>> > 123 cost*qty
>> > 111 cost-discount
>> > 222 sales*discount
>> > 333 cost-freight
>> > etc...
>> > There are 20 additional line items with different calculations.
>>> > From my SP, how do I incorporate the above to say:
>> > SELECT ??
>> > INTO #tmp1
>> > FROM GL_ACCOUNT GL,
>> > ITEMS I
>> > WHERE GL.ITEM_NO = I.ITEM_NO
>>> > TIA!
>> > Bob
>>>>>>>
>>|||Have you ever had a software engineering course? Probably not, because
this approach is a violaiton of the principle of cohesion and it
confuses data and code.

help on join statement

I have table A:
ID int
Name text

And Table B
ID int
Name text

Now, I want to select all records from A where there is no matching record in B based on the ID

I want to do this with a JOIN statement and not a subquery as I understood that the execution plan for JOIN statements is more efficient...

Any help?

Something like this:

select *
from TableA
left outer join TableB
on TableA.ID= TableB.ID
where TableB.ID is null

The inner query does an outer join, all records show up, some with nulls

The outer query gets the records with null

|||

SELECT A.* FROM A LEFT OUTER JOIN B on A.ID = B.ID WHERE B.ID IS NULL

I would strongly advise comparing performance with

SELECT * FROM A WHERE A.ID NOT IN (SELECT ID FROM B)

sql

help on Date handling ?

Dear all,
What is the way under sql to extract the month from a date and then defined
a querry whcih fetch only data based on that particular month ?
thaks for your help
regardsSomething like this ?
declare @.dateVar datetime
select @.dateVar = '10102004'-- or whatever
select <column list>
from table1
where month(@.DateVar) = month(dateColumn)
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EAF2309E-1EE2-4830-9FB7-41B3896A7257@.microsoft.com...
> Dear all,
> What is the way under sql to extract the month from a date and then
> defined
> a querry whcih fetch only data based on that particular month ?
> thaks for your help
> regards|||examnotes (sergecalderara@.discussions.microsoft.com)
writes:
> What is the way under sql to extract the month from a date and then
> defined a querry whcih fetch only data based on that particular month ?
One way is:
SELECT .. FROM tbl
WHERE datecol >= convert(char(6), @.date, 112) + '01'
AND datecol < convert(char(6), dateadd(Month, 1, @.date), 112) + '01'
There are ways to write this in shorter code, but it's important to not
put the date column into any expression, as that would preclude the
use of index on that column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
To get a particular month from the given date u can use:
SELECT MONTH(<given_date> )
FROM <TABLE>
or
SELECT DATEPART("m",<given_date> )
FROM <TABLE>
to get records based on a particular date.
SELECT *
FROM <TABLE>
WHERE DATEPART("m",<given_date> ) = <month>
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"serge calderara" wrote:

> Dear all,
> What is the way under sql to extract the month from a date and then define
d
> a querry whcih fetch only data based on that particular month ?
> thaks for your help
> regards

Help needed with Xquery

Hello,

I'm trying to retreive the values from multiple nodes based on the value of another , without any success. The XML source is stored in an SQL(2005) xml column .

'Sample XML

<!--Combat Flight Sim mission-->

<Mission>

<Params Version="3.0" Directive="nothing" Country="Britain" Aircraft="p_51b" Airbase="brod23" Date="8/10/1940" Time="12:00" Weather="scatteredclouds3.xml" Multiplayer="y" MultiplayerOnly="n" />

.......

<AirFormation ID="6003" Directive="nothing" Country="Britain" Skill="1" FormType="diamond">

<Unit ID="9459" Type="p_51b" IsPlayer="y" Skill="1" />

<Unit ID="9460" Type="p_51b" Skill="2" />

.........

<AirFormation ID="6000" Directive="nothing" Country="Britain" Points="2" DamagePercent="40" Skill="2" Payload="2" FormType="box">

<Unit ID="9467" Type="b_25c" Skill="2" Payload="3" />

<Unit ID="9468" Type="b_25c" Skill="2" Payload="3" />

.........

AirFormation ID="6007" Directive="nothing" Country="Germany" Skill="2" FormType="fingertip">

<Unit ID="9475" Type="bf_109g_6" Skill="2" Payload="6" />

<Unit ID="9476" Type="bf_109g_6" Skill="2"

'This is the SQL code:

SELECT DISTINCT nref.value('@.Type', 'varchar(100)') Aircraft

FROM dbo.MOG_Missions CROSS APPLY xmlData.nodes('//AirFormation/Unit') as T(nref)

WHERE id = @.id 'some additional condition here is needed but I cannot figure it out

Which returns the following values from the ?Type attribute :

b_25c
bf_109g_6
p_51b

What I would like to accomplish is to return only the values from ?Type where the AirFormation-Country attribute matches the ?Country attribute of the ?Params node.

Thank you in advance.

Your XML sample is not clear to me. What is the relationship between the Params element and the AirFormation elements? If that is known then you should simply be able to express the condition in an XPath predicate in your nodes call. For example if the Params element is a sibling of the AirFormation elements then you can check e.g.

Code Snippet

SELECT DISTINCT t.u.value('@.Type', 'nvarchar(10)') AS Type

FROM example1

CROSS APPLY xml.nodes('//AirFormation[@.Country = ../Params/@.Country]/Unit') AS t(u)

WHERE id = 3;

|||I should have asked for help sooner! Thank you so much!

Help needed urgently in CR...

Hi All, I'm a beginner in CR10. I am supposed to produced a report based on the time spent by each staff (weekly basis) on a specific projects. The report should be able to generate based on the staff name and date range (start date and end date). I'm using stored procedure, test it out in the SQL Query Analyzer and I got the output. But when I tried to pass the user id (from the stored procedure) in the CR, it doesnt work. Nothing came out...So here I am. Stuck and not moving. So would really appreciate if anybody, somebody could help me out...:( Thanks...

I'm using SQL Server and JSP.Pass proper parameter value from JSP
It will work

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!