finding first record ... !?

I have a table as follows:

- user_id (key)
- user_email_address (text)
- user_request_date (text)
- user_sent_date (time/date)

An automated macro will run every 5 minutes and needs to run a SQL UPDATE
that:

- finds the FIRST (ONE ROW ONLY) (youngest) record where
- user_request_date is the most recent
- AND user_sent_date is NULL (i.e. blank)

- THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
current system date/time)

For example:

- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL

SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)

- update record 33 - with NOW() time and date
THEN record 34, then 35, then 36, then 37
ONE ROW at a time per each SQL UPDATE

Any ideas!?
Thanks!
Richard

richard @. rcbuchanan . comRichard,

Try something like

update Richard set
user_sent_date = getdate()
where user_id = (
select top 1 user_id from Richard R
where R.user_sent_date is null
order by user_request_date
)

Your narrative and your example don't agree on whether
you want the most recent (youngest), or least recent (oldest)
user_request_date, so you may need to add DESC to the
order by clause.

Steve Kass
Drew University

Richard C Buchanan wrote:

>I have a table as follows:
>- user_id (key)
>- user_email_address (text)
>- user_request_date (text)
>- user_sent_date (time/date)
>An automated macro will run every 5 minutes and needs to run a SQL UPDATE
>that:
>- finds the FIRST (ONE ROW ONLY) (youngest) record where
> - user_request_date is the most recent
> - AND user_sent_date is NULL (i.e. blank)
> - THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
>current system date/time)
>
>For example:
>- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
>- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
>- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
>- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
>- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL
>SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)
>- update record 33 - with NOW() time and date
>THEN record 34, then 35, then 36, then 37
>ONE ROW at a time per each SQL UPDATE
>
>Any ideas!?
>Thanks!
>Richard
>richard @. rcbuchanan . com
>

finding duplicates

quick easy querstion:
I have an inventory table in a datbase and each record has
a serial number. what is the sql code to find duplicates.
tiaSee following example:
create table #cartype(manufacturer varchar(500), score int)
insert into #cartype values('Toyota', 1 )
insert into #cartype values('Toyota', 2)
insert into #cartype values('Toyota', 3)
insert into #cartype values('Honda', 2)
insert into #cartype values('Honda', 5)
insert into #cartype values('Honda', 8)
insert into #cartype values('GE', 1)
insert into #cartype values('GE', 3)
insert into #cartype values('GE', 9)
insert into #cartype values('GE1', 6)
Following query will give you manufacturers that are appearing more than
once.
select manufacturer from #cartype
group by manufacturer
having count(*) > 1
--Following is the query to get the complete row details
select a.* from #cartype a inner join
(select manufacturer from #cartype
group by manufacturer
having count(*) > 1) b
on a.manufacturer = b.manufacturer
--
-Vishal
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||Katie,
SELECT serial_number, COUNT(serial_number) [Count]
FROM dbo.inventory
GROUP BY serial_number
HAVING COUNT(serial_number) > 1
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||SELECT serial_number FROM inventory
GROUP BY serial_number
HAVING COUNT(*) > 1
And if you want the actual rows:
SELECT * FROM inventory i
INNER JOIN (
SELECT serial_number FROM inventory
GROUP BY serial_number
HAVING COUNT(*) > 1) AS j
ON i.serial_number = j.serial_number
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"katie" <kari@.wom3c.com> wrote in message
news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> quick easy querstion:
> I have an inventory table in a datbase and each record has
> a serial number. what is the sql code to find duplicates.
> tia|||not very sure but,
I think you are using wrong column name, make sure all the column names that
you use in the query exists in the table.(may be some spelling mistake)
--
-Vishal
"katie" <kadf@.hsd.com> wrote in message
news:00c801c35062$c8f3e4f0$a101280a@.phx.gbl...
> i am doing this in access and it asks me to put a
> parameter value in. what does this mean
> >--Original Message--
> >Katie,
> >
> >SELECT serial_number, COUNT(serial_number) [Count]
> >FROM dbo.inventory
> >GROUP BY serial_number
> >HAVING COUNT(serial_number) > 1
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"katie" <kari@.wom3c.com> wrote in message
> >news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> >> quick easy querstion:
> >> I have an inventory table in a datbase and each record
> has
> >> a serial number. what is the sql code to find
> duplicates.
> >>
> >> tia
> >
> >
> >.
> >|||I think square brackets are used in Access to indicate a parameter, so if
you leave them of you should be ok. Note that I last used Access quite a few
years ago, so don't bank on it :-)
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:u7VqUTGUDHA.1912@.tk2msftngp13.phx.gbl...
> not very sure but,
> I think you are using wrong column name, make sure all the column names
that
> you use in the query exists in the table.(may be some spelling mistake)
> --
> -Vishal
> "katie" <kadf@.hsd.com> wrote in message
> news:00c801c35062$c8f3e4f0$a101280a@.phx.gbl...
> > i am doing this in access and it asks me to put a
> > parameter value in. what does this mean
> > >--Original Message--
> > >Katie,
> > >
> > >SELECT serial_number, COUNT(serial_number) [Count]
> > >FROM dbo.inventory
> > >GROUP BY serial_number
> > >HAVING COUNT(serial_number) > 1
> > >
> > >--
> > >Dinesh.
> > >SQL Server FAQ at
> > >http://www.tkdinesh.com
> > >
> > >"katie" <kari@.wom3c.com> wrote in message
> > >news:070a01c3505f$74b74970$a001280a@.phx.gbl...
> > >> quick easy querstion:
> > >> I have an inventory table in a datbase and each record
> > has
> > >> a serial number. what is the sql code to find
> > duplicates.
> > >>
> > >> tia
> > >
> > >
> > >.
> > >
>

