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 records - HOW TO

Hi,
Is there a way that I can get the same sort of output as when you create a
relationship between two tables, but with only one table?
With a dataset containing two (or more) tables that has a data relation
added, you get a resultant output (say a datagrid) that groups by the column
detailed in the relationship. When the datagrid is displayed it shows each
parent record (row) with a '+' next to it. When selected you can then
display the related child records.
I have a single table with multiple records. Let's say one field is name.
There may be multiple records for each person who is displayed in the name
field. Instead of doing a simple sort and showing all records at the same
time, I would like to have the '+', and only show the persons name once. I
could then expand that record to show all for that person.
Is this possible?
I am using VB.Net, windows display (not IE), and sql server.
Hope there is an answer out there............even if it is NO ;-)
Rgds, PhilPhil,
use
SELECT DISTINCT name FROM table
for the first DataTable and
SELECT name, other_cols_needed FROM table
for the second DataTable.
Then add a DataRelation on the name column.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Phil" <Phil@.nospam.com> wrote in message
news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Hi,
> Is there a way that I can get the same sort of output as when you create a
> relationship between two tables, but with only one table?
> With a dataset containing two (or more) tables that has a data relation
> added, you get a resultant output (say a datagrid) that groups by the
> column detailed in the relationship. When the datagrid is displayed it
> shows each parent record (row) with a '+' next to it. When selected you
> can then display the related child records.
> I have a single table with multiple records. Let's say one field is name.
> There may be multiple records for each person who is displayed in the name
> field. Instead of doing a simple sort and showing all records at the same
> time, I would like to have the '+', and only show the persons name once. I
> could then expand that record to show all for that person.
> Is this possible?
> I am using VB.Net, windows display (not IE), and sql server.
> Hope there is an answer out there............even if it is NO ;-)
> Rgds, Phil
>|||Dejan,
Thanks for the response but I only have ONE table, as stated. If I had two
tables then it wouldn't be a problem for me. Can it be done with one table?
Cheers, Phil
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23r89FNBoFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Phil,
> use
> SELECT DISTINCT name FROM table
> for the first DataTable and
> SELECT name, other_cols_needed FROM table
> for the second DataTable.
> Then add a DataRelation on the name column.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Phil" <Phil@.nospam.com> wrote in message
> news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||> Thanks for the response but I only have ONE table, as stated. If I had two
> tables then it wouldn't be a problem for me. Can it be done with one
> table?
You can use two selects to fill two DataTable objects from a single SQL
table.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Grouping records

Hi,

I have the following tables :

Town
towncode
townname

Area
areano
areaname
towncode

Locality

locno
areano

RequestType
reqid
reqdtls

Eg:
1 - Addition
2 - Removal
3 - Relocate

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to find out the Town/Area/Locality wise Addition/Removal/Relocation request that have come
during the last 1 month.

The query I have written so far is as follows :

SELECT WebRequest.SummitId, RequestType.reqdtls, Area.areaname, Locality.locno, Town.townname
FROM RequestType INNER JOIN
WebRequest ON RequestType.reqid = WebRequest.reqid INNER JOIN
Area ON WebRequest.areano = Area.areano INNER JOIN
TownList ON Area.towncode = TownList.towncode INNER JOIN
Locality ON Area.areano = Locality.areano

However the results are entirely incorrect with a lot of duplicates.

Kindly suggest me the right query

Regards,

Vidya.

Vidya:

You have given a good description of your problem that includes (1) table schema (2) current query and (3) the specific problem -- duplicate rows. What is missing here is (1) sample data and (2) desired output. Without this we must to some extent guess at the problem. You might be able to fix the problem by simply adding a DISTINCT clause to your select statement. This can be done by changing the word SELECT into SELECT DISTINCT.

|||

IF I understand you correctly, you may want something more like this:

Code Snippet

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
l.LocNo,
t.TownName
FROM TownList t
JOIN Area a
ON t.TownCode = a.TownCode
JOIN Locality l
ON a.AreaNo = l.AreaNo
JOIN WebRequest w
ON w.AreaNo = a.AreaNo
JOIN RequestType ar
ON r.ReqID = w.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )

Without sample data, we can't test our suggestions.

|||

Hi,

Yes I understand that without sample data it is difficult to test the query. I am sorry.

I will try and take care of this point the next time i need some help.

Thanks for the help anyways. I will test it at my end and revert back.

Regards,

Vidya.

|||

Hi,

The results are the same as I got

Here's some sample data

Town

TownCode TownName

1 Conteck

Area

AreaNo AreaName TownCode

1 Area1 1

2 Area2 1

3 Area 3 1

4 Area4 1

5 Area5 1

6 Area6 1

7 Area7 1

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 2

4 Ben 12/6/2007 2 2

5 Dale 14/6/2007 2 3

6 Evjen 15/6/2007 3 1

7 Fuller 16/6/2007 1 4

8 Jimmy 16/6/2007 3 4

9 Kart 16/6/2007 1 5

10 Fuller 16/6/2007 1 5

Regards,

Vidya.

|||

Hello,

Any clues why the query is returning duplicate records. The data gets duplicated for every locality in that area.

Regards,

Vidya

|||

Thanks for providing the table information. It is now possible to realize that it is impossible to get the information you desire. There are significant 'design problems' that will prevent you from accurately getting what you want.

You are getting duplicates because each Area has Two Localities, there is NO way to associate a Town to a specific Locality, AND there is no way to associate a specific town to WebSummit Request.

