|
|
Posted by yin_n_yang74 on 04/09/07 22:42
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.
[Back to original message]
|