Firehose mode: group

Showing posts with label group. Show all posts

Friday, March 30, 2012

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

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 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 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 Records & Assigning Sequential Number

I need to group records and assign a setid to the group. I have a
table with data that looks like this

ColA ColB
94015 01065
94016 01065
94015 01085
94015 01086
33383 00912
32601 00912

I need to create a resultset using just sql to look like this

ColA ColB GRP
94015 01065 1
94016 01065 1
94015 01085 1
94015 01086 1
33383 00912 2
32601 00912 2

The tricky part is resolving the many to many issue. A value in ColA
can belong to multiple values in ColB and a value in ColB can have
multiple values in ColA.Please explain the logic that determines GRP. What rule makes the first four
rows GRP=1 and the next two GRP=2 ?

--
David Portas
SQL Server MVP
--|||"cjm" <cjm136@.optonline.net> wrote in message news:62be3d63.0402120756.f08195b@.posting.google.co m...
> I need to group records and assign a setid to the group. I have a
> table with data that looks like this
> ColA ColB
> 94015 01065
> 94016 01065
> 94015 01085
> 94015 01086
> 33383 00912
> 32601 00912
> I need to create a resultset using just sql to look like this
> ColA ColB GRP
> 94015 01065 1
> 94016 01065 1
> 94015 01085 1
> 94015 01086 1
> 33383 00912 2
> 32601 00912 2
> The tricky part is resolving the many to many issue. A value in ColA
> can belong to multiple values in ColB and a value in ColB can have
> multiple values in ColA.

Not completely sure I understand your grouping criteria but hopefully
this is helpful.

CREATE TABLE T
(
colA VARCHAR(10) NOT NULL,
colB VARCHAR(10) NOT NULL,
PRIMARY KEY (colA, colB)
)

INSERT INTO T (colA, colB)
VALUES ('94015', '01065')
INSERT INTO T (colA, colB)
VALUES ('94016', '01065')
INSERT INTO T (colA, colB)
VALUES ('94015', '01085')
INSERT INTO T (colA, colB)
VALUES ('94015', '01086')
INSERT INTO T (colA, colB)
VALUES ('33383', '00912')
INSERT INTO T (colA, colB)
VALUES ('32601', '00912')