Therefore, the result shows all Localities and all Towns for an Area.

(I think you want the specific town related to a WebSummit request.)

Here are my suggestions about 'design problems' that should be corrected.

The Towns table 'should' have LocNo. Locality is an attribute of the Town. From the LocNo, we can get the Area.

The Areas table 'should NOT' have the TownCode. TownName is NOT an attribute of the Area.

The WebSummit table 'should' have the TownCode -NOT the AreaNo. Without the TownCode, you cannot break it down below the level of the Area. You have NO way to associate a Locality or Town to a WebSummit row.

Think of it this way: A Ttown belongs to a Locality, a Locality belongs to an Area. A WebSummit Request is related to a specific Town, NOT to all towns in a Locality, or NOT to all towns in an Area.

(At least that is my interpretation of your outline -and it's late, so I could be totally wrong...)

|||

Hi Arnie,

You have written :

A Ttown belongs to a Locality, a Locality belongs to an Area.

whereas

A Town has areas, Areas have locality so...

A locality belongs to an area, an area belongs to a town.

Regards,

Vidya.

|||

Thanks for clarifying that. This is why this process works so much better when we are provided table DDL, sample data, desired results, AND an explanition of the data model.

Well, I was making assumptions based upon my interpretation of the data you provided. As I said, it's late, and I made some incorrect assumptions.

In your sample data, you have a Location (LocNo 1) belonging to multiple Areas. Is that correct?

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 1, 2 )
INSERT INTO @.Locality VALUES ( 2, 2 )

And the WebSummit rows are related to Areas, not Locality. So any attempt to bring Locality into the resultset will entail having all Localities for an Area.

It still seems that the WebSummit data does not capture the finest granular information and most likely should have LocNo instead of AreaNo. Otherwise, by removing Locality from the resultset, you can get one row per row in the WebSummit table.

My apologies for the misinterpretation...

|||

Hi Arnie,

Its my fault again. I shortened the structure of the websummit table to explain my problem.

The websummit contains the localityno as well as the areano.

I also forgot to mention that in table locality, both the LocNo and AreaNo together form a primary key.

You can easily change the data to avoid confusion :

Locality

LocNo AreaNo

1 1

2 1

3 1

33 2

34 2

35 2

1 3

2 3

6 3

66 4

67 4

22 5

23 5

24 5

Now even though locality 1 may be in area 1 or area 3 as shown in teh data above, the uniqueness is achieved using both the locno and areano together.

SummitId RequestorName DateOfRequest reqid AreaNo LocNo

1 John 12/6/2007 1 1 1

2 Jack 13/6/2007 1 1 2

3 Bill 12/6/2007 2 2 34

4 Ben 12/6/2007 2 2 35

5 Dale 14/6/2007 2 3 6

6 Evjen 15/6/2007 3 1 3

7 Fuller 16/6/2007 1 4 66

8 Jimmy 16/6/2007 3 4 66

9 Kart 16/6/2007 1 5 23

10 Fuller 16/6/2007 1 5 24

So in short, a locality is just a no. given to divide areas, however its important. A locality belongs to only one area since both locno and area no together form a unique row.

If i have caused much confusion, I am ready to start over again with a fresh query, explaining everything in one go.

Regards,

Vidya.

|||

Vidya,

Thanks, that was the 'missing' piece. Now it becomes possible to link all the data. (Amazing what we can do when we have all the 'facts'.)

This 'should' work for you as you have explained the problem. Note that I changed the WebSummit dates so the the previous month filter will work properly. I added a few rows of data that should be out of range in order to test exculsion.

Code Snippet

SET NOCOUNT ON

DECLARE @.Towns table
( TownCode int,
TownName varchar(20)
)

INSERT INTO @.Towns VALUES ( 1, 'Conteck' )
INSERT INTO @.Towns VALUES ( 3, 'BigConteck' )

DECLARE @.Areas table
( AreaNo int,
AreaName varchar(20),
TownCode int
)

INSERT INTO @.Areas VALUES ( 1, 'Area1', 1 )
INSERT INTO @.Areas VALUES ( 2, 'Area2', 1 )
INSERT INTO @.Areas VALUES ( 3, 'Area3', 1 )
INSERT INTO @.Areas VALUES ( 4, 'Area4', 1 )
INSERT INTO @.Areas VALUES ( 5, 'Area5', 1 )
INSERT INTO @.Areas VALUES ( 6, 'Area6', 1 )
INSERT INTO @.Areas VALUES ( 7, 'Area7', 1 )
INSERT INTO @.Areas VALUES ( 8, 'Area1', 3 )

DECLARE @.Locality table
( LocNo int,
AreaNo int
)

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 33, 2 )
INSERT INTO @.Locality VALUES ( 34, 2 )
INSERT INTO @.Locality VALUES ( 35, 2 )
INSERT INTO @.Locality VALUES ( 1, 3 )
INSERT INTO @.Locality VALUES ( 2, 3 )
INSERT INTO @.Locality VALUES ( 6, 3 )
INSERT INTO @.Locality VALUES ( 10, 3 )
INSERT INTO @.Locality VALUES ( 11, 3 )
INSERT INTO @.Locality VALUES ( 66, 4 )
INSERT INTO @.Locality VALUES ( 67, 4 )
INSERT INTO @.Locality VALUES ( 22, 5 )
INSERT INTO @.Locality VALUES ( 23, 5 )
INSERT INTO @.Locality VALUES ( 24, 5 )

