grouping rows by customer

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

Thanks

Vlad

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

Rafael Salas

|||

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

it is more complicated calculation, than just grouping.

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

|||

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

Rafael Salas

|||

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

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

Grouping Result

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

I want my result rows to have an uniqe value in col_3. How can I exclude rows (but one) that have the same value in col_3?

Thanx

/sf

SELECT TOP 1 col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

|||Thank you for your answer.

But will not this query only output one row?

I'll try to explain my problem better:

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

What I want is output all rows with unique value in col_3 and if several rows have the same value in col_3, I only want one of those rows. The following could have worked, but it's not valid sql:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1
GROUP BY col_3|||SELECT MAX(col_1), MAX(col_2), col_3, MAX(col_4) FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY MAX(col_1)
GROUP BY col_3

I used MAX() for the non-grouped values, but you could use MIN(), SUM(), AVG()

Grouping problem

I am trying to get a table to display where my like rows would sum together,
but now matter how I do it there are 2 rows (in my example) that always show
as separate rows and I want to combine them.
For example:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
Should not show 0 0 0 0
Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
the 60 and 90 column should be on the same line.
This was done with the following statement:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (p1.PurchasedProductID = p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1
If I add the (ProductTypeID = 1) to the last line:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)
Then I get:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
This gets rid of the last line (which I wanted).
What I would like it to look like is:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 30 0 20 10
(5) 60-Day Posting 5 0 5 0
How can I make it do that?
I assume I have to group it, but I can't seem to make that work with these
subqueries. I get errors, such as you can't use a subquery in a group by
clause.
Here is the table and data (really cut down).
drop table PurchasedProducts
go
CREATE TABLE [dbo].[PurchasedProducts] (
[PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductTypeID] [int] NULL,
[ProductName] [varchar] (20) NULL ,
[PostingsLeft] [int] NULL ,
[DateExpires] [datetime] NULL
) ON [PRIMARY]
GO
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('30-Day Posting',1,1,'11/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,10,'1/24/2006')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,20,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(5) 60-Day Posting',1,5,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('Should not show',2,90,'12/01/2005')
go
Thanks,
TomThe easiest way to solve this is to group your results as illustrated below:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
- Peter Ward
WARDY IT Solutions
"tshad" wrote:

> I am trying to get a table to display where my like rows would sum togethe
r,
> but now matter how I do it there are 2 rows (in my example) that always sh
ow
> as separate rows and I want to combine them.
> For example:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> Should not show 0 0 0 0
> Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
> the 60 and 90 column should be on the same line.
> This was done with the following statement:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (p1.PurchasedProductID = p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1
> If I add the (ProductTypeID = 1) to the last line:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)
> Then I get:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> This gets rid of the last line (which I wanted).
> What I would like it to look like is:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 30 0 20 10
> (5) 60-Day Posting 5 0 5 0
> How can I make it do that?
> I assume I have to group it, but I can't seem to make that work with these
> subqueries. I get errors, such as you can't use a subquery in a group by
> clause.
> Here is the table and data (really cut down).
> drop table PurchasedProducts
> go
> CREATE TABLE [dbo].[PurchasedProducts] (
> [PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductTypeID] [int] NULL,
> [ProductName] [varchar] (20) NULL ,
> [PostingsLeft] [int] NULL ,
> [DateExpires] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('30-Day Posting',1,1,'11/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,10,'1/24/2006')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,20,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(5) 60-Day Posting',1,5,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('Should not show',2,90,'12/01/2005')
> go
>
> Thanks,
> Tom
>
>|||"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:89A22616-2111-4C1B-9D6D-F9AF452FB238@.microsoft.com...
> The easiest way to solve this is to group your results as illustrated
below:
Does it!!
You just treated my select as another table. I can never seem to come up
with that myself. I always understand it when I see it, but I can't seem to
see it when I need it.
Not really sure of the thought process to come up with it.
I was almost there, but couldn't quite see it.
Thanks,
Tom
> SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
> SUM(days60) AS [60], SUM(days90) AS [90]
> FROM (select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> Days30 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> Days60 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> Days90 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)) AS a
> GROUP BY productname
>
> - Peter Ward
> WARDY IT Solutions
> "tshad" wrote:
>
together,
show
in
these
by
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]

Grouping output

I have a query that drives the generation of a table. The query is filtered
during output. If the filtering results in no rows being output for the
table, i'd like to put some verbage on the table footer indicating "No
Matching Records" or something similar.
Is there an easy way to do this that i'm missing?
Thanks!
BrianTake a look at NoRows table property.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:e8xqt8gqEHA.324@.TK2MSFTNGP11.phx.gbl...
> I have a query that drives the generation of a table. The query is
filtered
> during output. If the filtering results in no rows being output for the
> table, i'd like to put some verbage on the table footer indicating "No
> Matching Records" or something similar.
> Is there an easy way to do this that i'm missing?
> Thanks!
> Brian
>sql

Grouping Level in Matrix component

Hi all,

I have an other question about Matrix component.

I'm using grouping on rows whith Drill Down enabled. The problem is when I drill down, the level below is shown on the same row. I would like the level to be shown on the row below his parent level to keep the values grouped in the data area of the Matrix:

Here is how it looks

% Class.

Amount Class.

A

AA

AAA

98,82%

�� 5.325.409,41

AAB

93,40%

�� 42.264.672,37

AAC

95,21%

�� 17.277.397,23

AAD

95,65%

�� 116.121.355,60

AAE

99,88%

�� 426.218.010,59

AAF

94,63%

�� 35.828.624,45

98,20%

�� 643.035.469,65

98,20%

�� 643.035.469,65

Here is want I want:

% Class.

Amount Class.

A

95%

��12876562121

AA

95%

��12876562121

AAA

98,82%

�� 5.325.409,41

AAB

93,40%

�� 42.264.672,37

AAC

95,21%

�� 17.277.397,23

AAD

95,65%

�� 116.121.355,60

AAE

99,88%

�� 426.218.010,59

AAF

94,63%

�� 35.828.624,45

98,20%

�� 643.035.469,65

Any idea ?

There are 2 ways to go 1) You can to add a second group for "AA"

