Grouping By A Certain Range

I would like to create a report that would group the items by their
price. For example, I would like a group for all of the items that are
between 0-50 and then 51-100 (and so on).
Does anyone know an easy way for doing that using Visual Studio?
ANY information is appreciated. Thanks in advance!you can create a "calulated" field in your dataset like:
=iif(MyPrice > 50, '51-100', '0-50')
then use this new field in your report to render it. (group, filter, sort or
any other option which could use a formula)
"axels22" <ericspreher@.hotmail.com> wrote in message
news:1137429986.049744.315060@.g44g2000cwa.googlegroups.com...
>I would like to create a report that would group the items by their
> price. For example, I would like a group for all of the items that are
> between 0-50 and then 51-100 (and so on).
> Does anyone know an easy way for doing that using Visual Studio?
> ANY information is appreciated. Thanks in advance!
>

Group chart by month

I have a really basic chart that pulls data from a SQL table. I have
the dataset asking for a machine number. The date range is hard coded
in the query for now.
This is my table's basic data...
Machine | Performance | DateTime
____________________________
1125 | 60 | 11/16/06
1125 | 45 | 12/01/06
1125 | 35 | 12/15/06
The results give me all the machines details from sept06 through
feb07. The series is the machine, Y axis is the performace and the X
Axis is the date formated by MM/YY
Right now the chart shows me 3 records. one for 11/06 and two for
12/06. Each record has a bar for the proper performance number.
What I need is 2 records.
11/06 and the bar will represent the avg for all Nov records.
12/06 and the bar will represent the avg for all Dec records.
I'm not sure if I have to change the dataset query or adjust the
groupings or whatever in the table. I'm lost right now."Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1170436640.424961.261770@.p10g2000cwp.googlegroups.com...
>I have a really basic chart that pulls data from a SQL
>table. I have
> the dataset asking for a machine number. The date range
> is hard coded
> in the query for now.
> This is my table's basic data...
> Machine | Performance | DateTime
> ____________________________
> 1125 | 60 | 11/16/06
> 1125 | 45 | 12/01/06
> 1125 | 35 | 12/15/06
> The results give me all the machines details from sept06
> through
> feb07. The series is the machine, Y axis is the
> performace and the X
> Axis is the date formated by MM/YY
> Right now the chart shows me 3 records. one for 11/06 and
> two for
> 12/06. Each record has a bar for the proper performance
> number.
> What I need is 2 records.
> 11/06 and the bar will represent the avg for all Nov
> records.
> 12/06 and the bar will represent the avg for all Dec
> records.
> I'm not sure if I have to change the dataset query or
> adjust the
> groupings or whatever in the table. I'm lost right now.
>
I suggest you change the query. You don't mention a primary
key. You need one. You can then filter the data in your SQL
query. May I suggest you ask this question in one of the SQL
Server newsgroups (such as
microsoft.public.sqlserver.newusers, or
microsoft.public.sqlserver.programming) and give them your
full table structure and the query you are using. Include an
example of the results you want and the incorrect results
you are getting now.
Hope this helps.
Ron.

Group by Max Count ? how to do query?

Can anyone tell me how to do this query please?
3 fields. provider, site, visitdate. Given a date range for
visitdate,
Return all providers that have at least one visit in that date range
and also display which site has the most visitdates for EACH provider
for all records (not date constrained).
Since a provider can have visits at multiple sites I only want to
return the site where they have the most visits.
THANKS A LOTSome DDL (i.e. create table statements) would be useful.
Thomas
<bringmewater@.gmail.com> wrote in message
news:1114630228.267194.170960@.g14g2000cwa.googlegroups.com...
> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>|||Try,
create view v1
as
select
provider,
site,
count(*) as number_of_visit
from
t1
where
visitdate >= convert(char(8), @.sd, 112) and visitdate < dateadd(day, 1,
convert(char(8), @.ed, 112))
group by
provider,
site
having
count(*) > 0
go
select
provider,
site,
number_of_visit
from
v1 as a
where
number_of_visit = (select max(b.number_of_visit) from v1 as b where
b.provider = a.provider)
go
AMB
"bringmewater@.gmail.com" wrote:

> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>

Group By Date Range

I have the following sql query which I would like to group by date range in weeks. It is a table that records ipAddresses for various pages. I am trying to return total and distinct page hits per week.

SELECT page, count(*) AS total, count(DISTINCT ipAddress) AS ipAddress
FROM tblUniquePageHits
WHERE
date_hit >= '2003-09-03 10:00:00' AND
date_hit < '2003-10-17 10:00:00' AND
page = 'Main'
GROUP BY page

I want to group by date_hit from
'2003-09-03 10:00:00' to '2003-09-10 10:00:00'
'2003-09-10 10:00:00' to '2003-09-17 10:00:00'
and so on...see if this article might help you --