DECLARE @.RequestType table
( ReqID int,
ReqdTLS varchar(20)
)

INSERT INTO @.RequestType VALUES ( 1, 'Addition' )
INSERT INTO @.RequestType VALUES ( 2, 'Removal' )
INSERT INTO @.RequestType VALUES ( 3, 'Relocate' )

DECLARE @.WebSummit table
( SummitId int,
RequestorName varchar(20),
DateOfRequest datetime,
ReqID int,
AreaNo int,
LocNo int
)

INSERT INTO @.WebSummit VALUES ( 1, 'John', '2007/05/12', 1, 1, 1 )
INSERT INTO @.WebSummit VALUES ( 2, 'Jack', '2007/05/13', 1, 1, 2 )
INSERT INTO @.WebSummit VALUES ( 3, 'Bill', '2007/05/12', 2, 2, 34 )
INSERT INTO @.WebSummit VALUES ( 4, 'Ben', '2007/05/12', 2, 2, 35 )
INSERT INTO @.WebSummit VALUES ( 5, 'Dale', '2007/05/14', 2, 3, 6 )
INSERT INTO @.WebSummit VALUES ( 6, 'Evjen', '2007/05/15', 3, 1, 3 )
INSERT INTO @.WebSummit VALUES ( 7, 'Fuller', '2007/05/16', 1, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 8, 'Jimmy', '2007/05/16', 3, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 9, 'Kart', '2007/05/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 10, 'Fuller', '2007/05/16', 1, 5, 24 )
INSERT INTO @.WebSummit VALUES ( 11, 'Kart', '2007/06/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 12, 'Fuller', '2007/06/16', 1, 5, 24 )

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
a.AreaNo,
l.LocNo,
t.TownName
FROM @.WebSummit w
JOIN @.Locality l
ON ( w.AreaNo = l.AreaNo
AND w.LocNo = l.LocNo
)
JOIN @.Areas a
ON w.AreaNo = a.AreaNo
JOIN @.Towns t
ON t.TownCode = a.TownCode
JOIN @.RequestType r
ON w.ReqID = r.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )
)

|||

Bingo!!

Thanks a ton Arnie. Indeed, that's the query I was trying out.

Do me another favour. Suggest me a book to study T-sql or Sql server. I would now like to dedicate a lot of time to get strong in Sql.

See arnie, what you just did!! You inspired me!!

Regards,

Vidya.

|||

Itzak Ben-Gan has two books out on Microsoft Press -they are excellent!

A VERY worthwhile investment in your skills.

Good luck!

grouping records

Hi,
I've got three tables:
[Article]{ArticleID, Title}
[Journalist]{JournalistID, Name}
[ArticleJournalist]{JournalistID, ArticleID}
One article can be associated to one or more journalist.
Here's my request to get all of the records:
SELECT A.Title, A.ArticleID, J.JournalistID, J.Name
FROM Articles A INNER JOIN JournalistArticle JA ON A.ArticleID =
JA.ArticleID
INNER JOIN Journalist JON JA.JournalistID = J.JournalistID
It works fine but obviously there are duplicates in the resultset. How
can I store that in a temp table and have a column that would contain
the journalist's names comma-separated ?
ThanksAvoid having multiple values in a single column. It has obvious drawbacks
with respect to constraint enforcements and increased complexity.
Please search the archives of this newsgroup for a variety of workarounds.
For instance:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||" journalist's names comma-separated "
You are violating "First Normal Form" with this.
..
Do NOT violate First Normal Form. It is always a bad/stupid/idiotic idea.
http://databases.about.com/cs/speci...ducts/g/1nf.htm
<samuelberthelot@.googlemail.com> wrote in message
news:1148310249.207864.134000@.j73g2000cwa.googlegroups.com...
> Hi,
> I've got three tables:
> [Article]{ArticleID, Title}
> [Journalist]{JournalistID, Name}
> [ArticleJournalist]{JournalistID, ArticleID}
> One article can be associated to one or more journalist.
> Here's my request to get all of the records:
> SELECT A.Title, A.ArticleID, J.JournalistID, J.Name
> FROM Articles A INNER JOIN JournalistArticle JA ON A.ArticleID =
> JA.ArticleID
> INNER JOIN Journalist JON JA.JournalistID = J.JournalistID
> It works fine but obviously there are duplicates in the resultset. How
> can I store that in a temp table and have a column that would contain
> the journalist's names comma-separated ?
> Thanks
>|||I assume you want to do this for display reasons, and that you have no
intent of actually storing comma seperated values in a table. Storing the
data in that format would cause all sorts of problems.
Although there are several ways to do this in SQL, either with cursors or
XML (on 2005), this is something usually done in your presentation layer,
rather than on the database side. See if your application/reporting tool
can handle this. If you absolutley insist on doing this with SQL, there are
a number of solutions easily found by a quick search.
<samuelberthelot@.googlemail.com> wrote in message
news:1148310249.207864.134000@.j73g2000cwa.googlegroups.com...
> Hi,
> I've got three tables:
> [Article]{ArticleID, Title}
> [Journalist]{JournalistID, Name}
> [ArticleJournalist]{JournalistID, ArticleID}
> One article can be associated to one or more journalist.
> Here's my request to get all of the records:
> SELECT A.Title, A.ArticleID, J.JournalistID, J.Name
> FROM Articles A INNER JOIN JournalistArticle JA ON A.ArticleID =
> JA.ArticleID
> INNER JOIN Journalist JON JA.JournalistID = J.JournalistID
> It works fine but obviously there are duplicates in the resultset. How
> can I store that in a temp table and have a column that would contain
> the journalist's names comma-separated ?
> Thanks
>|||Make no mistake: result sets are different from tables. Result sets
consist of records and fields, not of rows and columns. Rules of
normalization do not apply to result sets.

