Grouping by defined number of days.

This is a question about custom grouping by a defined number of days. I
would like to have a start date, then group the data in 3 day blocks. Is
this possible ' Ideally, I would like to have a start date, end date, and
all the little intervals in-between, even if no data is in those intervals.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
I was screwing around with the datepart() function, but that did not work.
This does a grouping by hour.
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
In this example, say the start date was 2/10/2004 and the interval was 3
days, Ithink the output would be something like
interval # ev count
1 2 2
2 1 1
3 null 0
For the third result record listed above, if this cannot be done easily,
that is . I can work around it in code. I am really concerned with
getting the interval number and the count of EV's
Thanks for your time.SELECT DATEDIFF(dd, '20040210', dt)/3 + 1 AS Interval, ev, COUNT(*)
FROM YourTable
GROUP BY DATEDIFF(dd, '20040210', dt)/3 + 1, ev
ORDER BY Interval
You can get the missing intervals with a numbers table:
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
SELECT n.Number, yt.ev, COUNT(*)
FROM Numbers n
LEFT OUTER JOIN YourTable yt
ON n.Number = DATEDIFF(dd, '20040210', yt.dt)/3 +1
GROUP BY n.Number, yt.ev
HAVING n.Number <= (SELECT MAX( DATEDIFF(dd, '20040210', dt)/3 + 1) FROM
YourTable)
ORDER BY n.Number
Jacco Schalkwijk
SQL Server MVP
"Jack" <jack@.jack.net> wrote in message
news:g21qe.7805$R21.1536@.lakeread06...
> This is a question about custom grouping by a defined number of days. I
> would like to have a start date, then group the data in 3 day blocks. Is
> this possible ' Ideally, I would like to have a start date, end date,
> and all the little intervals in-between, even if no data is in those
> intervals.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
>
> I was screwing around with the datepart() function, but that did not work.
> This does a grouping by hour.
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> In this example, say the start date was 2/10/2004 and the interval was 3
> days, Ithink the output would be something like
> interval # ev count
> 1 2 2
> 2 1 1
> 3 null 0
> For the third result record listed above, if this cannot be done easily,
> that is . I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
>|||On Thu, 9 Jun 2005 15:05:12 -0400, Jack wrote:

> For the third result record listed above, if this cannot be done easily,
> that is . I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
I'm not sure I understand where you get your interval numbers... Here's
what I got from your sample data:
declare @.startdate datetime
set @.startdate='2004-02-10'
Select Floor(Datediff(d,@.startdate,dt)/3) "Interval #", ev,count(*) "Count"
From YourTable
GROUP BY ev, Floor(Datediff(d,@.startdate,dt)/3)
ORDER BY ev, Floor(Datediff(d,@.startdate,dt)/3)
Interval # ev Count
-- -- --
1 1 1
4 1 1
5 1 1
0 2 2

GROUPING at runtime

All...

I am using vb.net 2003. i am trying to set report groupings of a crystal report at runtime based on user defined options. MSDN says this:

Dim FieldDef As FieldDefinition
FieldDef =
Report.Database.Tables.Item(0).Fields.Item(comboBox1().Text)
Report.DataDefinition.Groups.Item(0).ConditionField = FieldDef

However, the .ConditionField is Read Only, you cannot assign anything to it. I have searched the object browser at each level of this object model, and cannot figure out how to assign a group to a report.

Heres the link to MSDN... the code for SORTING cr at runrime works perfectly, its just the GROUPING code im having trouble with...

http://msdn.microsoft.com/library/d...resentation.asp

Thanks.The following Example is specific to Crystal Reports 8.5 using RDC and VB6, but you may be able to tweak it a bit to work for you...

The following code can be used to Change the Group through VB Code:

1. In Crystal, create a Formula Field and add 1 column (a String column works best).
2. Insert a Group and choose your Formula for the GroupBy.
3. Now, you can comment out the Column you entered, or you can leave it there, your choice.
4. In VB, add this line of code, substituting {ttxFileName.ColumnName} for the column you want to sort by: Report.FormulaFields(1).Text = "{ttxFileName.ColumnName} ". The FormulaFields can only take a long for the index, so you need to know what number your Formula is so you don't replace the wrong one. The numbers start at 1 and are incremented based on the order they were created (they are listed in chronological order).

Grouping

I keep recieving an error: "The table 'table1' is in a list that has no group expressions defined for it. To use a data region in a list, the list must have group expressions." I have a list that contains a table, and would like the table to change with each item of the list. I looked at the grouping, but I didn't know what to do, and didn't see any tutorials out there for ReportViewer.Be sure that you are looking at the grouping for the list, not for the table. List Properties -> Edit Details Group...|||

