sql server codes: field

Showing posts with label field. Show all posts

Friday, March 30, 2012

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

help on updating a field in a table with the field content of another table

HI everybody need help on this..

I have two tables below

table1

country countryid

africa ___
usa ___
italy ___
Spain ___

table2

countryid country name

1 africa
2 germany
3 italy
4 usa

I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..

THE RESULT WOULD BE

country countryid

africa 1
usa 4
italy 3
spain 0

thanksShow us what you've come up with so far.|||yes trying this sql and it executed well and updated the first table

UPDATE table1
SET countryid=(SELECT countryid FROM table2
WHERE cntryname=country)

thanks anyway|||the solution worked fine until it didn't encounter a duplicate value... since it is a 1 to many relationship it gave me this error...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

thanks again|||you mean you have two different countries with the same name? which ones? my geography skills aren't as good as I thought... :)|||use JOIN, something like -

UPDATE table1
SET countryid= COALESCE(table2.countryid, 0)
from table1
left outer Joint table2
ON
table2.cntryname=table1.country|||alexyeth,
I think the problem is that "africa" is not a country but continent.
:)|||thanks everybody.. the solution of mihir is great using the coalesce function...

Wednesday, March 28, 2012

Help on SQL Query

Hi all,

I need some help on sql query.

I had a table named "tableA" where by I had a "timestart" and "timeend" field.

I would like to query all the records between timestart>= '2/2/2007 2:00:00 AM' and timeend<= '2/2/2007 3:00:00 AM' and also

timestart>= '2/3/2007 4:00:00 AM' and timeend<= '2/3/2007 5:00:00 AM'

Can I do this in a single sql statement instead of querying the table twice?

Please help.

Thanks

Yes you can

select *
from tableA
where (timestart>= '2/2/2007 2:00:00 AM' and timeend<= '2/2/2007 3:00:00 AM' )
and (timestart>= '2/3/2007 4:00:00 AM' and timeend<= '2/3/2007 5:00:00 AM' )

but in your example you will receive empty result because it is not possible for date to be valid for this query so maybe you mind this

select *
from tableA
where (timestart>= '2/2/2007 2:00:00 AM' and timeend<= '2/2/2007 3:00:00 AM' )
or (timestart>= '2/3/2007 4:00:00 AM' and timeend<= '2/3/2007 5:00:00 AM' )

Thanks

Monday, March 26, 2012

Help on Query

I have a table that has a field "Judge Name" that may contain names in the
format "J Jones" or "M Smith". I need to change that format to "Jones J" and
"Smith M". I am trying the following query thinking I can use that in an
Update query but I am getting the error:
"Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression."
I don't understand this? I am trying to say that if the second character is
a space, the name is in the old format and needs to be converted.
=============== Query ===================
IF (Select Substring([Judge Name], 2,1) From Test) = ' '
BEGIN
Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
Name],1)
From Test
END
Else
Select [Judge Name] From Test
========================================
=Wayne
If you want us to solve the problem, ,please post DDL+ sample data+ expected
result.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
>I have a table that has a field "Judge Name" that may contain names in the
>format "J Jones" or "M Smith". I need to change that format to "Jones J"
>and "Smith M". I am trying the following query thinking I can use that in
>an Update query but I am getting the error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression."
> I don't understand this? I am trying to say that if the second character
> is a space, the name is in the old format and needs to be converted.
> =============== Query ===================
> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
> Name],1)
> From Test
> END
> Else
> Select [Judge Name] From Test
> ========================================
=
>|||Here's one problem:

> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
You must limit the number of rows returned to one for this to work, or
better: look up EXISTS in Books Online.
Anyway, guessing from your post you need the CASE expression. Look it up in
Books Online.
Try this (untested, since you haven't posted DLL and sample data):
select case
when Substring([Judge Name], 2,1)
then RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' +
LEFT([Judge
Name],1)
else [Judge Name]
end
from Test
ML
http://milambda.blogspot.com/|||You are trying to use IF in a way that simply is not how it works.
Try something along these lines:
SELECT CASE WHEN Substring([Judge Name], 2,1) = ' '
THEN RIGHT([Judge Name], LEN([Judge Name])-2)
+ ' '
+ LEFT([Judge Name],1)
ELSE [Judge Name]
END as NewJudgeName
FROM Test
Roy
On Thu, 23 Feb 2006 04:26:40 -0700, "Wayne Wengert"
<wayneSKIPSPAM@.wengert.org> wrote:

>I have a table that has a field "Judge Name" that may contain names in the
>format "J Jones" or "M Smith". I need to change that format to "Jones J" an
d
>"Smith M". I am trying the following query thinking I can use that in an
>Update query but I am getting the error:
>"Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
>expression."
>I don't understand this? I am trying to say that if the second character is
>a space, the name is in the old format and needs to be converted.
>=============== Query ===================
>IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
>Name],1)
> From Test
> END
>Else
> Select [Judge Name] From Test
> ========================================
=
>|||How about :
update test
set [Judge Name] =
RIGHT([Judge Name], LEN([Judge Name])-2) +
' ' + LEFT([Judge Name],1)
where Substring([Judge Name], 2,1) = ' '
No case statements or procedural logic should be needed if this is all you
are trying to do.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
> I have a table that has a field "Judge Name" that may contain names in the
> format "J Jones" or "M Smith". I need to change that format to "Jones J"
and
> "Smith M". I am trying the following query thinking I can use that in an
> Update query but I am getting the error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression."
> I don't understand this? I am trying to say that if the second character
is
> a space, the name is in the old format and needs to be converted.
> =============== Query ===================
> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
> Name],1)
> From Test
> END
> Else
> Select [Judge Name] From Test
> ========================================
=
>|||Thanks. I was using a wrong approach.
Wayne
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:etO11NIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> How about :
> update test
> set [Judge Name] =
> RIGHT([Judge Name], LEN([Judge Name])-2) +
> ' ' + LEFT([Judge Name],1)
> where Substring([Judge Name], 2,1) = ' '
> No case statements or procedural logic should be needed if this is all you
> are trying to do.
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
> and
> is
>

Friday, March 23, 2012

Help on Date

Hi,
I'm new to SQL.
I like to pick up the previous date. How can I do this?
I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a query
to get the previous day's date. How do I convert the date to the previous
one?
For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
from the system date.
Please help,
Thanks,
Sarah>> I like to pick up the previous date. How can I do this?
Look up DATEADD function in SQL Server Books Online. The second argument for
this function can take negative values.
Anith|||Use DATEADD. something like this SELECT DATEADD(DAY, -1, GETDATE()) AS
PreviousDay
"SG" <sguo@.coopervision.ca> wrote in message
news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm new to SQL.
> I like to pick up the previous date. How can I do this?
> I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a
query
> to get the previous day's date. How do I convert the date to the previous
> one?
> For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
> from the system date.
> Please help,
> Thanks,
> Sarah
>|||Thanks so much for Tim and Anith quick response. I will give a try, good
start.
Appreicate
sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23WV1JrG7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Look up DATEADD function in SQL Server Books Online. The second argument
> for this function can take negative values.
> --
> Anith
>|||Hi,
I was in a view design, how can I convert GETDATE() TO day of the month,
like 1-31.
is there a function that I can use like MONTH() etc.
Thanks,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm new to SQL.
> I like to pick up the previous date. How can I do this?
> I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a
> query to get the previous day's date. How do I convert the date to the
> previous one?
> For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
> from the system date.
> Please help,
> Thanks,
> Sarah
>|||Use DATEPART
"SG" <sguo@.coopervision.ca> wrote in message
news:uFyK$fH7FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||Try using the DATEPART function.
"SG" wrote:

> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>
>|||Thanks, that works.
"Devers" <Devers@.discussions.microsoft.com> wrote in message
news:86461C90-7674-4F8B-8B70-A195C546218C@.microsoft.com...
> Try using the DATEPART function.
> "SG" wrote:
>|||Hi Everyone,
Actually DATEADD() works for me. Thanks everyone for your help.
Appreciate it and have a good day,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:uFyK$fH7FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>

Wednesday, March 21, 2012

Help on calculated member

I don't know how to do this.

I have one table "services" with a field "clasification" and a another table "serviceschange" with a field "clasificationchange". The two tables are related with foreign key. The serviceschange table has a field idservices (a fk to the services table).

I want to show in the fact table the name and the clasification of a service (the name is in the services table) but for the clasification i have to do this:

If the id of the service is in the serviceschange table i have to show the clasifiactionchange of this table, but if the serviceschange doesn't have the id i want to show the field clasification of the services table. How i can do this? is it possibly?

Assuming AS 2005, and that services is a dimension, classification can be defined as an attribute of the services dimension. A named query like the following could be created in the DSV, to be used as the dimension table for services (if there is at most 1 entry for a service in serviceschange - otherwise grouping may be necessary):

select sv.idservices, sv.servicename, coalesce(sc.classificationchange, sv.classification) as classification

from services sv

left outer join serviceschange sc

on sv.idservices = sc.idservices

sql

Help on "splitting up" data in a field

