Grouping with Reporting services

Can anyone help me with grouping in Reporting services. I am more used to crystal reports drill-down method

For example i have a simple table that has timestamp and three other columns. I want to drill down by (after Grouping) for Day-Then- Hour and then show the details for three columns. And also group by one of the columns, if i get above working.

All i could do with Reporting services was stepped down model, but i have same dates repeated more than once. i would like them to be grouped under day and then show time stamps for times of day .

-Thanks all

You can do this by creating a Table and adding a grouping, which is grouped by the date portion of the timestamp field, and then place the detail fields in the detail section, as you are now. The expressions should be something like the following:

Grouping expression:
=Fields!TimeStamp.Value.Date

Grouping header textbox for the date:

=Fields!TimeStamp.Value.ToString("D")

Detail textbox for the time:
=Fields!TimeStamp.Value.ToString("T")

Ian

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.sql

Grouping with non-existent data

I need to build a report that groups data by a given time frame increment (15
min, 30 min, 1 hour). But I need the report to show every increment weather
there is data in my result set or not. So if I was to run my query for the
time frame of 12pm - 1pm and got the following data:
12:00, 1, 1
12:10, 1, 1
12:20, 1, 1
12:50 1, 1
And was asked to display my report using the 15 minute increment I should
get this back:
12:00 - 2 records
12:15 - 1 record
12:30 - 0 record
12:45 - 1 records
Now my grouping expression works to group the data (Hours are limited in the
query):
= ((DATEPART("h", Fields!EntryDate.Value))
* (60 / Parameters!Granularity.Value)
+ (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
Parameters!Granularity.Value)))
However if there isn't any data in one of the incremental groups that group
isn't displayed. How can I make the grouping work so that even if there is
no data for my group I still get a row?
Let me know if this doesn't make any sence and I'll try to clarify it.I have to do this and it is not simple. You need to have the data there to
be able to show it. This means you need to have a stored procedure that
fills in the missing rows (or more likely, puts in all the rows and then
joins it with the query so you have all the times shown).
RS can't group on something that isn't there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"eoghain" <eoghain@.discussions.microsoft.com> wrote in message
news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> I need to build a report that groups data by a given time frame increment
(15
> min, 30 min, 1 hour). But I need the report to show every increment
weather
> there is data in my result set or not. So if I was to run my query for
the
> time frame of 12pm - 1pm and got the following data:
> 12:00, 1, 1
> 12:10, 1, 1
> 12:20, 1, 1
> 12:50 1, 1
> And was asked to display my report using the 15 minute increment I should
> get this back:
> 12:00 - 2 records
> 12:15 - 1 record
> 12:30 - 0 record
> 12:45 - 1 records
> Now my grouping expression works to group the data (Hours are limited in
the
> query):
> = ((DATEPART("h", Fields!EntryDate.Value))
> * (60 / Parameters!Granularity.Value)
> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> Parameters!Granularity.Value)))
> However if there isn't any data in one of the incremental groups that
group
> isn't displayed. How can I make the grouping work so that even if there
is
> no data for my group I still get a row?
> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||I was afraid of that. I guess I'll have to figure out how to build a
query/stored procedure that will return me the proper time sets given a
start/end and increment.
Thanks for your help.
"Bruce L-C [MVP]" wrote:
> I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> > I need to build a report that groups data by a given time frame increment
> (15
> > min, 30 min, 1 hour). But I need the report to show every increment
> weather
> > there is data in my result set or not. So if I was to run my query for
> the
> > time frame of 12pm - 1pm and got the following data:
> >
> > 12:00, 1, 1
> > 12:10, 1, 1
> > 12:20, 1, 1
> > 12:50 1, 1
> >
> > And was asked to display my report using the 15 minute increment I should
> > get this back:
> >
> > 12:00 - 2 records
> > 12:15 - 1 record
> > 12:30 - 0 record
> > 12:45 - 1 records
> >
> > Now my grouping expression works to group the data (Hours are limited in
> the
> > query):
> > = ((DATEPART("h", Fields!EntryDate.Value))
> > * (60 / Parameters!Granularity.Value)
> > + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> > Parameters!Granularity.Value)))
> >
> > However if there isn't any data in one of the incremental groups that
> group
> > isn't displayed. How can I make the grouping work so that even if there
> is
> > no data for my group I still get a row?
> >
> > Let me know if this doesn't make any sence and I'll try to clarify it.
> >
>
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||But you can do it with a query, i have done that sometimes, if you giove me
some tim i´ll try to find out if i have it right away, just email me to give
a reminder.
Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>

