More on merge replication an FK constraints

So, let me relate to my earlier post in this group about merge replication
and foreign keys.
As I've mentioned, I have one publication that pushes 'customers' table to
the subscribers.
I have another publication that pushes 'invoices' and 'invoices_det' tables
to the subscribers. In table invoices there is cust_id field wich is foreign
key to the customers table primary key 'cust_id'. I have created all
publications like so that first table is DROPed and then recreated. I have
'checked' options for creating primary keys and referential integrity.
FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer
table is created on the subscriber, primary key is set, everything is ok.
Then I push INVOICES publication to the subscriber. That's not ok. I don't
have foreign key to customers table in the table invoices.
Enterprise manage wizzard suggests that I include customers table in
INVOICES publication to avoid things like mentioned above. But, I have
problem with that approach. That would mean that I'd have only as many
publications as I have subscribers (one subscriber - one publication). That
means that I'd have to have, for instance, table 'customers' published dozen
times, if I have dozen subscribers.
How do I solve this one?
As far as I see it, i could:
1) drop the foreign key constraints
- I'd hate to do this!
2) set up publication snapshot so it 'delete the data on the subscriber':
This seems ok, but! If I, for any reason, need to break replication,
when setting it back agan, I'll still have data on the subscriber. I then
push CUSTOMERS publication, the snapshot tries to DELETE data from
'customers' table, but, since it's FKeyd to 'invoices' table, it will bring
up an error.
3) set up publication snapshot so it 'leaves the existing table unchanged'.
This is the best bet I could have, as far as I see. I only need to
make sure that the database on the remote is 'fresh and clear'. Wich means,
do the script to do the DDL on the subscriber (with all the constraints!),
and then push the snapshot in 'right' order (meaning, first the customers,
then the invoices, in the above example).
Any help here would be much appreciated.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
For foreign keys can you not set them all to "Not for replication", combine
CUSTOMERS, INVOICES and INVOICES_DET in one publication?
Jim.
"Mario Splivalo" wrote:

> So, let me relate to my earlier post in this group about merge replication
> and foreign keys.
> As I've mentioned, I have one publication that pushes 'customers' table to
> the subscribers.
> I have another publication that pushes 'invoices' and 'invoices_det' tables
> to the subscribers. In table invoices there is cust_id field wich is foreign
> key to the customers table primary key 'cust_id'. I have created all
> publications like so that first table is DROPed and then recreated. I have
> 'checked' options for creating primary keys and referential integrity.
> FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer
> table is created on the subscriber, primary key is set, everything is ok.
> Then I push INVOICES publication to the subscriber. That's not ok. I don't
> have foreign key to customers table in the table invoices.
> Enterprise manage wizzard suggests that I include customers table in
> INVOICES publication to avoid things like mentioned above. But, I have
> problem with that approach. That would mean that I'd have only as many
> publications as I have subscribers (one subscriber - one publication). That
> means that I'd have to have, for instance, table 'customers' published dozen
> times, if I have dozen subscribers.
> How do I solve this one?
> As far as I see it, i could:
> 1) drop the foreign key constraints
> - I'd hate to do this!
> 2) set up publication snapshot so it 'delete the data on the subscriber':
> This seems ok, but! If I, for any reason, need to break replication,
> when setting it back agan, I'll still have data on the subscriber. I then
> push CUSTOMERS publication, the snapshot tries to DELETE data from
> 'customers' table, but, since it's FKeyd to 'invoices' table, it will bring
> up an error.
> 3) set up publication snapshot so it 'leaves the existing table unchanged'.
> This is the best bet I could have, as far as I see. I only need to
> make sure that the database on the remote is 'fresh and clear'. Wich means,
> do the script to do the DDL on the subscriber (with all the constraints!),
> and then push the snapshot in 'right' order (meaning, first the customers,
> then the invoices, in the above example).
> Any help here would be much appreciated.
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> For foreign keys can you not set them all to "Not for replication", combine
> CUSTOMERS, INVOICES and INVOICES_DET in one publication?
I use 'NOT FOR REPLICATION' for foreign keys.
Combining all those into one publication now seems like a good thing. But, I
have dozen of publication of wich 8 are non-filtered publications. Customers
is an example of such publication. I have 30 stores around the country, and
I need to be able for all the stores to enter new customers, and that new
customer should be visible in all locations. Invoices, for instance, is
exmaple of filtered publication (i have 4 like those), only the cetnral
database (the publisher) needs to see all the invoices (for the reporting
purposes, and financial stuff). Other subscribers don't see other invoices.
Same goes for inventory lists, some other financial stuff I don't know
english names, and so on.
So, your approach leaves me with ONE publication for each subscriber! I
don't like that approach, it just creates mess. I'd like if someone could
tell me now 'stop, you're wrong here'.
My database well normalized (is that the term when you reference data trough
foreign keys?), for instance, in invoices I have payement methods (cash,
credit card,...) wich is FK to the table wich holds payement methods. That
table is updated ONLY at the publisher. Another example is stocks. Each
invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
Then tax rates. Then a lot of other things. All those tables are updated at
the publisher only, so I created snapshot replication to push that data to
subscribers. You suggest I put all that tables into merge publication, just
because they're referenced by FK from tables invoices and such? What is
wrong with my understanding of replication if I belive that is not the way?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||If you have a set of tables that rely on each other using foreign keys and
they are likely to be updated together then they should all be in the same
publication.
As I understand it, you have them separated and are getting foreign key
errors because items are not present when you need them.
You may have very good reasons for splitting them that I am not aware of but
I see no other solution to your problem.
Jim.
"Mario Splivalo" wrote:

> On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I use 'NOT FOR REPLICATION' for foreign keys.
> Combining all those into one publication now seems like a good thing. But, I
> have dozen of publication of wich 8 are non-filtered publications. Customers
> is an example of such publication. I have 30 stores around the country, and
> I need to be able for all the stores to enter new customers, and that new
> customer should be visible in all locations. Invoices, for instance, is
> exmaple of filtered publication (i have 4 like those), only the cetnral
> database (the publisher) needs to see all the invoices (for the reporting
> purposes, and financial stuff). Other subscribers don't see other invoices.
> Same goes for inventory lists, some other financial stuff I don't know
> english names, and so on.
> So, your approach leaves me with ONE publication for each subscriber! I
> don't like that approach, it just creates mess. I'd like if someone could
> tell me now 'stop, you're wrong here'.
> My database well normalized (is that the term when you reference data trough
> foreign keys?), for instance, in invoices I have payement methods (cash,
> credit card,...) wich is FK to the table wich holds payement methods. That
> table is updated ONLY at the publisher. Another example is stocks. Each
> invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
> Then tax rates. Then a lot of other things. All those tables are updated at
> the publisher only, so I created snapshot replication to push that data to
> subscribers. You suggest I put all that tables into merge publication, just
> because they're referenced by FK from tables invoices and such? What is
> wrong with my understanding of replication if I belive that is not the way?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> If you have a set of tables that rely on each other using foreign keys and
> they are likely to be updated together then they should all be in the same
> publication.
Yes. That goes for master/detail tables. But, what if I have a table with,
let's say, tax rates, and in invocie table I have reference via foreign key
to the tax rates table? I'm publishing tax rates table (along with others)
via snapshot publication. So, why would I include those in merge
publication? And tables in merge publication still reference those fields in
tax rates table. So, when deploying initial snapshot the invoices table
WON'T have FK constraint to tax rates table. How do I deal with that?

> As I understand it, you have them separated and are getting foreign key
> errors because items are not present when you need them.
Initial snapshot won't create foreign key constraints for tables that are
not in that publication. That is my problem.

> You may have very good reasons for splitting them that I am not aware of but
> I see no other solution to your problem.
Do you have any reference on the web where I could read more about this? I
see no point in puting all the database tables in one publication just
because almost all tables are FKey with each other. Or, to put it more
simple, how do I establish snapshot and merge publication so that tables in
merge publication have foreign keys to tables in snapshot publication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Yes, what you are doing is logical ok - I just don't know of a way to prevent
FK errors when you split things like that.
For me, even if the table rarely changes, I include everything in a
publication. The only price I pay for that is a larger initial snapshot.
The benefit I get is simplicity and easier administration.
|||On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> Yes, what you are doing is logical ok - I just don't know of a way to prevent
> FK errors when you split things like that.
> For me, even if the table rarely changes, I include everything in a
> publication. The only price I pay for that is a larger initial snapshot.
> The benefit I get is simplicity and easier administration.
So, you'd generaly be ok with this: put rarely changed tables as articles
into many publications, although you could have only one snapshot
publication for those?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||I try to keep it one publication, one snapshot. I then have many subscribers
- it is very easy to administer.
"Mario Splivalo" wrote:

> On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> So, you'd generaly be ok with this: put rarely changed tables as articles
> into many publications, although you could have only one snapshot
> publication for those?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-08, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I try to keep it one publication, one snapshot. I then have many subscribers
> - it is very easy to administer.
>
Thnx. I'll see with what i'll come up at the end, and post here...
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Mario,
I'm looking for the same answer as you, I have basically the same problem
with inicial snapshot with transactional replication, as you, I have also
Unchecked the enforce for replication in the relationship's dialog for the
foreign keys.
If I have some answers or more info I will post it here...
Adrian Parra.
"Mario Splivalo" wrote:

> On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I use 'NOT FOR REPLICATION' for foreign keys.
> Combining all those into one publication now seems like a good thing. But, I
> have dozen of publication of wich 8 are non-filtered publications. Customers
> is an example of such publication. I have 30 stores around the country, and
> I need to be able for all the stores to enter new customers, and that new
> customer should be visible in all locations. Invoices, for instance, is
> exmaple of filtered publication (i have 4 like those), only the cetnral
> database (the publisher) needs to see all the invoices (for the reporting
> purposes, and financial stuff). Other subscribers don't see other invoices.
> Same goes for inventory lists, some other financial stuff I don't know
> english names, and so on.
> So, your approach leaves me with ONE publication for each subscriber! I
> don't like that approach, it just creates mess. I'd like if someone could
> tell me now 'stop, you're wrong here'.
> My database well normalized (is that the term when you reference data trough
> foreign keys?), for instance, in invoices I have payement methods (cash,
> credit card,...) wich is FK to the table wich holds payement methods. That
> table is updated ONLY at the publisher. Another example is stocks. Each
> invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
> Then tax rates. Then a lot of other things. All those tables are updated at
> the publisher only, so I created snapshot replication to push that data to
> subscribers. You suggest I put all that tables into merge publication, just
> because they're referenced by FK from tables invoices and such? What is
> wrong with my understanding of replication if I belive that is not the way?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>

more membership in "master.sys.login_token" than "net group"

Hi.

I'm investigating whether I can lock down a feature exclusively to only those who belong to a certain domain group.

From what I understand, the master.sys.login_token view holds information on all the groups I'm a part of. However, I'm looking at the output from

SELECT * FROM master.sys.login_token

and see a number of groups that I can't verify I have access to. I have even run the DOS command "net group <domaingroup> /dom" and don't see myself listed in its output.

Is this normal? Is the output a culmination of everyone logged in at the time?

@.@.version = 9.00.2047.00

It will show your login along with any roles you are a member of, along with any Windows groups you are a member of.

For example, I have a test system with 3 instances of SQL Server installed. Each instance of SQL Server has 3 local Windows groups created. The service account for each instance is the same for all SQL Server services. It is also currently configured as a localadministrator. That account is what I used to login to this instance and it is also a member of the sysadmin role. (Think an XP machine with a single account that I log in with and also run the services under.) Querying this view shows me the Windows account, sysadmin, public, and every Windows group on the machine that this account is a member of. The vast majority of those groups are not principals in this particular instance of SQL Server. (It has me really confused as to why it would show that.)

So, based on this, it is going to show:

1. Your login

2. Any server roles you are a member of

3. Any Windows groups on the machine that your account is a member of (whether or not they are even principals in the instance you are querying)

So, yes, you could use the sys.login_token table to restrict access based on a group membership. I'm not exactly sure what you mean about locking down a feature. But, a more straightforward way for managing the security permissions is to add the necessary group to the SQL Server and grant permissions on the objects you want them to access and then revoke access from everyone else. But, that might not meet your needs such as locking out the feature within an application.

|||

Thanks for your quick response.... I guess what's shocking me more than anything is the fact that sys.login_token is suggesting I'm in groups I don't believe I belong in.

I've run this statement: SELECT name FROM master.sys.login_token

and get these results....