Hi
I'm having a problem finding out how I can split data in one field and
then use the values to match records in another table.
In table 1, I have a field where the values looks like
e.g. "229 231 233 235". What I'd like to do, is to match these 4
numbers with an ID in table 2 to get the values from table2. I.e. I'd
like to split up this one value to 4 values (229, 232, 233,235).
I've tried to use REPLACE to put in a "," between each so I could use it
as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
Has any of you any other suggestions to how it can be done? It's not
always the same number of numbers in the field (e.g. another one could
be "456 29580010" ). The field is a VARCHAR(1000) and there're also
some text strings in it. These seems to be some old crab though and I
don't need these values.
The only "general" thing with the formatting, seems to be that there are
2 spaces between each of the numbers I'd like to get out, so I think I
can use that as a "delimiter".
Anyone who has some hints to this?
Regards
SteenSteen
Take a look at Anith's script
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
drop table Numbers
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> Steen
> Take a look at Anith's script
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5,33,229,1,22'
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
> from numbers where substring(','+@.Ids,n,1)=','
> AND n < LEN(@.Ids) + 1
> drop table Numbers
>
>
>
>
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Thanks Uri
I must admit, that I can't really see the purpose of the script, and
also I can't see how it can be used to solve my problem.
I've tried to see if I could get some ideas from the script, but I can't
really see how I can use it?
Regards
Steen|||In SQL 2000 you have to properly normalize the data - i.e. parse the values
and store them in a new table or redesign the table.
In SQL 2005 you can use Anith's function to parse the values on-the-fly
using CROSS APPLY.
ML
http://milambda.blogspot.com/|||I'm really sorry Steen , by posting Anith's example I did mean to give you
an idea to solve the problem
See if this helps you
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE TABLE #Source (col1 INT NOT NULL)
CREATE TABLE #Target (col1 INT NOT NULL)
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 229 1 22'
--Inserting the values to the source table
INSERT INTO #Source
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Source
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 10 1 22'
--Inserting the values to the Target table
INSERT INTO #Target
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Target
-->>> e.g. "229 231 233 235". What I'd like to do, is to match these 4
SELECT * FROM #Source WHERE NOT EXISTS
(SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
> Uri Dimant wrote:
> Thanks Uri
> I must admit, that I can't really see the purpose of the script, and also
> I can't see how it can be used to solve my problem.
> I've tried to see if I could get some ideas from the script, but I can't
> really see how I can use it?
> Regards
> Steen|||I think this might help you out, but there are problems with the aproach...
For one, the string concatenation leaves you open to SQL injection.
Granted, because you are selecting the values from a table, any malicious
injection code needs to actually be stored in your table, but it is still a
possibility. Second, this assumes you are dealing with numeric values, if
you need character values you will have to add in quotes along with the
commas.
declare @.SelectString varchar(1000)
set @.SelectString = TABLE1.FIELD1
set @.SelectString = replace(@.SelectString,' ',',')
set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
@.SelectString + ')'
EXECUTE sp_executesql @.SelectString
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd
> like to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could
> be "456 29580010" ). The field is a VARCHAR(1000) and there're also
> some text strings in it. These seems to be some old crab though and I
> don't need these values.
> The only "general" thing with the formatting, seems to be that there are
> 2 spaces between each of the numbers I'd like to get out, so I think I
> can use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> I'm really sorry Steen , by posting Anith's example I did mean to give you
> an idea to solve the problem
> See if this helps you
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> CREATE TABLE #Source (col1 INT NOT NULL)
> CREATE TABLE #Target (col1 INT NOT NULL)
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 229 1 22'
> --Inserting the values to the source table
> INSERT INTO #Source
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
> SELECT * FROM #Source
>
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 10 1 22'
> --Inserting the values to the Target table
> INSERT INTO #Target
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
>
> SELECT * FROM #Target
>
> -->>> e.g. "229 231 233 235". What I'd like to do, is to match these
4
> SELECT * FROM #Source WHERE NOT EXISTS
> (SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
>
Thanks for you input. I'll have to look further at the example. Right
now I still can't figure out how I can use it, but I'll check it out
tomorrow with a "fresh" pair of eyes..:-).
REgards
Steen|||Jim Underwood wrote:
> I think this might help you out, but there are problems with the aproach..
.
> For one, the string concatenation leaves you open to SQL injection.
> Granted, because you are selecting the values from a table, any malicious
> injection code needs to actually be stored in your table, but it is still
a
> possibility. Second, this assumes you are dealing with numeric values, if
> you need character values you will have to add in quotes along with the
> commas.
> declare @.SelectString varchar(1000)
> set @.SelectString = TABLE1.FIELD1
> set @.SelectString = replace(@.SelectString,' ',',')
> set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
> @.SelectString + ')'
> EXECUTE sp_executesql @.SelectString
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Hi Jim
The script is only for my own use, so I'm not so worried about
injections. It's just for producing some check lists to a few users.
I'll check out your script to see if it works. I'm having both numeric
and text values in the field, so I'll have to remove the text strings first.
Regards
Steen|||There is a function called charindex() which returns the numeric position of
one string within another string. You can join the two tables using
charindex, so that each row in MyTableA is joined with 0 - many rows in
MyTableB where charindex( .. ) > 0.
select
MyTableA.IDS,
MyTableB.ID
from MyTableA
left join MyTableB
on charindex(' '+MyTableB.ID+' ',' '+MyTableA.IDS+' ') > 0
The issue is that this data model is not properly normalized because it is
storing multiple values in one column:
http://www.agiledata.org/essays/dat...html#Normalize
This presents in at least 3 problems:
1. Accuracy: Can you depend on the format of the delimited values
reliable? The purpose of appending additional spaces before and after the
strings is to insure that:
charindex('999','123 ABC999 456') = 0
2. Performance: A non indexed table scan will probably be used due to
using a function for the join expression
http://www.microsoft.com/technet/pr...s/c0618260.mspx
http://www.sql-server-performance.c...ing_indexes.asp
3. Your queries will be more complex to write.
Let's assume that you have a Customer table and a Discount table.What is
needed is a reference table called CustomerDiscount that associates 0 - many
promotions for each customer.
For example:
CustomerID PromotionID
200 10
200 11
212 10
212 13
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||On Thu, 19 Jan 2006 13:34:07 +0100, Steen Persson (DK) wrote:

>Hi
>I'm having a problem finding out how I can split data in one field and
>then use the values to match records in another table.
>In table 1, I have a field where the values looks like
>e.g. "229 231 233 235". What I'd like to do, is to match these 4
>numbers with an ID in table 2 to get the values from table2. I.e. I'd
>like to split up this one value to 4 values (229, 232, 233,235).
>I've tried to use REPLACE to put in a "," between each so I could use it
>as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
>Has any of you any other suggestions to how it can be done? It's not
>always the same number of numbers in the field (e.g. another one could
>be "456 29580010" ). The field is a VARCHAR(1000) and there're also
>some text strings in it. These seems to be some old crab though and I
>don't need these values.
>The only "general" thing with the formatting, seems to be that there are
>2 spaces between each of the numbers I'd like to get out, so I think I
>can use that as a "delimiter".
>Anyone who has some hints to this?
Hi Steen,
In addition to what others already wrote on this, I'll give you this
link:
http://www.sommarskog.se/arrays-in-sql.html
Also, try to change the design. Arrays really should not be stored in a
single column.
Hugo Kornelis, SQL Server MVP

Friday, March 9, 2012

Help needed in sp_pkeys

Hai ,

I am in the situation to find the p.key field from a table.So i use

sp_pkeys 'table' .It works nice.

But i want to select the pkey field alone instead of the rest of the
informations supplied by the sp.

How to get the P.key field name alone from a table .Or If u tell the
way to store the results of sp_pkeys 'table' into a table also ok to
me.

With Regards
Raghu"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0402030621.2bb732b@.posting.google.co m...
> Hai ,
> I am in the situation to find the p.key field from a table.So i use
> sp_pkeys 'table' .It works nice.
> But i want to select the pkey field alone instead of the rest of the
> informations supplied by the sp.
> How to get the P.key field name alone from a table .Or If u tell the
> way to store the results of sp_pkeys 'table' into a table also ok to
> me.
>
> With Regards
> Raghu

You didn't mention which version of SQL Server you have, but in 7/2000, you
can use the INFORMATION_SCHEMA views:

select
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
kcu.COLUMN_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
where
tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and
tc.TABLE_SCHEMA = 'dbo' and
tc.TABLE_NAME = 'MyTable'

Alternatively, you can put the output of sp_pkeys into a table:

create table #keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
go
insert into #keys
exec sp_pkeys 'MyTable'
go

Simon|||
Hai Simon

Thanks for your code.

I am in Sql server 7.0 and i make use of it

Raghu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

help needed a easy one though

I have a report and I want to display D if the particular field is true and then i want to display some name.

like D dummyFund.

and this is my expression to do it

=IIf(Fields!IsFundDefault.Value = 0,"","D") + Fields!InvestmentName.Value

but when i preview it in VS i dont get a D before the fundname if the expression is true.

any help appreciated.

Regards

Karen

never mind it worked.

Regards Karen

Wednesday, March 7, 2012

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.

Monday, February 27, 2012

Help Me Write This Query

Sorry for my newbieness:
I have a table (Cases) where Case_Number is the unique id field.
Another table (CommentsTable) contains these fields
Case_Number
Comment_Date
Comment
As users enter comments to the database, the comment gets a date/time
stamp (Comment_Date) and is recorded in the Comment field, and the case
number for that comment is also recorded.
I want my query to return fields from the Cases table, but also return
the last comment from the CommentsTable. I know I need to use the
Case_Number and MAX(Comment_Date) then return the Comment for that max
date and that case number, and I'm pretty sure this will be a sub
query, I just can't figure out how to put it all together.
Could somebody get me going in the right direction?
TIA,
todTod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
>
select * from CommentsTable ct where not exists(select 1 from
CommentsTable ct1
where ct.Case_Number = ct1.Case_Number and ct.Comment_Date <
ct1.Comment_Date)|||Tod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
> As users enter comments to the database, the comment gets a date/time
> stamp (Comment_Date) and is recorded in the Comment field, and the case
> number for that comment is also recorded.
> I want my query to return fields from the Cases table, but also return
> the last comment from the CommentsTable. I know I need to use the
> Case_Number and MAX(Comment_Date) then return the Comment for that max
> date and that case number, and I'm pretty sure this will be a sub
> query, I just can't figure out how to put it all together.
> Could somebody get me going in the right direction?
> TIA,
> tod
Untested:
SELECT C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases AS C
JOIN dbo.CommentsTable AS T
ON C.Case_Number = T.Case_Number
WHERE T.Comment_Date =
(SELECT MAX(Comment_Date)
FROM dbo.CommentsTable
WHERE Case_Number = T.Case_Number);
Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
could still get more than one row per Case_Number.
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
--|||This might be neater if you are returning a single case. The Case_Number is
stored in the @.CaseNumber variable in this example:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_Date DESC
If you wish to remove time adjustments from the equation (e.g. daylight
saving adjustments) then you could add an IDENTITY column to the
CommentsTable table (Comment_ID) and use the following:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_ID DESC
Chris
"David Portas" wrote:

> Tod wrote:
> Untested:
> SELECT C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases AS C
> JOIN dbo.CommentsTable AS T
> ON C.Case_Number = T.Case_Number
> WHERE T.Comment_Date =
> (SELECT MAX(Comment_Date)
> FROM dbo.CommentsTable
> WHERE Case_Number = T.Case_Number);
> Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
> could still get more than one row per Case_Number.
> --
> 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
> --
>|||Thanx. I think this is closest to what I need.
Chris Howarth wrote:
> This might be neater if you are returning a single case. The Case_Number i
s
> stored in the @.CaseNumber variable in this example:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_Date DESC
>
> If you wish to remove time adjustments from the equation (e.g. daylight
> saving adjustments) then you could add an IDENTITY column to the
> CommentsTable table (Comment_ID) and use the following:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_ID DESC
> Chris
>
> "David Portas" wrote:
>

help me with date

Hi i have date field. when used paratemized query from asp.net . in which
date field is null , it null the whole dynamix sql , then i decide to use
isnull function to convert null to '', i
like set @.mydate=isnull(@.mydate,'')
but this create another problem which it update date field with date
1/1/1900, but i want that field should be null instead of 1/1/1900 any one
has any idea how to do thatUse this option.
SET CONCAT_NULL_YIELDS_NULL OFF
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"amjad" wrote:

> Hi i have date field. when used paratemized query from asp.net . in which
> date field is null , it null the whole dynamix sql , then i decide to use
> isnull function to convert null to '', i
> like set @.mydate=isnull(@.mydate,'')
> but this create another problem which it update date field with date
> 1/1/1900, but i want that field should be null instead of 1/1/1900 any one
> has any idea how to do that|||or if its not in the dbend
then use DBNull.value
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> Use this option.
> SET CONCAT_NULL_YIELDS_NULL OFF
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "amjad" wrote:
>|||amjad wrote:
> Hi i have date field. when used paratemized query from asp.net . in which
> date field is null , it null the whole dynamix sql , then i decide to use
> isnull function to convert null to '', i
> like set @.mydate=isnull(@.mydate,'')
> but this create another problem which it update date field with date
> 1/1/1900, but i want that field should be null instead of 1/1/1900 any one
> has any idea how to do that
Your dynamic SQL needs to set the date field equal to NULL instead of
''. Post the code that builds your dynamic query.

Friday, February 24, 2012

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,

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

FROM
CreditTransactions INNER JOIN
StatementsHistory ON CreditTransactions.AccountNumber = StatementsHistory.AccountNumber

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!

Friday, March 30, 2012

Help on using CASE together with UPDATE

Help on using CASE together with UPDATE

help on updating a field in a table with the field content of another table

Wednesday, March 28, 2012

Help on SQL Query

Monday, March 26, 2012

Help on Query

Friday, March 23, 2012

Help on Date

Wednesday, March 21, 2012

Help on calculated member

Help on "splitting up" data in a field

Friday, March 9, 2012

Help needed in sp_pkeys

help needed a easy one though

Wednesday, March 7, 2012

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

Monday, February 27, 2012

Help Me Write This Query

help me with date

Friday, February 24, 2012

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

sql server codes

Blog Archive

About Me