Grouping Records & Assigning Sequential Number

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

John's query gives:

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

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

DECLARE @.grp INTEGER

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

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

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

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

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

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

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

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

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

GO

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

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

DECLARE @.grp INTEGER

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

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

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

RETURN
END

GO

SELECT * FROM TGroupings()

This is the result with your original test-data:

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

(6 row(s) affected)

And this is it with my extra row added:

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

(7 row(s) affected)

--
David Portas
SQL Server MVP
--

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

Grouping on first 2 characters of a number

Well, here I am again needing help. :(

My report shows a long list of numbers and I want to separate them into groups based only on the first two characters of each number. For example:

3102
3106
3103

3201
3203
3204

3506
3504
3508
3509

How do I tell Crystal to look at only the first 2 characters and sort accordingly, keeping all the "31" "32" and "35" together? My report is based on a stored procedure so I cannot change the number into a string.

Thank you!!!make a formula in crystal report with Left(cstr(Number field) ,2) and in groupby instead of number use this formula|||Thank you so much! That was it!!!

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?sql

Grouping for Sum

My query:

SELECT Ticket.Barrels, Lease.[RRC Lease Number], Lease.[Lease Name], Lease.[Field Name], Lease.OperatorID, Lease.OilGasOther, Lease.District,
Operator.[Operator Name], SUM(Ticket.Barrels) AS Expr1
FROM ((Ticket INNER JOIN
Lease ON Ticket.LeaseID = Lease.LeaseID) INNER JOIN
Operator ON Lease.OperatorID = Operator.OperatorID)
WHERE (Ticket.SWDNumber = ?) AND (Ticket.TicketDate BETWEEN ? AND ?)
GROUP BY Ticket.Barrels, Lease.[RRC Lease Number], Lease.[Lease Name], Lease.[Field Name], Lease.OperatorID, Lease.OilGasOther, Lease.District,
Operator.[Operator Name]
HAVING (Lease.District = ?) AND (Lease.OilGasOther = ?)

I would like to produce a table as follows:

RRC Number - Lease Name - Field Name -Sum of Barrels

0001 - Lease1 - Field1 - 120

0002 - Lease1 - Field3 - 340

0002 - Lease2 - Field3 - 120

Instead I have some of the data on several rows:

0001 - Lease1 - Field1 - 70

0001 - Lease1 - Field1 - 50

0002 - Lease1 - Field3 - 40

0002 - Lease1 - Field3 - 300

and so on ....

What am I doing wrong?

You need to clean up both the select columns and group by columns. I can see the Ticket.Barrels coumn included in both select and group by which is the reason for what you got. Try another on with this column and other unnecessary columns.
.
SELECT Lease.[RRC Lease Number], Lease.[Lease Name],

Lease.[Field Name], SUM(Ticket.Barrels) AS 'Sum of Barrels'
FROM ((Ticket INNER JOIN
Lease ON Ticket.LeaseID = Lease.LeaseID) INNER JOIN
Operator ON Lease.OperatorID = Operator.OperatorID)
WHERE (Ticket.SWDNumber = ?) AND (Ticket.TicketDate BETWEEN ? AND ?)
GROUP

BY Lease.[RRC Lease Number], Lease.[Lease Name],

Lease.[Field Name]
HAVING (Lease.District = ?) AND (Lease.OilGasOther = ?)

Grouping container, is there any limitation?

hi guys,

I was wondering about a silly thing but, anyway, which is the maximum number of grouping for a container? I mean, when you group from, i.e, a sql task and then over that group do again another one and so on...

Thanks in advance for your time,

Don't quote me on this, but I'm assuming that a container just has a container for children effectively creating a linked list. So the limit should be memory or the level that .Net would allow say For loops to be nested.|||thanks a lot

grouping by the avg

I'm trying to resolve the problem :
List the customer number, customer name, rep number, and credit balance for all customers whose credit balance is greater than the average credit balance for the customers who have the same rep. Sort by rep number. Use of 'exists' is required.
See the table behind . My query is

select customer_num, customer_name, rep_num, balance
from customer a
where exists
(select * from customer b
where balance >
(select AVG(balance) from customer c
where a.rep_num=b.rep_num)
and a.rep_num=b.rep_num)
order by rep_num;

but it lists all the customers from the table. Thank you.

CUS CUSTOMER_NAME STREET CITY ST ZIP BALANCE CREDIT_LIMIT RE
-- ------- ----- ---- -- -- ---- ---- --
148 Al's Appliance and S 2837 Fillmore FL 33336 6550 7,500 20
port Greenway

282 Brookings Direct 3827 Grove FL 33321 431.5 10,000 35
Devon

356 Ferguson's 382 Northfield FL 33146 5785 7,500 65
Wildwood

408 The Everything Shop 1828 Crystal FL 33503 5285.25 5,000 35
Raven

462 Bargains Galore 3829 Grove FL 33321 3412 10,000 65
Central

524 Kline's 838 Fillmore FL 33336 12762 15,000 20
Ridgeland

608 Johnson's Department 372 Sheldon FL 33553 2106 10,000 65
Store Oxford

687 Lee's Sport and Appl 282 Altonville FL 32543 2851 5,000 35
iance Evergreen

725 Deerfield's Four Sea 282 Sheldon FL 33553 248 7,500 35
sons Columbia

842 All Season 28 Lakeview Grove FL 33321 8221 7,500 20select customer_num, customer_name, rep_num, balance
from customer a
where a.balance > (select avg(balance) from customer where rep_num = a.rep_num);

Grouping by hour, day, month, etc

I have tables which record data entered by six users.
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

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 and TOP 10

I have a table (medical data relating to the number of patients admitted into a ward with different illnesses) which has 3 columns I am interested in.

Column 1 is Ward,
Column 2 is the diagnosis,
Column 3 is the nuber of patients admitted into the ward with the diagnosis.

Firstly, i know this data is not relational (it is a warehouse and we only have flat files) which i think might be what is causing my problems.

What i want to do is write a query that will give me the top 10 for each ward based on the number of patient admitted

eg.

Ward Diagnosis Number of Patients
######################################
1 Broken Leg 107
1 Broken Hip 98
1 Broken Nose 56
...
2 Lung Cancer 105
2 Liver Cancer 65
...
etc

Does anybody know how to do this as i keep going round in circles and i can't work out how to do it.

Thanks in advance,

Emma.Which DBMS? Some (e.g. Oracle) have "analytic functions" that make this sort of query easy. Without analytics, you could do something like:

select ward, diagnosis, num_patients
from table t1
where 10 > (select count(*) from table t2 where t1.ward = t2.ward and t2.num_patients > t1.num_patients);|||SQL Server back-end but i am running the query in an access front end (so either would be fine as i can put in in the front of back)|||and i worked it out from a post on the SQL Server board:

SELECT *
FROM tab AS a
WHERE ((a.num_patients) In (select top 3 num_patients from tab b where a.ward = b.ward))
ORDER BY a.Ward, a.num_patients DESC;

Thanks for the help.sql

Group Wise Page Numbers

Hi,
Iam printing a report having grouping, a group will spread over many pages and I want No. of pages for that group and also page number. for ex.

A report has 50 pages,

group 1 of 20 pages
group 2 of 10 pages
group 3 of 20 pages

I want No. of Pages 20 and current page as 1 of 20, 2 of 20. and for the second group again No. of Pages 10 and current page as 1 of 10, 2 of 10 and so on.

How do I do this.? can anyone help me on this.

thanks for ur suggestions in advance.Hi,

put Special Field "Page N of M"

now, Open Section Expert and navigate on first group level. there is one event "Reset Page Number After". Write below code on that

eg.
Previous({S_EVT_ACT.OWNER_LOGIN}) <> GroupName ({S_EVT_ACT.OWNER_LOGIN})

Using this code u get group wise Page N of M value.

-Yags|||Thanks ! it worked really.
I have one more question, which book is good for developing reports under vb 6.0 using crystal reports. suggest me one.

thanks again.|||Hi,

I have not prefered any book for Crystal Reports b'caz there is no book which
suitable for real crystal report development.

Sorry and most welcome for any issue regarding crystal reports

-Yags

Group Page number

I have a report which is group by invoice no. For each new invoice no, page
break is inserted. I want to set page number for each invoice (e.g. Page 1
of 1). But if the invoice has so many charge line that occupy more than 1
page, I want to set page 1 as Page 1 of 10, page 2 as Page 2 of 10 and so on
...
HOW CAN I DO IT ? Also the page number will be reset to 1 for new invoice.
Another question is that, I have sub-total for each new invoice which is
placed in Group footer. In crystal report, I can fix the footer position.
So the position of sub-total will not be altered (move up and down) no matter
how many charge lines under this invoice. Is RS has this feature ?Here is a reference to the answer:
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=reset+page+number+&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=030990a7-1731-4e50-9e0b-4ed69ac1bd63&mid=2bdeaef4-35b0-49ed-86c6-5c69a155be38
"May Liu" wrote:
> I have a report which is group by invoice no. For each new invoice no, page
> break is inserted. I want to set page number for each invoice (e.g. Page 1
> of 1). But if the invoice has so many charge line that occupy more than 1
> page, I want to set page 1 as Page 1 of 10, page 2 as Page 2 of 10 and so on
> ...
> HOW CAN I DO IT ? Also the page number will be reset to 1 for new invoice.
> Another question is that, I have sub-total for each new invoice which is
> placed in Group footer. In crystal report, I can fix the footer position.
> So the position of sub-total will not be altered (move up and down) no matter
> how many charge lines under this invoice. Is RS has this feature ?

group Number for millions of records.

I am using the following query which I found from a fragment of code
by itzik ben-gan to assign a common group id for group of records in my
case which have similar SSN and first Name and Last Name. if the SSN is
the same it should also check the first name and last name of the
record. Because records have more than three AKA names; I need to check
all the possibilities of first name last name combination to verify the
records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
Even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I
realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? Very
important issue.
2. While assigning group number, it doesn't give sequential numbers. It
skips some of the numbers( group Number 1,2 5,9...) I am just curious
about this(
not very important issue)
SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS grp_num
into tmp_FLS
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
GO
distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.
CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333I am assuming that SSN means U.S. Social Security Number. If this is not
the case, ignore the rest of this post.
If SSN is the same you should be able to assume the records are in fact the
same people (this is the rule, but due to human error there have been
exceptions). For the rare exceptions identify them after the fact. Odds
are you won't come accross a true duplicate SSN, although if your data is
innaccurate it is more likely.
If you can go this route, simply assign the same group to anyone with the
same SSN, it will make it much simpler and much, much, much faster. If you
have to do it with all the name comparisons, at least remove the following
subquery and replace it with a different method for assigning a unique
GRP_NUM. That extra full table lookup for every row is what is causing your
performance problems.
(SELECT 1 + COUNT(*)
FROM DISTFLS AS C2
WHERE C2.SSN < C1.SSN
OR (C2.SSN = C1.SSN
AND ( SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
OR SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
)
)
) AS GRP_NUM
<sql.greg@.gmail.com> wrote in message
news:1141742507.871093.35900@.i40g2000cwc.googlegroups.com...
> I am using the following query which I found from a fragment of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Because records have more than three AKA names; I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> Even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".
> 1. is there any other feasible and faster way to do this? Very
> important issue.
> 2. While assigning group number, it doesn't give sequential numbers. It
> skips some of the numbers( group Number 1,2 5,9...) I am just curious
> about this(
> not very important issue)
> SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS grp_num
> into tmp_FLS
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
> GO
> distinct firstname, lastname and SSN table from the tu_people_data.
> I created this table to increase the query performance.
> CREATE TABLE [distFLS] (
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> sample data
> there is a column count_id after the tu_id and before fname(tu_id and
> count_id are primary keys)
> tu_id fname lname SSN
> 156078480 KRISINA WALSH 999999000
> 156078480 KRISTINA GIERER 999999000
> 156078480 KRISTINA WALSH 999999000
> 151257883 J SOTO 999999111
> 151257883 JOSE LARIOS 999999111
> 151257883 JOSE SOTO 999999111
> 151257883 L SOTO 999999111
> 136312525 ELADIO GARCIA 999999222
> 136312525 ELADIO NAVA 999999222
> 136312525 ELADIO NAVAGARCIA 999999222
> 136312525 GARCIA NAVA 999999222
> 149180940 DARREN SAUERWINE 999999333
> 149180940 DARREN SUAERWIN 999999333
>|||A while back, I ran a query against a customer account table, and found over
1000 unique SSN was used with a different date of birth. Those SSNs with the
highest frequency were obviously fake or just stubbs (ex: 111111111), but
there were some apparently valid numbers that were duplicated several
hundred times by different account holders.
"They were shocked to learn that 18 of the 19 terrorists possessed either
state-issued or counterfeit driver's licenses or ID cards and all 19 had
obtained Social Security numbers (SSNs) - some real, some fake."
http://www.cis.org/articles/2002/back1202.html
"Linda Trevino, who lives in a Chicago suburb, applied for a job last year
at a local Target department store, and was denied. The reason? She already
worked there -- or rather, her Social Security number already worked there."
http://www.msnbc.msn.com/id/6814673/
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23drgo7fQGHA.5552@.TK2MSFTNGP10.phx.gbl...
>I am assuming that SSN means U.S. Social Security Number. If this is not
> the case, ignore the rest of this post.
> If SSN is the same you should be able to assume the records are in fact
> the
> same people (this is the rule, but due to human error there have been
> exceptions). For the rare exceptions identify them after the fact. Odds
> are you won't come accross a true duplicate SSN, although if your data is
> innaccurate it is more likely.
> If you can go this route, simply assign the same group to anyone with the
> same SSN, it will make it much simpler and much, much, much faster. If
> you
> have to do it with all the name comparisons, at least remove the following
> subquery and replace it with a different method for assigning a unique
> GRP_NUM. That extra full table lookup for every row is what is causing
> your
> performance problems.
> (SELECT 1 + COUNT(*)
> FROM DISTFLS AS C2
> WHERE C2.SSN < C1.SSN
> OR (C2.SSN = C1.SSN
> AND ( SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
> OR SUBSTRING(C2.FNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
> OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.LNAME,1,1)
> OR SUBSTRING(C2.LNAME,1,1) = SUBSTRING(C1.FNAME,1,1)
> )
> )
> ) AS GRP_NUM
>
> <sql.greg@.gmail.com> wrote in message
> news:1141742507.871093.35900@.i40g2000cwc.googlegroups.com...
>

Group Number

I have a table that groups on one field.
I would like the group row to show an incrementing number for each group, i.e. if there are twenty records in the dataset that are grouped into 4 groups on the report, I would like the group headers to show Group 1, Group 2, Group 3 and Group 4 respectively.
I have tried fiddling around with rownumber, but that just gives me the number of rows in the group or in the entire dataset. I can't figure a way of doing it in the SP (without using Cursors) as the grouping fields are not always the same. I have also tried fiddling around with previous but that also led nowhere.
Is there no GroupNumber property or something similar I can get at?Group on Fields!Group.Value (or something similar) and use the following
expression: =RunningValue(Fields!Group.Value, CountDistinct, Nothing)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Another frustrated developer"
<Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
news:C8E2AC24-C6FD-4C60-B190-480B1A395A3B@.microsoft.com...
> I have a table that groups on one field.
> I would like the group row to show an incrementing number for each group,
i.e. if there are twenty records in the dataset that are grouped into 4
groups on the report, I would like the group headers to show Group 1, Group
2, Group 3 and Group 4 respectively.
> I have tried fiddling around with rownumber, but that just gives me the
number of rows in the group or in the entire dataset. I can't figure a way
of doing it in the SP (without using Cursors) as the grouping fields are not
always the same. I have also tried fiddling around with previous but that
also led nowhere.
> Is there no GroupNumber property or something similar I can get at?|||Small clarification:
The first parameter should be the same field used in the group expression:
Fields!<FieldName>.Value
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:emNXUUpaEHA.2388@.TK2MSFTNGP11.phx.gbl...
> Group on Fields!Group.Value (or something similar) and use the following
> expression: =RunningValue(Fields!Group.Value, CountDistinct, Nothing)
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Another frustrated developer"
> <Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
> news:C8E2AC24-C6FD-4C60-B190-480B1A395A3B@.microsoft.com...
> > I have a table that groups on one field.
> >
> > I would like the group row to show an incrementing number for each
group,
> i.e. if there are twenty records in the dataset that are grouped into 4
> groups on the report, I would like the group headers to show Group 1,
Group
> 2, Group 3 and Group 4 respectively.
> >
> > I have tried fiddling around with rownumber, but that just gives me the
> number of rows in the group or in the entire dataset. I can't figure a
way
> of doing it in the SP (without using Cursors) as the grouping fields are
not
> always the same. I have also tried fiddling around with previous but that
> also led nowhere.
> >
> > Is there no GroupNumber property or something similar I can get at?
>|||Great Job guys, works a dream.
Thanks very much.
Rich.
"Bruce Johnson [MSFT]" wrote:
> Small clarification:
> The first parameter should be the same field used in the group expression:
> Fields!<FieldName>.Value
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:emNXUUpaEHA.2388@.TK2MSFTNGP11.phx.gbl...
> > Group on Fields!Group.Value (or something similar) and use the following
> > expression: =RunningValue(Fields!Group.Value, CountDistinct, Nothing)
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Another frustrated developer"
> > <Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
> > news:C8E2AC24-C6FD-4C60-B190-480B1A395A3B@.microsoft.com...
> > > I have a table that groups on one field.
> > >
> > > I would like the group row to show an incrementing number for each
> group,
> > i.e. if there are twenty records in the dataset that are grouped into 4
> > groups on the report, I would like the group headers to show Group 1,
> Group
> > 2, Group 3 and Group 4 respectively.
> > >
> > > I have tried fiddling around with rownumber, but that just gives me the
> > number of rows in the group or in the entire dataset. I can't figure a
> way
> > of doing it in the SP (without using Cursors) as the grouping fields are
> not
> > always the same. I have also tried fiddling around with previous but that
> > also led nowhere.
> > >
> > > Is there no GroupNumber property or something similar I can get at?
> >
> >
>
>

group number

i have the following data in the database:
hk a aa aaa
hk b bb bbb
hk c cc ccc
uk d dd ddd
uk e ee eee
us f ff fff
and they are displayed in a matrix like below
hk a aa aaa
b bb bbb
c cc ccc
uk d dd ddd
e ee eee
us f ff fff
i would like to add a number to any new row like below
1 hk a aa aaa
b bb bbb
c cc ccc
2 uk.....
3Us...
how can i do this...i have tried rownumber and runningvalue but it doesnot
work
please help~~
thank you so much in advanceTake a look at
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/21/199.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:32D440E6-EC85-4C1C-A4EA-913BD543C7F6@.microsoft.com...
> i have the following data in the database:
> hk a aa aaa
> hk b bb bbb
> hk c cc ccc
> uk d dd ddd
> uk e ee eee
> us f ff fff
> and they are displayed in a matrix like below
> hk a aa aaa
> b bb bbb
> c cc ccc
> uk d dd ddd
> e ee eee
> us f ff fff
> i would like to add a number to any new row like below
> 1 hk a aa aaa
> b bb bbb
> c cc ccc
> 2 uk.....
> 3Us...
> how can i do this...i have tried rownumber and runningvalue but it doesnot
> work
> please help~~
> thank you so much in advance

Group Header

Hi,
In my group header I have a cell that I want to display "the group number".
Ex.
Group A - 1
XXXXXXXXXX
Group B - 2
XXXXXXXXXX
Group C - 3
XXXXXXXXXX
etc.
I can't find an easy way to calculate the groupnumber.
I cannot use SUM distinctvalue for the grouping since the tabel is grouped
on two fields.
How can i get lastvalue for the textbox + 1.
Can somebody pleas help me out here!!
Thanks!!!
--
daniel_bTry doing a google search on "Reporting Services" and "Sleazy Hacks" -
I believe they cover this topic somewhat loosely in the "Green-band"
article.|||Thank you. That helped alot!!!
"papaboom" wrote:
> Try doing a google search on "Reporting Services" and "Sleazy Hacks" -
> I believe they cover this topic somewhat loosely in the "Green-band"
> article.
>|||You're welcome.

Group data by time slot

I would like to prepare a SQL such that I can perform query on a table
and count the number of record per time slot. The time slot is 2
seconds each, but the beginning time of each time slot is not fix, it
is determined by the create_date of the record, and this is the
example:
Raw Data:
seq cat create_date
-- -- --
151 A 2006-01-25 14:14:20.827
152 B 2006-01-25 14:14:20.983
161 A 2006-01-25 14:14:22.390
162 B 2006-01-25 14:14:22.543
171 A 2006-01-25 14:14:23.997
172 B 2006-01-25 14:14:24.153
181 A 2006-01-25 14:14:25.560
182 B 2006-01-25 14:14:25.717
191 A 2006-01-25 14:14:27.123
192 B 2006-01-25 14:14:27.280
Output:
seq cat create_date count
-- -- -- --
151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
191 A 2006-01-25 14:14:27.123 1 -- contain 191
152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
192 B 2006-01-25 14:14:27.280 1 -- contain 192
Any idea on how the SQL should write?
I just think of a SQL to get the intermediate grouping on which time
slot the record belongs to, but no idea on how to eliminate the
duplicated entry which is already included in another time slot....
The unfinished SQL:
SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
n.create_date, n.random_string
FROM bbs m
LEFT OUTER JOIN bbs n ON n.cat = m.cat
AND n.create_date > m.create_date AND
datediff(ss, m.create_date, n.create_date) < 2
ORDER BY m.create_dateTry this
CREATE TABLE #Temp(seq int, cat CHAR(1), cdate datetime)
INSERT INTO #Temp
SELECT
151, 'A', '2006-01-25 14:14:20.827' UNION ALL
SELECT 152, 'B', '2006-01-25 14:14:20.983' UNION ALL
SELECT 161, 'A', '2006-01-25 14:14:22.390' UNION ALL
SELECT 162, 'B', '2006-01-25 14:14:22.543' UNION ALL
SELECT 171, 'A', '2006-01-25 14:14:23.997' UNION ALL
SELECT 172, 'B', '2006-01-25 14:14:24.153' UNION ALL
SELECT 181, 'A', '2006-01-25 14:14:25.560' UNION ALL
SELECT 182, 'B', '2006-01-25 14:14:25.717' UNION ALL
SELECT 191, 'A', '2006-01-25 14:14:27.123' UNION ALL
SELECT 192, 'B', '2006-01-25 14:14:27.280'
SELECT MIN(seq) as seq, cat, min(cdate) as mindate,max(cdate) as maxdate,
count(*) as cnt
FROM #Temp
GROUP BY cat, YEAR(cdate), Month(cdate), day(cdate),
DATEPART(hh,cdate),DATEPART(mi,cdate),
CASE WHEN DATEPART(s,cdate) %2 = 0
THEN DATEPART(s,cdate)
ELSE DATEPART(s,cdate) - 1
END
DROP tABLE #Temp
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"John Shum" <eurostar@.gmail.com> wrote in message
news:1138177022.986492.77030@.o13g2000cwo.googlegroups.com...
>I would like to prepare a SQL such that I can perform query on a table
> and count the number of record per time slot. The time slot is 2
> seconds each, but the beginning time of each time slot is not fix, it
> is determined by the create_date of the record, and this is the
> example:
> Raw Data:
> seq cat create_date
> -- -- --
> 151 A 2006-01-25 14:14:20.827
> 152 B 2006-01-25 14:14:20.983
> 161 A 2006-01-25 14:14:22.390
> 162 B 2006-01-25 14:14:22.543
> 171 A 2006-01-25 14:14:23.997
> 172 B 2006-01-25 14:14:24.153
> 181 A 2006-01-25 14:14:25.560
> 182 B 2006-01-25 14:14:25.717
> 191 A 2006-01-25 14:14:27.123
> 192 B 2006-01-25 14:14:27.280
> Output:
> seq cat create_date count
> -- -- -- --
> 151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
> 171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
> 191 A 2006-01-25 14:14:27.123 1 -- contain 191
> 152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
> 172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
> 192 B 2006-01-25 14:14:27.280 1 -- contain 192
> Any idea on how the SQL should write?
> I just think of a SQL to get the intermediate grouping on which time
> slot the record belongs to, but no idea on how to eliminate the
> duplicated entry which is already included in another time slot....
> The unfinished SQL:
> SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
> n.create_date, n.random_string
> FROM bbs m
> LEFT OUTER JOIN bbs n ON n.cat = m.cat
> AND n.create_date > m.create_date AND
> datediff(ss, m.create_date, n.create_date) < 2
> ORDER BY m.create_date
>

Group chart by month

I have a really basic chart that pulls data from a SQL table. I have
the dataset asking for a machine number. The date range is hard coded
in the query for now.
This is my table's basic data...
Machine | Performance | DateTime
____________________________
1125 | 60 | 11/16/06
1125 | 45 | 12/01/06
1125 | 35 | 12/15/06
The results give me all the machines details from sept06 through
feb07. The series is the machine, Y axis is the performace and the X
Axis is the date formated by MM/YY
Right now the chart shows me 3 records. one for 11/06 and two for
12/06. Each record has a bar for the proper performance number.
What I need is 2 records.
11/06 and the bar will represent the avg for all Nov records.
12/06 and the bar will represent the avg for all Dec records.
I'm not sure if I have to change the dataset query or adjust the
groupings or whatever in the table. I'm lost right now."Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1170436640.424961.261770@.p10g2000cwp.googlegroups.com...
>I have a really basic chart that pulls data from a SQL
>table. I have
> the dataset asking for a machine number. The date range
> is hard coded
> in the query for now.
> This is my table's basic data...
> Machine | Performance | DateTime
> ____________________________
> 1125 | 60 | 11/16/06
> 1125 | 45 | 12/01/06
> 1125 | 35 | 12/15/06
> The results give me all the machines details from sept06
> through
> feb07. The series is the machine, Y axis is the
> performace and the X
> Axis is the date formated by MM/YY
> Right now the chart shows me 3 records. one for 11/06 and
> two for
> 12/06. Each record has a bar for the proper performance
> number.
> What I need is 2 records.
> 11/06 and the bar will represent the avg for all Nov
> records.
> 12/06 and the bar will represent the avg for all Dec
> records.
> I'm not sure if I have to change the dataset query or
> adjust the
> groupings or whatever in the table. I'm lost right now.
>
I suggest you change the query. You don't mention a primary
key. You need one. You can then filter the data in your SQL
query. May I suggest you ask this question in one of the SQL
Server newsgroups (such as
microsoft.public.sqlserver.newusers, or
microsoft.public.sqlserver.programming) and give them your
full table structure and the query you are using. Include an
example of the results you want and the incorrect results
you are getting now.
Hope this helps.
Ron.

GROUP BY, GROUP BY, and DISTINCT

I have an SQL-Server table that contains "date, name, and account number"
records like:
22-Jan-2005 Bill BA39833J
22-Jan-2005 Bill RJ3399K
22-Jan-2005 Bill KL9833LL
22-Jan-2005 Bill BA39833J
23-Jan-2005 Bill HP54599K
23-Jan-2005 Bill AA9833LL
23-Jan-2005 Bill BA90330Q
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill XX93939
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill BA8993PPQ
I need to "group by" the date, and the user... and then "count()" the total
number
of account numbers that appear for each record.
... but here's the odd part...
I need to count each account # as "1"... except when there are similar
account numbers... then those all count as "1".
(Not counting each as "each".)
So the result will look something like:
22-Jan-2005 Bill 3 (Not 4, because the 2 similar values count as
1)
23-Jan-2005 Bill 3
24-Jan-2005 Bill 2 (Not 5, because the 4 similar values count as
1)
I thought all I would have to do is to "group by" all 3 fields... "date,
user, and account
number"... but that, of course, gives me something that I do NOT want.
ThanksTry,
select
colA,
colB,
count(distinct colC)
from
t
group by
colA,
colB
order by
colA,
colB
AMB
""A_Michigan_User"" wrote:

> I have an SQL-Server table that contains "date, name, and account number"
> records like:
> 22-Jan-2005 Bill BA39833J
> 22-Jan-2005 Bill RJ3399K
> 22-Jan-2005 Bill KL9833LL
> 22-Jan-2005 Bill BA39833J
> 23-Jan-2005 Bill HP54599K
> 23-Jan-2005 Bill AA9833LL
> 23-Jan-2005 Bill BA90330Q
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill XX93939
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill BA8993PPQ
> I need to "group by" the date, and the user... and then "count()" the tota
l
> number
> of account numbers that appear for each record.
> ... but here's the odd part...
> I need to count each account # as "1"... except when there are similar
> account numbers... then those all count as "1".
> (Not counting each as "each".)
> So the result will look something like:
> 22-Jan-2005 Bill 3 (Not 4, because the 2 similar values count
as
> 1)
> 23-Jan-2005 Bill 3
> 24-Jan-2005 Bill 2 (Not 5, because the 4 similar values count
as
> 1)
> I thought all I would have to do is to "group by" all 3 fields... "date,
> user, and account
> number"... but that, of course, gives me something that I do NOT want.
> Thanks
>
>

Group by Top # entered in as Parameter

Background: I have a report that groups by Item number and gives adds
up total amount for that item number. What I want to do is have the
user enter in a numeric value as a parameter such as 10, 15, 20, etc
that will then only display the TOP 10, 15, 20, etc (what they entered
in the parameter) total amounts on the report. Can anyone help me out,
Im sure this can be done but it gets tricky with the parameters thrown
in the mix. Any suggestions is much appreciated. Thanks!hi brent
you can do this w/o issue by using a stored procedure as the source dataset
(and having your 'TOP' value included as one of the parameters).
next, you are going to need to supply a dataset for the dropdown:
select '10' as topval
union
select '20' as topval
union
select '3....
if you plan on 'rolling your own' ASP.NET interface, you can preload the
values for the dropdown in HTML.
Rob
"Brent" wrote:
> Background: I have a report that groups by Item number and gives adds
> up total amount for that item number. What I want to do is have the
> user enter in a numeric value as a parameter such as 10, 15, 20, etc
> that will then only display the TOP 10, 15, 20, etc (what they entered
> in the parameter) total amounts on the report. Can anyone help me out,
> Im sure this can be done but it gets tricky with the parameters thrown
> in the mix. Any suggestions is much appreciated. Thanks!
>