SELECT T.colA, T.colB, B.grp
FROM (SELECT B1.colB, COUNT(*) AS grp
FROM (SELECT colB
FROM (SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
GROUP BY colB) AS B1
INNER JOIN
(SELECT colB
FROM (SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
GROUP BY colB) AS B2
ON B2.colB <= B1.colB
GROUP BY B1.colB) AS B
INNER JOIN
(SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
ON A.colB = B.colB
INNER JOIN
T
ON T.colA = A.colA
ORDER BY B.grp, T.colB, T.colA

colA colB grp
32601 00912 1
33383 00912 1
94015 01065 2
94016 01065 2
94015 01085 2
94015 01086 2

Regards,
jag|||"John Gilson" <jag@.acm.org> wrote in message news:<W_7Xb.20407$Lp.1268@.twister.nyc.rr.com>...
> "cjm" <cjm136@.optonline.net> wrote in message news:62be3d63.0402120756.f08195b@.posting.google.co m...
Thanks JAG for the clever and clean solution!|||Is a given colB value allowed to belong to more than one group? If so then
John's solution looks good but I wasn't clear on this point from your sample
data.

Here's another solution that may or may not give the result you want (thanks
for the DDL and sample data John). I've added an extra row of sample data:

CREATE TABLE T
(
colA VARCHAR(10) NOT NULL,
colB VARCHAR(10) NOT NULL,
grp INTEGER NULL,
PRIMARY KEY (colA, colB)
)

INSERT INTO T (colA, colB)
VALUES ('32601', '01065')

John's query gives:

colA colB grp
---- ---- ----
32601 00912 1
33383 00912 1
32601 01065 1
94015 01065 2
94016 01065 2
94015 01085 2
94015 01086 2

Notice that 01065 appears in both groups. This iterative solution will put
all rows in the same group:

DECLARE @.grp INTEGER

UPDATE T
SET @.grp = grp = COALESCE(@.grp,0) + 1

WHILE @.@.ROWCOUNT>0
UPDATE T
SET grp =
(SELECT MIN(X.grp)
FROM T AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
WHERE EXISTS
(SELECT *
FROM T AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)

Note that the group numbers using this method are not "sequential" and may
have gaps but it's not clear from your original post exactly what the
sequence should be (if any).

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<jNadnasl3vRMQ7DdRVn-tw@.giganews.com>...
> Is a given colB value allowed to belong to more than one group? If so then
> John's solution looks good but I wasn't clear on this point from your sample
> data.
> Here's another solution that may or may not give the result you want (thanks
> for the DDL and sample data John). I've added an extra row of sample data:
> ...
There is no column called GRP in the table T and I don't want to alter
the table or create a temp table or otherwise UPDATE table T. How
would this be rewritten to return the result set as a query?

You made an important observation that a given colB value should
belong to only ONE group and I want to see the results of your code
with the record you added to the example. Sorry if this is a simple
conversion but I'm still learning.|||I'm not sure this is possible as a single query. It doesn't look like you
can avoid an iterative solution although you could turn it into a
table-valued function. I'm cross-posting to
microsoft.public.sqlserver.programming to see if anyone can come up with
better than this.

(http://groups.google.com/groups?sel...8195b%40posting.
google.com)

CREATE TABLE T(colA VARCHAR(10), colB VARCHAR(10) NOT NULL, PRIMARY KEY
(colA, colB))

INSERT INTO T (colA, colB) VALUES ('94015', '01065')
INSERT INTO T (colA, colB) VALUES ('94016', '01065')
INSERT INTO T (colA, colB) VALUES ('94015', '01085')
INSERT INTO T (colA, colB) VALUES ('94015', '01086')
INSERT INTO T (colA, colB) VALUES ('33383', '00912')
INSERT INTO T (colA, colB) VALUES ('32601', '00912')

/* Additional row makes it a single group: */
INSERT INTO T (colA, colB) VALUES ('32601', '01065')

CREATE FUNCTION TGroupings ()
RETURNS @.t TABLE (colA VARCHAR(10) NOT NULL, colB VARCHAR(10) NOT NULL, grp
INTEGER NULL, PRIMARY KEY (colA,colB))

BEGIN
INSERT INTO @.t (colA, colB)
SELECT colA, colB
FROM T

DECLARE @.grp INTEGER

UPDATE @.t
SET @.grp = grp = COALESCE(@.grp,0) + 1

WHILE @.@.ROWCOUNT>0
UPDATE T
SET grp =
(SELECT MIN(X.grp)
FROM @.t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
FROM @.t AS T
WHERE EXISTS
(SELECT *
FROM @.t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)

UPDATE T
SET grp =
(SELECT COUNT(DISTINCT grp)
FROM @.t AS X
WHERE grp <= T.grp)
FROM @.t AS T

RETURN
END

SELECT * FROM TGroupings()

This is the result with your original test-data:

colA colB grp
---- ---- ----
32601 00912 1
33383 00912 1
94015 01065 2
94015 01085 2
94015 01086 2
94016 01065 2

(6 row(s) affected)

And this is it with my extra row added:

colA colB grp
---- ---- ----
32601 00912 1
32601 01065 1
33383 00912 1
94015 01065 1
94015 01085 1
94015 01086 1
94016 01065 1

(7 row(s) affected)

--
David Portas
SQL Server MVP
--

"cjm" <cjm136@.optonline.net> wrote in message
news:62be3d63.0402201048.537be689@.posting.google.c om...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:<jNadnasl3vRMQ7DdRVn-tw@.giganews.com>...
> > Is a given colB value allowed to belong to more than one group? If so
then
> > John's solution looks good but I wasn't clear on this point from your
sample
> > data.
> > Here's another solution that may or may not give the result you want
(thanks
> > for the DDL and sample data John). I've added an extra row of sample
data:
> > ...
> There is no column called GRP in the table T and I don't want to alter
> the table or create a temp table or otherwise UPDATE table T. How
> would this be rewritten to return the result set as a query?
> You made an important observation that a given colB value should
> belong to only ONE group and I want to see the results of your code
> with the record you added to the example. Sorry if this is a simple
> conversion but I'm still learning.sql

Grouping Question

I have data from a dataset like this:

Dte Activity Agent Hours

-- - -

5/1/2007 activity1 agent1 11.0

5/1/2007 activity1 agent2 5.0

How would I group this data in a Table to display like this:

5/1/2007 activity1 agent1 11.0

agent2 5.0

What i've tried so far:

1) Using previous() to blank out Dte and Activity columns if previous is same as current. Does not work becuase agent2 shows many lines below agent1 depending on the size of activity(this field could be very long)

2) Grouping by Dte and Activity would not concat agent field and/ or hours field into one string.

3) Don't want to group concat in database if i don't have to. This makes suming values difficult on the reporting side.

