finding lower case data

I have a table with a VIN number column, I need to find all entries where th
e
VIN contains a lower case character, example '4d0131P2X4E146985'. This recor
d
should be included in the results because it contains 'd'.. What would be th
e
easiest way to write a query to return these, can I use regular expression i
n
T-SQL...?
Thanks,
DanAlien2_51,
Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column
in question. I guess it is using a case insensitive one. Use the COLLATE
clause to tell SQL Server which collation to use during the operation and us
e
a case sensitive one. You can also use function binary_checksum.
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where binary_checksum(c1) != binary_checksum(upper(c1))
AMB
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan|||You could do this:
select VIN
from yourTable
where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
-- or
select VIN
from yourTable
where VIN like '%[a-z]%'
collate Latin1_General_BIN
Note that it will not work to use the range [a-z] in the first
example, because in the CS_AS collation, [a-z] contains the
uppercase letters A through Y.
Steve Kass
Drew University
http://www.stevekass.com
Alien2_51 wrote:

>I have a table with a VIN number column, I need to find all entries where t
he
>VIN contains a lower case character, example '4d0131P2X4E146985'. This reco
rd
>should be included in the results because it contains 'd'.. What would be t
he
>easiest way to write a query to return these, can I use regular expression
in
>T-SQL...?
>Thanks,
>Dan
>|||Thanks Alejandro... Just what I needed, and so simple...
DB
"Alejandro Mesa" wrote:
> Alien2_51,
> Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the colu
mn
> in question. I guess it is using a case insensitive one. Use the COLLATE
> clause to tell SQL Server which collation to use during the operation and
use
> a case sensitive one. You can also use function binary_checksum.
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where binary_checksum(c1) != binary_checksum(upper(c1))
>
> AMB
>
> "Alien2_51" wrote:
>|||Alien, I guess you might want to check this out
http://vadivel.blogspot.com/2003/12...e-searches.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan

Finding date clashes

If I had a table of holidayItems (a holiday is made up of one or more
holidayItems) that contains the columns, Id, HolidayID, StartDate EndDate,
and I wanted to write a sproc that returned a table of clashes i.e the
Holiday ID,the Id of the holiday it clashed with and the dates the holidays
clash. How would I go about it? There will be no clashes of holiday item
dates within a holiday. I am looking for clashes between different holidays.
Sample data:
ID HolidayID StartDate EndDate
1 1 08/05/05 08/10/05
2 1 08/13/05 08/15/05
3 2 08/01/05 08/05/05
4 3 08/02/05 08/04/05
5 4 08/13/05 08/20/05
6 4 08/23/05 08/25/05
Result set
HolidayID ClashesWithHolidayID DateClash
1 2 08/05/05
1 4 08/13/05
1 4 08/14/05
1 4 08/15/05
2 3 08/02/05
2 3 08/03/05
2 3 08/04/05
Table script
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[HolidayItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HolidayItem]
GO
CREATE TABLE [dbo].[HolidayItem] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[HolidayID] [int] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GOSELECT H1.id, H2.id,
H1.startdate, H1.enddate, H2.startdate, H2.enddate
FROM HolidayItem AS H1
JOIN HolidayItem AS H2
ON (H1.startdate <= H2.enddate
AND H1.enddate >= H2.startdate)
AND H1.id < H2.id ;
To get the individual dates, just join to a calendar table on BETWEEN
startdate AND enddate.
Don't forget to add the important constraints. I don't quite understand
why your sample data has different start and end dates for the same
"holidayid" but however that may be, startdate ought to be part of a
composite unique key. Also, add the check constraint startdate <=
enddate
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--|||The reason for the two start and end dates for the same holidayid is that it
is a table of holiday items and a holiday can have one or more items making
up the holiday. The reason for this is so that i do not record wends in
the holiday period.
Thanks very much for your help
john
"David Portas" wrote:

> SELECT H1.id, H2.id,
> H1.startdate, H1.enddate, H2.startdate, H2.enddate
> FROM HolidayItem AS H1
> JOIN HolidayItem AS H2
> ON (H1.startdate <= H2.enddate
> AND H1.enddate >= H2.startdate)
> AND H1.id < H2.id ;
> To get the individual dates, just join to a calendar table on BETWEEN
> startdate AND enddate.
> Don't forget to add the important constraints. I don't quite understand
> why your sample data has different start and end dates for the same
> "holidayid" but however that may be, startdate ought to be part of a
> composite unique key. Also, add the check constraint startdate <=
> enddate
> --
> David Portas
> SQL Server MVP
> --
>
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Portas
does your query cover if start date starts after 2nd startdate and ends
before second end date.(intervening period_
similary of start date starts before 2nd startdate and ends after 2end end
dates(covering period)
and such scenarios
This union query may be used but not tested
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.StartDate >= HolidayItem_1.StartDate AND
dbo.HolidayItem.EndDate <= HolidayItem_1.EndDate AND
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate >= HolidayItem_1.EndDate
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate <= HolidayItem_1.EndDate
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate >= HolidayItem_1.EndDate
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.EndDate >= HolidayItem_1.StartDate AND
dbo.HolidayItem.EndDate <= HolidayItem_1.EndDate
r.d
"John" wrote:
> The reason for the two start and end dates for the same holidayid is that
it
> is a table of holiday items and a holiday can have one or more items makin
g
> up the holiday. The reason for this is so that i do not record wends in
> the holiday period.
> Thanks very much for your help
> john
> "David Portas" wrote:
>|||> does your query cover if start date starts after 2nd startdate and ends
> before second end date
Yes.
Compare your results to mine. There is a difference and I think some of
your joins need looking at. The following one looks wrong for a start
but your approach is basically sound, although a bit more typing than
mine!
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate <= HolidayItem_1.EndDate
David Portas
SQL Server MVP
--|||Your DDL had no key, you formatted the dates wrong, used IDENTITY and
lacked constraints. Is this what you really meant?
CREATE TABLE HolidaySchedules
(holiday_item INTEGER NOT NULL
start_date DATETIME NOT NULL
CHECK( << set to 00:00:00 Hrs>> ),
end_date DATETIME NOT NULL
CHECK( << set to 23:59:59.9999.. Hrs>> ),
CHECK (start_date < end_date),
PRIMARY KEY (holiday_item, start_date)); -- real key!!
INSERT INTO HolidaySchedules VALUES (1, '2005-08-05', '2005-08-10');
INSERT INTO HolidaySchedules VALUES (1, '2005-08-13', '2005-08-15');
INSERT INTO HolidaySchedules VALUES (2, '2005-08-01', '2005-08-05');
INSERT INTO HolidaySchedules VALUES (3, '2005-08-02', '2005-08-04');
INSERT INTO HolidaySchedules VALUES (4, '2005-08-13', '2005-08-20');
INSERT INTO HolidaySchedules VALUES (4, '2005-08-23', '2005-08-25');
s between different holidays. <<
Create a Calendar table with all the temporal data you use in your
enterprise. It will hlep with this query and a lot of others. First a
warm up query in a VIEW.
CREATE VIEW BadDates(cal_date, conflict_count)
AS
SELECT C.cal_date, COUNT(*)
FROM Calendar AS C1, HolidaySchedules AS H
WHERE C.cal_date BETWEEN H.start_date AND H.end_date
GROUP BY C.cal_date
HAVING COUNT(*) > 1;
This gives us rhe dates with conflicts. You can use those dates in the
desired query. Obviously, the VIEW can be made into a derived table or
CTE.
SELECT B.cal_date, H.holiday_item
FROM HolidaySchedules AS H, BadDates AS B
WHERE B.cal_date BETWEEN H.start_date AND H.end_date;
If you want to generate pairs of H.holiday_items, put this into another
VIEW and do a self-join on cal_dates. But I would do that in the front
end and not the database.|||yes, there are some joins where we need to add some more 'AND' OPERATOR.
FOR EX:
a date may start before second start date but ends before second end dates.
in this case we have to add one more condition : the first end date is
greater than second start date.
there is another scenario similar to this: it starts after first startdate
and ends after second end date.
bye
devaraj
"David Portas" wrote:

> SELECT H1.id, H2.id,
> H1.startdate, H1.enddate, H2.startdate, H2.enddate
> FROM HolidayItem AS H1
> JOIN HolidayItem AS H2
> ON (H1.startdate <= H2.enddate
> AND H1.enddate >= H2.startdate)
> AND H1.id < H2.id ;
> To get the individual dates, just join to a calendar table on BETWEEN
> startdate AND enddate.
> Don't forget to add the important constraints. I don't quite understand
> why your sample data has different start and end dates for the same
> "holidayid" but however that may be, startdate ought to be part of a
> composite unique key. Also, add the check constraint startdate <=
> enddate
> --
> David Portas
> SQL Server MVP
> --
>
> --
> David Portas
> SQL Server MVP
> --
>sql

Finding a user that has multiple log-ins at the same time

I have a table that contains the following

UserName - Which is unique to each user

TimeStart - Which is the time and date that the user logs in

TimeEnd - Which is the time and date that the user logs out

Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.

Try this query....

Select
MainQ.*
From
UserLog MainQ
Join
(
Select

Main.UserName,
Main.TimeStart,
Main.TimeEnd
From
UserLog Main
Join UserLog Started On Main.UserName = Started.UserName
And Main.TimeStart <= Started.TimeStart
And Main.TimeEnd >= Started.TimeStart

Group BY
Main.UserName,
Main.TimeStart,
Main.TimeEnd
Having Count(*) <> 1
) as Data
On MainQ.UserName = Data.UserName
And MainQ.TimeStart >= Data.TimeStart
And MainQ.TimeStart <= Data.TimeEnd

finding a data in my db (700 tbl)

Hi
I have a db that contains ~700 tbl. I am looking for a data in my db.
I don t know which table and column i need to look in, bu i know the
data exist. Is there any way to search my whole db to find the data?
Thanks in advance
You can search the web and find a script that does this (kondredi may have
one). There was a recent thread on the same issue.
You can also roll your own using sysobjects (type = 'u') and syscolumns and
dynamically create a bunch of select statements with where clauses using
LIKE %searcheddata%. If you are searching for numeric values it is a bit
easier since you can do a straight =.
TheSQLGuru
President
Indicium Resources, Inc.
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegr oups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>
|||On May 22, 11:20 am, esen...@.gmail.com wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
My first thought is to use a cursor to loop through your tables using
this query:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Then using dynamic sql, write a query to find your data.
|||One way to approach this is to dump all the tables out via bcp to text files
and then search these text files. You can easily script to bcp the data out
of these 700 tables. I would bet this is easier and faster than an
in-database search if you absolutely have no idea where that piece of data
may be.
Linchi
"esengul@.gmail.com" wrote:

> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>
|||Hi
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegr oups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>

finding a data in my db (700 tbl)

Hi
I have a db that contains ~700 tbl. I am looking for a data in my db.
I don t know which table and column i need to look in, bu i know the
data exist. Is there any way to search my whole db to find the data?
Thanks in advanceYou can search the web and find a script that does this (kondredi may have
one). There was a recent thread on the same issue.
You can also roll your own using sysobjects (type = 'u') and syscolumns and
dynamically create a bunch of select statements with where clauses using
LIKE %searcheddata%. If you are searching for numeric values it is a bit
easier since you can do a straight =.
--
TheSQLGuru
President
Indicium Resources, Inc.
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||On May 22, 11:20 am, esen...@.gmail.com wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
My first thought is to use a cursor to loop through your tables using
this query:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Then using dynamic sql, write a query to find your data.|||One way to approach this is to dump all the tables out via bcp to text files
and then search these text files. You can easily script to bcp the data out
of these 700 tables. I would bet this is easier and faster than an
in-database search if you absolutely have no idea where that piece of data
may be.
Linchi
"esengul@.gmail.com" wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||Hi
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>sql

finding a data in my db (700 tbl)

Hi
I have a db that contains ~700 tbl. I am looking for a data in my db.
I don t know which table and column i need to look in, bu i know the
data exist. Is there any way to search my whole db to find the data?
Thanks in advanceYou can search the web and find a script that does this (kondredi may have
one). There was a recent thread on the same issue.
You can also roll your own using sysobjects (type = 'u') and syscolumns and
dynamically create a bunch of select statements with where clauses using
LIKE %searcheddata%. If you are searching for numeric values it is a bit
easier since you can do a straight =.
TheSQLGuru
President
Indicium Resources, Inc.
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||On May 22, 11:20 am, esen...@.gmail.com wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
My first thought is to use a cursor to loop through your tables using
this query:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Then using dynamic sql, write a query to find your data.|||One way to approach this is to dump all the tables out via bcp to text files
and then search these text files. You can easily script to bcp the data out
of these 700 tables. I would bet this is easier and faster than an
in-database search if you absolutely have no idea where that piece of data
may be.
Linchi
"esengul@.gmail.com" wrote:

> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||Hi
http://vyaskn.tripod.com/search_all..._all_tables.htm
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>

Find the most recent date of the past

Hello,

I've a table which contains the nest following data:

Date Sales Price

01-01-07 5,00

31-03-07 6,00

16-04-07 5.75

26-04-07 6.25

For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.

How can I get it?

Thx!

This may work... there's probably an easier way.

select top 1 [date], [salesprice]

from table

group by [date], [salesprice]

having max(convert(varchar(10),[date],112)) < convert(varchar(10),current_timestamp,112)

order by [date] desc

cheers,

Andrew

|||

Here's a version without the grouping. Try this:

select top 1 SalesPrice

from PriceInfoTable

where Date < getdate()

order by Date desc

Jarret

|||

Jarret,

Your solutions works, but I discovered that my problem is a little bit more complex. I still have the same table, but in the table I have more items. My table looks like (for example):

No. Date Sales Price

001 01-01-07 5,00

001 31-03-07 6,00

001 16-04-07 5.75

001 26-04-07 6.25

002 21-01-07 7,00

002 27-02-07 7,00

002 06-04-07 7.75

002 26-04-07 7.25

003 11-01-07 2,00

003 17-02-07 1,00

003 01-04-07 0.75

003 26-04-07 1.25

If I use your code, I only get ItemNo. 001 with a price of 5.75. But I want for every item the most recent price:

001 5.75

002 7.75

003 0.75

I've tried some options, but I can't get the solution. Do you have any suggestions?

|||

Here's two ways you could do it...

select distinct t1.id,

( select top 1 price

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

select t1.id, t1.price

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

I don't get the first option.

Can you tell me what the function of 't1.id' is? And also from 't1.id=#t.id.'? Maybe them I can implement it to my query......

thx

|||

No the first option has a type, it is actually:

select distinct t1.id,

( select top 1 price

from PriceInfoTable t2

where Date < getdate()

and t1.id = t2.id

order by Date desc

) price

from PriceInfoTable t1

You can also use this query which is the 3rd option:

SELECT [No], SalesPrice

FROM PriceInfoTable p1

WHERE Date = (SELECT MAX(Date) FROM PriceInfoTable p2 WHERE p2.No = p1.No AND Date < GETDATE())

Shyam

|||

Sorry,

I was working with my own sample query to get the results you needed and was using a temp table (#t) to hold the data.

The t1.id corresponds to your "No." field and the #t corresponds to the PriceInfoTable in the subquery. Shyam's post has my query with it fixed. Sorry for the inconvenience.

Jarret

|||

Jarret/Shyam,

I got all the 3 solutions implemented and they are working

But the next step for me is to add another column at the "select top"-selection.

In my table I have also a 'Sales Code'. In my report the user must set a filter on this 'Sales Code' (with a parameter). The table looks like:

No. StartingDate Sales Price Sales Code

001 01-01-07 5,00 SC01

001 31-03-07 6,00 SC01

001 16-04-07 5.75 SC02

001 26-04-07 6.25 SC01

If the user select SC01, the price of 6.00 has to be displayed. By selecting SC02, 5.75.

I have tried to insert 'Sales Code' in one of your suggestions on this way,

select distinct t1.id,

( select top 1 price, Sales Code

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

but an error returns.

Also inserting 'Sales Code' after the 'Select distinct t1.id'-part, wasn't the right way. By doing this the same price was given to every sales code.

Can you help me out?

Thx a lot!

|||

Try this:

select t1.id, t1.price, t1.[sales code]

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

and [Sales Code] = @.SalesCodeParameter

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

thx for your help. Your suggestion is working!

But I see that I've me an error by posting an example of the table. The field StartingDate is not only the date but also the time, it's the starting date and time. So the code-part of

WHERE([Starting Date Time]) < GETDATE

has to be something else. I have tried GETDATETIME, but that was not correct. How can I solve it?

Starting Date Time is a combined field. The fields Starting Date and Starting Time are also seperated available, if necessary.

Thx

|||

Try this:

WHERE([Starting Date Time]) < GETDATE()

Jarret

|||

Jarret,

I did that suggestion and it didn't worked out.

The problem is that the report is filtering only on date, not on date and time....

Do you have a suggestion?

Thx

|||

This will remove the time from the current date time.

WHERE([Starting Date Time]) < convert(varchar, GETDATE(), 101)

If you need to remove the time part off of [Starting Date Time], just wrap it in the convert statement as well.

Hope this helps.

Jarret

|||

Jarret,

More than one price for a date can be inserted In my table. I can insert a price and give it a starting time of 22.00 tonight. Of course I want to see the price of now if it is not yet 22.00.

I think I need more something like GETTIME or someting like that.

I was thinking of a statement with

WHERE(([Starting Date Time]) < GETDATE() and Starting Date Time]) < GETTIME()

in it.

But this one is not working.

Do you have any suggestion?

Thx

Find the most recent date of the past

Hello,

I've a table which contains the nest following data:

Date Sales Price

01-01-07 5,00

31-03-07 6,00

16-04-07 5.75

26-04-07 6.25

For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.

How can I get it?

Thx!

This may work... there's probably an easier way.

select top 1 [date], [salesprice]

from table

group by [date], [salesprice]

having max(convert(varchar(10),[date],112)) < convert(varchar(10),current_timestamp,112)

order by [date] desc

cheers,

Andrew

|||

Here's a version without the grouping. Try this:

select top 1 SalesPrice

from PriceInfoTable

where Date < getdate()

order by Date desc

Jarret

|||

Jarret,

Your solutions works, but I discovered that my problem is a little bit more complex. I still have the same table, but in the table I have more items. My table looks like (for example):

No. Date Sales Price

001 01-01-07 5,00

001 31-03-07 6,00

001 16-04-07 5.75

001 26-04-07 6.25

002 21-01-07 7,00

002 27-02-07 7,00

002 06-04-07 7.75

002 26-04-07 7.25

003 11-01-07 2,00

003 17-02-07 1,00

003 01-04-07 0.75

003 26-04-07 1.25

If I use your code, I only get ItemNo. 001 with a price of 5.75. But I want for every item the most recent price:

001 5.75

002 7.75

003 0.75

I've tried some options, but I can't get the solution. Do you have any suggestions?

|||

Here's two ways you could do it...

select distinct t1.id,

( select top 1 price

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

select t1.id, t1.price

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

I don't get the first option.

Can you tell me what the function of 't1.id' is? And also from 't1.id=#t.id.'? Maybe them I can implement it to my query......

thx

|||

No the first option has a type, it is actually:

select distinct t1.id,

( select top 1 price

from PriceInfoTable t2

where Date < getdate()

and t1.id = t2.id

order by Date desc

) price

from PriceInfoTable t1

You can also use this query which is the 3rd option:

SELECT [No], SalesPrice

FROM PriceInfoTable p1

WHERE Date = (SELECT MAX(Date) FROM PriceInfoTable p2 WHERE p2.No = p1.No AND Date < GETDATE())

Shyam

|||

Sorry,

I was working with my own sample query to get the results you needed and was using a temp table (#t) to hold the data.

The t1.id corresponds to your "No." field and the #t corresponds to the PriceInfoTable in the subquery. Shyam's post has my query with it fixed. Sorry for the inconvenience.

Jarret

|||

Jarret/Shyam,

I got all the 3 solutions implemented and they are working

But the next step for me is to add another column at the "select top"-selection.

In my table I have also a 'Sales Code'. In my report the user must set a filter on this 'Sales Code' (with a parameter). The table looks like:

No. StartingDate Sales Price Sales Code

001 01-01-07 5,00 SC01

001 31-03-07 6,00 SC01

001 16-04-07 5.75 SC02

001 26-04-07 6.25 SC01

If the user select SC01, the price of 6.00 has to be displayed. By selecting SC02, 5.75.

I have tried to insert 'Sales Code' in one of your suggestions on this way,

select distinct t1.id,

( select top 1 price, Sales Code

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

but an error returns.

Also inserting 'Sales Code' after the 'Select distinct t1.id'-part, wasn't the right way. By doing this the same price was given to every sales code.

Can you help me out?

Thx a lot!

|||

Try this:

select t1.id, t1.price, t1.[sales code]

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

and [Sales Code] = @.SalesCodeParameter

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

thx for your help. Your suggestion is working!

But I see that I've me an error by posting an example of the table. The field StartingDate is not only the date but also the time, it's the starting date and time. So the code-part of

WHERE([Starting Date Time]) < GETDATE

has to be something else. I have tried GETDATETIME, but that was not correct. How can I solve it?

Starting Date Time is a combined field. The fields Starting Date and Starting Time are also seperated available, if necessary.

Thx

|||

Try this:

WHERE([Starting Date Time]) < GETDATE()

Jarret

|||

Jarret,

I did that suggestion and it didn't worked out.

The problem is that the report is filtering only on date, not on date and time....

Do you have a suggestion?

Thx

|||

This will remove the time from the current date time.

WHERE([Starting Date Time]) < convert(varchar, GETDATE(), 101)

If you need to remove the time part off of [Starting Date Time], just wrap it in the convert statement as well.

Hope this helps.

Jarret

|||

Jarret,

More than one price for a date can be inserted In my table. I can insert a price and give it a starting time of 22.00 tonight. Of course I want to see the price of now if it is not yet 22.00.

I think I need more something like GETTIME or someting like that.

I was thinking of a statement with

WHERE(([Starting Date Time]) < GETDATE() and Starting Date Time]) < GETTIME()

in it.

But this one is not working.

Do you have any suggestion?

Thx

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> level
> each
>|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
>|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the leve
l
> that contains the data rows (clustered index) ? Also how many pages at eac
h
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.

> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
(Please reply only to the newsgroup)

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000
Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>
|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> level
> each
>
|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
>
|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the level
> that contains the data rows (clustered index) ? Also how many pages at each
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.

> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
(Please reply only to the newsgroup)

Find all chars in table that are ASCII code 128 and Greater

Does anyone know how to query a field in a table where it contains an
ASCII code >= 128 - without looping through every field for every
record in table (using charindex)?

Ex of char I would like to find: which is char(252)DennBen (dbenedett@.hotmail.com) writes:

Quote:

Originally Posted by

Does anyone know how to query a field in a table where it contains an
ASCII code >= 128 - without looping through every field for every
record in table (using charindex)?
>
Ex of char I would like to find: which is char(252)

select *
from tbl
where col COLLATE Latin1_General_BIN
LIKE '%[^' + char(32) + '-' + char(126) + ']%'

If you want to run this for many in columns in many tables, you
will to run the query once per column and table.

--
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

find a string within a string regardless of position within that string

I have a SQL query but need an extra column to output:
1234 if column D contains 'ABCD',
3456 if column D contains 'CDEF'
5678 if column D contains 'FGHI'
I have tried using CASE but I think that function needs an exact match
Can anyone help?
Thanks
Kay"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137450168.459219.295770@.f14g2000cwb.googlegroups.com...
>I have a SQL query but need an extra column to output:
> 1234 if column D contains 'ABCD',
> 3456 if column D contains 'CDEF'
> 5678 if column D contains 'FGHI'
> I have tried using CASE but I think that function needs an exact match
> Can anyone help?
> Thanks
> Kay
>
SELECT
CASE
WHEN d LIKE '%ABCD%' THEN 1234
WHEN d LIKE '%CDEF%' THEN 3456
WHEN d LIKE '%FGHI%' THEN 5678
END
FROM your_table ;
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
--|||Thanks guys, my syntax was slightly muddled!
Appreciate the help
Rgds
Kay|||SELECT
D,
[output] = CASE
WHEN CHARINDEX('ABCD', D)>0 THEN '1234'
WHEN CHARINDEX('CDEF', D)>0 THEN '3456'
WHEN CHARINDEX('FGHI', D)>0 THEN '5678'
ELSE '--not found--'
END
FROM your_table_name;
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137450168.459219.295770@.f14g2000cwb.googlegroups.com...
>I have a SQL query but need an extra column to output:
> 1234 if column D contains 'ABCD',
> 3456 if column D contains 'CDEF'
> 5678 if column D contains 'FGHI'
> I have tried using CASE but I think that function needs an exact match
> Can anyone help?
> Thanks
> Kay
>|||"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137452120.646322.28340@.g49g2000cwa.googlegroups.com...
> Thanks guys, my syntax was slightly muddled!
> Appreciate the help
> Rgds
> Kay
>
Perhaps you weren't muddled. There are two possible versions of the CASE
syntax. Maybe you were just thinking of the simple case version. The
following article has examples:
http://www.databasejournal.com/feat...cle.php/3288921
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
--

Filters in rdl.

Hi,
I've a big trouble. According the documentation, the <Filters> section of a
rdl file contains a collection of <Filter>. But there is no information how
this collection is handled. It seem that the datasource is filtered based on
every <Filter> with AND.
If you are in the Filters tab of a graph, you can add several <Filter>. The
"and/or" column is greyed and a default AND is used.
My question is how to change this to OR.
My filters have to be like this
City="New York" or
City="New Jersey"
Instead of
City="New York" and
City="New Jersey"
who will never return any rows.
Any idea ?
Thanks.
Regardsm
--
Stéphaneif you select the same column name from the drop down it assumes "OR" if you
select diferent column name it assumes "and"
For e.g
contact id = 1 and when you go to the second line and select contact id you
can see it turns to "or".
if you select AccountName then it turns to "and"
Amarnath
"Suedois" wrote:
> Hi,
> I've a big trouble. According the documentation, the <Filters> section of a
> rdl file contains a collection of <Filter>. But there is no information how
> this collection is handled. It seem that the datasource is filtered based on
> every <Filter> with AND.
> If you are in the Filters tab of a graph, you can add several <Filter>. The
> "and/or" column is greyed and a default AND is used.
> My question is how to change this to OR.
> My filters have to be like this
> City="New York" or
> City="New Jersey"
> Instead of
> City="New York" and
> City="New Jersey"
> who will never return any rows.
> Any idea ?
> Thanks.
> Regardsm
>
> --
> Stéphane|||Thanks for you reply.
Didi you know if it's possible to do an 'or' on different columns ?
I'll try you tips and see what is done in XML file.
Regards,
--
Stéphane
"Amarnath" wrote:
> if you select the same column name from the drop down it assumes "OR" if you
> select diferent column name it assumes "and"
> For e.g
> contact id = 1 and when you go to the second line and select contact id you
> can see it turns to "or".
> if you select AccountName then it turns to "and"
> Amarnath
>
> "Suedois" wrote:
> > Hi,
> >
> > I've a big trouble. According the documentation, the <Filters> section of a
> > rdl file contains a collection of <Filter>. But there is no information how
> > this collection is handled. It seem that the datasource is filtered based on
> > every <Filter> with AND.
> >
> > If you are in the Filters tab of a graph, you can add several <Filter>. The
> > "and/or" column is greyed and a default AND is used.
> >
> > My question is how to change this to OR.
> >
> > My filters have to be like this
> > City="New York" or
> > City="New Jersey"
> > Instead of
> > City="New York" and
> > City="New Jersey"
> > who will never return any rows.
> >
> > Any idea ?
> >
> > Thanks.
> >
> > Regardsm
> >
> >
> >
> > --
> > Stéphane

Filtering SqlDataSource to show all vs. non-null records

Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (?) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance.

Bill

Construct a better SELECT that uses a parameter.

SELECT ...

FROM ...

WHERE (@.ShowAll=1)

OR (@.ShowAll=0 AND (col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL))

|||Sweet. Thank you much.