Grouping Problem

Hi Friends

i have records at different level, i.e, accounts in a tree fomat. i want to display top most account, then on drill down it should show its child accounts and so on.

PROBLEM: levels are different, few account have grand children account and few doesn't have.

Is it possible to achieve what i am trying.

Hope i explained my problem.
Waiting for Sugesstions.

RaheemHave you tried looking at the help on Hierarchical Grouping? Might be what you're looking for.|||Thanks Very much|||it is working perfectly thanks again, but a small problem :-)

when i am using indent in hierarchical grouping options, even my hierarchichal summary fields are getting indented.

Any help in this Regard

Grouping problem

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

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

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

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

Hi Alex:

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

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

(2) How can you allow customizable grouping?

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

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

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

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

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

Hope this helps.

PGoldy

|||Hi PGoldy,

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

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

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

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

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

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

Regards,
Alex|||

Hi Alex:

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

PGoldy

|||Dear PGoldy,

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

Regards,
Alex|||

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

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

sql

Grouping in Reporting services

I am pulling 200,000 + records from an AS400 database through ODBC into a report. I have a couple of questions and I am totally new to SQL Reporting Services, so I don't expect a full answer to these questions, but maybe a couple of pointers or web sites that I can view for further information.

1) One of the fields that I am pulling in are dates going back to 1988. I need to first group these dates by year. Do the expressions used in the cells of a report table allow for formulas like =date.year?

2) I have a couple of fields that I need to group on: first being year, then state. There is a numeric field (participants) that I need to do a sum on. Do the reports have a (+) next to the grouping levels so for instance I have the 2000 year "closed", I need this to show total participants during that year (for every state). If 2000 was opened then it would list each state (that could further be drilled into) that would show total participants by state. I hope this makes sense. I used to do this in Visual Basic Windows forms 2003 version using the DataView control.

Thanks for any information.

Brad

i don't know about the first one but the second bit is possible take a look at this article on how to do it

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

grouping in ranges

Table:

SomeFKID <pk>
StartDateTime <pk>
EndDateTime
SomeValue

Example Scenario:

Data is stored hourly. So there would be 24 records for today for each SomeFKID. I need to be able to pass a TimeSpan (in minutes), a StartDateTime, and an EndDateTime to a stored procedure and return totals in the date range grouped by the TimeSpan. So if I want all records today grouped by 2 hour intervals I would need to pass:
7/1/2004 00:00:00, 7/1/2004 23:59:59, 120 --> and return 12 records one for hours 0-2, one for hours 2-4, etc.

Any advice would be greatly appreciated!

Thanks in advance,

WheatsterThe assignment had to have given you more detail than that. Does the teacher want splits pro-rated? Do you select based solely on start time? What do you aggregate?

I think this would be a lot easier if you just posted the whole assignment and let us look at it.

-PatP|||Based soley on start time is fine. Value Column aggregated. If I am missing something really obvious then please just inform me of where I need to look in the tsql help files.

Also, this will work for the prototype I am working on now, but in the future the requested Split may be smaller than the span. In other words 15 minutes where data is stored hourly...but I can deal with that later.|||I'm still unsure of how to help you get to your goal. Can you at least post 24 hours (rows) worth of sample data, with the result sets that you'd like to see for 60, 90, and 120 minutes?

-PatP|||**For 60 I want the view below
**For 120 I want rows 1&2 grouped, 3&4 grouped, etc.
**Lets just use 60 minute increments for now. 90 can be dealt with later

ScheduleID StartDateTime EndDateTime Value
---- ---------------- ---------------- ----------------
1 2004-04-10 07:00:00.000 2004-04-10 08:00:00.000 257.0
1 2004-04-10 08:00:00.000 2004-04-10 09:00:00.000 252.0
1 2004-04-10 09:00:00.000 2004-04-10 10:00:00.000 242.0
1 2004-04-10 10:00:00.000 2004-04-10 11:00:00.000 247.0
1 2004-04-10 11:00:00.000 2004-04-10 12:00:00.000 257.0
1 2004-04-10 12:00:00.000 2004-04-10 13:00:00.000 282.0
1 2004-04-10 13:00:00.000 2004-04-10 14:00:00.000 317.0
1 2004-04-10 14:00:00.000 2004-04-10 15:00:00.000 347.0
1 2004-04-10 15:00:00.000 2004-04-10 16:00:00.000 352.0
1 2004-04-10 16:00:00.000 2004-04-10 17:00:00.000 342.0
1 2004-04-10 17:00:00.000 2004-04-10 18:00:00.000 317.0
1 2004-04-10 18:00:00.000 2004-04-10 19:00:00.000 302.0
1 2004-04-10 19:00:00.000 2004-04-10 20:00:00.000 287.0
1 2004-04-10 20:00:00.000 2004-04-10 21:00:00.000 277.0
1 2004-04-10 21:00:00.000 2004-04-10 22:00:00.000 267.0
1 2004-04-10 22:00:00.000 2004-04-10 23:00:00.000 262.0
1 2004-04-10 23:00:00.000 2004-04-11 00:00:00.000 267.0
1 2004-04-11 00:00:00.000 2004-04-11 01:00:00.000 277.0
1 2004-04-11 01:00:00.000 2004-04-11 02:00:00.000 287.0
1 2004-04-11 02:00:00.000 2004-04-11 03:00:00.000 297.0
1 2004-04-11 03:00:00.000 2004-04-11 04:00:00.000 307.0
1 2004-04-11 04:00:00.000 2004-04-11 05:00:00.000 297.0
1 2004-04-11 05:00:00.000 2004-04-11 06:00:00.000 277.0
1 2004-04-11 06:00:00.000 2004-04-11 07:00:00.000 252.0
1 2004-12-12 00:00:00.000 2004-12-12 01:00:00.000 12.0|||select dateadd(minute, datediff(minute, @.StartTime, @.TestTime)/@.IntervalMinutes, @.StartTime) as Range,
.
.
.
from YourTable
group by dateadd(minute, datediff(minute, @.StartTime, @.TestTime)/@.IntervalMinutes, @.StartTime)|||I came up with:CREATE TABLE dbo.theTable (
ScheduleID INT
, StartDateTime DATETIME
, EndDateTime DATETIME
, Value DECIMAL (4, 1)
)