Any ideas?

Thx

clarification:

1st grouping is date
2nd grouping is activity number

So why not just have two groupings?

(how do you tables in these forums?)

assume below is a grid 4 cols by 3 rows, representing a table in RS
where the top rop is "grouping one"
second row is "grouping two"
third row is detail
col1, row1: =fields!DateValue
col2,row2: = fields!ActivityValue
col3,row3: = fields!OtherData1.Value
col4,row3: = fields!OtherData2.Value
...etcsql

Grouping question

If I want to get a count of each type of item in a table, I can use a simply GROUP BY and include a Count() of what I want in the SELECT clause.

But how do I get subcounts of that data? Like I want to group by a first value, then show columns for all the possible values in a second column. For instance, let's say I have two columns FirstName and LastName. What would a t-sql statement look like to yield the following output? It's like using the Column gropuing in an Excel pivot table.

Is this possible?

	Totals
LastName	John	Jane
Doe	13	8
Schmoe	6	4

Thanks!

Dave

This is not possible to do in SQL without going through lot of hoops. You should do report generation on the client-side. Given few restrictions, you can generate a pivotted result like above.

grouping problems

how to i make a report like this?
group 1 start
group 2 start
group 2 end
group 3 start
group 3 end
group 1 end
i keep trying, but can only make a report like this:
group 1 start
group 2 start
group 3 start
group 3 end
group 2 end
group 1 end
i am using sql server 2000 reporting services, Visual studio .NET 2003
thanks for answeringHow are your groups defined? All on the same field or separate fields?
If on all the same field, what differentiates group1 from group2 from
group3? If you could provide examples of your data it'd be much easier
to visualize what you need.
newbie00100 wrote:
> how to i make a report like this?
> group 1 start
> group 2 start
> group 2 end
> group 3 start
> group 3 end
> group 1 end
> i keep trying, but can only make a report like this:
> group 1 start
> group 2 start
> group 3 start
> group 3 end
> group 2 end
> group 1 end
> i am using sql server 2000 reporting services, Visual studio .NET 2003
> thanks for answering|||hi,
i have a dataset storing the yearly sales volume of last year, let it be
group 1
the group2 would be the monthly sales volume of last month,
the group3 would be the quarterly sales volumn of last quarter
group2 and group3 both retrieve data from group1
go i want the structure to be like
group 1 start
group 2 start
group 2 end
group 3 start
group 3 end
group 1 end
and i also want to ask how do I filter the records of last month/last
quarter, without modifying the stored procedure? the filter on the group
options seems to filter a column but not a record.
thanks for answering
"toolman" wrote:
> How are your groups defined? All on the same field or separate fields?
> If on all the same field, what differentiates group1 from group2 from
> group3? If you could provide examples of your data it'd be much easier
> to visualize what you need.
> newbie00100 wrote:
> > how to i make a report like this?
> >
> > group 1 start
> > group 2 start
> > group 2 end
> > group 3 start
> > group 3 end
> > group 1 end
> >
> > i keep trying, but can only make a report like this:
> > group 1 start
> > group 2 start
> > group 3 start
> > group 3 end
> > group 2 end
> > group 1 end
> >
> > i am using sql server 2000 reporting services, Visual studio .NET 2003
> > thanks for answering
>

