Query between two dates applying grouping

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am trying to make an sql query that finds data between two dates a user
enters. The data needs to be grouped to eliminate nonunique entries.

This is the SQL I tried:

SELECT taskowner, sum(hours) as sumofhours FROM Results where Date >=
'::startdate::' AND Date <= '::enddate::' group by taskowner order by
taskowner

startdate and enddate are dates entered by the user prior to pushing a
submit button at which point the query is generated. This code does not
work. What is the problem?

Thanks
 
Date is a reserved word - date() is a function that returns today's
date.
You are effectively asking for today's date between two other dates -
which is nonsense.
Either rename the column in the database from Date to opDate (or
something else),
or place [] brackets around Date:

SELECT taskowner, sum(hours) as sumofhours FROM Results where [Date]
=
Click to expand...
'::startdate::' AND [Date] <= '::enddate::' group by taskowner order
by taskowner
 
The following sql code does work

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
#1/8/2005# AND perDate <= #31/8/2005# group by taskowner order by taskowner

But the following sql does not work

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
::startdate:: AND perDate <= ::enddate:: group by taskowner order by taskowner

I think it is because of the startdate and enddate parameter that the user
enters but I can not figure out how to remedy the problem.

Thanks


Ronx said:
Date is a reserved word - date() is a function that returns today's
date.
You are effectively asking for today's date between two other dates -
which is nonsense.
Either rename the column in the database from Date to opDate (or
something else),
or place [] brackets around Date:

SELECT taskowner, sum(hours) as sumofhours FROM Results where [Date]
=
Click to expand...
'::startdate::' AND [Date] <= '::enddate::' group by taskowner order
by taskowner

--
Ron Symonds
Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

Mark said:
Hi

I am trying to make an sql query that finds data between two dates a
user
enters. The data needs to be grouped to eliminate nonunique
entries.

This is the SQL I tried:

SELECT taskowner, sum(hours) as sumofhours FROM Results where Date
'::startdate::' AND Date <= '::enddate::' group by taskowner order
by
taskowner

startdate and enddate are dates entered by the user prior to pushing
a
submit button at which point the query is generated. This code does
not
work. What is the problem?

Thanks
Click to expand...
Click to expand...
 
You need to modify the query, like this:

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
#::startdate::# AND perDate <= #::enddate::# group by taskowner order by
taskowner

See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;284843&Product=fp2002

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/


Mark said:
The following sql code does work

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
#1/8/2005# AND perDate <= #31/8/2005# group by taskowner order by
taskowner

But the following sql does not work

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
::startdate:: AND perDate <= ::enddate:: group by taskowner order by
taskowner

I think it is because of the startdate and enddate parameter that the user
enters but I can not figure out how to remedy the problem.

Thanks


Ronx said:
Date is a reserved word - date() is a function that returns today's
date.
You are effectively asking for today's date between two other dates -
which is nonsense.
Either rename the column in the database from Date to opDate (or
something else),
or place [] brackets around Date:

SELECT taskowner, sum(hours) as sumofhours FROM Results where [Date]
=
Click to expand...
'::startdate::' AND [Date] <= '::enddate::' group by taskowner order
by taskowner

--
Ron Symonds
Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

Mark said:
Hi

I am trying to make an sql query that finds data between two dates a
user
enters. The data needs to be grouped to eliminate nonunique
entries.

This is the SQL I tried:

SELECT taskowner, sum(hours) as sumofhours FROM Results where Date
=
'::startdate::' AND Date <= '::enddate::' group by taskowner order
by
taskowner

startdate and enddate are dates entered by the user prior to pushing
a
submit button at which point the query is generated. This code does
not
work. What is the problem?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
That still does not solve my problem.

Kathleen Anderson said:
You need to modify the query, like this:

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
#::startdate::# AND perDate <= #::enddate::# group by taskowner order by
taskowner

See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;284843&Product=fp2002

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/


Mark said:
The following sql code does work

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
#1/8/2005# AND perDate <= #31/8/2005# group by taskowner order by
taskowner