Grouping with a full join

Hi,
I would like to know how to group the Amount of both tables while maintaing
all Ids.
-- Correct results for Table1
select
Table1.id
,sum(Table1.Amount) as AmountTable1
from Table1
group by Table1.id
order by 1
-- Correct results for Table2
select
Table2.id
,sum(Table2.Amount) as AmountTable2
from Table2
group by Table2.id
order by 1
-- How do I combine both results?
select
Table1.id
,sum(Table1.Amount) + sum(Table2.Amount) as AmountBoth
from Table1
left join Table2 on Table1.id = Table2.id
group by Table1.id
order by 1
/*
create table Table1 (Id int, Amount int)
create table Table2 (Id int, Amount int)
insert Table1 select 1, 100
insert Table1 select 2, 200
insert Table1 select 3, 300
insert Table1 select 4, 400
insert Table2 select 5, 500
insert Table2 select 2, 100
insert Table2 select 4, 400
insert Table2 select 6, 600
--drop table Table1
--drop table Table2
*/
---
select
coalesce(Table1.id,Table2.id) as id
,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
AmountBoth
from Table1
full outer join Table2 on Table1.id = Table2.id
group by coalesce(Table1.id,Table2.id)
order by 1|||Great, thank you!
<markc600@.hotmail.com> wrote in message
news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>
> select
> coalesce(Table1.id,Table2.id) as id
> ,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
> AmountBoth
> from Table1
> full outer join Table2 on Table1.id = Table2.id
> group by coalesce(Table1.id,Table2.id)
> order by 1
>|||You should be aware that this solution works when there is a one to
one relation ship between the two tables, but not if there is a one to
many (or many to many) relationship.
Here are two alternatives that avoid that problem.
SELECT id, sum(Amount) as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id
UNION ALL
select id, sum(Amount) as Amount
from Table1
group by id) as Combo
GROUP BY id
ORDER BY 1
SELECT COALESCE(T1.id,T2.id),
T1.Amount + T2.Amount as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id) as T1
FULL OUTER
JOIN (select id, sum(Amount) as Amount
from Table1
group by id) as T2
ON T1.id = T2.id
ORDER BY 1
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:53:11 +0300, "Yan" <yanive@.rediffmail.com>
wrote:

>Great, thank you!
>
><markc600@.hotmail.com> wrote in message
>news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.
Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.
|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
RotheryTelephone call out26/09/05 13:00:00
GinnellyAuto Order 26/09/05 12:55:03
AldcroftCustomer Services26/09/05 12:55:00
AldcroftAuto Order 26/09/05 12:54:20
BroadbentAuto Reminder26/09/05 12:54:07
BroadbentAuto Reminder26/09/05 12:51:23
PicklesTelephone call out26/09/05 12:50:00
BroadbentAuto Reminder26/09/05 12:49:50
PicklesTelephone call in26/09/05 12:43:00
BroadbentAuto Reminder26/09/05 12:41:22
RaceAuto Appointment26/09/05 12:41:13
RaceAuto Appointment26/09/05 12:36:27
BroadbentAuto Reminder26/09/05 12:36:04
BroadbentAuto Reminder26/09/05 12:33:38
BroadbentAuto Reminder26/09/05 12:30:01
BoothTelephone call out26/09/05 12:25:32
BoothTelephone call out26/09/05 12:23:00
PriorAuto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.
|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>sql

Grouping using two stored procedures

Hi,

I am creating a set of reports in Crystal showing emails sent and delivered from users within our organisation.

Each report uses a different stored procedure.

