I am trying to create a report in Crystal Reports (v 8.5). I have a
stored procedure to pull data from two databases and parameters.
There are multiple one-to-many relationships and the stored procedure
returns duplicates; e.g., one schedule may have multiple resources,
supplies, and/or orders (and one order may have multiple foods). Is
there a way to stop the duplication?

The stored procedure looks like this:

************************************************** **********************************
SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE usp_rpt1 (
@start_date smalldatetime,
@end_date smalldatetime,
@rpt_type varchar(3),
@rpt_id int
)
AS

set nocount on

--Set up some string variables to build the selection query for the
parameters supplied

declare @fields varchar(255)
declare @tables varchar(255)
declare @where varchar(2000)

CREATE TABLE #tmp_sched(sched_id int, rpt_type_desc varchar(100),
rpt_id int)

set end_date = midnight of next day
SELECT @end_date = DATEADD(day,1,@end_date)
SELECT @end_date = CONVERT(smalldatetime,
CONVERT(varchar(4),YEAR(@end_date)) + '-'
+
CONVERT(varchar(2),MONTH(@end_date)) + '-'
+
CONVERT(varchar(2),DAY(@end_date))

IF @rpt_type = 'LOC'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, l.loc_desc, l.loc_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_date srd ON s.sched_id = srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
LEFT JOIN tbl_loc l ON g.loc_id = l.loc_id
WHERE l.loc_id = CONVERT(varchar(12),@rpt_id)
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
ANd l.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_date_local >=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local <
CONVERT(varchar(20), @end_date+1,1)
IF @rpt_type = 'GRP'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, g.grp_desc, g.grp_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_date srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
WHERE (g.grp_id = CONVERT(varchar(12),@rpt_id)
OR g.parent_grp_id =
CONVERT(varchar(12),@rpt_id))
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_date_local >=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local <
CONVERT(varchar(20), @end_date+1,1)
IF @rpt_type = 'RES'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, r.res_desc, r.res_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_date srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
WHERE r.res_id = CONVERT(varchar(12),@rpt_id)
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_date_local >=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local <
CONVERT(varchar(20), @end_date+1, 1)
IF @rpt_type = 'REG'
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, reg.region_desc,
reg.region_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_date srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
LEFT JOIN tbl_loc l ON g.loc_id = l.loc_id
LEFT JOIN tbl_region reg ON l.loc_id = reg.region_id
WHERE reg.region_id = CONVERT(varchar(12),@rpt_id)
AND reg.obsolete_flag = 0
AND l.obsolete_flag = 0
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_date_local >=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local <
CONVERT(varchar(20), @end_date+1, 1)
IF @rpt_type NOT IN ('LOC','GRP','RES','REG')
INSERT INTO #tmp_sched
SELECT DISTINCT s.sched_id, g.grp_desc, g.grp_id
FROM tbl_sched s
LEFT JOIN tbl_sched_res_date srd ON s.sched_id =
srd.sched_id
LEFT JOIN tbl_res r ON srd.res_id = r.res_id
LEFT JOIN tbl_grp g ON r.grp_id = g.grp_id
WHERE (g.grp_id = 0 OR g.parent_grp_id = 0)
AND g.obsolete_flag = 0
AND r.obsolete_flag = 0
AND s.deleted_flag = 0
AND srd.mtg_start_date_local >=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local <
CONVERT(varchar(20), @end_date+1,1)

--This is the selection for our report
SELECT Description = ts.rpt_type_desc,
Date = CONVERT(varchar(12),srd.mtg_start_date_local,101),
StartTime = srd.mtg_start_date_local,
EndTime = srd.mtg_end_date_local,
SchedID = s.sched_id,
MeetingTitle = s.sched_desc,
ResourceUsed = r.res_desc,
ResourceSetup = su.setup_desc + ' (' +
CONVERT(varchar(10),rs.capacity) + ')',
NumberOfAttendees = Attendees.string_value,
OrderID = ord.order_id,
FoodQty = CONVERT (int,oi.order_qty),
FoodDesc = i.item_name,
Side = sidei.item_name,
MeetingDesc = ord.order_desc,
Supplies = suppliesudf.udf_desc,
SuppliesVal = supplies.value,
AccountCode = ord.order_user_acct_code,
host.string_value as MeetingHost,
CateringNotes = ord.order_notes,
FoodNotes = oi.order_notes

FROM #tmp_sched ts
JOIN tbl_sched s ON ts.sched_id = s.sched_id
JOIN tbl_sched_res_date srd ON ts.sched_id = srd.sched_id
JOIN tbl_res r ON srd.res_id = r.res_id
JOIN tbl_sched_res_setup srs ON s.sched_id = srs.sched_id and
r.res_id = srs.res_id
LEFT JOIN tbl_res_setup rs ON srs.setup_id = rs.setup_id AND
srs.res_id = rs.res_id
LEFT JOIN tbl_setup su ON rs.setup_id = su.setup_id
LEFT JOIN tbl_sched_request_tab_val supplies ON s.sched_id =
supplies.sched_id
AND ((supplies.request_tab_id =
(SELECT request_tab_id FROM tbl_request_tab WHERE
(request_tab_hdr = 'A) Meeting Supplies')))
OR (supplies.request_tab_id =
(SELECT request_tab_id FROM tbl_request_tab WHERE
(request_tab_hdr = 'Mtg Supplies-PEMC'))))
AND (CONVERT(varchar, supplies.value) NOT IN ('0', ''))
LEFT JOIN tbl_udf suppliesudf ON supplies.udf_id =
suppliesudf.udf_id
JOIN tbl_sched_udf_val attendees ON attendees.sched_id = s.sched_id
AND attendees.udf_id =
(SELECT udf_id FROM tbl_udf WHERE udf_desc = 'Number of
Attendees') --UDF For No of Attendees
JOIN tbl_sched_udf_val host ON host.sched_id = s.sched_id
AND host.udf_id =
(SELECT udf_id FROM tbl_udf WHERE udf_desc = 'Meeting
Host') --UDF For meeting host name
LEFT JOIN RSCatering.dbo.tbl_Order ord ON ord.order_sched_id =
s.sched_id --Our link to table in other database
JOIN RSCatering.dbo.tbl_order_item oi ON ord.order_id =
oi.order_id
LEFT JOIN RSCatering.dbo.tbl_menu_item mi ON oi.menu_item_id =
mi.menu_item_id
LEFT JOIN RSCatering.dbo.tbl_item i ON mi.item_id = i.item_id
LEFT JOIN RSCatering.dbo.tbl_order_item_sides side ON
oi.order_item_id = side.order_item_id
LEFT JOIN RSCatering.dbo.tbl_item sidei ON side.item_id =
sidei.item_id

WHERE ord.deleted_flag = 0 AND oi.deleted_flag = 0
ORDER BY
ts.rpt_type_desc,srd.mtg_start_date_local,srd.mtg_ end_date_local,
r.res_desc

DROP TABLE #tmp_sched
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO
************************************************** ****************************************

The simplified result looks like:

Sched2 Resource1 Supply1 Order5
Sched2 Resource1 Supply1 Order6
Sched2 Resource1 Supply3 Order5
Sched2 Resource1 Supply3 Order6
Sched2 Resource2 Supply1 Order5
Sched2 Resource2 Supply1 Order6
Sched2 Resource2 Supply3 Order5
Sched2 Resource2 Supply3 Order6

However, I want the result to look like:

Sched2 Resource1 Supply1 Order5
Sched2 Resource2 Supply3 Order6

Any suggestion is greatly appreciated.

yin_n_yang74 (AT) yahoo (DOT) com wrote:

These are unused and should be removed. (Unless they're used in code
that you edited out because it wasn't relevant to the problem at hand.)

The blocks following these appear to be identical. I recommend removing
the latter, and adding the following above the first INSERT INTO block:

IF @rpt_type NOT IN ('LOC','GRP','RES','REG') THEN
SET @rpt_type = 'GRP'

Oversimplified. Are these SchedID, ResourceUsed, SuppliesVal, and
OrderID?

We need more information about all the tables involved in the stored
procedure's final query, specifically

a) whether their relationships are 1:1 or 1:N or M:N

b) what criteria tell us that Resource1 should associate only with
Supply1 (not Supply3) and Order5 (not Order6), and similarly
for Resource2 and Supply3 (not Supply1) and Order6 (not Order5)

Thank you for your feedback.

The final query has all the columns specified in SELECT. Yes, your
guess is correct - SchedID, ResourceUsed, SuppliesVal, and OrderID
were what was on my mind as I typed the example.

Their relations are as follows:

Schedule to Resource is 1:N
Schedule to Supply is 1:N
Schedule to Order is 1:N
Order to Food is 1:N

Resource, Supply and Order are not directly related to each other.
There is no association that you were asking about in b).
Since we are pulling from two databases and using parameters, our
solution has been to use a stored procedure. Crystal Reports (v 8.5)
allows only one stored procedure.


On Apr 9, 6:47 pm, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:

yin_n_yang74 (AT) yahoo (DOT) com wrote:

Then it sounds like, instead of this monstrosity of a stored
procedure, what you really want is three Crystal subreports
side by side.

You can do this with views instead, e.g.
create view vTable2 as select * from other_database.dbo.Table2

(yin_n_yang74 (AT) yahoo (DOT) com) writes:
So when you want:

That's completely arbitrary, and you could just as well be satisfied
with:

Or any other combination? Get data into yet another temp table,
and

SELECT schedule, resource, min(Supply), Min(Order)
FROM #temp
GROUP BY schedule, resource

But arbitrary results sets do not really make sense to me.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 9 Apr 2007 15:42:24 -0700, yin_n_yang74 (AT) yahoo (DOT) com wrote:


Hi yin_n_yang74,

The first time you posted this question, I advised you to find a client
side solution and provided an outline of the algorithm to use. I also
pointed you to my bog entry that details a possible way to solve this
server-side, in case a client-side solution is not possible.

You now reposted the problem, with more detail, but my answer remains
the same. Either have Crystal Reports open three datasets and process
data from all of them at the same time, pairing data from the datasets
to form formatted output lines - or read my blog article at
http://sqlblog.com/blogs/hugo_kornel...ated-rows.aspx
if you really prefer a server-side solution and don't mind the
performance hit this will incur.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

EVERYTHING you are doing is TOTALLY wrong. You have just been cussed
out by one of the people who wrote this language. If you have brain
instead of an ego, you might want to listen.

This is a (bad) COBOL program written in SQL! There is so much
formatting done in SQL code! The bad news -- for me-- is that this
code is so awful I cannot use it in my next book as a bad example
because it is too proprietary! You could be famous!

Your code is so awful, you even use the "tbl-" prefixes to tell us you
have no idea about RDBMS! You keep converting dates to strings because
you are writing COBOL in SQL and want strings!

Why do your have "CREATE TABLE #tmp_sched" when view would work?
Answer: because magnetic tape files have to be materialized

Why do you spit on ISO-11179 rules and use a "tbl-" prefix? Because
you know only BASIC programming, which needs the prefixes for the one
pass compiler.

You write SQL with flags like it was 1950's Assembly language! Flags
in SQL!! Ghod Damn!! Varying length identifiers!? And I loved the way
spit on ANSI/ISO Standards with "SET QUOTED_IDENTIFIER OFF", etc.?

You need help you cannot get on a newsgroup.