public
sysadmin
DOMAIN\Domain Users
\Everyone
BUILTIN\Users
BUILTIN\Administrators
NT AUTHORITY\NETWORK
NT AUTHORITY\Authenticated Users
DOMAIN\G_Group1
DOMAIN\G_Group2
DOMAIN\G_Group3
DOMAIN\G_Group4
DOMAIN\G_Group5
DOMAIN\G_Group6
NT AUTHORITY\NTLM Authentication

I agree with most of these results... but when I go to a Command Window and type

net group G_Group5 /dom

I am not included in the output.

When I type

net user MyUserName /dom

I don't see G_Group5 in my output.

|||

It turns out DOMAIN\G_Group1 is a member of DOMAIN\G_Group5

net group doesn't post group info, neither "membership" nor "members of."

sql

More help with count and group by

Using these tables
tbFamily
FamilyID | FamilyName
tbChild
ChildID | FamilyID | ChildName | Birthday | etc...
tbVisitLog
VisitID | ChildID | DateTimeIn | etc...
I am trying to get how many children visited once, twice, three times, four
times and five times.
Example return recordset where the total # of visits is 400:
Freq | # of Children
1 200
2 100
3 75
4 23
5 2
Thanks in advance
Sonny
--Try:
select
Visits
, count (*) as Freq
from
(
select
ChildID
, count (*) as Visits
from
tbVisitLog
group by
ChildID
) as x
group by
Visits
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:OFQnQiC2FHA.3180@.TK2MSFTNGP14.phx.gbl...
Using these tables
tbFamily
FamilyID | FamilyName
tbChild
ChildID | FamilyID | ChildName | Birthday | etc...
tbVisitLog
VisitID | ChildID | DateTimeIn | etc...
I am trying to get how many children visited once, twice, three times, four
times and five times.
Example return recordset where the total # of visits is 400:
Freq | # of Children
1 200
2 100
3 75
4 23
5 2
Thanks in advance
Sonny
--|||Sorry Tom, but I think you wrong... Client wants statistics not for single
Child, his interest number of children visited at the SAME time & how many
times this happens.
endorsed by signature
*** Serg Yury ***
"Tom Moreau" <tom@.dont.spam.me.cips.ca> /
: news:O0AmekC2FHA.908@.tk2msftngp13.phx.gbl...
> Try:
> select
> Visits
> , count (*) as Freq
> from
> (
> select
> ChildID
> , count (*) as Visits
> from
> tbVisitLog
> group by
> ChildID
> ) as x
> group by
> Visits
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
>|||Hopefully, he can clarify the spec for us.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c2312$1_1@.isa.dvgd.mps...
> Sorry Tom, but I think you wrong... Client wants statistics not for single
> Child, his interest number of children visited at the SAME time & how many
> times this happens.
> --
> endorsed by signature
> *** Serg Yury ***
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> /
> : news:O0AmekC2FHA.908@.tk2msftngp13.phx.gbl...
>
>|||Ok.
Sonny, do you want to clarify?
endorsed by signature
*** Serg Yury ***
"Tom Moreau" <tom@.dont.spam.me.cips.ca> /
: news:elvAC7C2FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hopefully, he can clarify the spec for us.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
> news:435c2312$1_1@.isa.dvgd.mps...
single
many|||Yes...
I want to know how many children visited once...
And how many children visited twice... who did not visit once...
And how many children visited three times... who did not visit once or
twice...
Sonny
--
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c30cf$1_1@.isa.dvgd.mps...
> Ok.
> Sonny, do you want to clarify?
> --
> endorsed by signature
> *** Serg Yury ***
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> /
> : news:elvAC7C2FHA.1028@.TK2MSFTNGP12.phx.gbl...
> single
> many
>
>|||This didn't return what I need...
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0AmekC2FHA.908@.tk2msftngp13.phx.gbl...
> Try:
> select
> Visits
> , count (*) as Freq
> from
> (
> select
> ChildID
> , count (*) as Visits
> from
> tbVisitLog
> group by
> ChildID
> ) as x
> group by
> Visits
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:OFQnQiC2FHA.3180@.TK2MSFTNGP14.phx.gbl...
> Using these tables
> tbFamily
> FamilyID | FamilyName
> tbChild
> ChildID | FamilyID | ChildName | Birthday | etc...
> tbVisitLog
> VisitID | ChildID | DateTimeIn | etc...
> I am trying to get how many children visited once, twice, three times,
four
> times and five times.
> Example return recordset where the total # of visits is 400:
> Freq | # of Children
> 1 200
> 2 100
> 3 75
> 4 23
> 5 2
> Thanks in advance
> Sonny
> --
>|||Am I going to have to create separate queries for each frequency count...
This:
SELECT COUNT(VisitLogID), COUNT(DISTINCT ChildID)
FROM tbVisitLog
gives me the total visits and the total of unique children visiting.
I would be satisfied with help on how to find out how many children visited
twice.
--
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:uOF5UOE2FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Yes...
> I want to know how many children visited once...
> And how many children visited twice... who did not visit once...
> And how many children visited three times... who did not visit once or
> twice...
> Sonny
> --
> --
> "Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
> news:435c30cf$1_1@.isa.dvgd.mps...
how
>|||Ok. It's clearly expressed...
select Visits as Freq,count(*) as '# of Children'
from
(
select ChildID,count(*) as Visits from tbVisitLog group by ChildID
) v
group by Visits
order by Visits desc
endorsed by signature
*** Serg Yury ***
"Sonny Sablan" <sonny@.sablan.org> / :
news:uNz3emE2FHA.3660@.TK2MSFTNGP15.phx.gbl...
> Am I going to have to create separate queries for each frequency count...
>
> This:
> SELECT COUNT(VisitLogID), COUNT(DISTINCT ChildID)
> FROM tbVisitLog
> gives me the total visits and the total of unique children visiting.
> I would be satisfied with help on how to find out how many children
visited
> twice.
>
> --
> --
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:uOF5UOE2FHA.3256@.TK2MSFTNGP09.phx.gbl...|||Ahh Yes...
That's what I need.
Thank You
Sonny
--
--
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c5bf3$1_1@.isa.dvgd.mps...
> Ok. It's clearly expressed...
> select Visits as Freq,count(*) as '# of Children'
> from
> (
> select ChildID,count(*) as Visits from tbVisitLog group by ChildID
> ) v
> group by Visits
> order by Visits desc
> --
> endorsed by signature
> *** Serg Yury ***
> "Sonny Sablan" <sonny@.sablan.org> / :
> news:uNz3emE2FHA.3660@.TK2MSFTNGP15.phx.gbl...
count...
> visited
>
>sql