INSERT INTO theTable (ScheduleId, StartDateTime, EndDateTime, Value)
SELECT 1, '2004-04-10 07:00:00.000', '2004-04-10 08:00:00.000', 257.0
UNION SELECT 1, '2004-04-10 08:00:00.000', '2004-04-10 09:00:00.000', 252.0
UNION SELECT 1, '2004-04-10 09:00:00.000', '2004-04-10 10:00:00.000', 242.0
UNION SELECT 1, '2004-04-10 10:00:00.000', '2004-04-10 11:00:00.000', 247.0
UNION SELECT 1, '2004-04-10 11:00:00.000', '2004-04-10 12:00:00.000', 257.0
UNION SELECT 1, '2004-04-10 12:00:00.000', '2004-04-10 13:00:00.000', 282.0
UNION SELECT 1, '2004-04-10 13:00:00.000', '2004-04-10 14:00:00.000', 317.0
UNION SELECT 1, '2004-04-10 14:00:00.000', '2004-04-10 15:00:00.000', 347.0
UNION SELECT 1, '2004-04-10 15:00:00.000', '2004-04-10 16:00:00.000', 352.0
UNION SELECT 1, '2004-04-10 16:00:00.000', '2004-04-10 17:00:00.000', 342.0
UNION SELECT 1, '2004-04-10 17:00:00.000', '2004-04-10 18:00:00.000', 317.0
UNION SELECT 1, '2004-04-10 18:00:00.000', '2004-04-10 19:00:00.000', 302.0
UNION SELECT 1, '2004-04-10 19:00:00.000', '2004-04-10 20:00:00.000', 287.0
UNION SELECT 1, '2004-04-10 20:00:00.000', '2004-04-10 21:00:00.000', 277.0
UNION SELECT 1, '2004-04-10 21:00:00.000', '2004-04-10 22:00:00.000', 267.0
UNION SELECT 1, '2004-04-10 22:00:00.000', '2004-04-10 23:00:00.000', 262.0
UNION SELECT 1, '2004-04-10 23:00:00.000', '2004-04-11 00:00:00.000', 267.0
UNION SELECT 1, '2004-04-11 00:00:00.000', '2004-04-11 01:00:00.000', 277.0
UNION SELECT 1, '2004-04-11 01:00:00.000', '2004-04-11 02:00:00.000', 287.0
UNION SELECT 1, '2004-04-11 02:00:00.000', '2004-04-11 03:00:00.000', 297.0
UNION SELECT 1, '2004-04-11 03:00:00.000', '2004-04-11 04:00:00.000', 307.0
UNION SELECT 1, '2004-04-11 04:00:00.000', '2004-04-11 05:00:00.000', 297.0
UNION SELECT 1, '2004-04-11 05:00:00.000', '2004-04-11 06:00:00.000', 277.0
UNION SELECT 1, '2004-04-11 06:00:00.000', '2004-04-11 07:00:00.000', 252.0
UNION SELECT 1, '2004-12-12 00:00:00.000', '2004-12-12 01:00:00.000', 12.0
GO
-- ptp 20040701 re: http://www.dbforums.com/t1003286.html

CREATE PROCEDURE dbo.theQuery
@.pdStart DATETIME
, @.pdEnd DATETIME
, @.piInterval INT
AS

IF 0 <> @.piInterval % 60 RETURN

SELECT DateAdd(minute, delta * @.piInterval, @.pdStart), Sum(Value)
FROM (SELECT DateDiff(minute, @.pdStart, StartDateTime)
/ @.piInterval AS delta, Value
FROM dbo.theTable
WHERE StartDateTime BETWEEN @.pdStart AND @.pdEnd) AS a
GROUP BY delta

RETURN
GO

EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 60
EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 90
EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 120

DROP PROCEDURE dbo.theQuery
DROP TABLE dbo.theTableBlindman's query might work just as well, and it appears to be simpler.

-PatP|||Thanks guys, I really appreciate this! Pat, I just tested and your solution definitely works fine.

Blindman I tested but and not sure what you meant by @.TestDate, I tried a few scenarios and none worked.

Wheatster|||@.TestDate is the value you are checking to see what group range it belongs in.