Wednesday, March 28, 2012

Grouping problem

Ok, I need to create an invoice for different plans that customers are subscribed to but I can't seem to group it in crystal properly. The invoice needs to list the invoice # at the top, followed by all of the different plans the customer is subscribed to, then a quick one time sentence that reads: "Fees for services in connection with:", and finally I need to display the services.

So basically, I may get data from my database that looks like the following:
1 | 51271 (inv #) | Pension Plan (plan name) | Yearly Admin Fee (service desc.)
2 | 51271 (inv #) | Cafeteria Plan(plan name)| Government Forms (service desc.)

The report would then look something like this:
----------------
Invoice #
---------------
Plan(s):
Pension Plan
Cafeteria Plan
--------------
Fees for services in connection with:
--------------

-Yearly Admin Fee
-Government Forms
----------------

Does anyone know how I could group my report this way or can you point me to a tutorial that can help me out?

Thanks in advance.

-Goalie35Ok...try grouping by invoice number, list plans in the details section. In group footer, put a subreport with the fees for services linked to the main report based on invoice number.

Let me know if this helps :)

Grouping problem

I am working on a report for a small POS. The report should allow the user to choose the time interval to group sales records, e.g. 1 hour, 2 hours or 4 hours. I believe I can setup this by the DiscretizationMethod and DiscretizationBucketCount of the Hour attribute in my DimTime dimension. However, the problem that I am facing is this POS will support multiple branches. Each branch will have their particular opening and closing hour. So, how can I group all the transaction into groups, said "Before Shop Open" and "After Shop Closed"? This sounds strange but will happen quite often as overtime work is always expected in my living place.

If this is infeasible, is there any workaround? I think the business user certainly want to know how many transaction has been created in those extra time.

In the other report, it is required to generate a transaction count by amount. The user should be able to specify the amount interval and upper limit. e.g. if amount interval and upper limit are set to 50 and 150, then the transaction will be grouped into 4.
0<=amount<50
50<=amount<100
100<=amount<150
amount>=150

I have no idea to this. First, I don't know how can I get the amount for each sale order as my fact table is storing sales order item information only. Second, how can I make this customizable grouping just like the report stated above? Thanks!

Hi Alex:

You pose two difficult problems. I'll address the second problem because you provided the most detail and clearly stated the issues. To restate, the issues are:

(1) How can you get the amount for each sale order?

(2) How can you allow customizable grouping?

Addressing issue (1) about the amount for the sales order. If the sales amount for the sales order is not in your fact table then you will not be able to access the sales amount in your cube. You have to go back to the ETL process and bring in the sales amount as part of yur fact table.

Issue (2), customizable grouping, is best approached on the client side of your application. Alternatively you, as an administrator, could create a separate attribute hierarchy for each branch with it's own amount interval and upper limit. I think your choice of a solution (client side, or separate hierarchy per branch) depends upon how many branches you have, and how much management you want to put in as an administrator. Creating transaction count by amount on the client is simple if you have the transaction amount as a measure. Get the transaction count by using a calculated member with the MDX count() function. Within each query you can adjust the amount interval and upper limit for each user. Here's an example:

WITH MEMBER MEASURES.[Less than 50] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] < 50)'

MEMBER MEASURES.[Between 50 and 100] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] > 50 AND Measures.[Sales Amount] < 100)'

SELECT {MEASURES.[Less than 50] , MEASURES.[Between 50 and 100]} ON COLUMNS FROM [my cube]

Hope this helps.

PGoldy

|||Hi PGoldy,

First, thank you for your input to these difficult problems that I am facing right now. Actually, I have come up with sort of solution after the post but it still doesn't work very well.

For issue 1, I found out that even I don't have a total for the sales order stored in the fact table. I can get it by creating a "Named Query". In this query, I will group the fact table records by the transaction ID. In this way, I obtain the sales amount per transaction, not per item. It looks good.