Report 1 shows emails sent:

Sender: Person in our org
Recipient: Person outside our org
Number: number of emails sent

Report 2 shows emails recived:

Sender: person outside our org
Recipient: Person in our org
Number: Number of emails received

In crystal, the reports are grouped around the sender for report 1, and the recipient for group 2 (therefore the reports are grouped around the person in our organisation).

Now I need to create a report showing the details of the two reports combined into one, but this creates a problem when i try to group. i need to distinguish between who is in our organisation and who is it, and then to group by them.

does anyone have any idea how this may be done?

i want the report to look like this:

Person in our organisation:
----------
Sent: bob@.yahoo.co.uk 26
sally@.hotmail.com 4
peter@.msn.com 12
Subtotal: 42

Received: fred@.company.co.uk 45
vicky@.hotmail.com 10
Subtotal 55

Total 97

and so on for each person.

Many thanks if you can helpEither create it as two subreports in Crystal, or use a UNION query to create a single dataset from both SQL statements. With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".|||With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".

how do i do this?

at the moment, the most i can come up with is

select * from vw_sent
union
select * from vw_received

and that's where my question comes from really, how to determine what addresses in each result are @.mydomain.co.uk and then to group by those. Becuase the resultset of this query is:

Sender Recipient Number
person@.mydomain.co.uk person@.hotmail.com 5
otherperson@.mydomain.co.uk person@.hotmail.com 2
otherperson@.hotmail.com person@.mydomain.co.uk 10

so how do i add an extra field in my resultset to show which email address is in my domain?|||select 'SENT' as Direction, * from vw_sent
union
select 'RECEIVED' as Direction, * from vw_received

...though you really should enumerate your field names instead of using *, especially in an UNION query.

Grouping two similar column names but different data?

Hi All,
I have a need to group a column with he same name.
I have a column called "AccountType" which has data such as :
A1
A2
A3
A4
I am using an aggrate for this column:
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
I want to also group by the actual group type. Something like:
SELECT
AccountType,
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType, AccountType
Can someone please give me a little help with this?
Thanks very much,
John.John,
Can you post an example of the expected result?
AMB
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||John:
without knowing exactly what you want, its difficult to answer.
Is this what you want:
select t.[account type], count(*)
from (
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
) t
group by t.[Account Type]
If not then try posting some sample data set and the required output and i
am sure someone will be able to help you on that.
just incase if you wanna play around and understand what the above code is
doing then use northwind and execute this query
use northwind
go
select t.lessOrMore, count(*) , sum(t.OrderCount)
from (
select orderID, count(*) as OrderCount
, case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
from [Order Details]
group by OrderID ) t
group by t.LessOrMore
Hope the above helps
Abhishek
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||My current data result is something like this:
LastName | Account Type | NumCount
Miller | Good | 20
Miller | Not Good | 5
Jones | Not Good | 37
Miller | Not Good | 9
What I would like to see is the following:
LastName | Account Type Actual Type | NumCount
Miller | Good | A1 |
20
Miller | Not Good | A2 | 5
Jones | Not Good | A3 | 37
Miller | Not Good | A4 |
9
In the first example I am grouping by LastName, [Account Type]
In the second example I need to Group by the same and addition to the Actual
Account Type.
The problem here though is that the column "AccountType" needs to be used
twice and I don't know how to handle this. Unfortunately I can not use a
unique alias for each one that can be Grouped.
John.
"Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
> John:
> without knowing exactly what you want, its difficult to answer.
> Is this what you want:
> select t.[account type], count(*)
> from (
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account
> Type]
> FROM Tbl1
> GROUP BY AccountType
> ) t
> group by t.[Account Type]
>
> If not then try posting some sample data set and the required output and i
> am sure someone will be able to help you on that.
> just incase if you wanna play around and understand what the above code is
> doing then use northwind and execute this query
> use northwind
> go
> select t.lessOrMore, count(*) , sum(t.OrderCount)
> from (
> select orderID, count(*) as OrderCount
> , case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
> from [Order Details]
> group by OrderID ) t
> group by t.LessOrMore
>
> Hope the above helps
> Abhishek
> "John" wrote:
>|||John:
It seems you dont need a second groupby.. coz you are not doing another
group by. It seems you just need and extra column. This is what is reflected
in the result set you posted (NumCount remains the same and you just need an
extra column for actual account type)
But then again you will need to be more clear in what exacly you want
is this what you want:
LastName | Account Type | Actual Type | NumCount
Miller | Good | A1 | 20
Miller | Not Good | A2 | 3
Miller | Not Good | A3 | 2
Jones | Not Good | A3 | 30
Jones | Not Good | A4 | 7
Miller | Not Good | A4 | 9
Notice that for miller not good account i have further divided into 2 actual
account type and the sum of count 3+2 = 5.
similarly for Jones it is 30+7 = 37.
If above is what you want then you can simply code it like this
SELECT Lastname
, (CASE
WHEN AccountType = 'A1'
Then 'Good'
ELSE 'Not Good'
END) AS [Account Type]
, [Account type] AS [Actual type]
, count(*) as [NumCount]
FROM Tbl1
GROUP BY LastName, AccountType
Hope the above helps. Do let me know if this is what you were looking for.
Abhishek
"John" wrote:

> My current data result is something like this:
> LastName | Account Type | NumCount
> Miller | Good | 20
> Miller | Not Good | 5
> Jones | Not Good | 37
> Miller | Not Good | 9
> What I would like to see is the following:
> LastName | Account Type Actual Type | NumCount
> Miller | Good | A1 |
> 20
> Miller | Not Good | A2 |
5
> Jones | Not Good | A3 |
37
> Miller | Not Good | A4 |
> 9
> In the first example I am grouping by LastName, [Account Type]
> In the second example I need to Group by the same and addition to the Actu
al
> Account Type.
> The problem here though is that the column "AccountType" needs to be used
> twice and I don't know how to handle this. Unfortunately I can not use a
> unique alias for each one that can be Grouped.
> John.
> "Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
> message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
>
>

Grouping Sorting String and Numerical Fields

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

Hi,

guess that you did not want to group you wanted to just sort, right ? For getting the results pasted below you will have to Sort by Patient Desc, Albumin Desc. If that is not your intention, please post the RDL and the way you want to display the information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Grouping SMALL PIE CHART Slices -> Possible ?

Hi,
I have a problem with small pie chart slices. As you know in a pie
chart, very small slices are unreadable.
So I'am looking for a maner to GROUP little values of a graph in one
categorie (slices) called "OTHER". For example all value under 1% will
be grouped in the "OTHER" category
Is someone have an idea in how to do this with Reporting Services ?
THANKSI hope someone will help me coz i'am really desapointed with this
problem :-(|||I think the best way to handle your issue is in the query that provides the
data for your chart. I dont know what database you are using but you can
characterize all your values under 1% as the field "other" in so it comes
from your query that way.
"rebeuapaname@.hotmail.com" wrote:
> Hi,
> I have a problem with small pie chart slices. As you know in a pie
> chart, very small slices are unreadable.
> So I'am looking for a maner to GROUP little values of a graph in one
> categorie (slices) called "OTHER". For example all value under 1% will
> be grouped in the "OTHER" category
>
> Is someone have an idea in how to do this with Reporting Services ?
>
> THANKS
>|||Thank's you
I have already thinked to this way but i was looking for a solution
based on the reporting services features.
If it's not available, I think it's may be interesting to add it on the
futur SP or in the 2005 release :-)sql

grouping similar data

