Data Modeling: Managing a group of events, both Special & Regular.

Scenario: The system needs to manage events by group. There is no hierachica
l
concept in the events. Special events have different attributes that need to
be treated differently. Note that the DeviceID has a many-to-one relationshi
p
with a LocationID (not in the model). Not seen in the model also include
PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
Eventually the system will need to report all the related events for a
special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID i
n
SpecialEvent, what would be the advantages/divantages?
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, the drafted database design is as the
following:
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])Hi CTO,
1. I question the basic structure of the SpecialEvents Table. Are there
Multiple "Special Events" for any single Ordinary Event? If not then the
Special Event Table Primary Key should be the same as the Event Table Primar
y
Key, and it should be a one-to-one relationship, not a many-to-one
relationship. So, what's up with "TypeCode" in the SpecialEvents Table? If
there can be multiple Rows in this table witht he same SpecialEventID, but
with different "TypeCode" values, then this table, as structured, is NOT a
table of SpecialEvents, it's something else, (what I don't know) and should
be renamed, and a real specialEvents table should be added.
2. Please clarify what you mean by "Related Events". Do the groups of
Relate dEvents Overlap? i.e., could Event A be related to EventB and Event
B
be related to EventC, but EventC NOT be related to EventA? like for example
cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, even
though Bob and SAlly are unrelated... Or,
Are all the "Groups" formed by the relations non-overlapping distinct
groups?, as for eample Citys and States, or Football teams and Leagues, etc.
If it's the latter then the more appropriate way to "relate" the events, is
to just adda groupID in the events table, and make the value the same for
all the events in the group... If there's some data attribute that is
associated with the group, and not with teach individual event, then add an
EventGroup Table that just has GroupID (as PK) and those Group-specific Data
attributes as additional columns...
hth,
Charly
"C TO" wrote:

> Scenario: The system needs to manage events by group. There is no hierachi
cal
> concept in the events. Special events have different attributes that need
to
> be treated differently. Note that the DeviceID has a many-to-one relations
hip
> with a LocationID (not in the model). Not seen in the model also include
> PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
> Eventually the system will need to report all the related events for a
> special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3) Ple
ase
> comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID
in
> SpecialEvent, what would be the advantages/divantages?
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, the drafted database design is as the
> following:
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>

Data Modeling: Managing a group of events, both Special & Regu