Many Thanks, your First Post is very Helpfull

Thankswildbill0283

sql

Group By

I'm trying to aggregate values and grouping the results by defined periods (6
month, 12 month, 18 month, and lifetime). I'd like to do this with one pass,
but using a CASE statement did not aggregrate correctly. The results are
more like 6 months, 7-12 months, and 13-18 months, 19-lifetime. I would like
the results of the 12 month grouping to include all of the 6 month, the 18
month to include all of the 6 and 12 month grouping, etc.
/**/
CASE WHEN pd.accountingdate > 0 THEN 'LIFE'
WHEN pd.accountingdate BETWEEN '10/19/2004' AND '04/19/2007' THEN '18MONTH'
WHEN pd.accountingdate BETWEEN '04/19/2006' AND '04/19/2007' THEN '12MONTH'
WHEN pd.accountingdate BETWEEN '10/19/2006' AND '04/19/2007' THEN '06MONTH'
ELSE NULL
END
/**/
Thanks.
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:79FBE5E0-E112-406A-B406-A5D19626FA96@.microsoft.com...
> I'm trying to aggregate values and grouping the results by defined periods
> (6
> month, 12 month, 18 month, and lifetime). I'd like to do this with one
> pass,
> but using a CASE statement did not aggregrate correctly. The results are
> more like 6 months, 7-12 months, and 13-18 months, 19-lifetime. I would
> like
> the results of the 12 month grouping to include all of the 6 month, the 18
> month to include all of the 6 and 12 month grouping, etc.
>
> /**/
> CASE WHEN pd.accountingdate > 0 THEN 'LIFE'
> WHEN pd.accountingdate BETWEEN '10/19/2004' AND '04/19/2007' THEN
> '18MONTH'
> WHEN pd.accountingdate BETWEEN '04/19/2006' AND '04/19/2007' THEN
> '12MONTH'
> WHEN pd.accountingdate BETWEEN '10/19/2006' AND '04/19/2007' THEN
> '06MONTH'
> ELSE NULL
> END
In situations like this, you should post DDL, sample data, and the actual
query. A non-working snippet of a query doesn't really help anyone
understand the complete situation.
Making some assumptions, I believe your problem is related to the use of a
single column to represent different periods. The above case expression
represents what? Is it the 6 month data, the 12 month data, ...? It can't
represent more than one "attribute" - in this case, period.
The solution is to generate separate period values. This can be done in one
of two ways. Either you aggregate the periods as separate columns or you
create a situation where you join the data to be aggregated to a table
containing the periods. In the first example, you get period data as
separate columns within the result set. In the second example, you get
period data as separate rows. You decide which way you want to proceed. It
will facilitate discussion to use either Pubs or Northwind for sample data
and queries since most people have those available (and thus do not require
the posting of DDL or sample data).
|||Understood Scott, I've already considered the approach you've suggested, but
let me expand to help everyone better understand (as you recommended).
Using a case statement gives me the following from the Orders table in
Northwind:
customerid|period|orders|freight
AROUT|06MONTH|8|275.6900
AROUT|12MONTH|2|94.7100
AROUT|18MONTH|3|101.5500
BERGS|06MONTH|7|612.0700
BERGS|12MONTH|5|419.5500
BERGS|18MONTH|4|426.2300
BERGS|LIFE|2|101.6700
/* sample statement */
SELECTcustomerid,
CASEWHEN orderdate BETWEEN '11/06/1997' AND '05/06/1998' THEN '06MONTH'
WHEN orderdate BETWEEN '05/06/1997' AND '05/06/1998' THEN '12MONTH'
WHEN orderdate BETWEEN '11/06/1996' AND '05/06/1998' THEN '18MONTH'
WHEN orderdate > 0 THEN 'LIFE'
ELSE NULL
END period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
GROUP BY
customerid,
CASE WHEN orderdate BETWEEN '11/06/1997' AND '05/06/1998' THEN '06MONTH'
WHEN orderdate BETWEEN '05/06/1997' AND '05/06/1998' THEN '12MONTH'
WHEN orderdate BETWEEN '11/06/1996' AND '05/06/1998' THEN '18MONTH'
WHEN orderdate > 0 THEN 'LIFE'
ELSE NULL
END
/**/
However to get the results I seek I have to make four passes:
customerid|period|orders|freight
AROUT|06MONTH|8|275.6900
AROUT|12MONTH|8|275.6900
AROUT|18MONTH|13|471.9500
AROUT|LIFE|13|471.9500
BERGS|06MONTH|7|612.0700
BERGS|12MONTH|7|612.0700
BERGS|18MONTH|16|1457.8500
BERGS|LIFE|18|1559.5200
/* sample statement */
SELECTcustomerid,
'06MONTH' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate BETWEEN '11/06/1997' AND '05/06/1998'
GROUP BY customerid
UNION
SELECTcustomerid,
'12MONTH' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate BETWEEN '11/06/1997' AND '05/06/1998'
GROUP BY customerid
UNION
SELECTcustomerid,
'18MONTH' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate BETWEEN '11/06/1996' AND '05/06/1998'
GROUP BY customerid
UNION
SELECTcustomerid,
'LIFE' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate > 0
GROUP BY customerid
ORDER BY customerid, period
/**/
The results of the first query are affected by the sequence of the
conditions in the CASE statement. And there's the rub. I'd like to have my
results in rows because I'm aggregrating at least 20 columns and 4 periods
would push it to 80 columns. Thanks for you help.
"Scott Morris" wrote:

> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:79FBE5E0-E112-406A-B406-A5D19626FA96@.microsoft.com...
> In situations like this, you should post DDL, sample data, and the actual
> query. A non-working snippet of a query doesn't really help anyone
> understand the complete situation.
> Making some assumptions, I believe your problem is related to the use of a
> single column to represent different periods. The above case expression
> represents what? Is it the 6 month data, the 12 month data, ...? It can't
> represent more than one "attribute" - in this case, period.
> The solution is to generate separate period values. This can be done in one
> of two ways. Either you aggregate the periods as separate columns or you
> create a situation where you join the data to be aggregated to a table
> containing the periods. In the first example, you get period data as
> separate columns within the result set. In the second example, you get
> period data as separate rows. You decide which way you want to proceed. It
> will facilitate discussion to use either Pubs or Northwind for sample data
> and queries since most people have those available (and thus do not require
> the posting of DDL or sample data).
>
>
|||Understood Scott. I've already considered the approaches you've suggested,
but let me expand to help us better understand my problem as you recommended.
The CASE statement approach gives me the following results from the Orders
table in Northwind:
customerid|period|orders|freight
AROUT|06MONTH|8|275.6900
AROUT|12MONTH|2|94.7100 /* freight should = 370.40*/
AROUT|18MONTH|3|101.5500
BERGS|06MONTH|7|612.0700
BERGS|12MONTH|5|419.5500
BERGS|18MONTH|4|426.2300
BERGS|LIFE|2|101.6700
/* sample code */
SELECTcustomerid,
CASE
WHEN orderdate BETWEEN '11/06/1997' AND '05/06/1998' THEN '06MONTH'
WHEN orderdate BETWEEN '05/06/1997' AND '05/06/1998' THEN '12MONTH'
WHEN orderdate BETWEEN '11/06/1996' AND '05/06/1998' THEN '18MONTH'
WHEN orderdate > 0 THEN 'LIFE'
ELSE NULL
END period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
GROUP BY
customerid,
CASE
WHEN orderdate BETWEEN '11/06/1997' AND '05/06/1998' THEN '06MONTH'
WHEN orderdate BETWEEN '05/06/1997' AND '05/06/1998' THEN '12MONTH'
WHEN orderdate BETWEEN '11/06/1996' AND '05/06/1998' THEN '18MONTH'
WHEN orderdate > 0 THEN 'LIFE'
ELSE NULL
END
/**/
However, I would like my results to read:
customerid|period|orders|freight
AROUT|06MONTH|8|275.6900
AROUT|12MONTH|10|370.4000
AROUT|18MONTH|13|471.9500
AROUT|LIFE|13|471.9500
BERGS|06MONTH|7|612.0700
BERGS|12MONTH|12|1031.6200
BERGS|18MONTH|16|1457.8500
BERGS|LIFE|18|1559.5200
/* sample code */
SELECTcustomerid,
'06MONTH' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate BETWEEN '11/06/1997' AND '05/06/1998'
GROUP BY customerid
UNION
SELECTcustomerid,
'12MONTH' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate BETWEEN '05/06/1997' AND '05/06/1998'
GROUP BY customerid
UNION
SELECTcustomerid,
'18MONTH' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate BETWEEN '11/06/1996' AND '05/06/1998'
GROUP BY customerid
UNION
SELECTcustomerid,
'LIFE' period,
COUNT(orderid) orders,
SUM(freight) freight
FROMorders
WHEREcustomerid IN ('AROUT', 'BERGS')
AND orderdate > 0
GROUP BY customerid
ORDER BY customerid, period
/**/
The results of the first query are affected by the sequence of the
conditions in the CASE statement. And there's the rub. I need the 18 month
period to include 6 months and 12 months. The only way I know to do that is
through multiple passes. I prefer my results to rows as I'm aggregating at
least 20 columns and if I push the results to columns with 4 different
periods, then I would expect at least 80 columns! Any help is greatly
appreciated. Thanks.
"Scott Morris" wrote:

> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:79FBE5E0-E112-406A-B406-A5D19626FA96@.microsoft.com...
> In situations like this, you should post DDL, sample data, and the actual
> query. A non-working snippet of a query doesn't really help anyone
> understand the complete situation.
> Making some assumptions, I believe your problem is related to the use of a
> single column to represent different periods. The above case expression
> represents what? Is it the 6 month data, the 12 month data, ...? It can't
> represent more than one "attribute" - in this case, period.
> The solution is to generate separate period values. This can be done in one
> of two ways. Either you aggregate the periods as separate columns or you
> create a situation where you join the data to be aggregated to a table
> containing the periods. In the first example, you get period data as
> separate columns within the result set. In the second example, you get
> period data as separate rows. You decide which way you want to proceed. It
> will facilitate discussion to use either Pubs or Northwind for sample data
> and queries since most people have those available (and thus do not require
> the posting of DDL or sample data).
>
>
|||> The results of the first query are affected by the sequence of the
> conditions in the CASE statement. And there's the rub. I'd like to have
> my
> results in rows because I'm aggregrating at least 20 columns and 4 periods
> would push it to 80 columns. Thanks for you help.
The secret is that you need a table of periods - which you then join to your
data while aggregating. This can be done in one pass since each "raw data
row" joins once to each appropriate period. So a row for today joins to the
6 month period while a row from 10 months ago joins to both the 6 and 12
month periods (and so forth). The trick is to generate the period table -
dynamically if needed. Below is a query (that can be used as a derived
table) to do this. Obviously, the variable isn't actually required. You
could create a table-valued function to do the same thing (especially if you
needed to reuse this logic).
declare @.today datetime
set @.today = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)
select @.today, dateadd(month, -6, @.today)
union all
select @.today, dateadd(month, -12, @.today)
union all
select @.today, dateadd(month, -18, @.today)
You may need to adjust the logic depending on your definition of periods -
the last day of the month (esp Feb in and out of a leap year) is always fun
for these types of things. With the table of periods, you simply join to
the transaction table where date of transaction is between the period start
and end dates.
|||Your solution is perfect! This increases performance and keeps my DBA happy.
I'll add this technique to my bag of tricks. Thanks Scott!
"Scott Morris" wrote:

> The secret is that you need a table of periods - which you then join to your
> data while aggregating. This can be done in one pass since each "raw data
> row" joins once to each appropriate period. So a row for today joins to the
> 6 month period while a row from 10 months ago joins to both the 6 and 12
> month periods (and so forth). The trick is to generate the period table -
> dynamically if needed. Below is a query (that can be used as a derived
> table) to do this. Obviously, the variable isn't actually required. You
> could create a table-valued function to do the same thing (especially if you
> needed to reuse this logic).
> declare @.today datetime
> set @.today = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)
> select @.today, dateadd(month, -6, @.today)
> union all
> select @.today, dateadd(month, -12, @.today)
> union all
> select @.today, dateadd(month, -18, @.today)
> You may need to adjust the logic depending on your definition of periods -
> the last day of the month (esp Feb in and out of a leap year) is always fun
> for these types of things. With the table of periods, you simply join to
> the transaction table where date of transaction is between the period start
> and end dates.
>
>

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...
>

Group and subreport

Hi,
I have a report and I have defined a group in it.
both the group header and group footer has some information the detail section contain two subreports.
My requirement is that if there are more than 10 records in any of the subreport then only first 10 records will be displayed on each page along withh the group header and group footer. Somhow I managed to print the 10 records on each page with group header; but I am not able to get the footer on each page!!
Is there any option avaliable for this type of scenario.

I am using Crystal Report XI with ASP.NET.Hey.

Did u put a 'New Page After' in the group header or detail section of your main report?
If, then remove them there and place them in the Group Footer section.

Do not use any of the 'New Page Before/After' feathures in ure sub-reports.

Hope this helps.