Aggregates for date ranges (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid494805_tax285649,00.html)
(registration required, but it's free)

if you have any questions, let me know

rudy
http://r937.com/

Group By Date Query

Hi,
I want to arrange some of the data retrieved from the database with
respect to a range of date. The tables from which I am retrieving the
data are, namely;
1. Transaction
2. Credit
3. Debit
4. Account
My stored procedure is as follows
CREATE PROCEDURE [dbo].[SP_TransactionInfo]
@.startDate datetime, @.endDate datetime
AS
BEGIN
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
the problem is that I want to add a statement that would use the
@.startDate and @.endDate to as a range of dates according to which all
the data will be arranged in descending order.
Thanking you.
Chao.
Hi
Don't you have a column that identifies the debit, credit..whatever?
Add WHERE condtion like dt_column >=@.startDate AND dt_column
<DATEADD(d,1,@.endDate )
It is probably good to hhave an index of dt_column
<umairsyed19@.gmail.com> wrote in message
news:574964e1-dbc1-4f42-b7a3-cc5f1f6e5a9f@.n75g2000hsh.googlegroups.com...
> Hi,
> I want to arrange some of the data retrieved from the database with
> respect to a range of date. The tables from which I am retrieving the
> data are, namely;
> 1. Transaction
> 2. Credit
> 3. Debit
> 4. Account
> My stored procedure is as follows
> CREATE PROCEDURE [dbo].[SP_TransactionInfo]
> @.startDate datetime, @.endDate datetime
> AS
> BEGIN
> SELECT distinct
> T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
> C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
> AC.Account_Name as Deb_AccName
> FROM Transactions T
> JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
> JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
> JOIN Account AC ON C.Account_ID = AC.Account_ID
> JOIN Account AD ON D.Account_ID = AD.Account_ID
> the problem is that I want to add a statement that would use the
> @.startDate and @.endDate to as a range of dates according to which all
> the data will be arranged in descending order.
>
> Thanking you.
> Chao.
|||Perhaps you mean to filter the Transaction Date (as this is the only date
column based on the name) and order by it descending:
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date BETWEEN @.startDate AND @.endDate
ORDER BY T.Transaction_Date DESC
The above will have the date range including the start and end dates. To
exclude one or both of them you can change as follows (below the end date is
excluded):
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date >= @.startDate
AND T.Transaction_Date < @.endDate
ORDER BY T.Transaction_Date DESC
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Group By Date Query

Hi,
I want to arrange some of the data retrieved from the database with
respect to a range of date. The tables from which I am retrieving the
data are, namely;
1. Transaction
2. Credit
3. Debit
4. Account
My stored procedure is as follows
CREATE PROCEDURE [dbo].[SP_TransactionInfo]
@.startDate datetime, @.endDate datetime
AS
BEGIN
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
the problem is that I want to add a statement that would use the
@.startDate and @.endDate to as a range of dates according to which all
the data will be arranged in descending order.
Thanking you.
Chao.Hi
Don't you have a column that identifies the debit, credit..whatever?
Add WHERE condtion like dt_column >=@.startDate AND dt_column
<DATEADD(d,1,@.endDate )
It is probably good to hhave an index of dt_column
<umairsyed19@.gmail.com> wrote in message
news:574964e1-dbc1-4f42-b7a3-cc5f1f6e5a9f@.n75g2000hsh.googlegroups.com...
> Hi,
> I want to arrange some of the data retrieved from the database with
> respect to a range of date. The tables from which I am retrieving the
> data are, namely;
> 1. Transaction
> 2. Credit
> 3. Debit
> 4. Account
> My stored procedure is as follows
> CREATE PROCEDURE [dbo].[SP_TransactionInfo]
> @.startDate datetime, @.endDate datetime
> AS
> BEGIN
> SELECT distinct
> T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
> C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
> AC.Account_Name as Deb_AccName
> FROM Transactions T
> JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
> JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
> JOIN Account AC ON C.Account_ID = AC.Account_ID
> JOIN Account AD ON D.Account_ID = AD.Account_ID
> the problem is that I want to add a statement that would use the
> @.startDate and @.endDate to as a range of dates according to which all
> the data will be arranged in descending order.
>
> Thanking you.
> Chao.|||Perhaps you mean to filter the Transaction Date (as this is the only date
column based on the name) and order by it descending:
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date BETWEEN @.startDate AND @.endDate
ORDER BY T.Transaction_Date DESC
The above will have the date range including the start and end dates. To
exclude one or both of them you can change as follows (below the end date is
excluded):
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date >= @.startDate
AND T.Transaction_Date < @.endDate
ORDER BY T.Transaction_Date DESC
HTH,
Plamen Ratchev
http://www.SQLStudio.com