A minor correction: StandardEventID should read SpecialEventID
Having removed the TypeCode from the SpecialEvent, and knowing that any
EventID can be in anyway related to either relatedEvent or SpecialEvent, is
it safe to model as the following?
1. All events initially is created in Event table.
2. If there is a special event, which has additional attributes, then a
special event will be created (triggered?). The same eventID will be
identified in the SpecialEvent table. No related event has been created at
this point.
3. Say, when there are more than 100 events, for example, and we start
relating/grouping them, we can get EventID from Event to RelatedEvent Table.
Since Event can have both ordinary and special eventIDs, both eventIDs can b
e
related in RelatedEvent. Before I continue, are there any issues so far? To
continue, with this approach, I can see one possible problem here, for
reporting, each time we need to a special event with standard/common event
attributes, we will need to join the Event table. Besides, to identify a
special event from the Event table, we must join SpecialEvent. However, ther
e
should be no more than 100 special events in a w and the reporting period
usually does not span more than 13 ws.
"CBretana" wrote:
> Hi CTO,
> 1. I question the basic structure of the SpecialEvents Table. Are there
> Multiple "Special Events" for any single Ordinary Event? If not then the
> Special Event Table Primary Key should be the same as the Event Table Prim
ary
> Key, and it should be a one-to-one relationship, not a many-to-one
> relationship. So, what's up with "TypeCode" in the SpecialEvents Table?
If
> there can be multiple Rows in this table witht he same SpecialEventID, but
> with different "TypeCode" values, then this table, as structured, is NOT a
> table of SpecialEvents, it's something else, (what I don't know) and shoul
d
> be renamed, and a real specialEvents table should be added.
> 2. Please clarify what you mean by "Related Events". Do the groups of
> Relate dEvents Overlap? i.e., could Event A be related to EventB and Even
t B
> be related to EventC, but EventC NOT be related to EventA? like for examp
le
> cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, eve
n
> though Bob and SAlly are unrelated... Or,
> Are all the "Groups" formed by the relations non-overlapping distinct
> groups?, as for eample Citys and States, or Football teams and Leagues, et
c.
> If it's the latter then the more appropriate way to "relate" the events, i
s
> to just adda groupID in the events table, and make the value the same for
> all the events in the group... If there's some data attribute that is
> associated with the group, and not with teach individual event, then add a
n
> EventGroup Table that just has GroupID (as PK) and those Group-specific Da
ta
> attributes as additional columns...
>
> hth,
> Charly
> "C TO" wrote:
>1. No, do not use a trigger... Are you using stored procs exclusively to
access the system? If so, write yr Insert Update Stored Proc(s) (I use one
for both, but you may have one each) to take a parameter to Identify whethe
r
Event is "Special", (Or, just include Special Event's attribuites, with null
default values, and only passs them when it IS a special Event.
Then, inside SP Logic, after creating or updating Event Table, since SP
'Knows" (based on parameter flag, or presence/absence of Special Attribute
data), whether it's dealing with a regular or special event, either
insert/update, or delete (if Special is changing back to regular) the row in
the SpecualEvent Table...
2. If event "relations can overlap, then a single Event can be in more than
one "group". The structure you had only allows a Chain of Associations A-B,
B-C, C-D, D-E, etc... It does not allow A,B,C,D to all belong equally to the
same group. WHich of these models is correct? If it's the latter, then you
need a separate table to hold these associations...
Create Table EventAssociations
(GroupID Integer Not Null,
EventID Integer Not Null,
Primary Key (GroupID, EventID)
)
If you need to store any data attributes about the group, then you need an
EventGroups Table as well.
Create Table EventGroups
(GroupID Integer Primary Key Not Null,
GroupName VarChar(30),
..
)
3. <snip>Since Event can have both ordinary and special eventIDs, both
eventIDs can be related in RelatedEvent...</snip> NO. For every Event, a
row should be created in Events Table, For Special Events, a row should be
created in Events Table AND in SpecialEvents Table, but use the same EventI
D
in Both tables for that row... WHy use a different ID ? Then you just have
to create a mapping from one to the other. This way the EventID of
SpecialEvents TAble can be used not only as PK, but as FK back to EventID in
Events Table. In Stored Proc Code, create Event Record, get ID of record
created, (If you're using IDentity, and then use that VAlue t oinsert
SpecialEVent Record PK. I'd even call the SpecialEVent table PK column NAme
the exact same (EventID), not SpecialEventID, just to make it clearthey ar
e
the same value.
"C TO" wrote:
> A minor correction: StandardEventID should read SpecialEventID
> Having removed the TypeCode from the SpecialEvent, and knowing that any
> EventID can be in anyway related to either relatedEvent or SpecialEvent, i
s
> it safe to model as the following?
> 1. All events initially is created in Event table.
> 2. If there is a special event, which has additional attributes, then a
> special event will be created (triggered?). The same eventID will be
> identified in the SpecialEvent table. No related event has been created at
> this point.
> 3. Say, when there are more than 100 events, for example, and we start
> relating/grouping them, we can get EventID from Event to RelatedEvent Tabl
e.
> Since Event can have both ordinary and special eventIDs, both eventIDs can
be
> related in RelatedEvent. Before I continue, are there any issues so far? T
o
> continue, with this approach, I can see one possible problem here, for
> reporting, each time we need to a special event with standard/common event
> attributes, we will need to join the Event table. Besides, to identify a
> special event from the Event table, we must join SpecialEvent. However, th
ere
> should be no more than 100 special events in a w and the reporting peri
od
> usually does not span more than 13 ws.
> "CBretana" wrote:
>|||4. <snip>I can see one possible problem here, for reporting, each time we
need to a special event with standard/common event attributes, we will need
to join the Event table</snip> Yes, so what? The data is in the other
table... that's what relational databases are designed to do. The only
alternative is to put all the data into the Events Table. If you had
Employees Table, with 80k rows, and four extra really long data attributes
you needed to store for the 20 employees in the legal Department, would you
a) add those four attributes to all 80,000 employees,
b) Move 20 legal employees into a separate table that had the extra four
attributes,
(This means to get, or search, or Update against ALL EMployees you
have to UNion both tables together.),
c) Create a separate table for Just those four attributes, and leave
regular employee data in the main table.
ANSWER C.
Also, if determining whether an event is "Special" is a common task,
INDEPENDANT of accessing the Special Data, then add a flag to Events Table
to indicate whether the row is a Special Event... You could name it it
"IsSpecial" and type it as your favorite boolean type - (You can use Char(1)
with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0).
THis is a denormalization, (it is redundant, since presence of row with same
eventID in SpecialEvents table is same datum), but it will allow you to
determine "SpecialNess" without the Join.
"C TO" wrote:
> A minor correction: StandardEventID should read SpecialEventID
> Having removed the TypeCode from the SpecialEvent, and knowing that any
> EventID can be in anyway related to either relatedEvent or SpecialEvent, i
s
> it safe to model as the following?
> 1. All events initially is created in Event table.
> 2. If there is a special event, which has additional attributes, then a
> special event will be created (triggered?). The same eventID will be
> identified in the SpecialEvent table. No related event has been created at
> this point.
> 3. Say, when there are more than 100 events, for example, and we start
> relating/grouping them, we can get EventID from Event to RelatedEvent Tabl
e.
> Since Event can have both ordinary and special eventIDs, both eventIDs can
be
> related in RelatedEvent. Before I continue, are there any issues so far? T
o
> continue, with this approach, I can see one possible problem here, for
> reporting, each time we need to a special event with standard/common event
> attributes, we will need to join the Event table. Besides, to identify a
> special event from the Event table, we must join SpecialEvent. However, th
ere
> should be no more than 100 special events in a w and the reporting peri
od
> usually does not span more than 13 ws.
> "CBretana" wrote:
>|||FoA, Many Thanks.
1. Agreed. When I use the word "trigger", I did not mean trigger in database
sense. I agree with you.
2. I am with the concept of having a GroupID. the events do not
have a Chain of Association. For example 1-2, 1-4, 4-3,4-1,4-7, so when I
want to retrieve all events related to 4, I will yeild 1,3,4,7 in my view. I
t
is mainly for project management. Someone has to have the flexibility to
group the events the way they want for event management purpose. For example
,
say we have 5 TypeCodes in specialEvent, and:
a. if I want to create a new TypeCode of that EventID, I can create a new
EventID in Event and SpecialEvent with a that TypeCode.
b. Later, I can also relate an ordinary past EventID to this special
EventID. I can also add a new (future) ordinary EventID to relate to the sam
e
special EventID.
c. Eventually, the event/project manager needs to view all related events
for a particular events, to check if all the 5 TypeCodes have been taken
place, as well as to analyze all their related events.
There is no hierachical or sequencial concept when grouping. Is there a
better way to implement this scenario? Regardless, I am still interested in
the GroupID concept here, can you elaborate?
3. Yes, the specialEventID derives from EventID.
"CBretana" wrote:
> 4. <snip>I can see one possible problem here, for reporting, each time we
> need to a special event with standard/common event attributes, we will nee
d
> to join the Event table</snip> Yes, so what? The data is in the other
> table... that's what relational databases are designed to do. The only
> alternative is to put all the data into the Events Table. If you had
> Employees Table, with 80k rows, and four extra really long data attributes
> you needed to store for the 20 employees in the legal Department, would yo
u
> a) add those four attributes to all 80,000 employees,
> b) Move 20 legal employees into a separate table that had the extra four
> attributes,
> (This means to get, or search, or Update against ALL EMployees you
> have to UNion both tables together.),
> c) Create a separate table for Just those four attributes, and leave
> regular employee data in the main table.
> ANSWER C.
> Also, if determining whether an event is "Special" is a common task,
> INDEPENDANT of accessing the Special Data, then add a flag to Events Tabl
e
> to indicate whether the row is a Special Event... You could name it it
> "IsSpecial" and type it as your favorite boolean type - (You can use Char(
1)
> with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0
).
> THis is a denormalization, (it is redundant, since presence of row with sa
me
> eventID in SpecialEvents table is same datum), but it will allow you to
> determine "SpecialNess" without the Join.
> "C TO" wrote:
>|||Having said in #2c in my previous response, the fact I removed TypeCode from
the PK, I would have to create a different EventID for a related
specialEvent, which is preferable and good. It is prefereable because that i
s
consistent with the convention. It is good because another TypeCode event is
another event, not the same event. Because of that, however, if I want to be
able to track the changes in the event, for example, today two tasks have
been added for that event, and because they are for the same place, reason
(TypeCode), by the same people, I do not want to add new event, then I need
to have task table?
Create Table Task
(EventID Integer Not Null,
TaskID Integer Not Null,
StartDate DateTime Null,
EndDate DateTime Null
Primary Key (EventID, TaskID)
)
Good thing about doing this also includes SpecialEventHistory table may be
avoided. Bad thing, for example may be each time I need know the status of
the event with a specific TypeCode, I will need to, again, join this table.
How would you approch this?
Please note that I do not have specific requirements other than I mentioned.
What we design now should cover for most, if not almost all, possible
scenarios.
"CBretana" wrote:
> 4. <snip>I can see one possible problem here, for reporting, each time we
> need to a special event with standard/common event attributes, we will nee
d
> to join the Event table</snip> Yes, so what? The data is in the other
> table... that's what relational databases are designed to do. The only
> alternative is to put all the data into the Events Table. If you had
> Employees Table, with 80k rows, and four extra really long data attributes
> you needed to store for the 20 employees in the legal Department, would yo
u
> a) add those four attributes to all 80,000 employees,
> b) Move 20 legal employees into a separate table that had the extra four
> attributes,
> (This means to get, or search, or Update against ALL EMployees you
> have to UNion both tables together.),
> c) Create a separate table for Just those four attributes, and leave
> regular employee data in the main table.
> ANSWER C.
> Also, if determining whether an event is "Special" is a common task,
> INDEPENDANT of accessing the Special Data, then add a flag to Events Tabl
e
> to indicate whether the row is a Special Event... You could name it it
> "IsSpecial" and type it as your favorite boolean type - (You can use Char(
1)
> with values of Y/N or T/F or bit datatypes, I use TInyInts and values 1/0
).
> THis is a denormalization, (it is redundant, since presence of row with sa
me
> eventID in SpecialEvents table is same datum), but it will allow you to
> determine "SpecialNess" without the Join.
> "C TO" wrote:
>|||When you "Add" A related event to a special Event, that insert should
initially be handled exactly as any other new event... It should go into
regular Events Table, and then, if it's ALSO a Special Event, it should also
go in the SpecialEvents Table. Then and only then woud it go into the
EventAssociations Table, which stores Relations between events.
The concept of GroupID, Name it something Else like "RelationID" i just a
way t oassociate all the events that are related t oone another, but allow
events to be related in overlapping groups...
Say you had the following
GroupID EventID
1 1
1 3
1 5
2 1
2 2
2 5
3 2
3 5
3 7
Then Event 1 is related to Event 3, and Event 5 through one "relationship",
(Group 1)
and to Events 2 & 5 through another overlapping relationship...
If the relationships cannot overlap, then you would need a different
structure.
"C TO" wrote:
> Having said in #2c in my previous response, the fact I removed TypeCode fr
om
> the PK, I would have to create a different EventID for a related
> specialEvent, which is preferable and good. It is prefereable because that
is
> consistent with the convention. It is good because another TypeCode event
is
> another event, not the same event. Because of that, however, if I want to
be
> able to track the changes in the event, for example, today two tasks have
> been added for that event, and because they are for the same place, reason
> (TypeCode), by the same people, I do not want to add new event, then I nee
d
> to have task table?
> Create Table Task
> (EventID Integer Not Null,
> TaskID Integer Not Null,
> StartDate DateTime Null,
> EndDate DateTime Null
> Primary Key (EventID, TaskID)
>
> )
> Good thing about doing this also includes SpecialEventHistory table may be
> avoided. Bad thing, for example may be each time I need know the status of
> the event with a specific TypeCode, I will need to, again, join this table
.
> How would you approch this?
> Please note that I do not have specific requirements other than I mentione
d.
> What we design now should cover for most, if not almost all, possible
> scenarios.
> "CBretana" wrote:
>|||OK, let do a select illustration here:
GroupID EventID
1 1
1 3
1 5
2 1
2 2
2 5
3 2
3 5
3 7
Select EventID from EventAssociation E
where Exists ( Select 1 from EventAssociation G
Where E.GroupID = G.GroupID and EventID = 2)
Returns:
EventID
--
1
2
5
7
To get the same result, without a groupID, I have to do the following:
EventID RelatedEventID
1 3
1 2
2 5
3 5
2 7
5 7
3 1 -- this insert may or may not happen
Select Case when EventID = 2 then RelatedEventID
when RelatedEventID = 2 then EventID
end as EventID
from EventAssociation G
Where 2 in (EventID, RelatedEventID)
What would be the punishment? Obviously, I must have not seen the benefits
of GroupID here.
"CBretana" wrote:
> When you "Add" A related event to a special Event, that insert should
> initially be handled exactly as any other new event... It should go into
> regular Events Table, and then, if it's ALSO a Special Event, it should al
so
> go in the SpecialEvents Table. Then and only then woud it go into the
> EventAssociations Table, which stores Relations between events.
> The concept of GroupID, Name it something Else like "RelationID" i just a
> way t oassociate all the events that are related t oone another, but allow
> events to be related in overlapping groups...
> Say you had the following
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Then Event 1 is related to Event 3, and Event 5 through one "relationship"
,
> (Group 1)
> and to Events 2 & 5 through another overlapping relationship...
> If the relationships cannot overlap, then you would need a different
> structure.
>
> "C TO" wrote:
>|||No, to get related Events with GroupID structure, you just
Select Distinct O.EventID
From EventAssociation E
Join EventAssociation O
On O.GroupID = E.GroupID
Where E.EventID = 2
This is "Cleaner" In that it is asking "Show me all the Events that are in
any of the the Groups Event 2 is in", and it doesn't imply any ordering or o
f
one event to the other... As your design structure does... (EventID,
RelatedEventID implies a directional, binary relationship, not a grouping o
f
2 or more events that are all equally related to one another .
So what I suggested more closely represents a Business model where multiple
events (2 or many) are related to one another with no distinction between
which one is "master" and which ones are "subordinate". Your structure woul
d
be better id business model for a "relationship" which IS directional (in
each pair there is a left/right, or master/subordinate, or etc.) and if the
relationships are all binary (pairs), and there is therefore a distinction
between a direct Relationship and an Indirect one...
EventID RelatedEventID
1 3
3 2
-- here Event 1 is Directly related to Event 3,
and Event 3 is directly related to Event 2,
but Event 1 is only indirectly related to Event 2
I don't know which business model more accurately represents your business,
and without that knowledge I can not tell yo which of these structure would
be more appropriate..
"C TO" wrote:
> OK, let do a select illustration here:
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Select EventID from EventAssociation E
> where Exists ( Select 1 from EventAssociation G
> Where E.GroupID = G.GroupID and EventID = 2)
>
> Returns:
> EventID
> --
> 1
> 2
> 5
> 7
> To get the same result, without a groupID, I have to do the following:
> EventID RelatedEventID
> 1 3
> 1 2
> 2 5
> 3 5
> 2 7
> 5 7
> 3 1 -- this insert may or may not happen
> Select Case when EventID = 2 then RelatedEventID
> when RelatedEventID = 2 then EventID
> end as EventID
> from EventAssociation G
> Where 2 in (EventID, RelatedEventID)
> What would be the punishment? Obviously, I must have not seen the benefits
> of GroupID here.
>
> "CBretana" wrote:
>|||Your structure would be appropriate for relationships among people in a
Table, where you want to model Teacher-Student Relationships. It is:
1. Binary,
2. Drectional, (Just because I am your teacher, doesn't mean you are my
teacher), If we were each other's Teacher, we would put two records in the
table, one for [me to you], and one for [you to me]...
3. And there are direct and indirect relationships... If Bob is Mary's
Teacher, and Dave is Bob's Teacher, then Dave is only indirectly Teacher to
Mary...
"C TO" wrote:
> OK, let do a select illustration here:
> GroupID EventID
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 5
> 3 2
> 3 5
> 3 7
> Select EventID from EventAssociation E
> where Exists ( Select 1 from EventAssociation G
> Where E.GroupID = G.GroupID and EventID = 2)
>
> Returns:
> EventID
> --
> 1
> 2
> 5
> 7
> To get the same result, without a groupID, I have to do the following:
> EventID RelatedEventID
> 1 3
> 1 2
> 2 5
> 3 5
> 2 7
> 5 7
> 3 1 -- this insert may or may not happen
> Select Case when EventID = 2 then RelatedEventID
> when RelatedEventID = 2 then EventID
> end as EventID
> from EventAssociation G
> Where 2 in (EventID, RelatedEventID)
> What would be the punishment? Obviously, I must have not seen the benefits
> of GroupID here.
>
> "CBretana" wrote:
>|||Thank you. The relationship in this model is not binary, but it is true for
(2) and (3). I am learning toward using GroupID now but the only thing left
is that it does not provide indirect relationship...
Perhaps indirect relationship with GroupID can be achived this way:
From the prev example of EventID 2, to related any direct relationship with
EventID 2, we get 1,5,7. If we are looking for one level up, anything that
has a relationship to EventID 2's siblings (1,5,7), we would look through
Groups that EventID 2 are in, right?
"CBretana" wrote:
> Your structure would be appropriate for relationships among people in a
> Table, where you want to model Teacher-Student Relationships. It is:
> 1. Binary,
> 2. Drectional, (Just because I am your teacher, doesn't mean you are my
> teacher), If we were each other's Teacher, we would put two records in th
e
> table, one for [me to you], and one for [you to me]...
> 3. And there are direct and indirect relationships... If Bob is Mary's
> Teacher, and Dave is Bob's Teacher, then Dave is only indirectly Teacher
to
> Mary...
> "C TO" wrote:
>

Data Modeling Question

I'm designing a database for a medical group that must keep track of various
"People"
Some are doctors and some are patients.
The client currently categorizes patients according to the type of
procedure(s) they have been seen for (e..g, "Jane is a Botox patient because
she had Botox injections" while "Ralph is a hair transplant patient because
he's had hair transplants." And on and on it goes). These procedures are
obviously not mutually exclusive given that any given patient can have more
than one type of procedure.
As I see the situation we have [Patients] and [Procedures]. We do NOT have
[patient types] even though that's how the client understands them. We just
have patients who have various procedures.
My whiz bang plan is to simply have a many-to-many relationship between
[Patients] and [Procedures].
This will work fine for identifying the so called "patient types"... just
SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
"the Botox patients".
Question 1: What would be a good way to classify a patient who has not yet
had any procedure? Say Bambi comes in and gets scheduled for Botox. The
doctors would want her to show up on reports as a "Botox patient" even
though she hasn't yet had the procedure.
Question 2: Given that [Doctors] and [Patients] are fundamentally different
"things" in this database, is it reasonable to have two tables - one for
Doctors and another for Patients... or is it recommended to have one table
("People") and then have some "PersonType" column that flags the person as a
doctor or a patient (and then have a bunch of NULLS for columns not relevant
to each row's designated "person type"). The one-table approach seems kind
of ugly. Just wanted some feedback on this before I go off and implement.
Thank you for your time and consideration.
-JThis has similarities to the database I work with, which is hr/payroll
data. There is a table of 'positions' (job titles a person can have).
You may have an equivalent 'procedures' table. Procedures table would
likely have budget/costs associated with the procedure.
Another table would have the procedure history for a person. A person
could have multiple records in that table, each would have a key for
the person, the procedure name, the procedure key (for joining to
'procedures' table). This table would have a startdate and enddate for
the procedure. A person who is scheduled, but has not yet had the
procedure merely has a futuredated record in this table (based on
startdate). When the procedure is done, you give the record an
enddate.
Doctors and patients all belong in the same table b/c a doctor could be
a patient and vice versa. Each person has their own unique id and also
a second field which is the id of that persons PCP. So if you have:
name, uniqueid, PCPid
dr smith, 1, 0
dr jones, 2, 0
sick guy,3,1
dr williams,4,2
This means that sick guy goes to dr. smith. Dr williams goes to Dr.
Jones.
You likely WILL need a flag field that lets you clearly determine who
is a doc ('flagdoc' that has y/n or 1/0 for everyone).
Based on similar relationships, this is how my company does it.
Theoretically, maybe you don't have to put the procedure name in the
procedure history, but it's nice having it there.
HTH,
wayne|||>> is it reasonable to have two tables - one for Doctors and another for Pat
ients... or is it recommended to have one table ("People") <<
Doctors and patients are logically different, so I would scrape the
idea of a general "Peoiple" table. Where is the "Treatments" table
that would show the dates (scheduled, actual, etc.), location,
doctor(s), etc. for Bambi's Botox?
As a patient. The assorted procedures done to them are events and not
attributes of the patient himself.|||> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
I would suggest you document people throughout their lifecycle with you. So
when the patient comes in, planning to get Botox, a row is created in the
Patients and PatientProcedures table. Another table would be related to the
patientProcedures table that would document the status of the relationship.
Planned, Scheduled, Occurred, FollowUp, OopsPatientLooksLikeJoanRivers and
so on (I will assume you are with the jokes since you started it out
with "Bambi" "). Then you have the best of both scenarios.

> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
Tough call. I would would not suggest the one table approach, but a table
for generic "people" attributes, and another for patient attributes. I
wouldn't have a PersonType in this case because a person could be both (the
key of the two subordinate tables would be the same as for the Person table
so a person could only be mapped once.) The existance of a row in the
patient table would indicate that the person is a patient. (Will you have
nurses, sleep makers (can't spell anesthesiologist) and such. Particularly
for billing and/or scheduling I would imagine.)
Now you have everything you need (I think) you can tell the type of patient
immediately, including their status "Planned" "Botox", "Scheduled" "Hair
Transplant" and after > 1 procedures takes place: "Planned" "Repeat"
"Botox". Then they can get specific about the types of patient that they
are looking at.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jordan R." <A@.B.COM> wrote in message
news:ux0URV4LGHA.3100@.tk2msftngp13.phx.gbl...
> I'm designing a database for a medical group that must keep track of
> various "People"
> Some are doctors and some are patients.
> The client currently categorizes patients according to the type of
> procedure(s) they have been seen for (e..g, "Jane is a Botox patient
> because she had Botox injections" while "Ralph is a hair transplant
> patient because he's had hair transplants." And on and on it goes). These
> procedures are obviously not mutually exclusive given that any given
> patient can have more than one type of procedure.
> As I see the situation we have [Patients] and [Procedures]. We do NOT have
> [patient types] even though that's how the client understands them. We
> just have patients who have various procedures.
> My whiz bang plan is to simply have a many-to-many relationship between
> [Patients] and [Procedures].
> This will work fine for identifying the so called "patient types"... just
> SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
> "the Botox patients".
> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
> Thank you for your time and consideration.
> -J
>|||wouldn't it be pretty common for a doctor to also be a patient of
his/her own group practice'
The futuredated record mentioned above could be a bit dangerous b/c
people will definitely back out on things. Our place has a whole
module for 'applicants'. If they are hired, then they get records for
jobs, etc. The data model for people who say they 'want to do something
in the future' could be pretty complex...|||The more I ponder, the one table layout works well when the patient
only goes to one doctor and does not switch too often. We use the
above layout for employees and their dependents (which is a nice,
static relationship).
It does sound like the patients at your place can have numerous doctors
work on them over the course of time. Your db revolves around the
procedure--which can have one patient, one or two docs. Splitting out
may well be the best way to go.|||>> wouldn't it be pretty common for a doctor to also be a patient of his/her
own group practice? <<
No, not in the US; insurnace companies would go nuts. Can you say
"FRAUD!!"?
So we need both an actuial and schedule appointment date. Sounds like
a good source for stats and predictions!|||Louis Davidson wrote:
> Tough call. I would would not suggest the one table approach, but a table
> for generic "people" attributes, and another for patient attributes.
Which country? The generic "people" approach seems to be the one taken
by the UK's National Health Service (the world's largest?) In the
interest of standards, they have published their data dictionary:
http://www.nhsia.nhs.uk/datastandar...
.asp?shownav=1
Jamie.|||Yes, but a person in one country is still a person in another. I would
suggest that whatever this person needs would be the best approach. At a
minimum First Name, Last Name, mailing address, etc, perhaps some form of Id
Number, perhaps. The basics.
Some of these things in their list would be very offensive to Americans.
For some reason we will give up our "tax" number (social security number,
which is becoming too much of a citizen id number)
Then the patient would have a file number, perhaps if all records are stored
in a paper format, medical information, etc. This table would be related to
the appointment calendar, billing.
Then doctors information, abilities, schedule, etc.
By no means is this a required way to do it. Having two tables with some
minor overlap of information is not horrible when the two concepts are going
to have little interaction (for example, if you had to balance a doctor's
appointment schedule as a patient AND a doctor, this would be essential.)
Frankly if the only overlapping information in a medical system is that a
doctor is entered as a patient of another doctor AND a doctor of patients,
the world would rejoice at not having to explain why they want to see the
doctor 10 times.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1139819374.327665.281610@.f14g2000cwb.googlegroups.com...
> Louis Davidson wrote:
> Which country? The generic "people" approach seems to be the one taken
> by the UK's National Health Service (the world's largest?) In the
> interest of standards, they have published their data dictionary:
> http://www.nhsia.nhs.uk/datastandar...t.asp?shownav=1
> Jamie.
> --
>|||Louis Davidson wrote:
> Some of these things in their list would be very offensive to Americans.
> For some reason we will give up our "tax" number (social security number,
> which is becoming too much of a citizen id number)
That's why I opened with, 'Which country?" :) If the OP (or other
interested reader) is in the UK then choosing to follow the NHS model
is one way of resolving the quandary.
FWIW here we seem to be moving in the opposite direction e.g. identity
cards for all :(
Jamie

Data Model Question

I posted to programming but thought maybe this is a btter group:
There may be some tools out there already developed that can handle this but
I haven't had success in finding them.
Essentially what I am trying to do is create a data model that can
accomodate the creation of a variety of forms to be filled out.
The end user should be able to create a question and select what type of
answer it will bne, multi select, checkbox, radio box, free text. Provide
the possible answers if needed. And then allow the form to be filled out
and the responses saved.
That part shouldn't be too tough. Forms may need to be able to be related
to other forms or multiple forms could make up 1 form to fill out. Like
form A and D.
If there is something like this already I don't want to reinvent the wheel.
Also if someone has experience with this type of model has it been good or
bad.
Thanks.You had one response. Was that not helpful? There are also some posts from
Janmar on a similar topic. Did you read those posts?|||A response in this group or the programming one? I didn't see any.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ucBrV62lFHA.3300@.TK2MSFTNGP15.phx.gbl...
> You had one response. Was that not helpful? There are also some posts
> from
> Janmar on a similar topic. Did you read those posts?
>

data manipulation group by problem

Hi I have a data manipulation query. I have the table below which gets
populated on a daily basis
CREATE TABLE [dbo].[FreeSpace] (
[Drive] [char] (1) not null ,
[MB_Free] [int] not null ,
[day_time] [datetime] default getdate()NOT NULL
) ON [PRIMARY]
insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
populates table
I run the query included below but the data comes out as example below
drive monday tuesday etc....
c 100mb null
c null 100mb
d 200mb null
d null 200mb
I would like to display like this
drive monday tuesday etc....
c 100mb 100mb
D 200mb 200mb
My query below
SELECT Drive,
case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Monday,
case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Tuesday,
case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Wednesday,
case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Thursday,
case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Friday,
case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Saturday,
case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' MB
Free Space'
end as Sunday
from FreeSpace
group by Drive,datepart(dd, day_time),MB_Free
order by drive
thanks for any help
SammySammy
See if this helps you
SELECT Drive,
MAX(case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end) as Monday,
MAX(case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
MB
Free Space'
end )as Tuesday
......
from FreeSpace
group by Drive
order by drive
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:E4C10338-8829-42B6-A3B9-F87B1224EFF9@.microsoft.com...
> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +'
> MB
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy|||Thanks Uri
"Sammy" wrote:

> Hi I have a data manipulation query. I have the table below which gets
> populated on a daily basis
> CREATE TABLE [dbo].[FreeSpace] (
> [Drive] [char] (1) not null ,
> [MB_Free] [int] not null ,
> [day_time] [datetime] default getdate()NOT NULL
> ) ON [PRIMARY]
> insert into FreeSpace(Drive,MB_Free) exec master..xp_fixeddrives--this
> populates table
> I run the query included below but the data comes out as example below
> drive monday tuesday etc....
> c 100mb null
> c null 100mb
> d 200mb null
> d null 200mb
> I would like to display like this
> drive monday tuesday etc....
> c 100mb 100mb
> D 200mb 200mb
> My query below
> SELECT Drive,
> case datepart(dd, day_time) when 1 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Monday,
> case datepart(dd, day_time) when 2 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Tuesday,
> case datepart(dd, day_time) when 3 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Wednesday,
> case datepart(dd, day_time) when 4 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Thursday,
> case datepart(dd, day_time) when 5 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Friday,
> case datepart(dd, day_time) when 6 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Saturday,
> case datepart(dd, day_time) when 7 then cast(mb_free as varchar (12)) +' M
B
> Free Space'
> end as Sunday
> from FreeSpace
> group by Drive,datepart(dd, day_time),MB_Free
> order by drive
> thanks for any help
> Sammy

Data in Page Header...

Group,
I would like to have a value (basically a scalar from a stored proc) in my
header but I keep getting the compile error:
'The value expression for the textbox 'LastSale' refers to a field. Fields
cannot be used in page headers or footers.'
I tried putting a subreport in the header and that does not work either. It
would be very limiting to not allow database data in the header of a report!
(Very easy to do in Crystal Reports)
Also keep in mind that I do not want my header in the 'body' region.
Thanks everyone.Place the field in any of the textbox(say textbox10) in body section
Then use =ReportItems!textbox10.value in pageheader section
Ponnurangam
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:en7UJiFwEHA.728@.TK2MSFTNGP11.phx.gbl...
> Group,
> I would like to have a value (basically a scalar from a stored proc) in my
> header but I keep getting the compile error:
> 'The value expression for the textbox 'LastSale' refers to a field.
> Fields cannot be used in page headers or footers.'
> I tried putting a subreport in the header and that does not work either.
> It would be very limiting to not allow database data in the header of a
> report! (Very easy to do in Crystal Reports)
> Also keep in mind that I do not want my header in the 'body' region.
> Thanks everyone.
>|||Hi, Ponnurangam..
This way only can show the field value in first page,
the second page, cause the textbox doesn't exist,
so the value of reportitems become null...
I want the value of reportitems can keep in whole report,
no mattter report has how many pages!
Any idea about this?
Thanks!
Angi
"Ponnurangam" <ponnurangam@.trellisys.net> ¼¶¼g©ó¶l¥ó·s»D
:eTA7VcKwEHA.2908@.tk2msftngp13.phx.gbl...
> Place the field in any of the textbox(say textbox10) in body section
> Then use =ReportItems!textbox10.value in pageheader section
> Ponnurangam
> "Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
> news:en7UJiFwEHA.728@.TK2MSFTNGP11.phx.gbl...
> > Group,
> > I would like to have a value (basically a scalar from a stored proc) in
my
> > header but I keep getting the compile error:
> >
> > 'The value expression for the textbox 'LastSale' refers to a field.
> > Fields cannot be used in page headers or footers.'
> >
> > I tried putting a subreport in the header and that does not work either.
> > It would be very limiting to not allow database data in the header of a
> > report! (Very easy to do in Crystal Reports)
> > Also keep in mind that I do not want my header in the 'body' region.
> >
> > Thanks everyone.
> >
> >
>|||Hi,
(1) You can have a textbox that exists for all pages(by making this textbox
visible false) or
(2) Try using Parameters
Hope this Helps
Ponnurangam
"angi" <angi@.microsoft.public.sqlserver.olap> wrote in message
news:um99qaLwEHA.4048@.TK2MSFTNGP15.phx.gbl...
> Hi, Ponnurangam..
> This way only can show the field value in first page,
> the second page, cause the textbox doesn't exist,
> so the value of reportitems become null...
> I want the value of reportitems can keep in whole report,
> no mattter report has how many pages!
> Any idea about this?
> Thanks!
> Angi
> "Ponnurangam" <ponnurangam@.trellisys.net> ¼¶¼g©ó¶l¥ó·s»D
> :eTA7VcKwEHA.2908@.tk2msftngp13.phx.gbl...
>> Place the field in any of the textbox(say textbox10) in body section
>> Then use =ReportItems!textbox10.value in pageheader section
>> Ponnurangam
>> "Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
>> news:en7UJiFwEHA.728@.TK2MSFTNGP11.phx.gbl...
>> > Group,
>> > I would like to have a value (basically a scalar from a stored proc) in
> my
>> > header but I keep getting the compile error:
>> >
>> > 'The value expression for the textbox 'LastSale' refers to a field.
>> > Fields cannot be used in page headers or footers.'
>> >
>> > I tried putting a subreport in the header and that does not work
>> > either.
>> > It would be very limiting to not allow database data in the header of a
>> > report! (Very easy to do in Crystal Reports)
>> > Also keep in mind that I do not want my header in the 'body' region.
>> >
>> > Thanks everyone.
>> >
>> >
>>
>

Data in a group loops and repeats

I have a report with some subreports. The report is grouped by customer. For some reason one customer is showing the same data twice. Its only happening to this one customer and everyone else is normal. In the left column with all the groups, its only shown once, so it wouldnt be a problem with the database. What can I look for to help solve this?
ThanksI have a report with some subreports. The report is grouped by customer. For some reason one customer is showing the same data twice. Its only happening to this one customer and everyone else is normal. In the left column with all the groups, its only shown once, so it wouldnt be a problem with the database. What can I look for to help solve this?
Thanks

Hi,

Im having the same prob.. still did not get any answ... but i guess i could help u some reason, coz my issues is bit serious, i m using VS2003 + CR 9
a report with grouping was working ok, suddenly the grouping field satrts repeating (with out doing any changes) all the field values (e.g buyer names), this is not possible coz
there is no way once grouped and the field is placed at the right place and this hppening

May be resons,

1) there is conflicting versions of CR in my machine
2) or im using Dot net 2003, CR Editor to do the report design

and U R Problem, accord.. to the way i got the issue, the solut...

1) if u r repettion getting in the Sub Rpt, did u use the propper grouping for the Sub Rpt as well

