A sql query question

Hi all
Could anyone tell me how can I construct an sql sentence so that it can show m
the latest readings for counters, given a date
To get the idea clear, let me show the table I have
Table: countin
ID COUNTER COUNTING_DAT
1 c1 01-01-0
2 c1 01-02-0
3 c1 01-03-0
4 c1 01-04-0
5 c1 01-05-0
6 c2 01-01-0
7 c2 01-02-0
I need the rows that have the very previous readings given a date, i.e. if I have myDate=01-04-04,
the result of the query should be all rows that have readings for the date immediately before or
equal to myDate
4 c1 01-04-0
7 c2 01-02-0
Thank you for all your time in advance
Marco.SELECT id, counter, counting_date
FROM Counting AS C
WHERE counting_date = (SELECT MAX(counting_date)
FROM Counting
WHERE counting_date <= @.MyDate
AND counter = C.counter)
--
David Portas
SQL Server MVP
--|||Try this:
SELECT A.Counter,
(SELECT TOP 1 Counting_date FROM Your_Table
WHERE Counting_date <= '1/4/2004'
AND Counter = A.Counter
ORDER BY Counting_date DESC)
FROM Your_Table A
GROUP BY A.Counter
--
Rohtash Kapoor
http://www.sqlmantra.com
"Oysterec" <anonymous@.discussions.microsoft.com> wrote in message
news:A13B76B6-5523-45DB-8C85-9F59B20A809C@.microsoft.com...
> Hi all,
> Could anyone tell me how can I construct an sql sentence so that it can
show me
> the latest readings for counters, given a date?
> To get the idea clear, let me show the table I have:
> Table: counting
> ID COUNTER COUNTING_DATE
> 1 c1 01-01-04
> 2 c1 01-02-04
> 3 c1 01-03-04
> 4 c1 01-04-04
> 5 c1 01-05-04
> 6 c2 01-01-04
> 7 c2 01-02-04
> I need the rows that have the very previous readings given a date, i.e. if
I have myDate=01-04-04,
> the result of the query should be all rows that have readings for the
date immediately before or
> equal to myDate:
> 4 c1 01-04-04
> 7 c2 01-02-04
>
> Thank you for all your time in advance,
> Marco.

A sql query question

Hi all,
Could anyone tell me how can I construct an sql sentence so that it can show
me
the latest readings for counters, given a date?
To get the idea clear, let me show the table I have:
Table: counting
ID COUNTER COUNTING_DATE
1 c1 01-01-04
2 c1 01-02-04
3 c1 01-03-04
4 c1 01-04-04
5 c1 01-05-04
6 c2 01-01-04
7 c2 01-02-04
I need the rows that have the very previous readings given a date, i.e. if I
have myDate=01-04-04,
the result of the query should be all rows that have readings for the date i
mmediately before or
equal to myDate:
4 c1 01-04-04
7 c2 01-02-04
Thank you for all your time in advance,
Marco.SELECT id, counter, counting_date
FROM Counting AS C
WHERE counting_date =
(SELECT MAX(counting_date)
FROM Counting
WHERE counting_date <= @.MyDate
AND counter = C.counter)
David Portas
SQL Server MVP
--|||Try this:
SELECT A.Counter,
(SELECT TOP 1 Counting_date FROM Your_Table
WHERE Counting_date <= '1/4/2004'
AND Counter = A.Counter
ORDER BY Counting_date DESC)
FROM Your_Table A
GROUP BY A.Counter
Rohtash Kapoor
http://www.sqlmantra.com
"Oysterec" <anonymous@.discussions.microsoft.com> wrote in message
news:A13B76B6-5523-45DB-8C85-9F59B20A809C@.microsoft.com...

quote:

---

> Hi all,
> Could anyone tell me how can I construct an sql sentence so that it can

---

show me

quote:

---

> the latest readings for counters, given a date?
> To get the idea clear, let me show the table I have:
> Table: counting
> ID COUNTER COUNTING_DATE
> 1 c1 01-01-04
> 2 c1 01-02-04
> 3 c1 01-03-04
> 4 c1 01-04-04
> 5 c1 01-05-04
> 6 c2 01-01-04
> 7 c2 01-02-04
> I need the rows that have the very previous readings given a date, i.e. if

---

I have myDate=01-04-04,

quote:

---

> the result of the query should be all rows that have readings for the

---

date immediately before or

quote:

---

> equal to myDate:
> 4 c1 01-04-04
> 7 c2 01-02-04
>
> Thank you for all your time in advance,
> Marco.

---

A Microsoft Native XML Database Someday?

Hi,
I was wondering if you might provide some idea of whether Microsoft is
considering building a native Xml database as Xml has become an important
part of so many of its products. I know about SQL 2005 and its method for
Xml handling, but couldn't that product family be expanded to include
something that is Xml only? Perhaps during the data-enhancements planned for
C#.
Thanks,
James
PS - Please give Anders a hug from us C# developers, he's a godsend.Hi James.
I doubt that we will ever create an XML only database system. But if you
give us more encouraging feedback, we will work on making SQL Server even
more usable as an XML database system :-).
Best regards
Michael
PS: We have forwarded the hugs to Anders :-).
"James White" <james@.rationalpath.com> wrote in message
news:u%23$PMrwrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if you might provide some idea of whether Microsoft is
> considering building a native Xml database as Xml has become an important
> part of so many of its products. I know about SQL 2005 and its method for
> Xml handling, but couldn't that product family be expanded to include
> something that is Xml only? Perhaps during the data-enhancements planned
> for
> C#.
> Thanks,
> James
> PS - Please give Anders a hug from us C# developers, he's a godsend.
>
>

A Microsoft Native XML Database Someday?

Hi,
I was wondering if you might provide some idea of whether Microsoft is
considering building a native Xml database as Xml has become an important
part of so many of its products. I know about SQL 2005 and its method for
Xml handling, but couldn't that product family be expanded to include
something that is Xml only? Perhaps during the data-enhancements planned for
C#.
Thanks,
James
PS - Please give Anders a hug from us C# developers, he's a godsend.
Hi James.
I doubt that we will ever create an XML only database system. But if you
give us more encouraging feedback, we will work on making SQL Server even
more usable as an XML database system :-).
Best regards
Michael
PS: We have forwarded the hugs to Anders :-).
"James White" <james@.rationalpath.com> wrote in message
news:u%23$PMrwrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if you might provide some idea of whether Microsoft is
> considering building a native Xml database as Xml has become an important
> part of so many of its products. I know about SQL 2005 and its method for
> Xml handling, but couldn't that product family be expanded to include
> something that is Xml only? Perhaps during the data-enhancements planned
> for
> C#.
> Thanks,
> James
> PS - Please give Anders a hug from us C# developers, he's a godsend.
>
>