Finding Consecutive Values

I'm stuck in trying to find out how to solve this.
Within a record set, I need to know when a id has been a certain value
for x consecutive years. For example,
Create table #Test (
tid int NOT NULL,
yr int NOT NULL,
value int NOT NULL)
insert into #Test values (100, 1998, 0)
insert into #Test values (100, 1999, 0)
insert into #Test values (100, 2000, 0)
insert into #Test values (100, 2001, 0)
insert into #Test values (100, 2002, 1)
insert into #Test values (100, 2003, 0)
insert into #Test values (100, 2004, 0)
insert into #Test values (100, 2005, 0)
insert into #Test values (100, 2006, 0)
insert into #Test values (100, 2007, 0)
insert into #Test values (100, 2008, 1)
insert into #Test values (200, 1999, 0)
insert into #Test values (200, 2001, 0)
insert into #Test values (200, 2002, 0)
insert into #Test values (300, 2001, 0)
insert into #Test values (300, 2002, 0)
insert into #Test values (300, 2003, 0)
insert into #Test values (300, 2004, 0)
insert into #Test values (300, 2005, 0)
FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
months.
RESULTS would yield
tid startdate enddate years
100 1998 2001 4
100 2003 2007 5
300 2001 2005 5
TIAselect t_from.tid, t_from.yr start_year, t_to.yr end_year, t_from.value
from
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr+1))
)t_from
join
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr-1))
)t_to
on t_from.tid=t_to.tid and t_from.value=t_to.value and
(t_from.yr+3)<=t_to.yr
and t_from.yr + (select count(*) from Test t where t_from.tid=t.tid
and t_from.yr<t.yr and t.yr<t_to.yr and t_from.value=t.value) + 1 =
t_to.yr
order by t_from.tid, t_from.yr
tid start_year end_year value
-- -- -- --
100 1998 2001 0
100 2003 2007 0
300 2001 2005 0
(3 row(s) affected)|||select tid, min(yr) as start, maxyr as [end], count(*) as yrs
from (
select *,
isnull(
(select max(yr)
from #test t2
where t2.tid=t1.tid
and t2.yr>=t1.yr
and t2.value=0
and t2.yr<=(select min(yr) from #test where tid=t2.tid and
yr>=t1.yr and value=1)
),
(select max(yr)
from #test t3
where t3.tid=t1.tid
)
) as maxyr
from #test t1
where value=0
) t4
group by tid, maxyr
having count(*)>=4
order by tid, start
carmaboy@.gmail.com wrote:
> I'm stuck in trying to find out how to solve this.
> Within a record set, I need to know when a id has been a certain value
> for x consecutive years. For example,
> Create table #Test (
> tid int NOT NULL,
> yr int NOT NULL,
> value int NOT NULL)
> insert into #Test values (100, 1998, 0)
> insert into #Test values (100, 1999, 0)
> insert into #Test values (100, 2000, 0)
> insert into #Test values (100, 2001, 0)
> insert into #Test values (100, 2002, 1)
> insert into #Test values (100, 2003, 0)
> insert into #Test values (100, 2004, 0)
> insert into #Test values (100, 2005, 0)
> insert into #Test values (100, 2006, 0)
> insert into #Test values (100, 2007, 0)
> insert into #Test values (100, 2008, 1)
> insert into #Test values (200, 1999, 0)
> insert into #Test values (200, 2001, 0)
> insert into #Test values (200, 2002, 0)
> insert into #Test values (300, 2001, 0)
> insert into #Test values (300, 2002, 0)
> insert into #Test values (300, 2003, 0)
> insert into #Test values (300, 2004, 0)
> insert into #Test values (300, 2005, 0)
>
> FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
> months.
> RESULTS would yield
> tid startdate enddate years
> 100 1998 2001 4
> 100 2003 2007 5
> 300 2001 2005 5
> TIA
>|||A different, shorter version:
DROP TABLE Foobar;
CREATE TABLE Foobar
(tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL);
INSERT INTO Foobar VALUES (100, 1998, 0);
INSERT INTO Foobar VALUES (100, 1999, 0);
INSERT INTO Foobar VALUES (100, 2000, 0);
INSERT INTO Foobar VALUES (100, 2001, 0);
INSERT INTO Foobar VALUES (100, 2002, 1);
INSERT INTO Foobar VALUES (100, 2003, 0);
INSERT INTO Foobar VALUES (100, 2004, 0);
INSERT INTO Foobar VALUES (100, 2005, 0);
INSERT INTO Foobar VALUES (100, 2006, 0);
INSERT INTO Foobar VALUES (100, 2007, 0);
INSERT INTO Foobar VALUES (100, 2008, 1);
INSERT INTO Foobar VALUES (200, 1999, 0);
INSERT INTO Foobar VALUES (200, 2001, 0);
INSERT INTO Foobar VALUES (200, 2002, 0);
INSERT INTO Foobar VALUES (300, 2001, 0);
INSERT INTO Foobar VALUES (300, 2002, 0);
INSERT INTO Foobar VALUES (300, 2003, 0);
INSERT INTO Foobar VALUES (300, 2004, 0);
INSERT INTO Foobar VALUES (300, 2005, 0);
SELECT X.tid, MIN(X.yr), X.end_yr, (X.end_yr - MIN(X.yr)) AS duration
FROM
(SELECT F1.tid, F1.yr, MAX(F2.yr) AS end_yr
FROM Foobar AS F1, Foobar AS F2
WHERE F1.yr < F2.yr
AND F1.tid = F2.tid
AND 0
= ALL (SELECT value
FROM Foobar AS F3
WHERE F1.tid = F3.tid
AND F3.yr BETWEEN F1.yr AND F2.yr)
AND (F2.yr - F1.yr +1)
= (SELECT COUNT(*)
FROM Foobar AS F4
WHERE F1.tid = F4.tid
AND F4.yr BETWEEN F1.yr AND F2.yr)
GROUP BY F1.tid, F1.yr) AS X(tid, yr, end_yr)
GROUP BY X.tid, X.end_yr
HAVING (X.end_yr - MIN(X.yr)) > 1;|||A thoughful solution that leaves one to ponder the rationality
of the methodology and of the environment that spawned it :)
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e5k25kf9FHA.2844@.TK2MSFTNGP10.phx.gbl...
> select tid, min(yr) as start, maxyr as [end], count(*) as yrs
> from (
> select *,
> isnull(
> (select max(yr)
> from #test t2
> where t2.tid=t1.tid
> and t2.yr>=t1.yr
> and t2.value=0
> and t2.yr<=(select min(yr) from #test where tid=t2.tid and
> yr>=t1.yr and value=1)
> ),
> (select max(yr)
> from #test t3
> where t3.tid=t1.tid
> )
> ) as maxyr
> from #test t1
> where value=0
> ) t4
> group by tid, maxyr
> having count(*)>=4
> order by tid, start
>
> carmaboy@.gmail.com wrote:|||this problem would be solved like that? ;)
CREATE TABLE Puzzles..YearVals(
tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL,
PRIMARY KEY (tid, yr));
INSERT INTO Puzzles..YearVals VALUES (100, 1998, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 1999, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2000, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2002, 1);
INSERT INTO Puzzles..YearVals VALUES (100, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2005, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2006, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2007, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2008, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 1999, 0);
--INSERT INTO Puzzles..YearVals VALUES (200, 2000, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (200, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2005, 0);
SELECT tid, MIN(yr) AS beg_yr, MAX(yr) AS fin_yr, COUNT(*) AS tally
FROM
(SELECT B.tid,B.yr,B.yr-COUNT(*)
FROM Puzzles..YearVals AS B
JOIN Puzzles..YearVals AS L
ON L.tid = B.tid AND L.yr <= B.yr AND L.value=B.value
WHERE B.value = 0
GROUP BY B.tid,B.yr) AS G(tid,yr,grp)
GROUP BY grp,tid
HAVING COUNT(*) >= 4|||rrr... i did not remove a reference on my local db
sorry|||Thanks to everyone for taking the time to help. I should have all I
need to get my results.

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MGWhy is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
David Portas

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
Why is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
David Portas
sql

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MGWhy is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MG|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
--
David Portas

Find values from record that had a max value in a group by

I am looking for the MS SQL variant of the oracle 'KEEP DENSE_RANK' construction.

If you have a table like this:

key date item 1 20070101 Apple 1 20070202 Banana 1 20070303 Cherry

The query for retrieving the item from the last date should pick the Cherry, but what would be the most efficient way to do this (without using multiple queries, in oracle I don't need a sub query!).

You would like to do something like:

Select Key, max(Date) as LastDate, PickLastValue(Cherry) on Date desc

from Table

group by Key.

any suggestions?

hans

You can do the same funciton in SQL Server 2005..

Code Snippet

Create Table #data (

[key] Varchar(100) ,

[date] Varchar(100) ,

[item] Varchar(100)

);

Insert Into #data Values('1','20070101','Apple');

Insert Into #data Values('1','20070202','Banana');

Insert Into #data Values('1','20070303','Cherry');

Insert Into #data Values('2','20070101','Apple');

Insert Into #data Values('2','20070202','Banana');

Code Snippet

--Over all

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Order By [date] Desc) rank From #Data

)