If possible Send me a Copy of the Rpt to get an idea of it (if u dont mind)

Sorry for inadequate dirrect answer, at least i tried
will get back to u when i get the solut...|||I updated to CR11 Release 2 and seems to have gone back to normal

Data Header on every page

I have a report grouped by accounts. Can I generate something like this:-
Joe Blogs Inc... [Header]
--
Client: Microsoft Group Page 1/5
Total Page 1/25
Account No:- 123456 Address :- 123 Infinate Loop
Account Name : MSFT Location :- Competition Headquarters
Name | Date | Item | Code | Desc | Qty | Price
MSFT 6/7/05 123 3456 Netscape 1 $2
________________________________________________ [5 pages later]
Client :- GOOGLE Group Page 1/20
Total Page 6/25
Account No:- 876543 Address :- 678 Google Valley
Account Name : GOOG Location :- Googlville
Name | Date | Item | Code | Desc | Qty | Price
GOOG 6/7/05 123 3456 Netscape 1 $2
--
Page 6 - 25 [Footer]
Above shows 2 groups, each group has a small synopsis before the detail data
begins. I have been racking my brains trying to get RS to do just this. I
intend to have 1 dataset which contains all this data(joined in the sql),
however I have no problems having 2 datasets, 1 for Detail and 2 for Synopsis
if it is so requried - I just need directions on how to join the 2.
Hope ya'll can help...It would probably be best to use only one dataset. It looks like you can do
all of this in a single table with groups and merged columns. If not, then
a list with an embedded table.
Paul Turley
"d pak" <dipakb@.exchnage.ml.com> wrote in message
news:414D274C-BB96-42DD-9BD0-B5DE142EC79A@.microsoft.com...
>I have a report grouped by accounts. Can I generate something like this:-
> Joe Blogs Inc... [Header]
> --
> Client: Microsoft Group Page 1/5
> Total Page 1/25
> Account No:- 123456 Address :- 123 Infinate Loop
> Account Name : MSFT Location :- Competition Headquarters
> Name | Date | Item | Code | Desc | Qty | Price
> MSFT 6/7/05 123 3456 Netscape 1 $2
> ________________________________________________ [5 pages later]
> Client :- GOOGLE Group Page 1/20
> Total Page 6/25
> Account No:- 876543 Address :- 678 Google Valley
> Account Name : GOOG Location :- Googlville
> Name | Date | Item | Code | Desc | Qty | Price
> GOOG 6/7/05 123 3456 Netscape 1 $2
> --
> Page 6 - 25 [Footer]
> Above shows 2 groups, each group has a small synopsis before the detail
> data
> begins. I have been racking my brains trying to get RS to do just this. I
> intend to have 1 dataset which contains all this data(joined in the sql),
> however I have no problems having 2 datasets, 1 for Detail and 2 for
> Synopsis
> if it is so requried - I just need directions on how to join the 2.
> Hope ya'll can help...

