Jump to a group section of a subreport

I have a parent report, whenever the user click a item, the user will jump
from parent report to a corresponding section of subreport like a group
section.
How do I get such function?
Thanks for your kind help.I suggest instead of a subreport, implement this a a drill through. I.e.
when they click on it you jump to the report. Modify your subreport to
include another parameter so that the report shows just the subset of data
you want. This is very powerful and users understand it quickly. Drillthough
is a great way to present data to the user.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sally" <Sally@.discussions.microsoft.com> wrote in message
news:C9B8B574-EFBD-4790-9914-A2192BDF3C73@.microsoft.com...
> I have a parent report, whenever the user click a item, the user will
jump
> from parent report to a corresponding section of subreport like a group
> section.
> How do I get such function?
> Thanks for your kind help.

Joins issue

Sorry if this is the wrong group but..
I have a query that still has a few minor issues the main problem i had with
nulls is sorted however i am joining 5 tables together and if a row doesnt
exist in a table i dont get a row at all, i have a table that i know a
record always exists in and i am using left outer joins to join it to other
tables. I thought that a left join would get a record regardless of whether
or not there is a matching record. My query is posted below so you can
maybe let me know whats wrong with it, i am sorry for the lack of aliases
and probably readibility but i havent really had time to sort it.
SELECT dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance, dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC +
dbo.DM_OtherCredit.redemtionsecured AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee, dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee, dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear, dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails LEFT OUTER JOIN
dbo.DM_Valuation ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_Valuation.FK_ApplicationID LEFT
OUTER JOIN
dbo.DM_Payout ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_Mortgage ON dbo.DM_LoanDetails.FK_ApplicationID
= dbo.DM_Mortgage.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_OtherCredit ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1') AND
(dbo.DM_LoanDetails.FieldIdent = '1:1') AND (dbo.DM_Valuation.FieldIdent =
'1:1') AND
(dbo.DM_Payout.FieldIdent = '1:1') AND
(dbo.DM_OtherCredit.FieldIdent = '1:1' OR
dbo.DM_OtherCredit.FieldIdent = '1:2' OR
dbo.DM_OtherCredit.FieldIdent = '1:3' OR
dbo.DM_OtherCredit.FieldIdent = '1:4' OR
dbo.DM_OtherCredit.FieldIdent = '1:5' OR
dbo.DM_OtherCredit.FieldIdent = '1:6' OR
dbo.DM_OtherCredit.FieldIdent = '1:7' OR
dbo.DM_OtherCredit.FieldIdent = '1:8' OR
dbo.DM_OtherCredit.FieldIdent = '1:9' OR
dbo.DM_OtherCredit.FieldIdent = '1:10')
Thanks in advanceA LEFT OUTER JOIN will always return rows, provided that your WHERE
criteria doesn't limit the results of your query using a column from
the inner side of the join. In your case, the criteria :
(dbo.DM_Mortgage.FieldIdent = '1:1')
tells SQL Server to limit the results to include data from both
DM_LoanDetails (all rows) and DM_Mortgage (only those rows where
FieldIDent = '1:1'). Basically, you've nullified your OUTER JOIN.
HTH,
Stu|||So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didn=B4t mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||Yeah the query is right the 1:1 condition needs to be there otherwise it
returns other iterations of the record and you end up with dupes i didnt
design the database its software that was ourchased a few years before i
started here, its hard to explain why the iterations are there and why they
work, I do need that clause in there though i have tried it without and
still get the same results.
I can better explain my problem now i think. The sql i have given is used
in another view that performs some calculations and basically if the value
is null makes it zero, the problem lies in the dm_payout and dm_valuation
tables, basically the case has died before anyone has been able to complete
the fields i need from those tables.
However i need to show what the value of the deal was regardless of whether
or not we got to add our fees on top, so if they wanted 100k but no other
fields were completed then it should show 100k
As i have mentioned this calculation is done in another view, the problem
lies in the fact that no record exists in the payout or valuation table so
it is for some unknown reason causing it not to get any results at all.
This other view (main view) is as follows
We have a table of phone numbers of people who have called in on a certain
number that we got from our dialler database this is joined to a table in
the database that has the phone number so that we can get the
fk_applicationID, this is present in all the tables as it is the unique
identifier. We then join this table to another table to get the persons
surname and 2 views, one of the views tells us what the applications status
is of the record, the 2nd view accesses the information in the view which is
the SQL i posted. Basically this view only pulls the required information
that is needed from the view i posted and if the value is null sets it to
zero. I then in this (main view) add the fields together i need.
I get all the records i would expect but i get null where the value of the
calculation should be because in the view i posted no row is returned.
I hope this is making sense. Maybe i wont be able to have a value here and
null is all i can expect but as i said a left join should as far as i know
just give me the rest of the information which then shouldnt mess up my
calc.
thanks for the help so far
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132142679.094129.141370@.g14g2000cwa.googlegroups.com...
So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didnt mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||On Wed, 16 Nov 2005 10:46:25 -0000, Steven Scaife wrote:

>Sorry if this is the wrong group but..
>I have a query that still has a few minor issues the main problem i had wit
h
>nulls is sorted however i am joining 5 tables together and if a row doesnt
>exist in a table i dont get a row at all, i have a table that i know a
>record always exists in and i am using left outer joins to join it to other
>tables. I thought that a left join would get a record regardless of whethe
r
>or not there is a matching record. My query is posted below so you can
>maybe let me know whats wrong with it, i am sorry for the lack of aliases
>and probably readibility but i havent really had time to sort it.
Hi Stevan,
A quick visit to http://www.sqlinform.com/ was all it took to get the
SQL a whole lot more readable. Here's a better formatted version of your
query:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1')
AND (dbo.DM_LoanDetails.FieldIdent = '1:1')
AND (dbo.DM_Valuation.FieldIdent = '1:1')
AND (dbo.DM_Payout.FieldIdent = '1:1')
AND (dbo.DM_OtherCredit.FieldIdent = '1:1'
OR dbo.DM_OtherCredit.FieldIdent = '1:2'
OR dbo.DM_OtherCredit.FieldIdent = '1:3'
OR dbo.DM_OtherCredit.FieldIdent = '1:4'
OR dbo.DM_OtherCredit.FieldIdent = '1:5'
OR dbo.DM_OtherCredit.FieldIdent = '1:6'
OR dbo.DM_OtherCredit.FieldIdent = '1:7'
OR dbo.DM_OtherCredit.FieldIdent = '1:8'
OR dbo.DM_OtherCredit.FieldIdent = '1:9'
OR dbo.DM_OtherCredit.FieldIdent = '1:10')
Now, it is immediately clear that the reason for your query not working,
is that you build WHERE clauses on columns from all outer-join'ed
tables. Stu already explained why that is bad - but it seems that he
only catched one of the culprits.
If you really need these joins to be outer joins, then you'll have to
move all selections from the WHERE clause to the ON clauses:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
AND dbo.DM_Valuation.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
AND dbo.DM_Payout.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
AND dbo.DM_Mortgage.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
AND dbo.DM_OtherCredit.FieldIdent IN ('1:1', '1:2', '1:3', '1:4',
'1:5', '1:6', '1:7', '1:8', '1:9', '1:10')
WHERE dbo.DM_LoanDetails.FieldIdent = '1:1'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Joining Two Measure Groups

Hey all,

Our product structure is:

Category

Class

Subclass

Item

We have 1 measure group by category, and another measure group by item. So we are forced to join by Category. Which works fine except for whereever we join on Category some reason the measures get rolled down all the way to the Item level.

Example:

Category Sales

00001 50.75

Product Measure Group 1 Measure Group 2
Category 00001 50.75 50.75

Class 00002 0.00 50.75

Subclass 00003 0.00 50.75

Item 00004 0.00 50.75

It's important to note Measure Group 1 is ONLY at the category level. Some reason it rolls down when to joni to Measure Group 2? Any ideas how to prevent this?

Thanks a lot.

Hello! I have written a short post about this problem on my blog: http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!381.entry

My example is from the Adventure Works project but the way to solve this should work for your problem also.

HTH

Thomas Ivarsson

|||

Perfect - thanks!

Joining on and Grouping by CASE function column alias (URGENT)

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
 CASE
 WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
 END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC RossYou could do it as a sub query

Select NewColumn from (Select case....) MySub group by MySub.NewColumn...etc, etc|||Not tested, but you should be able to do it like this:

SELECT NewColumn=
 CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I'm sure there's some rule, but I've never figured out when SQL lets you use an alias and when it doesn't. But, in this case, it apparently doesn't, so just use the CASE statement and you should be all set.|||I didn't know you could use a CASE in the JOIN syntax? Does it work?|||Works for me|||Cool I'll have to remember that one, top tip.

Joining of datasets supported in future version?

There are a number of posts in this group that have asked if datasets
can be joined which suggests that there is some requirement for this
functionality. We are currently comparing RS to Brio which allows
this.
Does anyone know if this feature will be introduced in a future
version?
Thanks,
PaulYou can join datasets using the OpenRowset feature of SQL Server.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Taylor" <pjtaylor@.email.com> wrote in message
news:3e0bce4c.0407260213.79edf12e@.posting.google.com...
> There are a number of posts in this group that have asked if datasets
> can be joined which suggests that there is some requirement for this
> functionality. We are currently comparing RS to Brio which allows
> this.
> Does anyone know if this feature will be introduced in a future
> version?
> Thanks,
> Paul|||It's a high-priority item on our wishlist for a future version, but we don't
have any specific plans for it right now (the high cost of implementation
makes it unlikely this will make it into SQL 2005).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Paul Taylor" <pjtaylor@.email.com> wrote in message
news:3e0bce4c.0407260213.79edf12e@.posting.google.com...
> There are a number of posts in this group that have asked if datasets
> can be joined which suggests that there is some requirement for this
> functionality. We are currently comparing RS to Brio which allows
> this.
> Does anyone know if this feature will be introduced in a future
> version?
> Thanks,
> Paul

Join.

select e.dname, count(e.sid) as total_num from enroll e
group by e.dname

select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid and e.dname = m.dname
group by e.dname
----------------------
Hi, buddys,
How can I join above two results of queries by dname if I don't have any create temporary table permission?

Thx,

Neilselect e.dname, count(e.sid) as total_num
from enroll e
group by e.dname

UNION

select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;

It works with Oracle ...|||Originally posted by Littlefoot

select e.dname, count(e.sid) as total_num
from enroll e
group by e.dname

UNION

select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;

It works with Oracle ...|||I don't need to union set, I mean.
I want total_num and major_enroll are two columns in the same table.

thx.
neil

Originally posted by Littlefoot

select e.dname, count(e.sid) as total_num
from enroll e
group by e.dname

UNION

select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;

It works with Oracle ...

JOIN, GROUP BY, or HAVING question

Consider the following two tables:

ProjectHours
hoursID {primary key}
employeeID {foreign key}
ProjectDate
ProjectID
ProjectHours

DataComplete
DataCompleteID {primary key}
employeeID {foreign key}
CompleteDate

The first table should be self-explanatory. The second table is there to let me know that a particular employee has entered all of the data for a given date. This tells me that I can include this data in reports and charts.

Here's where I'm having troubles ...

I want to calculate the average hours worked by an employee on a project during a given time period. For example, what is the average number of hours John worked on Project X during the past week? The tricky part is that the employee/date combo must also be found in the
DataComplete table.

Problem: The following SQL statement averages ALL of the data even for dates NOT included in the DataComplete table:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
JOIN
DataComplete d
ON
h.employeeID = d.employeeID
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

How do I get aggregate info only for dates found in the DataComplete table?I see you have a primary key on your ProjectHours table, but what is the NATURAL key?

Do employeeID, ProjectDate, and ProjectID constitute a unique records? If so, try this:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
INNER JOIN DataComplete d
ON h.employeeID = d.employeeID
AND h.ProjectDate = d.CompleteDate
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

If this is not the case, I suspect you will need to modify (normalize) your table design to do what you want to do.

blindman

Join, Count and Group By

Hello,

I have two tables containing the following info I need to use in a query...

Table 1
---

repair_no
contactor_code

Table 2
---

repair_no
area_code
log_date

I have a query to list all contractors starting with code 'SC', for a given area and between the dates shown.

SELECT
a.repair_no,
a.contractor_code,
b.area_code,
b.log_date
FROM
contractors a,
repairs b
where
a.repair_no = b.repair_no and
a.contractor_code like 'SC%' and
b.area_code like 'CH%' and
b.log_date >= date('01.02.2003') and b.log_date <= date('01.02.2004')
order by
a.contractor_code