Select [key],[date],[item] from CTE Where rank=1

Code Snippet

--For each Key

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Partition By [Key] Order By [date] Desc) rank From #Data

)

Select [key],[date],[item],rank from CTE Where rank=1

|||

If you use sql server 2000 then you have to use the subquery...

Code Snippet

--For Overall

Select * from #data Where [date] in (Select max(date) From #data)

--For Each Key

Select * from #data Data

Join (Select [Key], max(date) date From #data Group By [Key]) Sub On

Sub.[Key]=Data.[Key] and Sub.date=Data.date

|||

This will do!

thanx

hans

Find number of records in text file.

Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
[vbcol=seagreen]
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL serve
r.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Find number of records in text file.

Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Hi All,
> >
> > Is there a way to find out how many records (rows) in the text file in
> > sql. ( It is a fix lengh record)
> >
> > Example: I have a text file in my local machine
> >
> > c:\test\t1.txt
> > c:\test\t2.txt
> >
> > in t1.txt I have these rows:
> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >
> > and t2.txt have this rows:
> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >
> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >
> > Please let me know. Thanks in advance
> >
> > Teed Lee
> >
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > Hi All,
>> >
>> > Is there a way to find out how many records (rows) in the text file in
>> > sql. ( It is a fix lengh record)
>> >
>> > Example: I have a text file in my local machine
>> >
>> > c:\test\t1.txt
>> > c:\test\t2.txt
>> >
>> > in t1.txt I have these rows:
>> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
>> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
>> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
>> >
>> > and t2.txt have this rows:
>> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
>> >
>> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
>> >
>> > Please let me know. Thanks in advance
>> >
>> > Teed Lee
>> >
>> One way would be to use OPENROWSET, documented in Books Online, and lots
>> of samples online.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;HDR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Thank You,
> > I try this statement
> >
> > select count(*)
> > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> > Test.txt')
> >
> > but when I try to run your statement I got this error:
> >
> > OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> > returned message "Unspecified error".
> > Msg 7303, Level 16, State 1, Line 1
> > Cannot initialize the data source object of OLE DB provider
> > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> >
> > The different between your statement vs. mine is "my file is on the
> > network"
> > Do I have to setup ODBC connect on the server where the file locate or
> > somehting.
> > Would you please tell me what else I need to setup or what do I do
> > wrong here.
> >
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > I work fine on the local account. On the local account I have to setup
> > the "SQL Server Surface Area Configuration"
> >
> > The problem I have is:
> > my sql server is on different machine Server "MachineA"
> > my files is on difrrent server "MachineB"
> >
> > I am login as my account and I am able to get in (permission) to both
> > server.
> >
> > On the "MachineA" do I have to set something to connect to "MachineB"
> > or
> > On the MachineB server do I have to set something
> > ('Microsoft.Jet.OLEDB.4.0')?
> > I am not sure. Please help.
> >
> > Teed
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL server.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Okay...but that doesn't make sense. Looks like we're losing
things in translation here.
Try this. Open up Enterprise Manager. Select the server.
Right click and select properties. Then click on the
security tab. In the bottom of the screen on the security
tab, there is a section named Start up service account.
Which one of the two is selected - System Account or This
Account? It can only be one of the two.
-Sue
On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
>Hi,
>Yes,
>I login (domain\username) is part of the group (domain\groupname) in
>the SQL service account and also my login (domain\username) also have
>permission to access that network share.
>Teed
>
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > I work fine on the local account. On the local account I have to setup
>> > the "SQL Server Surface Area Configuration"
>> >
>> > The problem I have is:
>> > my sql server is on different machine Server "MachineA"
>> > my files is on difrrent server "MachineB"
>> >
>> > I am login as my account and I am able to get in (permission) to both
>> > server.
>> >
>> > On the "MachineA" do I have to set something to connect to "MachineB"
>> > or
>> > On the MachineB server do I have to set something
>> > ('Microsoft.Jet.OLEDB.4.0')?
>> > I am not sure. Please help.
>> >
>> > Teed
>> Huh? I repeat my question - "Does the SQL service account have
>> permission to access that network share?"
>> If I understand your response, it sounds like you have SQL running under
>> the "Local System" context. If so, you cannot access network resources
>> this way - SQL Server MUST run under a domain account, and that domain
>> account MUST have permission to access the desired network resources.
>> This has nothing to do with the login that you use to connect to SQL server.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, if I login I login as "sa", then I am able
to run the query find.
I get my NT login does not have enough persimision on the server
somewhere right?
Thanks for your time and help.
Teed
Sue Hoegemeier wrote:
> Okay...but that doesn't make sense. Looks like we're losing
> things in translation here.
> Try this. Open up Enterprise Manager. Select the server.
> Right click and select properties. Then click on the
> security tab. In the bottom of the screen on the security
> tab, there is a section named Start up service account.
> Which one of the two is selected - System Account or This
> Account? It can only be one of the two.
> -Sue
> On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >Hi,
> >Yes,
> >
> >I login (domain\username) is part of the group (domain\groupname) in
> >the SQL service account and also my login (domain\username) also have
> >permission to access that network share.
> >
> >Teed
> >
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > I work fine on the local account. On the local account I have to setup
> >> > the "SQL Server Surface Area Configuration"
> >> >
> >> > The problem I have is:
> >> > my sql server is on different machine Server "MachineA"
> >> > my files is on difrrent server "MachineB"
> >> >
> >> > I am login as my account and I am able to get in (permission) to both
> >> > server.
> >> >
> >> > On the "MachineA" do I have to set something to connect to "MachineB"
> >> > or
> >> > On the MachineB server do I have to set something
> >> > ('Microsoft.Jet.OLEDB.4.0')?
> >> > I am not sure. Please help.
> >> >
> >> > Teed
> >>
> >> Huh? I repeat my question - "Does the SQL service account have
> >> permission to access that network share?"
> >>
> >> If I understand your response, it sounds like you have SQL running under
> >> the "Local System" context. If so, you cannot access network resources
> >> this way - SQL Server MUST run under a domain account, and that domain
> >> account MUST have permission to access the desired network resources.
> >> This has nothing to do with the login that you use to connect to SQL server.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, at my local machine I bring up the "SQL
Server Management Studio" then connect to that dabase as "sa", then I
am able to run the query fine. Also if I remote login that server as as
Administrator, then I am able to run the query fine, but at my local
machine I bring up the "SQL Server Management Studio" and login that
database as my "NT domain username" then I got error message above.
Thanks for your time and help. I am new to this.
ntuyen01@.yahoo.com wrote:
> Teed
>
> Sue Hoegemeier wrote:
> > Okay...but that doesn't make sense. Looks like we're losing
> > things in translation here.
> > Try this. Open up Enterprise Manager. Select the server.
> > Right click and select properties. Then click on the
> > security tab. In the bottom of the screen on the security
> > tab, there is a section named Start up service account.
> > Which one of the two is selected - System Account or This
> > Account? It can only be one of the two.
> >
> > -Sue
> >
> > On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >
> > >Hi,
> > >Yes,
> > >
> > >I login (domain\username) is part of the group (domain\groupname) in
> > >the SQL service account and also my login (domain\username) also have
> > >permission to access that network share.
> > >
> > >Teed
> > >
> > >
> > >Tracy McKibben wrote:
> > >> ntuyen01@.yahoo.com wrote:
> > >> > I work fine on the local account. On the local account I have to setup
> > >> > the "SQL Server Surface Area Configuration"
> > >> >
> > >> > The problem I have is:
> > >> > my sql server is on different machine Server "MachineA"
> > >> > my files is on difrrent server "MachineB"
> > >> >
> > >> > I am login as my account and I am able to get in (permission) to both
> > >> > server.
> > >> >
> > >> > On the "MachineA" do I have to set something to connect to "MachineB"
> > >> > or
> > >> > On the MachineB server do I have to set something
> > >> > ('Microsoft.Jet.OLEDB.4.0')?
> > >> > I am not sure. Please help.
> > >> >
> > >> > Teed
> > >>
> > >> Huh? I repeat my question - "Does the SQL service account have
> > >> permission to access that network share?"
> > >>
> > >> If I understand your response, it sounds like you have SQL running under
> > >> the "Local System" context. If so, you cannot access network resources
> > >> this way - SQL Server MUST run under a domain account, and that domain
> > >> account MUST have permission to access the desired network resources.
> > >> This has nothing to do with the login that you use to connect to SQL server.
> > >>
> > >>
> > >> --
> > >> Tracy McKibben
> > >> MCDBA
> > >> http://www.realsqlguy.com

find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
--
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
--
David Portas
SQL Server MVP
--

Find max entry in table

I would like to select the highest id in a table for each computer record.

example data:
ID Computer name
24 computer1
23 computer1
22 computer2
24 computer3

The max id is not going to be the same for every computer.

When the script below is run, I receive the highest number only. All IDs with 24 will be listed. I need all the computer names and the highest ID they are associated with. In the case above, 24 computer1 should be on the list and 23 computer1 should not.

Thanks,

Script...

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
where s.id_snapshot = (Select MAX (id_snapshot)
From servers)Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||mpereziii, your query should work too ... Do you have any issues?

Originally posted by rdjabarov
Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||With the original query mpereziii was getting only rows where id_snapshot was equal to the maximum value in the table.|||Thanks for the response.
I adjusted my script with your suggestion. I received too many rows. 424,000 rows and it searched for 18sec.

The query returned every id_snapshot for every server_name (computer name).

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid|||How about this:

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select server_name, sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid and s.server_name = maxsid.server_name|||Thank you sjp. The query now works very well. I really appreciate everyones input. Thank you.

Find Match Count

is there anyway i can find match count in a field record. for instance:
SELECT CountText(Content, "@.") AS Found FROM tb_Messages
which would return something like this:
RecordID Found
-- --
1 3
2 8
3 10
4 30
4 records affected.
where:
Content is the column containing some text.
@. is a character that i am searching for.
Found is total match found
tb_Messages is the table
and CountText is the supposed founction that counts the occurrence.
Anyhelp would be appreciated.
TascienWrite a user defined function, that takes the parameters you detailed below.
The udf should contain a loop, use the string functions CHARINDEX and
SUBSTRING, and return the count.
<tascienu@.ecoaches.com> wrote in message
news:1133131413.601235.276130@.z14g2000cwz.googlegroups.com...
> is there anyway i can find match count in a field record. for instance:
> SELECT CountText(Content, "@.") AS Found FROM tb_Messages
> which would return something like this:
> RecordID Found
> -- --
> 1 3
> 2 8
> 3 10
> 4 30
> 4 records affected.
> where:
> Content is the column containing some text.
> @. is a character that i am searching for.
> Found is total match found
> tb_Messages is the table
> and CountText is the supposed founction that counts the occurrence.
> Anyhelp would be appreciated.
> Tascien
>|||(tascienu@.ecoaches.com) writes:
> is there anyway i can find match count in a field record. for instance:
> SELECT CountText(Content, "@.") AS Found FROM tb_Messages
> which would return something like this:
> RecordID Found
> -- --
> 1 3
> 2 8
> 3 10
> 4 30
> 4 records affected.
> where:
> Content is the column containing some text.
> @. is a character that i am searching for.
> Found is total match found
> tb_Messages is the table
> and CountText is the supposed founction that counts the occurrence.
This should work:
SELECT CountText = datalength(Content) -
datalength(Replace(Content, '@.', '')))
FROM tb_MEssages
If your columns are declared as nvarchar/nchar/ntext, you need to divide
result by 2, as datalength returns the number of bytes.
(Some people might want to use len() here, but that will not fly.
Using len() we would return a incorrect result for a string like:
'This is @. a test string @.'
as len() does not count trailing blanks.)
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|||On 27 Nov 2005 14:43:33 -0800, tascienu@.ecoaches.com wrote:

>is there anyway i can find match count in a field record. for instance:
>SELECT CountText(Content, "@.") AS Found FROM tb_Messages
>which would return something like this:
>RecordID Found
>-- --
>1 3
>2 8
>3 10
>4 30
>4 records affected.
>
(snip)
Hi Tascien,
Try this:
SELECT RecordID,
LEN(Content) - LEN(REPLACE(Content, '@.', '')) AS Found
FROM tb_Messages
(untested - see www.aspfaq.com/5006 if you prefer a tested reply, or if
I misunderstood your requirements)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 27 Nov 2005 23:02:24 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>(Some people might want to use len() here, but that will not fly.
>Using len() we would return a incorrect result for a string like:
> 'This is @. a test string @.'
>as len() does not count trailing blanks.)
Hi Erland,
This is the first time that an error in one of my post is corrected even
before I managed to post it. You're really quick today!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wonderful. Yes, these suggestions really work... I think I am going
with DataLength.
Thanks everyone...
T.

find last record message

I have a part software package and I would like to get a message from SQL
sent to the user. I want the SQL message to be sent every time the user
creates a new part. The message will say the last part number is "X", X
beiing the last partnumber added in the part table, there is a field in the
table for date created.
Any idea on how to do that ?, is trigger be used ?Throwing a message back to the user from a trigger is an ugly hack. If the
part is being inserted via a stored procedure and the part number is an auto
incremented identity value, then just return SCOPE_IDENTITY() using an
output parameter.
"FRED" <FRED@.discussions.microsoft.com> wrote in message
news:FFDA08E6-8031-4099-9903-75515AE4AC7A@.microsoft.com...
> I have a part software package and I would like to get a message from SQL
> sent to the user. I want the SQL message to be sent every time the user
> creates a new part. The message will say the last part number is "X", X
> beiing the last partnumber added in the part table, there is a field in
the
> table for date created.
> Any idea on how to do that ?, is trigger be used ?
>