More grouping Woes!

I have a result set of consisting of names and addresses. each person at a
particular address (1-n people) gets an identical groupid to group them in a
"Family".
My report lists each column, groupid, name, address, with one row for each
person. I would like for it to group on the groupid, then list each person in
a subgroup sorted by name.
When I set a "group" row on my report and group on the groupid, all i get is
the first row of the family, and the rest of the data is dropped. how do i
get the group to show all the data? I am using RS2005 with VS2005.
thank you in advance for your help!!I ran into a similar problem a little while ago. I had numerical data based
on the # of hours worked and then a description of what was done in those
hours. I grouped on the person and date and then summed the hours they
worked for that span of time so my output looked something like:
John Doe
11/12/2006
Regular OT Description
On-Site 8 0 Worked on
server.
Remote 0 2
However the description for the remote would not print out, even though the
output was =fields!onsitenotes.value & fields!remotenotes.value, because they
were in the same group
What I wound up doing was setting a different group underneath the name/date
for each row of data(on-site, remote) I'm not sure if you will be able to do
exactly that but I basically had to assign some flags based on the work-type
(on-site, remote, etc) in the query and then group on those flags.
So it wound up being something like:
Group1(
John Doe
11/12/2006
Regular OT
Description
Group2 ( On-Site 8 0 Worked
on server.)
Group3 ( Remote 0 2 Helped on
the phone.)
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1

More grouping questions - sum of maximum

I have a table with two groups, unit and domicile. Structure appears as follows:

GH1 - Domicile Group: Bentwood
GH2- Unit Group: 200
Detail: LegID Unit ID Miles TripMiles
1 200 25 150
2 200 75 150
3 200 50 150

Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value)
3 150 150

This works, what breaks is the subtotal for GH1. on the TripMiles Field.
It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.

Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.

I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.

<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>

To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.

In the Domicile Group, I want to sum all of those Unit Values.
if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450>
if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400>
My total for the Domicile would be 250 <not 850>

