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 on time

Hello,
I have a table with 2 columns, time and amount. I want to be able to group
by an interval and sum the amount see below of a sample of the data.
Time Amount
2005-02-16 05:41:00.000 100
2005-02-16 05:41:01.000 100
2005-02-16 05:41:02.000 100
2005-02-16 05:41:03.000 100
2005-02-16 05:41:04.000 100
2005-02-16 05:41:05.000 100
2005-02-16 05:41:06.000 100
2005-02-16 05:41:07.000 100
2005-02-16 05:41:08.000 100
2005-02-16 05:41:09.000 100
2005-02-16 05:41:10.000 100
2005-02-16 05:41:11.000 100
2005-02-16 05:41:12.000 100
2005-02-16 05:41:13.000 100
2005-02-16 05:41:14.000 100
so the result of the above with an interval of 5 seconds would be
Time Amount
2005-02-16 05:41:04.000 500
2005-02-16 05:41:09.000 500
2005-02-16 05:41:14.000 500
any ideas?
ThanksTry,
use northwind
go
create table t (
[Time] datetime,
Amount int
)
go
insert into t values('2005-02-16 05:41:00.000', 100)
insert into t values('2005-02-16 05:41:01.000', 100)
insert into t values('2005-02-16 05:41:02.000', 100)
insert into t values('2005-02-16 05:41:03.000', 100)
insert into t values('2005-02-16 05:41:04.000', 100)
insert into t values('2005-02-16 05:41:05.000', 100)
insert into t values('2005-02-16 05:41:06.000', 100)
insert into t values('2005-02-16 05:41:07.000', 100)
insert into t values('2005-02-16 05:41:08.000', 100)
insert into t values('2005-02-16 05:41:09.000', 100)
insert into t values('2005-02-16 05:41:10.000', 100)
insert into t values('2005-02-16 05:41:11.000', 100)
insert into t values('2005-02-16 05:41:12.000', 100)
insert into t values('2005-02-16 05:41:13.000', 100)
insert into t values('2005-02-16 05:41:14.000', 100)
go
select
max([time]) as max_time,
sum(amount) as sum_amount
from
t
group by
datediff(second, convert(char(8), [time], 112), [time]) / 5
go
drop table t
go
AMB
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||This was responded yesterday ( assumption is that there exists one row for
every monotonically increasing second ):
[url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[/u
rl]
Anith|||use something like that
select dateadd(ss,-datepart(ss,time)%5,time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||CREATE TABLE ReportPeriods
(period_id CHAR(10) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));
Load your times into the table then:
SELECT period_id, COUNT(*)
FROM ReportPeriods AS P1, Foobar AS F1
WHERE F1.event_time BETWEEN start_time AND end_time;|||sorry i made a mistake the script should be
select max(time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
the problem with the response of alejandro mesa is that if you have the same
time in different days the two rows will be grouped together
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||can you explan this part please?
-datepart(ss,time)%5
"sergiu" <sergiu@.discussions.microsoft.com> wrote in message
news:C3A9AA65-1277-4AEF-A517-60E4E03CED9B@.microsoft.com...
> sorry i made a mistake the script should be
> select max(time),sum(amount) from @.t group by
> dateadd(ss,-datepart(ss,time)%5,time)
> the problem with the response of alejandro mesa is that if you have the
> same
> time in different days the two rows will be grouped together
>
> "Fab" wrote:
>|||your assumption is wrong is my skip a second or two...
any ideas?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23uL11yVMFHA.568@.TK2MSFTNGP09.phx.gbl...
> This was responded yesterday ( assumption is that there exists one row for
> every monotonically increasing second ):
> [url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[
/url]
> --
> Anith
>|||On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:

>your assumption is wrong is my skip a second or two...
>any ideas?
Hi Fab,
So why didn't you indicate that the assumption was wrong in the original
thread? Half an hour ago, I saw the original thread with only Anith's
answer; I took the time to try a solution, write a message and send it.
And now, I find that you reposted the question in a new thread and
already got some replies.
If you had posted a follow-up to your original question instead of
starting a new thread, then I'd have seen the answers and moved on the
the next question, instead of wasting my time and cluttering the group
with yet another answer that isn't really any different from Alejandro's
suggestion.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||so now that you know your assumption was wrong are you still willing to help
me with my issue?
I need to group based on 5 seconds intervals...the result of the table will
roll up based on time not on the values in the table...so the results
should start at second 00 and end at second 04...anything that falls in
that 1st group will be rolled up...and so on for each interal all the way up
to 60.
let me know if you have any questions b4 you provide a solution.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g45941liog7iufggqi8eqvp8mngammggir@.
4ax.com...
> On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:
>
>
> Hi Fab,
> So why didn't you indicate that the assumption was wrong in the original
> thread? Half an hour ago, I saw the original thread with only Anith's
> answer; I took the time to try a solution, write a message and send it.
> And now, I find that you reposted the question in a new thread and
> already got some replies.
> If you had posted a follow-up to your original question instead of
> starting a new thread, then I'd have seen the answers and moved on the
> the next question, instead of wasting my time and cluttering the group
> with yet another answer that isn't really any different from Alejandro's
> suggestion.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Grouping attribute member in Dimension

Hi,

I have a Dimension with this structure:

ServiceID

ServiceType

ServiceTypeDesc

These are the sample of their members:

Service ID Service Type ServiceType Desc

1 Walk-In Walk-In

2 Contract Contract

3 HomeService Home Service

4 HomeService2 Home Service

5 Contract2 Contract

6 WalkIn2 Walk-In

7 Contract3 Contract

I assigned the ServiceTypeDesc in Service ID's (this serves as the key column) name column. When I drag it to filter, I could see 7 items, instead of grouping them into 3 (Walk-In, Contract, Home Service).

Is it possible to group them into 3 instead of giving me all the members?

cherriesh

You would want to create at least 2 attributes in your dimension. One based on Service ID which would be the key attribute, which you would use to join to the fact table(s) and another based on ServiceTypeDesc. In your example the ServiceTypeDesc attribute would want to have ServiceTypeDesc used as the key and name.

You get a distinct member in your attribute for each distinct value in the key column.

Grouping and Average Question

Hi,
My problem is as follows:
I would like to take the average value of count column grouping by drive
letter and date.
Sample table
DriveLetter Date Count
K: 2005-07-05 06:00:00:000 33.555
K: 2005-07-05 06:30:00:000 35.555
K: 2005-07-05 07:00:00:000 48.555
K: 2005-07-05 07:30:00:000 52.555
h: 2005-07-05 06:00:00:000 33.555
h: 2005-07-05 06:30:00:000 35.555
h: 2005-07-05 07:00:00:000 48.555
h: 2005-07-05 07:30:00:000 52.555
i: 2005-07-05 06:00:00:000 33.555
i: 2005-07-05 06:30:00:000 35.555
i: 2005-07-05 07:00:00:000 48.555
i: 2005-07-05 07:30:00:000 52.555
Thanks
MikeDragon9994 wrote:

> I would like to take the average value of count column grouping by
> drive letter and date.
> Sample table
> DriveLetter Date Count
> K: 2005-07-05 06:00:00:000 33.555
> K: 2005-07-05 06:30:00:000 35.555
> K: 2005-07-05 07:00:00:000 48.555
> K: 2005-07-05 07:30:00:000 52.555
select DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME) as
[Date], avg([Count]) as AvgCount
from SampleTable
group by DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME)
HTH,
Stijn Verrept.|||>> .. average value of count column grouping by drive
letter and date. <<
DATE and COUNT are reserved words in SQL.
SELECT drive_letter, foobar_date, AVG(foobar_count)
FROM Foobar
GROUP BY drive_letter, foobar_date;
I have no Stijn wants to CAST() temporal data into strings. I also
have no idea why he is also using CONVERT() unless he likes proprietary
code.|||--CELKO-- wrote:

> DATE and COUNT are reserved words in SQL.
Very true that date and count reserved words are, it's indeed better
not to use them, that's also why I put them between brackets.

> I have no Stijn wants to CAST() temporal data into strings. I also
> have no idea why he is also using CONVERT() unless he likes
> proprietary code.
Well if you check the original message you'll see that his sample date
column also contains hours and he wanted to group by date.
It maybe is a little confusing since the OP also uses Date as the
column name so it's not sure if he wants to sort by date or by the
column date (which also contains the time). HOWEVER, if you look at
the sample data you'll see that every hour only occurs once per drive
letter so it would be useless to get an average value, that why we can
be pretty sure that the OP means date (in date without time).
That's why your query is pretty much useless. If you run that query on
the data he has supplied you'll get exactly that same data back.
The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
of the time and only look at the date.
Hope this clears things up,
Stijn Verrept.|||Thanks for your help. It is what I needed to do.
Sorry for the confussion on the Date Column.
Mike
"Stijn Verrept" wrote:

> --CELKO-- wrote:
>
> Very true that date and count reserved words are, it's indeed better
> not to use them, that's also why I put them between brackets.
>
>
> Well if you check the original message you'll see that his sample date
> column also contains hours and he wanted to group by date.
> It maybe is a little confusing since the OP also uses Date as the
> column name so it's not sure if he wants to sort by date or by the
> column date (which also contains the time). HOWEVER, if you look at
> the sample data you'll see that every hour only occurs once per drive
> letter so it would be useless to get an average value, that why we can
> be pretty sure that the OP means date (in date without time).
> That's why your query is pretty much useless. If you run that query on
> the data he has supplied you'll get exactly that same data back.
> The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
> of the time and only look at the date.
> --
> Hope this clears things up,
> Stijn Verrept.
>

Group-based authorization in Forms Authentication

Hi,
I am using the Forms Authentication Sample to authenticate users in RS. I am
trying to find a sample code to check setup groups and assign access on
Folders to groups instead of users.
Please help.
Cheers
SaiSearch the below URL for 'forms authentication groups'. I found quite a few
results:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs
--
Adrian M.
MCP
"Sai Vootla" <Sai@.discussions.microsoft.com> wrote in message
news:86332D5C-5A49-4F29-9ED0-B2ABBEDFBF6E@.microsoft.com...
> Hi,
> I am using the Forms Authentication Sample to authenticate users in RS. I
> am
> trying to find a sample code to check setup groups and assign access on
> Folders to groups instead of users.
> Please help.
> Cheers
> Saisql

Group Total..

Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
>
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
>
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as = > >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>
>Custom field is available when you right click on the fields window in
Report Designer and click add. Type whatever expression you want. For
recursive functions, you add the keyword recursive as the last parameter of
the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message
news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Thanks Brian.
> Can you give me an example of using a custom field and using the Recusrive
> keyword? If there is a sample in BOL, then pls provide any reference/links
> (I wasn't able to locate any help on this topic)
> appreciate it.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
>> While I appreciate your eagerness to help, I think that most people would
>> prefer that you refrain from advertising other products in a forum
> dedicated
>> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
>> promise
>> not to post there. :)
>> That being said, you should be able to define a custom field that does
>> the
>> calculation and then referce the custom field in a sum in the group
> footer.
>> Presumably, you only need to add values from the inner group as the outer
>> group is just summary. If you want it to do parent / child hierarcy
>> aggregates, you need to use the recursive keyword.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Brian" <Brian@.discussions.microsoft.com> wrote in message
>> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>> >I recently came across a new software, that I think you might want to
> look
>> >into.
>> >
>> > www.simx.com/simx/home_report%20manager.htm
>> >
>> > Works with SQL Server, and I was able to do reporting much like what
>> > you
>> > are describing.
>> >
>> > "newmem" <"" wrote:
>> >
>> >> I'm working on a Financial Report which contains a column "XYZ" , its
>> >> value
>> >> is calculated from a formula by passing the row's record id and
>> >> commission
>> >> rate. (the formula is inside a custom dll). The values are correctly
>> >> computed. Now, the footer should display the total of all the rows in
> the
>> >> group.
>> >> for instance:
>> >>
>> >> "Unit" "BrandName" "XYZ Total" "Comments"
>> >> Sodas
>> >> Pepsi $361,000 gfyeefyefffee
>> >> Coca Cola $475,250 djfdfjdfddddd
>> >> RCola $28,757 re8reruejreerr
>> >>
>> >> fdfsfnfsfssf
>> >> _________________________________________
>> >> Total: $ 865,007
>> >>
>> >> Each of the "XYZ Total" in the above example, uses an expression as =>> >> FindTotal(recID!value, comm_rate!value)
>> >> In this case, how do I get the total in the footer? How to recursively
>> >> add
>> >> the FindTotal expression when it contains the row's unique record id?
>> >>
>> >> Thanks
>> >>
>> >> P.S. The above data is a sample data. The actual report contains 3
>> >> different
>> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> >> Transaction Title
>> >>
>> >>
>> >>
>>
>|||I was able to add the custom field and assign the expression for the
calculation with the inner group as scope.
Hwever, I need to sum unique dollar amount for each of the first row in
the inner group. Right now , its adding all the dollar amounts in the
group1.
The sample data below would give a better overview.
The database contains different rec IDs for the "Regular" and the "Diet"
Category. But the report should display the amounts only once, if the
category exitss for a soda then only the name of the category is displayed
in the second level.
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
=> Group1
Pepsi Regular $361,000 gfyeefyefffee
=> Group2
Diet
ffrfrefegfegegeg => Group 3
_____________
Pepsi Totals: $361,000
Coca Cola Regular $475,250 djfdfjdfddddd
Diet
tefdnfdfdjgdgdgd
Vanila $20,000
dewrwrwrwrwrwr
__________
Coca Cola Totals: $495,250
RCola $28,757
re8reruejreerr
==============================================Soda Totals: $885,007
When I use the custom expression to get the group total, for example in case
of "Pepsi", i get the total as $ 722,000. whch is incorrect.
Can you suggest a way to handle this case?
Thanks. Appreciate all the help.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:uyOAmreXEHA.736@.TK2MSFTNGP10.phx.gbl...
> Custom field is available when you right click on the fields window in
> Report Designer and click add. Type whatever expression you want. For
> recursive functions, you add the keyword recursive as the last parameter
of
> the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "newmem" <""> wrote in message
> news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Thanks Brian.
> > Can you give me an example of using a custom field and using the
Recusrive
> > keyword? If there is a sample in BOL, then pls provide any
reference/links
> > (I wasn't able to locate any help on this topic)
> >
> > appreciate it.
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> >> While I appreciate your eagerness to help, I think that most people
would
> >> prefer that you refrain from advertising other products in a forum
> > dedicated
> >> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
> >> promise
> >> not to post there. :)
> >>
> >> That being said, you should be able to define a custom field that does
> >> the
> >> calculation and then referce the custom field in a sum in the group
> > footer.
> >> Presumably, you only need to add values from the inner group as the
outer
> >> group is just summary. If you want it to do parent / child hierarcy
> >> aggregates, you need to use the recursive keyword.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> >> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >> >I recently came across a new software, that I think you might want to
> > look
> >> >into.
> >> >
> >> > www.simx.com/simx/home_report%20manager.htm
> >> >
> >> > Works with SQL Server, and I was able to do reporting much like what
> >> > you
> >> > are describing.
> >> >
> >> > "newmem" <"" wrote:
> >> >
> >> >> I'm working on a Financial Report which contains a column "XYZ" ,
its
> >> >> value
> >> >> is calculated from a formula by passing the row's record id and
> >> >> commission
> >> >> rate. (the formula is inside a custom dll). The values are correctly
> >> >> computed. Now, the footer should display the total of all the rows
in
> > the
> >> >> group.
> >> >> for instance:
> >> >>
> >> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> >> Sodas
> >> >> Pepsi $361,000 gfyeefyefffee
> >> >> Coca Cola $475,250 djfdfjdfddddd
> >> >> RCola $28,757 re8reruejreerr
> >> >>
> >> >> fdfsfnfsfssf
> >> >> _________________________________________
> >> >> Total: $ 865,007
> >> >>
> >> >> Each of the "XYZ Total" in the above example, uses an expression as
=> >> >> FindTotal(recID!value, comm_rate!value)
> >> >> In this case, how do I get the total in the footer? How to
recursively
> >> >> add
> >> >> the FindTotal expression when it contains the row's unique record
id?
> >> >>
> >> >> Thanks
> >> >>
> >> >> P.S. The above data is a sample data. The actual report contains 3
> >> >> different
> >> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> >> Transaction Title
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>|||Something got mangled in your posting so I don't quite understand what you
are looking for. It seems like you just need to hide the group header row
and drop the group label down to the inner group and set 'hide repeating' on
the group name. And just make sure you are not computing sums in your query.
You can let the report engine do them for you.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message news:uqcyNIhXEHA.712@.TK2MSFTNGP11.phx.gbl...
>I was able to add the custom field and assign the expression for the
> calculation with the inner group as scope.
> Hwever, I need to sum unique dollar amount for each of the first row in
> the inner group. Right now , its adding all the dollar amounts in the
> group1.
> The sample data below would give a better overview.
> The database contains different rec IDs for the "Regular" and the "Diet"
> Category. But the report should display the amounts only once, if the
> category exitss for a soda then only the name of the category is displayed
> in the second level.
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> => Group1
> Pepsi Regular $361,000 gfyeefyefffee
> => Group2
> Diet
> ffrfrefegfegegeg => Group 3
> _____________
> Pepsi Totals: $361,000
> Coca Cola Regular $475,250 djfdfjdfddddd
> Diet
> tefdnfdfdjgdgdgd
> Vanila $20,000
> dewrwrwrwrwrwr
> __________
> Coca Cola Totals: $495,250
> RCola $28,757
> re8reruejreerr
> ==============================================> Soda Totals: $885,007
> When I use the custom expression to get the group total, for example in
> case
> of "Pepsi", i get the total as $ 722,000. whch is incorrect.
> Can you suggest a way to handle this case?
> Thanks. Appreciate all the help.
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:uyOAmreXEHA.736@.TK2MSFTNGP10.phx.gbl...
>> Custom field is available when you right click on the fields window in
>> Report Designer and click add. Type whatever expression you want. For
>> recursive functions, you add the keyword recursive as the last parameter
> of
>> the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "newmem" <""> wrote in message
>> news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
>> > Thanks Brian.
>> > Can you give me an example of using a custom field and using the
> Recusrive
>> > keyword? If there is a sample in BOL, then pls provide any
> reference/links
>> > (I wasn't able to locate any help on this topic)
>> >
>> > appreciate it.
>> >
>> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> > news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
>> >> While I appreciate your eagerness to help, I think that most people
> would
>> >> prefer that you refrain from advertising other products in a forum
>> > dedicated
>> >> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
>> >> promise
>> >> not to post there. :)
>> >>
>> >> That being said, you should be able to define a custom field that does
>> >> the
>> >> calculation and then referce the custom field in a sum in the group
>> > footer.
>> >> Presumably, you only need to add values from the inner group as the
> outer
>> >> group is just summary. If you want it to do parent / child hierarcy
>> >> aggregates, you need to use the recursive keyword.
>> >>
>> >> --
>> >> Brian Welcker
>> >> Group Program Manager
>> >> SQL Server Reporting Services
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >>
>> >> "Brian" <Brian@.discussions.microsoft.com> wrote in message
>> >> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>> >> >I recently came across a new software, that I think you might want to
>> > look
>> >> >into.
>> >> >
>> >> > www.simx.com/simx/home_report%20manager.htm
>> >> >
>> >> > Works with SQL Server, and I was able to do reporting much like what
>> >> > you
>> >> > are describing.
>> >> >
>> >> > "newmem" <"" wrote:
>> >> >
>> >> >> I'm working on a Financial Report which contains a column "XYZ" ,
> its
>> >> >> value
>> >> >> is calculated from a formula by passing the row's record id and
>> >> >> commission
>> >> >> rate. (the formula is inside a custom dll). The values are
>> >> >> correctly
>> >> >> computed. Now, the footer should display the total of all the rows
> in
>> > the
>> >> >> group.
>> >> >> for instance:
>> >> >>
>> >> >> "Unit" "BrandName" "XYZ Total" "Comments"
>> >> >> Sodas
>> >> >> Pepsi $361,000 gfyeefyefffee
>> >> >> Coca Cola $475,250 djfdfjdfddddd
>> >> >> RCola $28,757 re8reruejreerr
>> >> >>
>> >> >> fdfsfnfsfssf
>> >> >> _________________________________________
>> >> >> Total: $ 865,007
>> >> >>
>> >> >> Each of the "XYZ Total" in the above example, uses an expression as
> =>> >> >> FindTotal(recID!value, comm_rate!value)
>> >> >> In this case, how do I get the total in the footer? How to
> recursively
>> >> >> add
>> >> >> the FindTotal expression when it contains the row's unique record
> id?
>> >> >>
>> >> >> Thanks
>> >> >>
>> >> >> P.S. The above data is a sample data. The actual report contains 3
>> >> >> different
>> >> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> >> >> Transaction Title
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>

Group Ranking

This summary is not available. Please click here to view the post.

Group By Constant

I know you can't use an alias in a group by, but is there any way to group b
y
a constant in the select list? See the following northwind sample of my
problem.
use NORTHWIND
select city, 'blah'
from customers
group BY ciy, 'blah'
having count(*) > 5You don't need to put the constant in the GROUP BY list:
select city, 'blah'
from customers
group BY city
having count(*) > 5
If that doesn't answer your question then please show us exactly what
result you want from this query.
David Portas
SQL Server MVP
--|||Why do you need to group by that? It doesn't make any sense. Just use:
select city, 'blah'
from customers
group BY city
having count(*) > 5
(Since 'blah' will be the same in every single row.)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:66E8D62C-144A-4DA2-85EB-EA3ABAE1D17D@.microsoft.com...
>I know you can't use an alias in a group by, but is there any way to group
>by
> a constant in the select list? See the following northwind sample of my
> problem.
> use NORTHWIND
> select city, 'blah'
> from customers
> group BY ciy, 'blah'
> having count(*) > 5|||I just thought everything that wasn't an agragate had to be in the group by
stmt. I tried everything except for leaving the constant out of the group by
.
Thanks!!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why do you need to group by that? It doesn't make any sense. Just use:
> select city, 'blah'
> from customers
> group BY city
> having count(*) > 5
> (Since 'blah' will be the same in every single row.)
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:66E8D62C-144A-4DA2-85EB-EA3ABAE1D17D@.microsoft.com...
>
>

Group by

I have a table defined with the following field names.
TableID Varchar(20)
CrcNbr Int
RegionName Varchar(25)
Sample Data:
--
pr_mstr,11500,Test
pr_mstr,11500,Trng
prd_det,12000,Test
prd_det,12005,Trng
prd_det,12005,Prod
I want to produce a report from this data that gives me this info. Saying
In what Regions does the table exist and are there a difference in the
CrcNbr's.
Table-Name Test Trng Prod Test/Trng Crc
Trng/Prod Crc
pr_mstr Y Y N Match
If 'N' under Prod tab leave this blank
prd_det Y Y Y Test Doesn't match Trng
MatchHi
It is usually better to do this one the client, but if not you can self join
the table and use case to determine the Ys or Ns such as:
SELECT T.Name,
CASE WHEN D1.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Test,
CASE WHEN D2.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Trng,
CASE WHEN D3.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Prod,
CASE WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr <>
D2.CrcNbr THEN 'No Matches'
WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr =
D2.CrcNbr THEN 'Trng Matches Prod'
...
END AS [Crc Checks]
FROM MyTables T
LEFT JOIN MyData d1 on T.TableId = D1.TableId AND D1.RegionName = 'Test'
LEFT JOIN MyData d2 on T.TableId = D2.TableId AND D2.RegionName = 'Trng'
LEFT JOIN MyData d3 on T.TableId = D2.TableId AND D3.RegionName = 'Prod'
John
"Hoosbruin" wrote:

> I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc
> Trng/Prod Crc
> pr_mstr Y Y N Match
> If 'N' under Prod tab leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||hi,
Select TableName,
Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
Case When Sum (Case When RegionName = 'Test' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Test' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Test/Trng',
Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Prod' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Prod/Trng'
From <YourTable>
Group by TableName
"Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc Trng/Prod Crc
> pr_mstr Y Y N Match If 'N' under Prod tab
> leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||Thanks...
worked GREAT !!!!!!
"arik" <arikf@.top4.com> wrote in message
news:OmQytdcjFHA.3580@.TK2MSFTNGP09.phx.gbl...
> hi,
> Select TableName,
> Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
> Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
> Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
> Case When Sum (Case When RegionName = 'Test' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Test' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Test/Trng',
> Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Prod' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Prod/Trng'
> From <YourTable>
> Group by TableName
>
> "Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
> news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>