In layout view: how do I group all the company names together who have the same value for a certain field? For instance, 10 companies all have the same booth size, but in Preview mode, it lists each company seprately with the booth size on each line. I need to have the booth size listed once with all the companies who share that common size. I have tried adding a group and using the expression for the company name as well as the booth size and it does not work. Can someone provide some detailed instructions?
Thank you,
Bil
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf I am understanding correctly you want to have the booth size and then
show all companies under that that match that description. Have you tried
grouping it by the booth size and then put your companies in the detail?
"Bkana" <nospam@.developmentnow.com> wrote in message
news:97f68ac4-a6e2-429d-a4e5-3b98fed63020@.developmentnow.com...
> In layout view: how do I group all the company names together who have the
> same value for a certain field? For instance, 10 companies all have the
> same booth size, but in Preview mode, it lists each company seprately with
> the booth size on each line. I need to have the booth size listed once
> with all the companies who share that common size. I have tried adding a
> group and using the expression for the company name as well as the booth
> size and it does not work. Can someone provide some detailed instructions?
> Thank you,
> Bill
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Grouping several items in one group

Hi Everyone,
I am new to reporting services and I am trying to create groups which
contains more then one code .
Table
Name, Code, Amount
paper 1101 £10
Pens 1102 £5
Shoes 2512 £20
Clothes 3455 £5
I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
group 2 with total.
At the moment I can only seem to group each one individually.
Please help.
John
--
John HoYou question is more of a SQL problem, and there is more than one way
to solve your problem.
SELECT 'GRP1' as groupcode, amount from paper where code =3D 1101
UNION
SELECT 'GRP1' as groupcode, amount from pens where code =3D 1102
UNION
SELECT 'GRP2' as groupcode, amount from shoes where code =3D 2512
UNION
SELECT 'GRP2' as groupcode, amount from clothes where code =3D 3455
save the above query to a View object. When you open the view, you'll
see this:
<pre>
groupcode | amount
GRP1 | =A310
GRP1 | =A35
GRP2 | =A320
GRP2 | =A35
</pre>
Now you can group & sum on your view for your report. I'm sure there
are more elegant solutions (perhaps using StoredProcs), but this is
dirty and quick...heh.
On Apr 7, 11:05 am, Learner <Lear...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I am new to reporting services and I am trying to create groups which
> contains more then one code .
> Table
> Name, Code, Amount
> paper 1101 =A310
> Pens 1102 =A35
> Shoes 2512 =A320
> Clothes 3455 =A35
> I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
> group 2 with total.
> At the moment I can only seem to group each one individually.
> Please help.
> John
> --
> John Ho

GROUPING SETS in What's New (Database Engine)

Just as a usability issue, most of the topics mentioned in the "What's New" documents have a link to further information. The GROUPING SETS item under "What's New in CTP 1" at /s10de_0evalplan/html/8f625d5a-763c-4440-97b8-4b823a6e2439.htm should contain a link to the GROUPING SETS topic. Of course, the GROUPING SETS entry in the index currently points to /s10de_6tsql/html/c1050658-b19f-42ee-9a05-ecd6a73b896c.htm which is actually the topic GROUPING_ID (Transact-SQL).

I realize that "What's New in CTP 1" will go away but I assume these groups of topics will be combined into a grander "What's New in SQL Server 2008" section distributed with RTM Books Online.

Hi Aaron,

Thanks for the good suggestions.

Regards,

Gail

GROUPING SETS in What's New (Database Engine)

Just as a usability issue, most of the topics mentioned in the "What's New" documents have a link to further information. The GROUPING SETS item under "What's New in CTP 1" at /s10de_0evalplan/html/8f625d5a-763c-4440-97b8-4b823a6e2439.htm should contain a link to the GROUPING SETS topic. Of course, the GROUPING SETS entry in the index currently points to /s10de_6tsql/html/c1050658-b19f-42ee-9a05-ecd6a73b896c.htm which is actually the topic GROUPING_ID (Transact-SQL).

I realize that "What's New in CTP 1" will go away but I assume these groups of topics will be combined into a grander "What's New in SQL Server 2008" section distributed with RTM Books Online.

Hi Aaron,

Thanks for the good suggestions.

Regards,

Gail

Grouping Select Statements with where clause

Hello

What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.

campaign Col1 Col2

<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>No you don't. You just think you do. What you really want to do is create a CROSSTAB query. Look it up in Books Online.sql

grouping select query