For issue 2, I use the "Named Query" that just created a bit further. In that query, besides the total amount per transaction. I create another field which is a floored amount. I am using this function.

floor(convert(decimal, sum(ItemAmount)) / 50) * 50

By doing this, I am able to make those sales total into the starting value of their groups. e.g. 38 returns 0, 59 returns 50 and 160 returns 150.
It seems really good at first. However, I have another problem to make this perfect or really usable. In SSAS, if there's no data exists for a specific group. It won't get display. e.g. if I got 38, 59 and 160 in my sales order total. I will only get the groups 0~49, 50~99 and 150~149. The problem is the missing 100~149. For business user, I think it's not acceptable to have a gap in the report like this. So, how can I fill in this gap?

Moreover, is there any best practice for my situation? I think this is a very common scenario but I can't find any useful reference.

Regards,
Alex|||

Hi Alex:

Best practice is creation of a hierarchy which has the "bucket" ranges you want. Then link each fact table record to the appropriate bucket with a foreign key. It's a common practice and used in most implementations. Below is a link to a series of articles by Bill Pearson which articulate (very well) the functionality you're looking for and a lot more. Good luck.

PGoldy

|||Dear PGoldy,

Could you please check whether the links has been posted? Thanks!

Regards,
Alex|||

Hi Alex. Sorry about the delay. Below is the link. PaulG

http://www.databasejournal.com/article.php/1459531

sql

Grouping problem

I need to provide a summary row at the bottom of each group that sums accounts of a specific type and subtracts it from the group total.

Acct Balance Prior Balance

A 10 5

B 4 8

C 7 6

D 9 12

Total 30 31

exp accts 13 20

Final 17 11

Is there an easy way to do this in reporting services?

So you want to perform a conditional sum based on another field (account type) in the dataset?

You need add some additional rows to the group footer and put an Iif statement inside the Sum that returns the value to aggregate for a match and 0 otherwise e.g.

Sum(Iif(Fields!account_type.Value = "exclude", Fields!Balance.value, 0))

Then in the last row subtract one row from the other.

The following is RDL code for an example:

<?xml version="1.0" encoding="utf-8"?>

<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>

</ConnectionProperties>

<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>

</DataSource>

</DataSources>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>balance_1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>prior_balance_1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!prior_balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>exc accts</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Final</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<DataSetName>DataSet1</DataSetName>

<rd:DefaultName>account</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>=Fields!account.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>balance</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>prior_balance</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!prior_balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>account</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>balance</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>prior balance</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance

union

select 'B', 2, 4, 8

union

select 'C', 1, 7, 6

union

select 'D', 2, 9, 12</CommandText>

<DataSourceName>master</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

<DataField>account</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>balance</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>prior_balance</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

Grouping prblm

Hi

This is my first post here so please be gentle.

I have a group sp which gets my data like below

spTest Surname DateSold PlotPrice Comp date SalesPerson Percentage RefundedAmount Site SaleID
Smith 06/04/2006 3,600.00 04/04/2006 Cooper 5 500.00 Site1 945
Smith 06/04/2006 11,600.00 06/04/2006 Cooper 5 500.00 Site2 945

The problem is i only want the RefundedAmount to be 500.00 once the rest to be 0 if the SaleID is the same

How can I get around this?

Any help would be great

CheersspTest Surname DateSold PlotPrice Comp date SalesPerson Percentage RefundedAmount Site SaleID
Smith 06/04/2006 3,600.00 04/04/2006 Cooper 5 500.00 Site1 945
Smith 06/04/2006 11,600.00 06/04/2006 Cooper 5 500.00 Site2 945

The problem is i only want the RefundedAmount to be 500.00 once the rest to be 0 if the SaleID is the same

Plz explain a bit more...whats that "once the rest to be 0" ?|||Hi sorry bad english.

The problem is i only want the RefundedAmount to be 500.00 once. The rest to be 0 if the SaleID is the same.

Hope that helps|||What you posted is the existing result set from your stored procedure ?

Can you post your table structure, sample data from the table and the expected result.