Thank you for any assistance.

I can't think of a way of doing this but surely in your case the subtotal at the Domicile level for TripMiles column should equal that of the Miles column.

I would cheat and just put Sum(Fields!Miles.Value) in the subtotal for the TripMiles column in the Domicile group footer.

|||

Bah I jinxed myself using a perfect scenario in that mielage column didnt I <sigh>

Yes I agree with you in this particular instance with the data I displayed. TripMiles never change, it is a base amount for the order. I should have added two more rows to show that the two columns do not always match.

Let's pretend that the TripColumn was labeled something else, Like fuel Consumed.

If I could do an aggregate on the Max/min/first of that field I would be set :(

Edit: OK, I found another thread similar to my situation here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106424&SiteID=1

Where it is stated that at the time that post was made, One year ago last week, you could not do an aggregate of an aggregate and gave a work expression that is one I've tried but errors out.

They then stated to 'modifey' the query to insert nulls at the duplicate. So in my example I'd have
150
null
null
null

How do I do that?

|||

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY LegID) WHEN 1 THEN TripMiles ELSE NULL END
FROM YourTable

More grouping questions - sum of maximum

I have a table with two groups, unit and domicile. Structure appears as follows:

GH1 - Domicile Group: Bentwood
GH2- Unit Group: 200
Detail: LegID Unit ID Miles TripMiles
1 200 25 150
2 200 75 150
3 200 50 150

Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value)
3 150 150

This works, what breaks is the subtotal for GH1. on the TripMiles Field.
It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.

Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.

I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.

<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>

To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.

In the Domicile Group, I want to sum all of those Unit Values.
if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450>
if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400>
My total for the Domicile would be 250 <not 850>

Thank you for any assistance.

I can't think of a way of doing this but surely in your case the subtotal at the Domicile level for TripMiles column should equal that of the Miles column.

I would cheat and just put Sum(Fields!Miles.Value) in the subtotal for the TripMiles column in the Domicile group footer.

|||

Bah I jinxed myself using a perfect scenario in that mielage column didnt I <sigh>

Yes I agree with you in this particular instance with the data I displayed. TripMiles never change, it is a base amount for the order. I should have added two more rows to show that the two columns do not always match.

Let's pretend that the TripColumn was labeled something else, Like fuel Consumed.

If I could do an aggregate on the Max/min/first of that field I would be set :(

Edit: OK, I found another thread similar to my situation here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106424&SiteID=1

Where it is stated that at the time that post was made, One year ago last week, you could not do an aggregate of an aggregate and gave a work expression that is one I've tried but errors out.

They then stated to 'modifey' the query to insert nulls at the duplicate. So in my example I'd have
150
null
null
null

How do I do that?

|||

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY LegID) WHEN 1 THEN TripMiles ELSE NULL END
FROM YourTable

|||what other solution, if i'm using SQL 2000? what other method to achived the above reasult?|||

Code Block

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE LegID

WHEN (SELECT MIN(LegID) FROM YourTable)

THEN TripMiles

ELSE NULL

END
FROM YourTable

More complex Sum and Group by a week period