select dateadd(minute, datediff(minute, @.StartTime, [YourDateTimeValue])/@.IntervalMinutes, @.StartTime) as Range,
.
.
.
from YourTable
group by dateadd(minute, datediff(minute, @.StartTime, [YourDateTimeValue])/@.IntervalMinutes, @.StartTime)

Grouping data by x-axis labels

Hey guys,

I have thousands of records in the data source(i.e. Cube), which includes datetime information. I want to aggregate and present the data on a bar chart on monthly basis. That means the interval between the dates should be one month. Assume that the dates value will be labeled on the x-axis of the bar chart.

I tried to find out the solution for many days. I will really appreciate if anybody give me some idea.

Sincerely,

Amde

Try creating a category group with two expressions, using the following grouping expressions.

=Fields!DateField.Value.Year
=Fields!DateField.Value.Month

For the label you could use something like, =Fields!DateField.Value.ToShortDateString()

You could also use two category groups, if you wanted an inner set of labels for the month and another outer set for the year.|||

Hi,

Thank youy for your feedback, however that doesn't solve my problem: here is the thing;

I created a bar chart report. The x-axis value of this chart is a timestamp(datetime) field of a dimension. Thousands of records are inserted in to this field everyday, as a result, I will have the timestamp information every second or minute. So here is the thing, If for instance, I want to present last 5 months data in the bar chart(x-axis), the chart can not accomodate all the data and it doesn't look good to present a data which occured every minute or hours. So I want to present the data on monthly basis based on the StartDate and EndDate parameters value provided by the user.

Assume the user wants to preview 5 months record from 2006-03-04 to 2006-07-04, the data should be aggregated and presented on monthly basis as shown below, instead of directly displaying all the data as they appear in the dataset.

---

2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04

Please let me know if you need more clarification.

Sincerely,

Amde

|||Adding the category fields with the groupings mentioned above should produce the grouping structure you are looking for. When you tried it what happened that was incorrect?

The bounds provided by the StartDate and EndDate parameters can either be used in the the sql query. Or, if it can't be done there, then you can set a filter for the category group. Also, the reason there is a group expression for Year is that the data may span multiple years and I'm assuming that you don't want the data for the same month in multiple years to be aggregated together.