Grouping on multiple Datasets

is it possible to group 2 or more datasets?
i.e i have a row that returns count of sales, ATV etc from one view
and paidout sales from another view this has to come from 2 datasets
due to them having different selection criteria
i am grouping by region on dataset1 but need to group by region on
dataset2 aslo?
Any help much appreciatednot possible today.
An option is to create a subreport.
The first report group the objects from the first dataset, the subreport
display the data from the second dataset filtered by a paremeter which is
the region.
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1181571015.624028.95320@.c77g2000hse.googlegroups.com...
> is it possible to group 2 or more datasets?
> i.e i have a row that returns count of sales, ATV etc from one view
> and paidout sales from another view this has to come from 2 datasets
> due to them having different selection criteria
> i am grouping by region on dataset1 but need to group by region on
> dataset2 aslo?
> Any help much appreciated
>|||many thanks|||It depends on what you are doing.
Are the datasets just select commands?
If so a simple union query is all that is required.
It you have a table per dataset, then you group each one accordingly.
More information is required but there are always many
'workarounds'...
Regards,
Tom Bizannes
Reporting Services Designer
Sydney Australia

Grouping on ISNULL SP

I use the folliowing SP in a report in which i group by categorydescription:

SELECT ISNULL(categorydescription,'No category indicator code') as categorydescription, AccountMV, AccountFeeLY, ISNULL(company,'other') from snapsraw
where branchstate = @.state and (monthend = @.date)

problem is it doesnt show the isnull value for categorydescription in the group table, its just blank?

nevermind, doesntshow up in VS, but shows up on websql

grouping my month/year desending by month/year

i have some classes that I want to group by month/year (note:i dont need the day of the month)

how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so..

11/2006

12/2006

1/2007

3/2007

i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.

dbo.classgiven.classdate date of the class.thank you all

SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )

SELECT DISTINCT MONTH(dbo.classgiven.classdate)}, YEAR(dbo.classgiven.classdate) FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ORDER BY 2, 1

You will have to join or format the two columns of data in your display code.

|||

Add this ORDER BY to your SELECT statement:

ORDERBYConvert(INT,(Convert(char(4),YEAR(dbo.classgiven.classdate))+Convert(varchar,RIGHT('0'+CAST(Month(dbo.classgiven.classdate)ASvarchar),2))))

|||

is there a way to change to format of a date from 1/3/2006 to 1/2006 using a format command in sql

example fortmat (datevalue,"mm/yyyy") .

and conver does not seem to work in a sql line

|||

You will have to use something like:

MONTH(dbo.classgiven.classdate) + '/' + YEAR(dbo.classgiven.classdate)

In the SELECT statement.

|||

yes i try that then

i getDescription:An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message:BC30451: Name 'dbo' is not declared.

SELECT DISTINCT year(dbo.classgiven.classdate)" + "/" + MONTH(dbo.classgiven.classdate) AS monthcodev,MONTH(dbo.classgiven.classdate) as monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ORDER BY 1", conNorthwind)

|||i see my mistake!! thanks it work on the money!!|||

do you know why when i use MONTH(dbo.classgiven.classdate) + '/' + YEAR(dbo.classgiven.classdate)

it does this 12-2006 as a math command and not as text

|||

Sorry! I left out Cast:

CAST(MONTH(OrderDate) AS varchar(2)) + '/' + CAST(YEAR(OrderDate) AS varchar(4))

|||thankssql

Friday, March 30, 2012

Grouping with a full join

Grouping Views by Dates

Grouping Views by Dates

Grouping Views by Dates

Grouping two similar column names but different data?

Grouping Sorting String and Numerical Fields

grouping similar data

Grouping several items in one group

Grouping Select Statements with where clause

Grouping Records & Assigning Sequential Number

Grouping Question

Grouping question

grouping problems

Wednesday, March 28, 2012

Grouping problem

Grouping problem

Grouping problem

Grouping prblm

Grouping on multiple Datasets

Grouping on ISNULL SP

grouping my month/year desending by month/year

Firehose mode

Blog Archive

About Me