find and replace

To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?

For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.

So you want to search all user tables for the value to be deleted? It's hard to control which tables to be search and it will be a huge workload when there are many tables. If you just want to maintenance the Reference Integrity, why not use constraints? You can create PK/FK between the tables, and cascading changes on the referencing columns. For more information about this, you can refer to:

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

Find a newly inserted record from table without using MAX or TOP

i have a table employee and i want to find the newly inserted record from employee table without using MAX And TOP ...it possible ? yes then How?

Quote:

Originally Posted by Ripendra007

i have a table employee and i want to find the newly inserted record from employee table without using MAX And TOP ...it possible ? yes then How?

i'm assuming you're using an identity column as the primary key on your table. if so you can say:

SELECT @.@.IDENTITY

your development environment might also provide this information in a more seamless way

Find a date that occurs every 2 days from a fixed date

Hi
I am trying to write a script that only sums the values in a column if the
date for the record occurs every 2 days after another date. The trouble is,
it is a recurring 2 day cycle. i.e Only sum the values if the record date is
every 2nd day from the first date.
eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
05/03, 07/04 etc. If the record does not occur every second day, it should
return 0.
I can use datediff to get the difference and I can cast it into a decimal
and divide by 2 but can't get it to validate in the statement.
Is there an easier way?Mark,
Post your DDL & T-SQL ?
Robert
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
> Hi
> I am trying to write a script that only sums the values in a column if the
> date for the record occurs every 2 days after another date. The trouble
> is,
> it is a recurring 2 day cycle. i.e Only sum the values if the record date
> is
> every 2nd day from the first date.
> eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
> 05/03, 07/04 etc. If the record does not occur every second day, it should
> return 0.
> I can use datediff to get the difference and I can cast it into a decimal
> and divide by 2 but can't get it to validate in the statement.
> Is there an easier way?
>|||Here is the short version of a complex query:
select guest.arrival, multirate.room_type, multirate.rate_date,
count (multirate.room_type)
from guest, multirate
where guest.property=multirate.property and guest.account=multirate.account
and
multirate.rate_date='xxxxx'
The problem is this:
Assume the guest.arrival='2006-03-01'
If the multirate.rate_date in the where statment is '2006-03-02', it must
ignore the record from the count because the datediff between 02/03 and 01/0
3
is not a multiple of 2.
If the multitrate.rate_date='2006-03-03', it must count the record into the
result because it occurs 2 days after the arrival.
The multirate.rate_date will be a variable passed to the statement, so the
query must be able to validate that the rate_date has a multiple of 2
difference between the rate_date and arrival.
Does this make sense?
"Robert Ellis" wrote:

> Mark,
> Post your DDL & T-SQL ?
> Robert
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
>
>|||Mark:
Does the following example help? There may be other ways to do it.
IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
CREATE TABLE blah
(
BlahGroup CHAR(2) NOT NULL,
FirstDate DATETIME NOT NULL,
SecondDate DATETIME NOT NULL
)
GO
SET DATEFORMAT YMD
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-02'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-06'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-07'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-06'
GO
SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
FROM blah b
WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2) =
(FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
GROUP BY b.BlahGroup
GO
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
> Here is the short version of a complex query:
> select guest.arrival, multirate.room_type, multirate.rate_date,
> count (multirate.room_type)
> from guest, multirate
> where guest.property=multirate.property and
> guest.account=multirate.account
> and
> multirate.rate_date='xxxxx'
> The problem is this:
> Assume the guest.arrival='2006-03-01'
> If the multirate.rate_date in the where statment is '2006-03-02', it must
> ignore the record from the count because the datediff between 02/03 and
> 01/03
> is not a multiple of 2.
> If the multitrate.rate_date='2006-03-03', it must count the record into
> the
> result because it occurs 2 days after the arrival.
> The multirate.rate_date will be a variable passed to the statement, so the
> query must be able to validate that the rate_date has a multiple of 2
> difference between the rate_date and arrival.
> Does this make sense?
> "Robert Ellis" wrote:
>|||Additionally:
obviously you would add something like:
AND (FirstDate = @.ParamDate)
to the 'WHERE' clause for your implementation...
cheers,
Robert
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>|||% modulus
argh
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>