Here is a sample report that uses the northwind database to show the number of orders placed for each month. It contains a bar chart and two parameters, which are used in the sql query.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=localhost; Initial Catalog=Northwind</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>40232364-d6a5-4917-bcad-13308e3a8f62</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>7/1/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>11/30/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title />
<Height>5.125in</Height>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.375in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>Northwind</DataSetName>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Bar</Type>
<Width>6.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Year</GroupExpression>
<GroupExpression>=Fields!OrderDate.Value.Month</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=MonthName(Fields!OrderDate.Value.Month)</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Count(Fields!OrderID.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>5.75in</Height>
</Body>
<rd:ReportID>6323408e-15e1-4a7f-8151-ac96e7ebf862</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>="SELECT OrderDate, OrderID FROM Orders WHERE Orders.OrderDate BETWEEN '" & Parameters!StartDate.Value & "' AND '" & Parameters!EndDate.Value & "'"</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>9.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||

Yes, that is correct. The assumption, group expression for year is also correct.

Thank you for your cooperation.

sql

Grouping by unrelated field- SQL masters, try this!

I would like to retrieve 10(dynamic) records of table x (proucts) for
each user in table y (users). Can this be done?
I would like the end result to be something like this: (would this be
a union?)
__________________________
y.name | x.pid | x.pname
Bob | 1 | fork
Bob | 2 | spoon
... | |
Bob | 10 | potato
Jeff | 11 | pen
etc....
__________________________
But also with the number to return based off of a query, ex-
select @.pcount = count(products)
select @.ucount = count(users)
select @.pcount / @.ucount
10
And lump all this in an Stored procedure
ex-
get number of total records in x, divide by total y = z
select z records for each user in y.
You would be a master in my book if you can give me hints on this one!
Thanks,
JeffHi

It is always better to post DDL ( CREATE TABLE statements etc...) and
example data (as insert statements) with the expected results that you
require from that data. That removes most of the ambiguities and reduces
that number of assumptions that someone answers your question will have to
make.

This seems to be something similar to what you require
http://tinyurl.com/28dhn

John

"JC" <ujjc001@.charter.net> wrote in message
news:b8c0d25d.0407061959.2f9791ca@.posting.google.c om...
> I would like to retrieve 10(dynamic) records of table x (proucts) for
> each user in table y (users). Can this be done?
> I would like the end result to be something like this: (would this be
> a union?)
> __________________________
> y.name | x.pid | x.pname
> Bob | 1 | fork
> Bob | 2 | spoon
> ... | |
> Bob | 10 | potato
> Jeff | 11 | pen
> etc....
> __________________________
> But also with the number to return based off of a query, ex-
> select @.pcount = count(products)
> select @.ucount = count(users)
> select @.pcount / @.ucount
> 10
> And lump all this in an Stored procedure
> ex-
> get number of total records in x, divide by total y = z
> select z records for each user in y.
> You would be a master in my book if you can give me hints on this one!
> Thanks,
> Jeffsql

Grouping by datecolumn

Hi,

i have a table which contains the following columns:

Id = bigint id 1 +1
Machineid = bigint
logdate = datetime
vtp = bigint,

There are more records for 1 machine p/day
i want to group them together by complete day and get the sum of vtp for every different machine for each day.

Someone knows how to do this?
Thanx in front!
CHeers Wimselect machineid, convert(char(10),logdate,120), sum(vtp)
from <your_table>
where <if any conditions apply>
group by machineid, convert(char(10),logdate,120)
order by machineid, convert(char(10),logdate,120)

/*
look up cast / convert in BOL for other date / time formats
*/

grouping by a datetime column

i want to group the records in a table by day , using a datetime column.
Therefore I have to get rid of the time of that column before grouping.
What is the proper way to do that?
thnks..prefect wrote:
> i want to group the records in a table by day , using a datetime column
.
> Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>
GROUP BY
DATEPART(month, datevalue),
DATEPART(day, datevalue),
DATEPART(year, datevalue)|||SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>i want to group the records in a table by day , using a datetime column.
>Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>|||> GROUP BY
> DATEPART(month, datevalue),
> DATEPART(day, datevalue),
> DATEPART(year, datevalue)
FYI, on a large table, this can be a significant performance hit...
In fact, my solution is only marginally better. The best solution would
probably combine a static calendar table (see http://www.aspfaq.com/2519 for
some practical usage).|||that is what i look for.
thanks..
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||Aaron Bertrand [SQL Server MVP] wrote:
> FYI, on a large table, this can be a significant performance hit...
> In fact, my solution is only marginally better. The best solution would
> probably combine a static calendar table (see http://www.aspfaq.com/2519 f
or
> some practical usage).
>
Agreed.|||Aaron , I want to send the DateColumnName to a UDF for some processing,
then return something.
But I have a error like "DateColumnName is not in group by clause..."
My usage is as follows:
SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||What exactly are you doing, formatting it for the client? Why don't you let
the presentation/client tier do this? What does dbo.MyUDF do, exactly, that
CONVERT() with a style option couldn't do?
Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that exist
in the SELECT list that are not constants or aggregates must also appear in
GROUP BY clause. But a slightly more efficient way would be to perform the
function against the result instead of during the aggregation:
SELECT
dt,
dbo.MyUDF(dt),
cnt
FROM
(SELECT
dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
cnt = COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
) x
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1|||I created a computed Column for DateColumnName
as DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
and used this computed column for grouping and parameter for MyUdf. it is
working.
But i would wanna know if there is a better way..
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||
> What exactly are you doing, formatting it for the client? Why don't you
> let the presentation/client tier do this?
yes it should be this way. But for some reason off my hand , i am not able
to do
in the presentation layer.

> What does dbo.MyUDF do, exactly, that CONVERT() with a style option
> couldn't do?
no, unfortunately..

> Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that
> exist in the SELECT list that are not constants or aggregates must also
> appear in GROUP BY clause. But a slightly more efficient way would be to
> perform the function against the result instead of during the aggregation:
>
> SELECT
> dt,
> dbo.MyUDF(dt),
> cnt
> FROM
> (SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> cnt = COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ) x
> ORDER BY 1;
i will try that, can you comment my other post?

> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
>

grouping and ordering in OUTER JOIN

Here's table 1:
Fans
FanID, username
Table 2:
Photos
PhotoID, FanID, photofilename, defaultPhoto (true or false)
Not all records in Fans will have a corresponding record in Photos.
I'm doing an outer join that gets ALL the Fan records, and if they have
a record in Photos, and displays the Photofilename. If it does not
have a Photo record, it displays NULL.
Here's the query:
SELECT username, fans.FanID, photofilename FROM Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID
Where DefaultPhoto = 'True' or photofilename is NULL
This results in a recordset like this:
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
hollywood 16 hollywood_1.jpg
Ed Shiv 71 (NULL)
monte 18 (NULL)
abm 19 abm_1.jpg
and2 20 (NULL)
and 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
dave 24 dave_2.jpg
I need to organize these results with the records WITH a Photofilename
value FIRST and then all those with NULL in the Photofilename next.
I will then also like to order by NEWID() (randomize within these
groups)
Something like this:
hollywood 16 hollywood_1.jpg
abm 19 abm_1.jpg
dave 24 dave_2.jpg
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
Ed Shiv 71 (NULL)
monte 18 (NULL)
and2 20 (NULL)
andl 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
I've tried to use GROUP BY but I get an error if I don't include ALL
the fields from the select.
I thought I'd be able to say GROUP BY Photofilename ORDER BY NEWID()
but it's not working.
Any suggestions?You don't need a GROUP BY and I don't believe you need that WHERE clause.
(Please post your DDL.) That said, try:
SELECT username, fans.FanID, photofilename FROM Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID
ORDER BY
case when Photos.FanID is null then 0 else 1 end
, newid()
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<andymilk@.gmail.com> wrote in message
news:1146606774.674581.3760@.g10g2000cwb.googlegroups.com...
Here's table 1:
Fans
FanID, username
Table 2:
Photos
PhotoID, FanID, photofilename, defaultPhoto (true or false)
Not all records in Fans will have a corresponding record in Photos.
I'm doing an outer join that gets ALL the Fan records, and if they have
a record in Photos, and displays the Photofilename. If it does not
have a Photo record, it displays NULL.
Here's the query:
SELECT username, fans.FanID, photofilename FROM Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID
Where DefaultPhoto = 'True' or photofilename is NULL
This results in a recordset like this:
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
hollywood 16 hollywood_1.jpg
Ed Shiv 71 (NULL)
monte 18 (NULL)
abm 19 abm_1.jpg
and2 20 (NULL)
and 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
dave 24 dave_2.jpg
I need to organize these results with the records WITH a Photofilename
value FIRST and then all those with NULL in the Photofilename next.
I will then also like to order by NEWID() (randomize within these
groups)
Something like this:
hollywood 16 hollywood_1.jpg
abm 19 abm_1.jpg
dave 24 dave_2.jpg
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
Ed Shiv 71 (NULL)
monte 18 (NULL)
and2 20 (NULL)
andl 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
I've tried to use GROUP BY but I get an error if I don't include ALL
the fields from the select.
I thought I'd be able to say GROUP BY Photofilename ORDER BY NEWID()
but it's not working.
Any suggestions?|||Thank you! Looks like that's doing the trick!|||After all this...is there a way to retrieve a RANGE of these?
For instance, records 100-115...
Since I'm pulling down 13000 records, it's causing a timeout
Thanks,
Andy|||Could you give us an example? It's not clear what you want.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<andymilk@.gmail.com> wrote in message
news:1146691570.341007.131600@.y43g2000cwc.googlegroups.com...
After all this...is there a way to retrieve a RANGE of these?
For instance, records 100-115...
Since I'm pulling down 13000 records, it's causing a timeout
Thanks,
Andy|||I just want to retrieve 100 or so records...or a specific range of
records from this query:
SELECT
(fans. Fanid*(1+datepart(s,getDate()))*(1+datep
art(ms,getDate())))%1000
sortid, username, fans.FanID, PhotoID from Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID where defaultphoto
= 'true' or photoID is NULL
ORDER BY
case when Photos.FanID is null then 1 else 0 end, sortid
Also, should this query be taking 20 seconds to execute?
The tables it's pulling from have 14,000 (Fans) and 20,000 (Photos)
records in them.|||The first thing I'd do is look at your indexing. Does Photos have an index
on FanID?
As for limiting the number of rows, you could use SELECT TOP 100, for
example, to get 100 rows. Be sure to use an appropriate ORDER BY clause.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<andymilk@.gmail.com> wrote in message
news:1146750558.397604.245250@.i39g2000cwa.googlegroups.com...
I just want to retrieve 100 or so records...or a specific range of
records from this query:
SELECT
(fans. Fanid*(1+datepart(s,getDate()))*(1+datep
art(ms,getDate())))%1000
sortid, username, fans.FanID, PhotoID from Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID where defaultphoto
= 'true' or photoID is NULL
ORDER BY
case when Photos.FanID is null then 1 else 0 end, sortid
Also, should this query be taking 20 seconds to execute?
The tables it's pulling from have 14,000 (Fans) and 20,000 (Photos)
records in them.

Grouping and Custom Code

Hello everyone,

I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.

TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal

I have the following in my Code window.

Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function

This gives me incorrect results and I can't figure out why. Here's how it shows on my report:

Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00

The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.

As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?

Jarret

Hi Jarret,

The reason for seeing 0 (I think) Is that after a group ends, Reporting Services basically creates a new instance of your custom code and therefore any saved values get cleared.

I am not sure how your GF1 shows a value though... I could be wrong, but this is the experience I have had...

Regards,
Neil

|||The way I approached it...

I ordered my duplicate values... or some way of identifying that the value was not needed, and if the previous item = that item then do not add it to the total... Then for each footer call the same code and passing in the same values.

So each footer will be identical ... passing in the value and some other way of identifying if the value is unique...

Hope this helps...

Regards,
Neil

Group with Time Values - Please Help

I have two tables that are joined by a left outer join.
Table A (Hours) just has 24 records that represent each our in the day. For example:
00:00, 01:00, ...23:00. Table B (Data) has the data I need to report off of. I joined
Table A to Table B.

I created a group on Table A because I always want to display all 24 hours even if there
is no data in Table B for that hour. So I now have 24 sections in my group.

My problem is that when I put data into the details section, I'm only getting data where
the hours exactly match. For example, Group section 08:00 is only returning data where
the hour is 08:00. I actually need it to return all data where the hour is between
08:00 and 08:59. I've been working on this for a while and I'm really stuck.

I'm using an access database and the hours field in both table is a date/time field.

Any help would be greatly appreciated. Thanks so much.

- StephanieHi,

I had experienced the same problem. I had to display the data for all the days in a month regardless of the data they have.

Used the same left outer join concept. But it didn't work. Then I had created a temp table and written code to get the result.

If any body knows why the left outer join concept is not working in crystal, please share with us

sample

table1 : contains simply all the dates from 1 to lastday
table2 : contains data for the dates in table1 (not for all the days)|||Hi Stephanie
One possible solution would be to create a report based only on Table A(with hours registered) and subreport based on Table B.Don't make any links between them.In main report insert group for field that holds hours.If you view preview now you would see all records from Table B for each hour.
In main report create a formula and add shared variable and assign only first two characters from group field.Values will be 00,01,02 etc.
Now,in subreport supress all records where first two characters of your hour fiels in Table B are not equal to shared variable.
I tested this in CR 8.0 and it worked fine.|||Thanks Denan, that's a very interesting suggestion! I'm going to try that right now!

Stephanie|||Good idea!

But what about the performance?

For a single day, the report will be called 24 times?|||Hi
Performance is definetly not optimal here.Best solution would be to filter data in subreport but unfortunately CR (at least 8.0) doesn't allow shared variables in record selection.
Biggest problem here is that you can not link those two tables.Left outer join doesn't help since it also requires a match in both tables.
Another solution would be to add another field in Table B that would hold values like 08:00,09:00 etc.That means you would need to add application logic to compute that value.I think that solution is more expensive.

Đenan