First thanks to Mike and Baie for helping me before.
I have a set of data similar to the below one.
issueID IssueDate
1 1/17/2005
2 1/18/2005
3 1/19/2005
4 1/24/2005
5 1/27/2005
6 2/7/2005
I need to query the set between 2 dates for example,
1/3/2005 and 6/5/2005 and what I need to return is the
sum of issues per 7 day groups starting from 1/3/2005.
Even if there are no issues opened in the 7 days
following the 1/3/2005 date, I need the query to return
0.
I need The query output to look like this:
WStart Count
1/3/2005 0
1/10/2005 0
1/17/2005 3
1/24/2005 2
1/31/2005 1
The reason I need to see this output this way, is that I
need to compare it to a projected set that the managers
estimated per 7 day periods starting from 1/3/2005.
Is this doable as a query? I only have read access to
the database, so I won't be able to create stored
procedures or views.
Please let me know if you have ideas.Having a calendar table (http://www.aspfaq.com/show.asp?id=2519), you can tr
y
something like:
declare @.sd datetime, @.ed datetime
set @.sd = '20050103'
set @.ed = '20050605'
select
min(c.td),
count(t.IssueID)
from
calendar as c
left join
t
on c.dt = t.IssueDate
where
c.dt >= @.sd and c.dt < dateadd(day, 1, @.ed)
group by
datediff(day, c.dt, @.sd) / 7
go
Not tested.
AMB
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||If it's possible, (as your sample data indicates) to NOT have actual data in
some of the date range groups you need to output as aggregate "buckets", the
n
your best bet, if you can, is to create a calendar table, and do an Outer
Join from it to the data table(s)...
But you say you have only read access, so then the only real option is to,
within a batch, create a table variable that contains the range of dates you
want to group on, and join this table variable to the data table(s)
Declare @.Dts Table(Dt DateTime Not Null)
Declare @.DT DateTime Set @.DT = '20050103'
While @.DT < '20050630' Begin
Insert @.DTs (DT) Values(@.DT)
End
Select D.DT, Count(*)
From @.DTs D Left Join DataTable T
On T.IssueDate Between D.DT And DateAdd(w, 1, D.DT)
Group By D.DT
-- Something like that--
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||Just to throw one more option onto the pile...
To generate the dates for the ws that you wish to query for you could use
the following query
select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605'
This will generate start and end dates for the target ws which you could
put in a table variable as CBretana suggests. You can also embed this query
in your original query like so...
select start_dt, count(issueID)
from (select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605') ws
left outer join test1
on issuedate >= start_dt and issuedate < exclusive_end_dt
group by start_dt
This approach is likely only useful for small sets of ws.
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||Danny Mansour wrote:
> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
A table of (nonnegative) integers is often handy.
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
CREATE TABLE N
(
i INT NOT NULL CHECK (i>=0) PRIMARY KEY
)
INSERT INTO N (i)
SELECT Ones.d + 10*Tens.d + 100*Hundreds.d + 1000*Thousands.d +
10000*TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands
CREATE VIEW Calendar (d)
AS
SELECT CAST('19000101' AS DATETIME) + N.i
FROM N
CREATE TABLE Issues
(
issue_id INT NOT NULL PRIMARY KEY,
issue_date DATETIME NOT NULL
)
-- Sample data
INSERT INTO Issues (issue_id, issue_date)
VALUES (1, '20050117')
INSERT INTO Issues (issue_id, issue_date)
VALUES (2, '20050118')
INSERT INTO Issues (issue_id, issue_date)
VALUES (3, '20050119')
INSERT INTO Issues (issue_id, issue_date)
VALUES (4, '20050124')
INSERT INTO Issues (issue_id, issue_date)
VALUES (5, '20050127')
INSERT INTO Issues (issue_id, issue_date)
VALUES (6, '20050207')
-- This is a purely relational solution. If you need to do
-- more general queries, this might be helpful.
-- start_date and end_date is the overall period
-- interval is a number of days
-- period_start and period_end is the period we're tallying where
-- period_start is inclusive and period_end is exclusive
-- issue_tally is the tally for the period
CREATE VIEW IssueTally
(start_date, end_date, interval,
period_start, period_end, issue_tally)
AS
SELECT SD.d, ED.d, L.i,
SD.d + L.i * T.i, SD.d + L.i * (T.i + 1),
COUNT(I.issue_id)
FROM Calendar AS SD -- start date
INNER JOIN
Calendar AS ED -- end date
ON ED.d > SD.d
INNER JOIN
N AS L -- interval length
ON L.i > 0
INNER JOIN
N AS T -- number of intervals
ON T.i <= DATEDIFF(DAY, SD.d, ED.d) / L.i
LEFT OUTER JOIN
Issues AS I
ON I.issue_date >= SD.d + L.i * T.i AND
I.issue_date < SD.d + L.i * (T.i + 1)
GROUP BY SD.d, ED.d, L.i, T.i
SELECT start_date, end_date, interval,
period_start, period_end, issue_tally
FROM IssueTally
WHERE start_date = '20050103' AND
end_date = '20050605' AND
interval = 7
ORDER BY period_start
JAG|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.

monitoring a job

Hello Group,
Can I monitor a job say JOB A using another job say JOB B, and restart JOB A if it stops or fails?
Can anyone help or suggest me how to accomplish this task.
Thanks in advance
qAnandIn the advanced section of the job steps you can set many options , one of which is the no of retries in case the job fails. You can use this for the same,