2) you can right click on "A" and select "Add a row below" and in column/row expression place the value for "AA"

I hope this helps.

Hammer.

Grouping in columns rather than rows using table control?

Is there a way to transform the table object to display group data in columns
instead of rows? Here is my example:
Report services table can do this when grouping on YEAR
[1 GROUP Header (YEAR)
[Header
[ BODY Parameter1 Parameter 2 Parameter 3
[FOOTER
[1 GROUP Footer (SUM)
Example
Year 2000
Mike John Mary
5 1 4
5 2 2
SUM 10 3 6
Year 2001
Mike John Mary
1 6 5
2 2 2
SUM 3 8 7
What I want is this:
[ Group Header ] [Table Header] [DATA] [Table Footer] [Group
Footer]
YEAR Parameter 1
SUM
Parameter 2
Parameter 3
2000 SUM 2001 SUM
Mike 5 5 10 1 2 3
John 1 2 3 6 2 8
Mary 4 2 6 5 2 7
So the idea is to group by Year but display the SUMs in a column not in a
row. I just can't figure out how to use the Matrix control, I want to use the
table control functionality but with column output.
Thanksyou can use a matrix to do just that
"Ramez" wrote:
> Is there a way to transform the table object to display group data in columns
> instead of rows? Here is my example:
> Report services table can do this when grouping on YEAR
> [1 GROUP Header (YEAR)
> [Header
> [ BODY Parameter1 Parameter 2 Parameter 3
> [FOOTER
> [1 GROUP Footer (SUM)
> Example
> Year 2000
> Mike John Mary
> 5 1 4
> 5 2 2
> SUM 10 3 6
> Year 2001
> Mike John Mary
> 1 6 5
> 2 2 2
> SUM 3 8 7
> What I want is this:
> [ Group Header ] [Table Header] [DATA] [Table Footer] [Group
> Footer]
> YEAR Parameter 1
> SUM
> Parameter 2
> Parameter 3
> 2000 SUM 2001 SUM
> Mike 5 5 10 1 2 3
> John 1 2 3 6 2 8
> Mary 4 2 6 5 2 7
> So the idea is to group by Year but display the SUMs in a column not in a
> row. I just can't figure out how to use the Matrix control, I want to use the
> table control functionality but with column output.
> Thanks

Grouping consecutive rows of data

This is driving me crazy, I have the following table
Unit Line ProductCode StartTime EndTime
-- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27
11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-27
12:51:52.423
3 1 283724 2003-05-27 12:51:52.423 2003-05-30
07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16
10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04
12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-07
14:25:18.483
3 1 260775 2003-07-07 14:25:18.483 2003-07-08
07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14
12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14
12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15
08:09:30.030
3 1 255779 2003-07-15 08:09:30.030 2003-07-15
16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15
16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18
10:53:48.927
3 1 203716 2003-07-18 10:53:48.927 2003-07-18
10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18
10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-18
15:40:57.113
3 1 203716 2003-07-18 15:40:57.113 2003-07-18
15:41:05.660
3 1 203716 2003-07-18 15:41:05.660 2003-07-18
16:22:23.050
3 1 203716 2003-07-18 16:22:23.050 2003-07-23
16:25:37.407
I want to be able to group each Unit, Line and ProductCode together so
I can pick up the StartTime and EndTime for consecutive product runs.
i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
until 2003-07-18 10:55:17.677.
This is then replaced by ProductCode 102001 which runs from 2003-07-18
10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
16:25:37.407.
I tried the following view:
SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode
ORDER BY StartTime, Unit, Line
But it takes the first time a Product is run and the last time it was
run i.e.
for ProductCode 203716
StartTime 2003-07-15 16:06:17.527
EndTime 2003-07-23 16:25:37.407
Instead of two records.
Many thanks
JimThis is a multi-part message in MIME format.
--=_NextPart_000_014B_01C35111.500514B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
Unit
, Line
, ProductCode
, Type
, case when Type =3D 1 then min (StartTime) else max (EndTime)
from
MyTable
cross join
(
select 1 as Type
union all
select 2
) as x
group by
Unit
, Line
, ProductCode
, Type
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jim" <jim.holmes@.devro-casings.com> wrote in message =news:68dfae14.0307230741.2f1a9b49@.posting.google.com...
This is driving me crazy, I have the following table
Unit Line ProductCode StartTime EndTime
-- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27
11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-27
12:51:52.423
3 1 283724 2003-05-27 12:51:52.423 2003-05-30
07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16
10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04
12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-07
14:25:18.483
3 1 260775 2003-07-07 14:25:18.483 2003-07-08
07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14
12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14
12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15
08:09:30.030
3 1 255779 2003-07-15 08:09:30.030 2003-07-15
16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15
16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18
10:53:48.927
3 1 203716 2003-07-18 10:53:48.927 2003-07-18
10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18
10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-18
15:40:57.113
3 1 203716 2003-07-18 15:40:57.113 2003-07-18
15:41:05.660
3 1 203716 2003-07-18 15:41:05.660 2003-07-18
16:22:23.050
3 1 203716 2003-07-18 16:22:23.050 2003-07-23
16:25:37.407
I want to be able to group each Unit, Line and ProductCode together so
I can pick up the StartTime and EndTime for consecutive product runs.
i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
until 2003-07-18 10:55:17.677.
This is then replaced by ProductCode 102001 which runs from 2003-07-18
10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
16:25:37.407.
I tried the following view:
SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode
ORDER BY StartTime, Unit, Line
But it takes the first time a Product is run and the last time it was
run i.e.
for ProductCode 203716 StartTime 2003-07-15 16:06:17.527 EndTime 2003-07-23 16:25:37.407
Instead of two records.
Many thanks
Jim
--=_NextPart_000_014B_01C35111.500514B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=Unit
, =Line
, ProductCode
, =Type
, case when =Type =3D 1 then min (StartTime) else max (EndTime)
from
=MyTable
cross join
(
select 1 as =Type union all select 2) as x
group by
=Unit
, =Line
, ProductCode
, =Type-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jim" wrote in message news:68dfae=14.0307230741.2f1a9b49@.posting.google.com...This is driving me crazy, I have the following tableUnit =Line ProductCode StartTime &nbs=p; EndTime-- -- -- ----3  =; 1 120064 2002-08-30 =12:31:26.810 2003-05-2711:39:51.1573 =1 283724 2003-05-27 11:39:51.157 2003-05-2712:51:52.4233 =1 283724 2003-05-27 12:51:52.423 2003-05-3007:12:38.9973 =1 285775 2003-05-30 07:12:38.997 2003-06-1610:37:01.8133 =1 230571 2003-06-16 10:37:01.813 2003-07-0412:40:32.0973 =1 260775 2003-07-04 12:40:32.097 2003-07-0714:25:18.4833 =1 260775 2003-07-07 14:25:18.483 2003-07-0807:02:04.3033 =1 265775 2003-07-08 07:02:04.303 2003-07-1412:37:42.5133 =1 215690 2003-07-14 12:37:42.513 2003-07-1412:38:23.4503 =1 255779 2003-07-14 12:38:23.450 2003-07-1508:09:30.0303 =1 255779 2003-07-15 08:09:30.030 2003-07-1516:05:53.3233 =1 205716 2003-07-15 16:05:53.323 2003-07-1516:06:17.5273 =1 203716 2003-07-15 16:06:17.527 2003-07-1810:53:48.9273 =1 203716 2003-07-18 10:53:48.927 2003-07-1810:55:17.6773 =1 102001 2003-07-18 10:55:17.677 2003-07-1810:55:54.1903 =1 203716 2003-07-18 10:55:54.190 2003-07-1815:40:57.1133 =1 203716 2003-07-18 15:40:57.113 2003-07-1815:41:05.6603 =1 203716 2003-07-18 15:41:05.660 2003-07-1816:22:23.0503 =1 203716 2003-07-18 16:22:23.050 2003-07-2316:25:37.407I want to be able to group each Unit, =Line and ProductCode together soI can pick up the StartTime and EndTime for consecutive product runs.i.e.ProductCode 203716 starts at 2003-07-15 =16:06:17.527 and runsuntil 2003-07-18 10:55:17.677.This is =then replaced by ProductCode 102001 which runs from =2003-07-1810:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by203716 which runs =from 2003-07-18 10:55:54.190 until 2003-07-2316:25:37.407.I tried =the following view:SELECT TOP 100 PERCENT =Unit, Line, ProductCode, MIN(StartTime) ASStartTime, MAX(EndTime) AS =EndTime FROM D_ProductionLogGROUP BY Unit, Line, ProductCodeORDER =BY StartTime, Unit, LineBut it takes the first time a Product is =run and the last time it wasrun i.e.for ProductCode 203716 StartTime 2003-07-15 16:06:17.527 EndTime 2003-07-23 16:25:37.407 Instead =of two records.Many thanksJim

---

--=_NextPart_000_014B_01C35111.500514B0--|||I am confused, do you want to get one record per ProductCode or you just
want to order them together?
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230741.2f1a9b49@.posting.google.com...
> This is driving me crazy, I have the following table
>
> Unit Line ProductCode StartTime EndTime
> -- -- -- --
> --
> 3 1 120064 2002-08-30 12:31:26.810 2003-05-27
> 11:39:51.157
> 3 1 283724 2003-05-27 11:39:51.157 2003-05-27
> 12:51:52.423
> 3 1 283724 2003-05-27 12:51:52.423 2003-05-30
> 07:12:38.997
> 3 1 285775 2003-05-30 07:12:38.997 2003-06-16
> 10:37:01.813
> 3 1 230571 2003-06-16 10:37:01.813 2003-07-04
> 12:40:32.097
> 3 1 260775 2003-07-04 12:40:32.097 2003-07-07
> 14:25:18.483
> 3 1 260775 2003-07-07 14:25:18.483 2003-07-08
> 07:02:04.303
> 3 1 265775 2003-07-08 07:02:04.303 2003-07-14
> 12:37:42.513
> 3 1 215690 2003-07-14 12:37:42.513 2003-07-14
> 12:38:23.450
> 3 1 255779 2003-07-14 12:38:23.450 2003-07-15
> 08:09:30.030
> 3 1 255779 2003-07-15 08:09:30.030 2003-07-15
> 16:05:53.323
> 3 1 205716 2003-07-15 16:05:53.323 2003-07-15
> 16:06:17.527
> 3 1 203716 2003-07-15 16:06:17.527 2003-07-18
> 10:53:48.927
> 3 1 203716 2003-07-18 10:53:48.927 2003-07-18
> 10:55:17.677
> 3 1 102001 2003-07-18 10:55:17.677 2003-07-18
> 10:55:54.190
> 3 1 203716 2003-07-18 10:55:54.190 2003-07-18
> 15:40:57.113
> 3 1 203716 2003-07-18 15:40:57.113 2003-07-18
> 15:41:05.660
> 3 1 203716 2003-07-18 15:41:05.660 2003-07-18
> 16:22:23.050
> 3 1 203716 2003-07-18 16:22:23.050 2003-07-23
> 16:25:37.407
> I want to be able to group each Unit, Line and ProductCode together so
> I can pick up the StartTime and EndTime for consecutive product runs.
> i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
> until 2003-07-18 10:55:17.677.
> This is then replaced by ProductCode 102001 which runs from 2003-07-18
> 10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
> 203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
> 16:25:37.407.
> I tried the following view:
> SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode
> ORDER BY StartTime, Unit, Line
> But it takes the first time a Product is run and the last time it was
> run i.e.
> for ProductCode 203716
> StartTime 2003-07-15 16:06:17.527
> EndTime 2003-07-23 16:25:37.407
> Instead of two records.
> Many thanks
> Jim|||Greetings,
Unless you breathe,sniff,snort and live S2k sql queries like
this are difficult to construct.An easy way to solve this
problem is to create a column with the same value for every
combination of Unit,Line and ProductCode based on the sort
order of StartTime.This new column or rank can then be used
as a grouping column to find the min and max times for each
Unit,Line and ProductCode combination.Current versions of
Oracle and DB2 have this sql99 functionality,S2k does not.
create table #D_ProductionLog(Unit int,Line int,
ProductCode int,StartTime datetime,EndTime datetime)
go
insert #D_ProductionLog values(3,1,120064,'2002-08-30
12:31:26.810','2003-05-27 11:39:51.157')
insert #D_ProductionLog values(3,1,283724,'2003-05-27
11:39:51.157','2003-05-27 12:51:52.423')
insert #D_ProductionLog values(3,1,283724,'2003-05-27
12:51:52.423','2003-05-30 07:12:38.997')
insert #D_ProductionLog values(3,1,285775,'2003-05-30
07:12:38.997','2003-06-16 10:37:01.813')
insert #D_ProductionLog values(3,1,230571,'2003-06-16
10:37:01.813','2003-07-04 12:40:32.097')
insert #D_ProductionLog values(3,1,260775,'2003-07-04
12:40:32.097','2003-07-07 14:25:18.483')
insert #D_ProductionLog values(3,1,260775,'2003-07-07
14:25:18.483','2003-07-08 07:02:04.303')
insert #D_ProductionLog values(3,1,265775,'2003-07-08
07:02:04.303','2003-07-14 12:37:42.513')
insert #D_ProductionLog values(3,1,215690,'2003-07-14
12:37:42.513','2003-07-14 12:38:23.450')
insert #D_ProductionLog values(3,1,255779,'2003-07-14
12:38:23.450','2003-07-15 08:09:30.030')
insert #D_ProductionLog values(3,1,255779,'2003-07-15
08:09:30.030','2003-07-15 16:05:53.323')
insert #D_ProductionLog values(3,1,205716,'2003-07-15
16:05:53.323','2003-07-15 16:06:17.527')
insert #D_ProductionLog values(3,1,203716,'2003-07-15
16:06:17.527','2003-07-18 10:53:48.927')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
10:53:48.927','2003-07-18 10:55:17.677')
insert #D_ProductionLog values(3,1,102001,'2003-07-18
10:55:17.677','2003-07-18 10:55:54.190')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
10:55:54.190','2003-07-18 15:40:57.113')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
15:40:57.113','2003-07-18 15:41:05.660')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
15:41:05.660','2003-07-18 16:22:23.050')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
16:22:23.050','2003-07-23 16:25:37.407')
You can use the RAC utility for S2k to easily create this
virtual column.The 'drank' column binds together the same Unit,Line
and ProductCode combinations in the sort order of StartTime.
Exec Rac
@.transform='_dummy_',@.style='121',@.datelen='25',
@.rows='Unit & Line & ProductCode & StartTime(date) & EndTime(date)',
@.rowsort='StartTime & Unit & Line & ProductCode',
@.pvtcol='Report Mode',
@.from='#D_ProductionLog',
-- create the virtual column drank using the @.rowindicators parameter.
@.rowindicators='ProductCode{drank}',@.counterdatatype='integer',
@.grand_totals='n',@.rowbreak='n',@.racheck='y',@.defaultexceptions='dumy'
This result shows drank* column and how it binds.It is simply an
incrementing integer for each combination.
Unit Line ProductCode StartTime EndTime
drank*
-- -- -- -- --
-- --
3 1 120064 2002-08-30 12:31:26.810 2003-05-27 11:39:51.157
1
3 1 283724 2003-05-27 11:39:51.157 2003-05-27 12:51:52.423
2
3 1 283724 2003-05-27 12:51:52.423 2003-05-30 07:12:38.997
2
3 1 285775 2003-05-30 07:12:38.997 2003-06-16 10:37:01.813
3
3 1 230571 2003-06-16 10:37:01.813 2003-07-04 12:40:32.097
4
3 1 260775 2003-07-04 12:40:32.097 2003-07-07 14:25:18.483
5
3 1 260775 2003-07-07 14:25:18.483 2003-07-08 07:02:04.303
5
3 1 265775 2003-07-08 07:02:04.303 2003-07-14 12:37:42.513
6
3 1 215690 2003-07-14 12:37:42.513 2003-07-14 12:38:23.450
7
3 1 255779 2003-07-14 12:38:23.450 2003-07-15 08:09:30.030
8
3 1 255779 2003-07-15 08:09:30.030 2003-07-15 16:05:53.323
8
3 1 205716 2003-07-15 16:05:53.323 2003-07-15 16:06:17.527
9
3 1 203716 2003-07-15 16:06:17.527 2003-07-18 10:53:48.927
10
3 1 203716 2003-07-18 10:53:48.927 2003-07-18 10:55:17.677
10
3 1 102001 2003-07-18 10:55:17.677 2003-07-18 10:55:54.190
11
3 1 203716 2003-07-18 10:55:54.190 2003-07-18 15:40:57.113
12
3 1 203716 2003-07-18 15:40:57.113 2003-07-18 15:41:05.660
12
3 1 203716 2003-07-18 15:41:05.660 2003-07-18 16:22:23.050
12
3 1 203716 2003-07-18 16:22:23.050 2003-07-23 16:25:37.407
12
Using the drank column in a GROUP BY easily solves the problem in
a single execution of RAC.
Exec Rac
@.transform='_dummy_',@.style='121',@.datelen='25',
@.rows='Unit & Line & ProductCode & StartTime(date) & EndTime(date)',
@.rowsort='StartTime & Unit & Line & ProductCode',
@.pvtcol='Report Mode',
@.from='#D_ProductionLog',
@.rowindicators='ProductCode{drank}',@.counterdatatype='integer',
@.grand_totals='n',@.rowbreak='n',@.racheck='y',
-- Use a simple group by query to solve the problem.
-- You could also include the count of each combination if desired.
@.select='select Unit,Line,ProductCode,MIN(StartTime) AS StartTime,
MAX(EndTime) AS EndTime
from rac
group by drank,Unit,Line,ProductCode
order by drank'
Unit Line ProductCode StartTime EndTime
-- -- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27 11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-30 07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16 10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04 12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-08 07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14 12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14 12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15 16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15 16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18 10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18 10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-23 16:25:37.407
RAC v2.2 and QALite released.
www.rac4sql.net

group several rows in a SQL query

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

id value
1 1
2 3
3 5
4 7
5 9

The result I expect from the query are 3 rows: [9, 15,21]:
1st row: 1+3+5 = 9
2nd row: 3+5+7 = 15
3rd row: 5+7+9 = 21

Help is appreciated,
Guidoa few sql commands? how about just one? :)select this.value + prev.value + next.value as result
from daTable as this
inner
join daTable as prev
on prev.id =
( select max(id)
from daTable
where id < this.id )
inner
join daTable as next
on next.id =
( select min(id)
from daTable
where id > this.id )|||Thanks for your response, its awesome, but I dont think it fits my needs. I gave an example with just three consecutive rows accumulated, in real life it will be hundreds of rows.
The number of consecutive rows isnt fixed, either, it depends on user input.

Thank you anyway,
Guido|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

i'm done in this thread|||Asking the question you want anwsered? Hmm....a radical concept.

This code will work provided your IDs are consecutively numbered with no gaps:

declare @.RangeLength
set @.RangeLength = 3

select t1.id,
sum(t2.value)
from [YourTable] t1
inner join [YourTable] t2 on t2.id between t1.id-@.RangeLength+1 and t1.id
group by t1.id

If your IDs are not uniformly sequential, then things get more complex, and might best be solved using a temporary table.|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

I did... not as clearly as I should have, but I did:

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

@.blindman:
Yes, my IDs are consecutively numbered, without gaps. Ill try your solution, many thanks.

Guido|||Maybe this does what you want:WITH t(value, nr) AS
(SELECT value, (ROW_NUMBER() OVER (ORDER BY id) - 1)/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nr(Just replace 17 by the number of rows you want to group.)|||Alternatively, if your SQL doesn't have ROW_NUMBER(), and/or if your "id" column just contains all numbers 1, 2, 3 etc. (which means they already contain the ROW_NUMBER()s), the following gives the same result:WITH t(value, nr) AS
(SELECT value, id/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nrOr possibly even, if your SQL allows grouping by expression:SELECT SUM(value)
FROM myTable
GROUP BY id/17or possibly (in case your SQL doesn't perform integer division):SELECT SUM(value)
FROM myTable
GROUP BY CAST(id/17 AS int)|||Thanks Peter,

I will test your script, too.|||Guido

The following seems to work fine and may be easier to understand.
Again the id's need to be sequential but I believe you said they were.

Mike

select t1.val + t2.val + t3.val as vals
from my_table t1,
my_table t2,
my_table t3
where t1.id < t2.id
and t2.id < t3.id
and t3.id < t1.id + 3;|||Easier to understand than a single join? I don't think so.
Plus, you need to read GNiewerth's second post. Your solution is not scalable.|||you need to read GNiewerth's second post. Your solution is not scalable.
You're right - I didn't see that requirement - please ignore my code then!
Mike

Group header printed unnecessarily on page because no rows below

I am using a table with grouping - but found that the group header was
printed at the bottom of the page (when exported to PDF) with no rows below
it. The following page printed the group header again with the rows.
Is there a way to stop that behaviour?
Example...
Page1.
Group Header
Row
Row
Group Footer
Group Header <<< this printed unnecessarily
Page 2
Group Header
Row
Group Footer
--
McGeeky
http://mcgeeky.blogspot.comStill no workaround for this? I hate, hate , hate telling my users they just
have to deal with it.
"McGeeky" wrote:
> I am using a table with grouping - but found that the group header was
> printed at the bottom of the page (when exported to PDF) with no rows below
> it. The following page printed the group header again with the rows.
> Is there a way to stop that behaviour?
> Example...
> Page1.
> Group Header
> Row
> Row
> Group Footer
> Group Header <<< this printed unnecessarily
> Page 2
> Group Header
> Row
> Group Footer
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>

Group Header Alternating color

I am trying to get alternating colors on group headers.

The rownumber() doesn't work; that only seems to be the count of rows in the group.

Does anyone have any great ideas for this?

Thanks!

BobP

Have you tried CountDistinct(<group expression fields>)?|||

Yes, I get a "1" for each group header row.

I have also tried countrows.

BobP

|||I realize this was a year ago, but was wondering if anyone had figured out a solution. I am trying to alternate colors at the group level and am not having any luck...
|||

If I understand you correctly, this should give you the desired result:

Add this to your code window:

Code Snippet

Dim numFooterRow as Double

Function GetRowNumber() as Double
numFooterRow += 1
Return numFooterRow
End Function

Add a new column to the table and mark it as not visible. On the group header row (the invisible column), add this expression:

Code Snippet

=Code.GetRowNumber()

You will need the name of the textbox that you just entered the expression into for the final part. In the header row background color add the following expression:

Code Snippet

=iif(ReportItems!textbox18.Value Mod 2 = 0,"LightCoral","RosyBrown")

You now have alternating colors at the group header level.

Simone

|||Wow, thanks so much, Simone! I've been banging my head against the wall on this one for the last hour, trying to think up ways to do it in SQL, using the Previous() function, etc. This worked like a charm!
|||I am trying to do something where I say look through the row. When you come across the word "Start" color that box green and color all other boxes to the right in that row green as well until you come upon the word "stop". Is this possible? If I am not making sense just let me know and I will try to explain better. Thanks in advance for any help that I get.|||You might want to start a new thread, although somewhat similar, it seems to me its a different topic...|||Ok will do.

Group Header Alternating color

I am trying to get alternating colors on group headers.

The rownumber() doesn't work; that only seems to be the count of rows in the group.

Does anyone have any great ideas for this?

Thanks!

BobP

Have you tried CountDistinct(<group expression fields>)?|||

Yes, I get a "1" for each group header row.

I have also tried countrows.

BobP

|||I realize this was a year ago, but was wondering if anyone had figured out a solution. I am trying to alternate colors at the group level and am not having any luck...
|||

If I understand you correctly, this should give you the desired result:

Add this to your code window:

Code Snippet

Dim numFooterRow as Double

Function GetRowNumber() as Double
numFooterRow += 1
Return numFooterRow
End Function

Add a new column to the table and mark it as not visible. On the group header row (the invisible column), add this expression:

Code Snippet

=Code.GetRowNumber()

You will need the name of the textbox that you just entered the expression into for the final part. In the header row background color add the following expression:

Code Snippet

=iif(ReportItems!textbox18.Value Mod 2 = 0,"LightCoral","RosyBrown")

You now have alternating colors at the group header level.

Simone

|||Wow, thanks so much, Simone! I've been banging my head against the wall on this one for the last hour, trying to think up ways to do it in SQL, using the Previous() function, etc. This worked like a charm!
|||I am trying to do something where I say look through the row. When you come across the word "Start" color that box green and color all other boxes to the right in that row green as well until you come upon the word "stop". Is this possible? If I am not making sense just let me know and I will try to explain better. Thanks in advance for any help that I get.|||You might want to start a new thread, although somewhat similar, it seems to me its a different topic...|||Ok will do.

Group Header Alternating color

I am trying to get alternating colors on group headers.

The rownumber() doesn't work; that only seems to be the count of rows in the group.

Does anyone have any great ideas for this?

Thanks!

BobP

Have you tried CountDistinct(<group expression fields>)?|||

Yes, I get a "1" for each group header row.

I have also tried countrows.

BobP

|||I realize this was a year ago, but was wondering if anyone had figured out a solution. I am trying to alternate colors at the group level and am not having any luck...
|||

If I understand you correctly, this should give you the desired result:

Add this to your code window:

Code Snippet

Dim numFooterRow as Double

Function GetRowNumber() as Double
numFooterRow += 1
Return numFooterRow
End Function

Add a new column to the table and mark it as not visible. On the group header row (the invisible column), add this expression:

Code Snippet

=Code.GetRowNumber()

You will need the name of the textbox that you just entered the expression into for the final part. In the header row background color add the following expression:

Code Snippet

=iif(ReportItems!textbox18.Value Mod 2 = 0,"LightCoral","RosyBrown")

You now have alternating colors at the group header level.

Simone

|||Wow, thanks so much, Simone! I've been banging my head against the wall on this one for the last hour, trying to think up ways to do it in SQL, using the Previous() function, etc. This worked like a charm!
|||I am trying to do something where I say look through the row. When you come across the word "Start" color that box green and color all other boxes to the right in that row green as well until you come upon the word "stop". Is this possible? If I am not making sense just let me know and I will try to explain better. Thanks in advance for any help that I get.|||You might want to start a new thread, although somewhat similar, it seems to me its a different topic...|||Ok will do.

GROUP BY syntax

I'm trying to group rows in ItemKey to produce a sum total of prices. Then
update ItemKeyprice. The select statement works, but I get the below error.
Where is my syntax wrong?
thanks
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
error: Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.shank
(untested)
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
Note: Without seeing your table stucture + samle data I could only guess
,it is possible yo9u get an error like
"Subquery returned more than 1 value."
"shank" <shank@.tampabay.rr.com> wrote in message
news:eTrms$l$FHA.1288@.TK2MSFTNGP09.phx.gbl...
> I'm trying to group rows in ItemKey to produce a sum total of prices. Then
> update ItemKeyprice. The select statement works, but I get the below
> error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
>|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to group rows in ItemKey to produce a sum total of prices.
> Then update ItemKeyprice. The select statement works, but I get the
> below error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
The immediate error is the inclusion of ItemKey.KeyNumber in the Select
list. You are assigning RegPrice value, but you are sending it two
values.
However, when you fix that error, you will get another error saying that
subquery returned more than one value.
Here are two ways of writing what I think you want to achieve:
UPDATE ItemKeyPrice
SET RegPrice = (SELECT SUM(ItemKey.Price)
FROM ItemKey
WHERE ItemKey.KeyNumber = ItemKeyPrice.KeyNumber)
UPDATE ItemKeyPrice
SET RegPrice = ik.totprice
FROM ItemKeyPrice ikp
JOIN (SELECT KeyNumber, totprice = SUM(ItemKey.Price)
FROM ItemKey
GROUP BY KeyNumber) AS ik ON ik.KeyNumber = ikp.KeyNumber
The first uses a correlated subquery, and this syntax is by the ANSI
standard and should run on any DBMS. The second uses a FROM clause in
the UPDATE statement. This syntax is particular to MS SQL Server and
Sybase, and thus less portable. This query also includes a derived
table (which is part of ANSI SQL) to compute the sums per key.
While the first syntax is portable, my preference is very strongly
for the second, as it is easier to write and understand, and usually
also gives better performance. Furthermore, assume that you would have
more columns to set, like an average price, min price etc. In this case,
you would have to have several correlated subqueries, but the FROM
solution is very extensible in this regard.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Group by statement problem

I am using the T-SQL code below to pull patient information. The code returns 86 rows, however, there are only 9 distinct account numbers. Why is the group by statement not grouping these together to only display the 9 distinct accounts and associated data?

select
srm.episodes.episode_type as Visit_Type,
srm.episodes.account_number as Account_Number,
srm.episodes.medrec_no as MRN,
dbo.PtMstr.PatientFullName,
left(srm.episodes.admission_date,11) as Admit_Date,
left(srm.episodes.episode_date,11) as Disch_Date,
dbo.PtMstr.Cases as Cases,
dbo.PtMstr.TotCharges,
srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,
srm.cdmab_base_info.adm_dx_adt as Admitting_Dx
,srm.event_types.event_type_code
from srm.cdmab_base_info inner join
srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key
inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key
inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key
inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number
where srm.cdmab_base_info.abst_cmp_status <> 'Y'
and srm.episodes.episode_date is not null
and srm.event_types.event_type_code <> 'ACOD'
AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate
AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)
Group By srm.episodes.account_number,
dbo.PtMstr.TotCharges,
srm.episodes.episode_type,
srm.episodes.medrec_no,
dbo.PtMstr.PatientFullName,
srm.episodes.admission_date,
srm.episodes.episode_date,
dbo.PtMstr.Cases,
srm.cdmab_base_info.abst_cmp_status,
srm.cdmab_base_info.adm_dx_adt,
srm.event_types.event_type_code

Use the following query..

select

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

Group By

srm.episodes.account_number,

dbo.PtMstr.TotCharges,

srm.episodes.episode_type,

srm.episodes.medrec_no,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases,

srm.cdmab_base_info.abst_cmp_status,

srm.cdmab_base_info.adm_dx_adt,

srm.event_types.event_type_code

|||

I had to remove the AS portion of the group by clause to get the code to work , however, it still returns 86 rows versus the expected 9 distinct rows.

|||

How you know there is only 9 distinct record. You only the get the number of rows as per the following query..& i didn't understand your requirement on your query(there is no group by funcations used).

select Distinct

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

|||

I appreciate you help. I ordered the data by account number and saw there were 9 distinct account numbers. I also noticed the srm.event_types.event_type_code field should not have been in this query; once I removed it, the code returned the expected 9 rows of data using either of the examples you provided. Thanks again for your assistance.

|||

You are grouping several additional columns after the account number.

If you just want the nine accounts listed, you'll need to just group on that column.

Then you can apply aggregates to get sums, etc. of the other data you desire.

Group By Row Count And Force Page Break

Hello All!

Is there a "clean" way to group rows from a table by the row count, and then force a page break each time the count hits a maximum number (say 25 rows)?

As an alternative, can you set the maximum count of rows in a table, and then force a page break if it reaches that count? (this may actually be a better solution for my needs).

Thanks!

Create a group, set the expression for grouping to "=Fix((RowNumber("table1")-1) / 25)", and set the page break at end option on. To vary the number of rows per page, change 25 to your desired value.|||This works good, except that your table footer will always be on another page. Any ideas on how to prevent this using the solution above?|||In order to get table header and footer on all pages (and no hanging footer on last page), put the table in a list and do the grouping on the list.

Group By Row Count And Force Page Break

Hello All!

Is there a "clean" way to group rows from a table by the row count, and then force a page break each time the count hits a maximum number (say 25 rows)?

As an alternative, can you set the maximum count of rows in a table, and then force a page break if it reaches that count? (this may actually be a better solution for my needs).

Thanks!

Create a group, set the expression for grouping to "=Fix((RowNumber("table1")-1) / 25)", and set the page break at end option on. To vary the number of rows per page, change 25 to your desired value.|||This works good, except that your table footer will always be on another page. Any ideas on how to prevent this using the solution above?|||In order to get table header and footer on all pages (and no hanging footer on last page), put the table in a list and do the grouping on the list.

Group by query returning too many rows

Can anyone help ?
Why is this query:

select bp, sum(msg) as 'msg'
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

returning 666 rows, while this query

select bp
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

is only returning 20.
The "correct" result is 20 rows, one for each bp.
The fisrt query returns alot of duplicate bp.

By the way: What is faster: Converting the sqldate field to a varchar and comparing with another varchar, or converting the varchar to a date and then comparing it to the sqldate field ?do you want the SUM of all the msg attributes or the number of msg (messages?) for each bp? try using COUNT(*) in place of the SUM(msg).

In your case you are probably doing table scans due to the convertion of the date attribute to a varchar. SQL Server can efficiently convert and test a (var)char variable to a date attribute + you can take advantage of indexes.|||Sorry, I guess I should have made things clearer.
What I want is the sum of all the numbers stored in the msg column, i.e. the number of msgs for each bp. So the result set should have one row for each bp. This works fine without the sum(msg) part, and the result looks something like this:

HENNES
HENTEXTRA
KANAL5

But when I add the sum(msg) to get the number of messages pr. bp then the result looks like this:

Wow. Something strange just happened. When I ran the query to produce the results I added "order by bp" at the end, and then there was suddenly just one row for each 20 in total. Without it the resultset returns 666 rows.

Is the group by clause dependent upon the order one retrieves the rows ?|||No. The order by is used to sort the result set and does not affect the group by.

I set up a simple test...

Code:
----------------------------
create table #tmp(f1 varchar(10),f2 int)
insert into #tmp values('A',2)
insert into #tmp values('A',4)
insert into #tmp values('C',3)
insert into #tmp values('C',1)
insert into #tmp values('C',1)
insert into #tmp values('B',2)
insert into #tmp values('B',3)
insert into #tmp values('B',4)

select f1,sum(f2) as 'Sum'
from #tmp
group by f1
order by f1
----------------------------

is this anything close to what you are working on?|||That is pretty much what I am working on, except that my view has alot more columns. At the moment I am really only interested in getting one row for each bp, with one sum of messages for each.
My query does produce the desired results, as long as I have the "order by bp" clause at the end.
So my problem is really solved, but I don't really understand why though. If you want to find out why, and need any more information from me just let me know.
The view I am querying is based on two other views, but I can't see that making much of a difference.

This is the result I was looking for, and I get with the order bp:

davinci 1333
E-CLIPS 1864
HENNES 1397
KANAL5 6470
MRJET 6
PASSAGEN 70
SIMONTV 12
SPORTAL 828
STARLIFE 1004
TISCALI 2484
YAHOO 3
...
...
20 rows in total

This is some of what I get without the order bp:
SPORTAL 8
davinci 11
E-CLIPS 11
davinci 1
E-CLIPS 1
davinci 7
E-CLIPS 7
davinci 9
E-CLIPS 9
davinci 2
E-CLIPS 2
davinci 8
...
...
...
666 rows in total

Group by in a view can this be used ?

In a complex view the group by gives strange (wrong) results.
(Adding a group by and having clause generates more rows instead of less).
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
from view_A
Results in : 720 rows
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F, count(*)
from view_A
group by A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
having count(*) > 1
Results in : 33678 rows (a lot of them containing a 1 in the count(*)
column)
(There should be only 57 rows)
If the view is put into a table : select * into table_A from view_A
First result : 720
Second result : 57
Is this a (known) bug,
(Removing the count(*) from the first query results in a :
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.
)
The view uses union to get the results form 3 queries, which each have
several tables. There are no correlated subqueries.
ben brugmanPlease can you post some code to reproduce the problem: the DDL for the base
tables and the view (just the key columns and the columns involved in the
query will do) plus a small sample of data (post as INSERT statements).
--
David Portas
--
Please reply only to the newsgroup
--|||I could generate a sample, but I would have
to anominise all data and meta data (table, view and column names).
There is a number of tables involved and I would have
to create suetable data.
(Sorry my organisation does not allow me to do this otherwise).
But then it does take such a form that I do not expect anybody to
look at the problem. And it would take a considerable amount of time
to prepare this.
At the end of this message I have done this only for the views and the
offending queries.
(Just as an example to show that this is not very user friendly).
Thanks for your attention
ben brugman
LOOK AT THE EXAMPLE AT YOUR OWN PERIL.
/* View for a selection. */
CREATE VIEW dbo.View_S
AS
SELECT T157TABLE.F637FIELD,
T157TABLE.F687FIELD AS F346FIELD,
T157TABLE.F638FIELD,
'SE' AS F347FIELD,
T116TABLE.F280FIELD AS F345FIELD,
T104TABLE.F703FIELD AS F238FIELD,
T157TABLE.F652FIELD,
T157TABLE.F744FIELD,
T157TABLE.F745FIELD,
T116TABLE.F277FIELD AS F246FIELD,
T116TABLE.F278FIELD AS F342FIELD,
T157TABLE.F324FIELD, T157TABLE.F309FIELD,
T157TABLE.F311FIELD,
T157TABLE.F588FIELD,
T157TABLE.F590FIELD,
T157TABLE.F747FIELD
FROM T157TABLE INNER JOIN
T116TABLE ON
T116TABLE.F637FIELD = T157TABLE.F637FIELD AND
T116TABLE.F687FIELD = T157TABLE.F687FIELD
LEFT OUTER JOIN
T104TABLE ON
T104TABLE.F637FIELD = T157TABLE.F637FIELD AND
T104TABLE.F687FIELD = T157TABLE.F687FIELD
AND T104TABLE.F230FIELD = 'SIS'
/* The main View */
CREATE VIEW dbo.View_A
AS
SELECT T122TABLE.F637FIELD,
T122TABLE.F344FIELD AS F346FIELD,
T122TABLE.F638FIELD,
'CT' AS F347FIELD,
T122TABLE.F262FIELD AS F345FIELD,
T122TABLE.F238FIELD,
T122TABLE.F246FIELD, T122TABLE.F342FIELD,
T122TABLE.F324FIELD,
T122TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T122TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T122TABLE.F747FIELD, NULL AS F652FIELD, NULL
AS F744FIELD, NULL
AS F745FIELD
FROM T122TABLE, T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T122TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T122TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT T123TABLE.F637FIELD,
T123TABLE.F348FIELD AS F346FIELD,
T123TABLE.F638FIELD,
'TT' AS F347FIELD,
T123TABLE.F737FIELD AS F345FIELD,
T123TABLE.F238FIELD,
T123TABLE.F246FIELD,
T123TABLE.F342FIELD,
T123TABLE.F324FIELD,
T123TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T123TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T123TABLE.F747FIELD, NULL
AS F652FIELD, NULL AS F744FIELD, NULL
AS F745FIELD
FROM T123TABLE,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T123TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T123TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT View_S.F637FIELD,
View_S.F346FIELD,
View_S.F638FIELD,
View_S.F347FIELD,
View_S.F345FIELD,
View_S.F238FIELD,
View_S.F246FIELD,
View_S.F342FIELD,
View_S.F324FIELD,
View_S.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
View_S.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
View_S.F747FIELD,
View_S.F652FIELD,
View_S.F744FIELD,
View_S.F745FIELD
FROM View_S,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE View_S.F311FIELD = IHCP_creator.F702FIELD
AND
View_S.F590FIELD = IHCP_mutator.F702FIELD
/* The query which goes 'wrong' */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD, count(*) from View_A
group by F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD
having count(*) > 1
/* The simple Query */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD from View_A