Data Grouping

hi,

I want to group data in matrix column. Lets say i have a field say Weekday which has weekdays from monday to friday. then suppose i have measure "my expenditure".

I will place Weekdays in column field of matrix and "my expenditure" in data field". lets not worry about rows.

Now i want something like this.

Group my expenditure in three categories like

1. My expenditure on monday

2. My expenditure on tuesday

3. My expenditure on days other than monday and tuesday.(means it should show me data for wednesday,thursday,friday and also if no weekday is entered)

what I am doing now is i m writting IIF expression in the column field but with that i get data for monday and tuesday but data for all the other days is not getting clubbed.

how can i do this?

Thanks

rohit

Is my problem not clear?

Please anybody help!!

Thanks & regards

|||

Instead of using a matrix table top create your report, you could use a query to pivot your data.

(SUM(CASE WHEN DAY = 'MONDAY' THEN EXPENDITURE ELSE 0 END)) AS [MON EXP],

(SUM(CASE WHEN DAY = 'TUESDAY' THEN EXPENDITURE ELSE 0 END)) AS [TUES EXP],

(SUM(CASE WHEN (DAY <> 'MONDAY' AND DAY <>'TUESDAY') THEN EXPENDITURE ELSE 0 END)) AS [ROW EXP]

FROM EXPENDITURE_TABLE

Then insert the fields from the datasource into a std table component in a report.