But the following sql does not work

SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
::startdate:: AND perDate <= ::enddate:: group by taskowner order by
taskowner

I think it is because of the startdate and enddate parameter that the user
enters but I can not figure out how to remedy the problem.

Thanks


Ronx said:
Date is a reserved word - date() is a function that returns today's
date.
You are effectively asking for today's date between two other dates -
which is nonsense.
Either rename the column in the database from Date to opDate (or
something else),
or place [] brackets around Date:

SELECT taskowner, sum(hours) as sumofhours FROM Results where [Date]
=
'::startdate::' AND [Date] <= '::enddate::' group by taskowner order
by taskowner

--
Ron Symonds
Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

Hi

I am trying to make an sql query that finds data between two dates a
user
enters. The data needs to be grouped to eliminate nonunique
entries.

This is the SQL I tried:

SELECT taskowner, sum(hours) as sumofhours FROM Results where Date
=
'::startdate::' AND Date <= '::enddate::' group by taskowner order
by
taskowner

startdate and enddate are dates entered by the user prior to pushing
a
submit button at which point the query is generated. This code does
not
work. What is the problem?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
What specific error are you getting when you use that change in delimiters

--




| That still does not solve my problem.
|
| "Kathleen Anderson [MVP - FrontPage]" wrote:
|
| > You need to modify the query, like this:
| >
| > SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
| > #::startdate::# AND perDate <= #::enddate::# group by taskowner order by
| > taskowner
| >
| > See:
| > http://support.microsoft.com/default.aspx?scid=kb;en-us;284843&Product=fp2002
| >
| > --
| > ~ Kathleen Anderson
| > Microsoft MVP - FrontPage
| > Spider Web Woman Designs
| > web: http://www.spiderwebwoman.com/resources/
| >
| >
| > | > > The following sql code does work
| > >
| > > SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
| > > #1/8/2005# AND perDate <= #31/8/2005# group by taskowner order by
| > > taskowner
| > >
| > > But the following sql does not work
| > >
| > > SELECT taskowner, sum(hours) as sumofhours FROM Results where perDate >=
| > > ::startdate:: AND perDate <= ::enddate:: group by taskowner order by
| > > taskowner
| > >
| > > I think it is because of the startdate and enddate parameter that the user
| > > enters but I can not figure out how to remedy the problem.
| > >
| > > Thanks
| > >
| > >
| > > "Ronx" wrote:
| > >
| > >> Date is a reserved word - date() is a function that returns today's
| > >> date.
| > >> You are effectively asking for today's date between two other dates -
| > >> which is nonsense.
| > >> Either rename the column in the database from Date to opDate (or
| > >> something else),
| > >> or place [] brackets around Date:
| > >>
| > >> SELECT taskowner, sum(hours) as sumofhours FROM Results where [Date]
| > >> >=
| > >> '::startdate::' AND [Date] <= '::enddate::' group by taskowner order
| > >> by taskowner
| > >>
| > >> --
| > >> Ron Symonds
| > >> Microsoft MVP (FrontPage)
| > >> Reply only to group - emails will be deleted unread.
| > >>
| > >> | > >> > Hi
| > >> >
| > >> > I am trying to make an sql query that finds data between two dates a
| > >> > user
| > >> > enters. The data needs to be grouped to eliminate nonunique
| > >> > entries.
| > >> >
| > >> > This is the SQL I tried:
| > >> >
| > >> > SELECT taskowner, sum(hours) as sumofhours FROM Results where Date
| > >> > >=
| > >> > '::startdate::' AND Date <= '::enddate::' group by taskowner order
| > >> > by
| > >> > taskowner
| > >> >
| > >> > startdate and enddate are dates entered by the user prior to pushing
| > >> > a
| > >> > submit button at which point the query is generated. This code does
| > >> > not
| > >> > work. What is the problem?
| > >> >
| > >> > Thanks
| > >>
| > >>
| > >>
| >
| >
| >
 
Back
Top