Stored procedure returns duplicates

SQL Apprentice Question
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 < rpt_type =" 'GRP'" sched_id =" srd.sched_id" res_id =" r.res_id" grp_id =" g.grp_id" grp_id =" CONVERT(varchar(12),@rpt_id)" parent_grp_id =" CONVERT(varchar(12),@rpt_id))" obsolete_flag =" 0" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < rpt_type =" 'RES'" sched_id =" srd.sched_id" res_id =" r.res_id" res_id =" CONVERT(varchar(12),@rpt_id)" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < rpt_type =" 'REG'" sched_id =" srd.sched_id" res_id =" r.res_id" grp_id =" g.grp_id" loc_id =" l.loc_id" loc_id =" reg.region_id" region_id =" CONVERT(varchar(12),@rpt_id)" obsolete_flag =" 0" obsolete_flag =" 0" obsolete_flag =" 0" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < sched_id =" srd.sched_id" res_id =" r.res_id" grp_id =" g.grp_id" grp_id =" 0" parent_grp_id =" 0)" obsolete_flag =" 0" obsolete_flag =" 0" deleted_flag =" 0">=
CONVERT(varchar(20), @start_date, 1)
AND srd.mtg_start_date_local < 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" numberofattendees =" Attendees.string_value," orderid =" ord.order_id," foodqty =" CONVERT" 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," cateringnotes =" ord.order_notes," foodnotes =" oi.order_notes" sched_id =" s.sched_id" sched_id =" srd.sched_id" res_id =" r.res_id" sched_id =" srs.sched_id" res_id =" srs.res_id" setup_id =" rs.setup_id" res_id =" rs.res_id" setup_id =" su.setup_id" sched_id =" supplies.sched_id" request_tab_id =" (SELECT" request_tab_hdr =" 'A)" request_tab_id =" (SELECT" request_tab_hdr =" 'Mtg" udf_id =" suppliesudf.udf_id" sched_id =" s.sched_id" udf_id =" (SELECT" udf_desc =" 'Number" sched_id =" s.sched_id" udf_id =" (SELECT" udf_desc =" 'Meeting" order_sched_id =" s.sched_id" order_id =" oi.order_id" menu_item_id =" mi.menu_item_id" item_id =" i.item_id" order_item_id =" side.order_item_id" item_id =" sidei.item_id" deleted_flag =" 0" deleted_flag =" 0">

Celko Answers
>> Any suggestion is greatly appreciated. <<


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.