How can I use this to obtain and list the same fields as above, but provide a count of (and possibly group by) all similar contractors?

I can get the count I need with the following. Do I need to run the two queries separately or in some way combine the two?

select
a.contractor_code,
count(a.contractor_code) as contr_count
from
contractors a,
repairs b
where
a.repair_no = b.repair_no and
a.contractor_code like 'SC%' and
b.area_code like 'CH%' and
b.log_date >= date('01.02.2003') and b.log_date <= date('01.02.2004')
group by
a.contractor_code

Thanksyou could combine them with UNION ALL, but it would be kluge (http://www.clueless.com/jargon3.0.0/kluge.html)y

i can whip up an example for you if you really need it

if you are returning the detail rows to an application program, you can simply calculate the counts while printing them, and you wouldn't need the second query at all

however, if you want the totals to precede the details in your listing, as in this example:

contractor SC001 has the following 3 repairs:
b0032 416 2004-02-01
b0077 905 2004-02-03
b0032 416 2004-02-05

contractor SC002 has the following 2 repairs:
b0050 905 2004-02-02
b0066 905 2004-02-04

then you might want to give the union a try, otherwise you will have to do two sets of loops in your code, one to count the rows per contractor, and the second to print them|||Thanks.

I've got something working using the two separate queries, which is acceptable.

However, if you do have an example of a union, and you don't mind, I would be grateful to see it. It will at least give me something to play around with.

Many thanks.|||Group totals and details in one database query (http://r937.com/grouptotals.htm)

that page is an unfinished article (i.e. you cannot find it in the archives, it was never published on my site)

the content (i.e. the sql and coldfusion logic) is fine, i just never figured out how to mark it up with colours that i'm happy with|||A query can obtain its output either from table(s) or from other queries: something that is called a view.

The exact means of doing this depend upon what tool you are using, but you can expect to find it with any fully SQL-compliant tool. (Microsoft Access, for example, doesn't support the concept of "views" but does allow you to include a query as well as a table in a query-designw window; thus, the same result, at least for our purposes here.)

In your example, I'd suggest that you use this approach simply because it's easy to visualize. You see, you've already got a query that does the first part: selecting the base records you want. It's a fairly complicated query and it might be a pervasive one: that is, "something you might wish to use in the same way in lots of different places." If you base subsequent inputs directly upon this query, you'll only have to change this query; not a whole slew of 'em.

Final note: when you combine queries in this way, the query optimizer will consider all of them at once to determine their combined effect, building the execution plan accordingly. It doesn't actually "run them one-at-a-time." So you don't [necessarily] pay a performance penalty in your quest for clarity.

And I prize clarity just about most-of-all.

join SmallDateTime column very slow

Hello,
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
[IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
--
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> [IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks

join SmallDateTime column very slow

Hello,
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
& #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> & #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks

Join records of each group

Hello, thanks in advance for your help / comments

I have 2 tables:

SampleInfo contains 2 columns: SampleID & SampleName
Analysis contains 2 columns: SampleID & Elements

I link these 2 tables, get the SampleName & Elements out by the code:

SELECT SampleInfo.SampleName, Analysis.Elements

FROM SampleInfo INNER JOIN Analysis ON (SampleInfo.SampleID = Analysis.SampleID)

It would display

SampleName | Elements
A | a
A | b
A | c
A | f
B | a
B | g
B | l
C | c
C | s
C | o
C | m
C | n

I need to display the report as following:

SampleName | Elements
A | a, b, c, f
B | a, g, l
C | c, s, o, m, n

QUESTION: is it possible? If it is, how should I do this?

FYI, I use CR10 & SQLServer 2000 database

Regards,
tHi,

I found some solution to yor post.

I created a report with excel as datasource.

Grouped the report on Sample Name field.
Created two formula fields to get the results.
1. Elements -- Formula Field
Code for formula filed as follows :
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
2.ResetElementValue -- Formula Field
whileprintingrecords;
shared stringvar Elements;
Elements:="";
-- Place the @.Elements fromula filed in detail section and suppress the section.
-- Place the Group Name in Group Footer section
-- Place @.Elements in the same group Footer section
-- Place the ResetElementValue formula filed in the Group Header section and suppres

Try with the following format and let me know will it fulfills your requirement.

Thanks,
Vidu.
-- Group|||Vidu,
Thanks for your help. It was a great start. I only had to change the Elements (formula field) a little as below. If I don't have the ELSE IF, it will double the last element in each group.

whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else IF Elements <> Right(Elements, length({Sheet1_.Elements}) then
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;

Again, your help is truly appreciated.|||or place the formula in group footer and suppress the details and group header

join question.

Hello, folks
Desc. two tables table A
CriteriaID
1
2
3
Table B
UID ResultID CriteriaID
1 1 1
2 1 2
3 1 3
4 2 1
Want to build a query which gonna show me group by ResultID only records
from table B where B.CriteriaID = {1,2,3} from table APlease don't multi-post.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jonh Smith" <support@.rtsplus.com> wrote in message
news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>|||What do mean by "group By ResultID" If you group by resultID, then you will
get only one record in the ouput for each distinct value of ResultID {1,2}
which means that in the outout SQL needs t obe told what yo put in the other
columns.
We can easily just output the ResultID...
Select ResultID From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a count of original records...
Select ResultID, COunt(*)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a Sum, or Avg of original UID Values...
Select ResultID, Sum(UID), Avg(UID)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
... But if you want to Groyp BY, you have to tell the query processor what
else you want besides the Group By COlumn...
"Jonh Smith" wrote:

> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>
>|||Thank you for your sugestion ! next time i will ..
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%234gars0RFHA.164@.TK2MSFTNGP12.phx.gbl...
> Please don't multi-post.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Jonh Smith" <support@.rtsplus.com> wrote in message
> news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
>|||Thank you for your response
It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID =
3 where {1,2,3} from table A
It is now make sense ?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
> What do mean by "group By ResultID" If you group by resultID, then you
> will
> get only one record in the ouput for each distinct value of ResultID {1,2}
> which means that in the outout SQL needs t obe told what yo put in the
> other
> columns.
> We can easily just output the ResultID...
> Select ResultID From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a count of original records...
> Select ResultID, COunt(*)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a Sum, or Avg of original UID Values...
> Select ResultID, Sum(UID), Avg(UID)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> ... But if you want to Groyp BY, you have to tell the query processor what
> else you want besides the Group By COlumn...
> "Jonh Smith" wrote:
>|||Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, all
at the same time...
Do you mean OR instead of of AND ?
But even then, that doesn't answer my question...
Sorry If I'm off-track, but it seems you might have some difficulties with
English.. Do you understand my question about "Group By" ? If Not, get
someone at your site who is a bit more fluent in English to read it and
explain it to you...
"Jonh Smith" wrote:

> Thank you for your response
> It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID
=
> 3 where {1,2,3} from table A
> It is now make sense ?
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
>
>|||I mean 'AND', .. so this is the point of my problem.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:9182725D-9E48-48D0-9430-F2F0C3D66491@.microsoft.com...
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3,
> all
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||I guess he is talking about relational division.
Relational Division
http://www.dbazine.com/ofinterest/o...br />
division
Example:
select
b.ResultID
from
tableB as b
inner join
tableA as a
on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
group by
b.ResultID
having
count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
and count(distinct a.CriteriaID) = 3;
AMB
"CBretana" wrote:
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, al
l
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||Case closed, thank you Alejandro Mesa.
This is solution
SELECT B.ResultID FROM A
JOIN B ON B.CriteriaID = A.CriteriaID
GROUP BY B.ResultID
HAVING (COUNT(B.ResultID) = (SELECT COUNT(CriteriaID ) FROM
A))
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6D45BAB4-B39E-41DF-836E-8E2F50AA596D@.microsoft.com...
>I guess he is talking about relational division.
> Relational Division
> http://www.dbazine.com/ofinterest/o... />
20division
> Example:
> select
> b.ResultID
> from
> tableB as b
> inner join
> tableA as a
> on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
> group by
> b.ResultID
> having
> count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
> and count(distinct a.CriteriaID) = 3;
>
> AMB
>
> "CBretana" wrote:
>|||Alej,
Got it now... Didn't understand...
On Celko's article (yr Link), I was familiar with the problem, but did
not know it was called "Relational Division".
I have in past used the "Not Exists... Where Not Exists..." syntax for
this issue, but I was unfamiliar with the approach you used. Thanks...
In his article, Celko makes the point that the two approaches return
different results when the divisor is empty, The nested Not Exists return
ALL records, and the Having Count(*)... approach returns an empty set...
On a purely academic note, I kinda think the former, nested approach,
which returns all records, is more mathematically "accurate"... Using Joes
example, All the pilots have the skill to fly "every" plane in an empty
hanger ...

join or subselect ??

Not sure if this is the right group to post this to but.

This is the current query that I have.

select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
order by tableB.price DESC'

What I need is, for each row returned I need information from a third
and fourth table. tableC, and tableD.

tableC has information ( the tableA.id = tableC.eventId) that I need to
obtain tableC.accountId = tableD.accountId in order do select the
the binding information in tableD between a Vendor(name,address..etc..)
and tableB.image

Any help would be greatly appreciated.If I'm reading you correctly, you should be able to do this pretty easily.
In the SELECT statement, list all the fields from each table that you want
to see. In the FROM statement list the tables. In the WHERE statement list
all the parameters and relationships. So:

SELECT
tableA.fields
tableB.fields
tableC.fields
tableD.fields
FROM
TableA
TableB
TableC
TableD
WHERE
tableA.image = tableB.image AND
tablea.id = tableC.eventID AND
talbeC.accountID = tableD.accountID AND
tableB.price >0 AND
tableB.price < 20
ORDER by tableB.price DESC

"kjc" <ksitron@.elp.rr.com> wrote in message
news:OnIXc.52730$xi6.21027@.fe2.texas.rr.com...
> Not sure if this is the right group to post this to but.
> This is the current query that I have.
> select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
> tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
> order by tableB.price DESC'
> What I need is, for each row returned I need information from a third
> and fourth table. tableC, and tableD.
>
> tableC has information ( the tableA.id = tableC.eventId) that I need to
> obtain tableC.accountId = tableD.accountId in order do select the
> the binding information in tableD between a Vendor(name,address..etc..)
> and tableB.image
> Any help would be greatly appreciated.|||[Top posting is annoying and confusing. Rearranging ...]

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in
news:cgo06l$10ga$1@.lettuce.bcit.ca:

> "kjc" <ksitron@.elp.rr.com> wrote in message
> news:OnIXc.52730$xi6.21027@.fe2.texas.rr.com...
>> Not sure if this is the right group to post this to but.
>>
>> This is the current query that I have.
>>
>> select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
>> tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
>> order by tableB.price DESC'
>>
>> What I need is, for each row returned I need information from a third
>> and fourth table. tableC, and tableD.
>>
>>
>> tableC has information ( the tableA.id = tableC.eventId) that I need
>> to obtain tableC.accountId = tableD.accountId in order do select the
>> the binding information in tableD between a
>> Vendor(name,address..etc..) and tableB.image
>>
>> Any help would be greatly appreciated.
>
> If I'm reading you correctly, you should be able to do this pretty
> easily. In the SELECT statement, list all the fields from each table
> that you want to see. In the FROM statement list the tables. In the
> WHERE statement list all the parameters and relationships. So:
> SELECT
> tableA.fields
> tableB.fields
> tableC.fields
> tableD.fields
> FROM
> TableA
> TableB
> TableC
> TableD
> WHERE
> tableA.image = tableB.image AND
> tablea.id = tableC.eventID AND
> talbeC.accountID = tableD.accountID AND
> tableB.price >0 AND
> tableB.price < 20
> ORDER by tableB.price DESC

This will indeed work fine, and the optimizer should have no problem
(given sufficient foreign key constraints and indexing) rewriting it to
run with maximum efficiency. However, future programmers may thank you
if you separate the join relationships from the filtering clauses:

SELECT
tableA.fields,
tableB.fields,
tableC.fields,
tableD.fields
FROM
tableA
INNER JOIN tableB on tableA.image = tableB.image
INNER JOIN tableC on tableA.id = tableC.eventID
INNER JOIN tableD on tableC.accountID = tableD.accountID
WHERE
tableB.price > 0 AND tableB.price < 20