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

Hi;
is there a function to add quotes to a string sentence?; like this
exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
if @.NAME is only William, i need to add quotes to it to get a sentence like:
Select * from Customers where Name='William' and AGECUST > 25
is there a function to do that?There is no special function for that. you can do in 2 ways.
1. use double quotes and set quote identifier off so that you can use both
double and single quotes.
2. for using single quote in your string you need to put one more single
quote.
ie taking your e.g
exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST > 25')
please note it looks like double quote it is not, it is 2 single quotes.
Try this.
Amarnath, MCTS
"Willo" wrote:
> Hi;
> is there a function to add quotes to a string sentence?; like this
> exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
> if @.NAME is only William, i need to add quotes to it to get a sentence like:
> Select * from Customers where Name='William' and AGECUST > 25
> is there a function to do that?
>
>
>|||"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> There is no special function for that. you can do in 2 ways.
> 1. use double quotes and set quote identifier off so that you can use both
> double and single quotes.
where can i set that?
> 2. for using single quote in your string you need to put one more single
> quote.
> ie taking your e.g
> exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> 25')
> please note it looks like double quote it is not, it is 2 single quotes.
>
i got a syntax error here|||You need to set in the data tab itself. e.g
SET QUOTED_IDENTIFIER OFF
exec
("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
AGECUST > 25")
Just paste this in your data tab it will work. ps: To make it clear I have
left space in between the double quotes. once you get the idea you can remove
the space. Just to check whether the sql query is correct just replace "exec"
with "select"
you will get the full query itself for you to check.
Amarnath, MCTS
"Willo" wrote:
> "Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
> news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> > There is no special function for that. you can do in 2 ways.
> > 1. use double quotes and set quote identifier off so that you can use both
> > double and single quotes.
> where can i set that?
> > 2. for using single quote in your string you need to put one more single
> > quote.
> > ie taking your e.g
> >
> > exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> > 25')
> > please note it looks like double quote it is not, it is 2 single quotes.
> >
> i got a syntax error here
>
>
>|||Thank! Amarnath, works great.
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:41C16D7C-954B-43D6-A165-1AC2D887AD3F@.microsoft.com...
> You need to set in the data tab itself. e.g
> SET QUOTED_IDENTIFIER OFF
> exec
> ("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
> AGECUST > 25")
> Just paste this in your data tab it will work. ps: To make it clear I have
> left space in between the double quotes. once you get the idea you can
> remove
> the space. Just to check whether the sql query is correct just replace
> "exec"
> with "select"
> you will get the full query itself for you to check.
> Amarnath, MCTS

A query sentence return a puzzle result

the talbe row like this:

ID Name Scoe
11 Tome 20
12 Jack 30
11 Tome 40
12 Jack 10
13 John 10

My query command like this:

Select T1.Id,T1.Name,T2.math
from st T1
right join
(Select Id as Id2,Sum(Math) as Math from St group by id) T2
on T1.id=t2.id2
where t1.id = t2.id2

While the reuslt is :

Id Name Score

11 Tom 60

11 Tom 60

12 Jake 40

12 Jack 40

13 John 10

I am wonder :the T1 gives a table with six rows, the T2 gives a table with three rows, and I use RIGHT JOIN to connect the two table,the result should be a table with only three rows.I tried INNER JOIN, the result is same.

but why ? please help me !

This might be what you want?

select ID, Name, Sum(Score) as Math from St group by ID, Name

result;

ID Name Math ---- ---------------- ---- 12 Jack 4013 John 1011 Tome 60(3 row(s) affected)