Hi,
I have data stored as in below sample :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
I have to group the select query in this way :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
When I run below grouped statement, I get follewed result:
SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
--+--+--
--
How can I do a query like in 2nd sample from top?
best regards,
rustam bogubaevThis is a periodicity problem, not a SQL syntax problem.
You have to define how the period is to be divided first. In essence,
however you decide to calculate the period, the data would logically contain
the following information.
--+--+--
--
DateBegin | DateEnd | Rate |
Period
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000 1
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000 1
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000 2
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000 2
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000 2
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000 2
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000 2
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000 3
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000 3
--+--+--
--
With the periods defined, however that is done, your problem will be easy.
Perhaps something like the following would help find the boundarys of the
periods.
SELECT b.DateBegin
FROM MyTable a JOIN MyTable b
ON a.DateEnd = b.DateBegin
WHERE a.Rate != b.Rate
RLF
<rustam.bogubaev@.gmail.com> wrote in message
news:1131461007.812709.108200@.g49g2000cwa.googlegroups.com...
> Hi,
> I have data stored as in below sample :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
>
> I have to group the select query in this way :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> --+--+--
--
> How can I do a query like in 2nd sample from top?
> best regards,
> rustam bogubaev
>|||On 8 Nov 2005 06:43:27 -0800, rustam.bogubaev@.gmail.com wrote:
(snip)
>I have to group the select query in this way :
>--+--+--
--
> DateBegin | DateEnd | Rate
>--+--+--
--
>2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
>2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
>2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
>--+--+--[/c
olor]
Hi rustam,
If my assumptions about your table and the reasons for your expected
results are correct, then try:
SELECT a.DateBegin, MAX(b.DateEnd), a.Rate
FROM X AS a
INNER JOIN X as b
ON b.Rate = a.Rate
AND b.DateBegin >= a.DateStart
WHERE NOT EXISTS
(SELECT *
FROM X AS c
WHERE c.DateBegin = DATEADD(day, -1, a.DateBegin)
AND c.Rate = a.Rate)
AND NOT EXISTS
(SELECT *
FROM X AS d
WHERE d.DateBegin > a.DateEnd
AND d.DateEnd < b.DateBegin
AND d.Rate <> a.Rate)
GROUP BY a.DateBegin, a.Rate
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

grouping select query

Hi,

I have data stored as in below sample :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-14 00:00:0063.0000
2005-11-14 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-16 00:00:0045.0000
2005-11-16 00:00:002005-11-17 00:00:0045.0000
2005-11-17 00:00:002005-11-18 00:00:0045.0000
2005-11-18 00:00:002005-11-19 00:00:0045.0000
2005-11-19 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-21 00:00:0063.0000
2005-11-21 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

I have to group the select query in this way :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

When I run below grouped statement, I get follewed result:

SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-22 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
----------+----------+-----

How can I do a query like in 2nd sample from top?

best regards,
rustam bogubaevPYCTAM wrote:
> Hi,
> I have data stored as in below sample :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
>
> I have to group the select query in this way :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> ----------+----------+--
---
> How can I do a query like in 2nd sample from top?

Care to explain by what you want to group? I cannot recognize it from
your sample output.

robert|||On 8 Nov 2005 06:42:33 -0800, PYCTAM wrote:

(snip)

Hi rustam,

You posted an exact identical copy of this question in the group
microsoft.public.sqlserver.programming, and I posted a reply there.

Please do not post the same question independently to multiple groups.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

grouping rows by customer

my source flat file has many rows per customer,
but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups).
I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database?
As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Thanks

Vlad

won't A Flat file source and aggregation transform suffice your needs?

Rafael Salas

|||

I tried this, but I do not think it would help :-(

it is more complicated calculation, than just grouping.

I would rather do it in the script load into a Collection, loop, calculate, replace, substitute etc. and then save to database.

|||

Well you know your data...good luck with that!

Rafael Salas

|||

The Aggregate transformation does more than Group By. You don't want to do this in script. You can do SUM, AVG, MIN, MAX with the Aggregrate Transformation. If you need to then do something else combine the Derived Column Transformation with it.

http://msdn2.microsoft.com/en-US/library/ms138031.aspx