Duplicate Records due to one to many relationship

[DmikeR]

The problem I am having is a little complex so I will try to keep it simple. The report will be exported to Excel. See below for store procedure. I created 4 temp tables. The #DateList table combines sales order dates and daily production dates so I can group on report by date. The other tables select specific information to display on report. The problem I am having is I may have one or multiple sales records with same shipdate and/or one or multiple daily production records with same daily production dates. The user would like the information in the same Excel format before using a database.

I tried suggestions from this site and many others such as suppressing records based on formulas. I thought I could do a distinct count on the record Identity using Running Total feature and set up a formula with Previous Function but I get an "This field has no previous or next value. Any help would be greatly appreciated!

STORE PROCEDURE

CREATE PROCEDURE [dbo].[sp_Daily_Prod_SO_List]
(
@startdate datetime, --parameters to select records
@enddate datetime,
@PartNo varchar (25)
)

AS
BEGIN
CREATE TABLE #DateList --Temp table to combine SO and DP dates; used to group in Crytal Reports

(
CombDate datetime NULL,
Descript varchar (35) NULL,
qtycommit numeric default '0',
DLpartno varchar (25) NULL
)

CREATE TABLE #SOLIST --Temp table to store selected records

(
sono varchar (6) NULL,
shipdate datetime NULL,
partno varchar (25) NULL,
qtyship numeric default '0',
orddate datetime NULL,
duedate datetime NULL,
status varchar (10) NULL,
city varchar (20) NULL,
state varchar (20) NULL,
qtyorder numeric default '0',
SOidentity int
)

CREATE TABLE #DPLIST --Temp table to store selected records

(
partno varchar (25) NULL,
dpdate datetime NULL,
ncase int default '0',
dpno varchar (9) NULL,
machno varchar (4) NULL,
DPidentity int
)

CREATE TABLE #InvTrans --Temp table to sum qty
(
TQtyShipped numeric default '0',
transdate datetime,
ITpartno varchar (25) NULL
)

BEGIN
INSERT INTO #DateList (CombDate, Descript, qtycommit, DLpartno)

SELECT DailyProd.[date], InvMaster.descript, InvMaster.qtycommit,DailyProdDetail.partno
FROM (DailyProd INNER JOIN DailyProdDetail ON DailyProd.dpno = DailyProdDetail.dpno) INNER JOIN InvMaster ON InvMaster.partno = DailyProdDetail.partno
WHERE DailyProd.[date] BETWEEN @startdate AND @enddate and DailyProdDetail.partno = @PartNo

UNION

SELECT SOMaster.shipdate, InvMaster.descript, InvMaster.qtycommit, SOItems.partno
FROM (SOMaster INNER JOIN SOItems ON SOMaster.sono = SOItems.sono) INNER JOIN InvMaster ON InvMaster.partno = SOItems.partno
WHERE SOMaster.shipdate BETWEEN @startdate AND @enddate and SOItems.partno =@PartNo AND SOMaster.status in ("closed", "open")

END

BEGIN
INSERT INTO #SOLIST(sono, shipdate, partno, qtyship, orddate, duedate, status, city, state, qtyorder, SOidentity)

Select somaster.sono, somaster.shipdate, soitems.partno, soitems.qtyship, somaster.orddate, somaster.duedate, somaster.status,
somaster.city, somaster.state, soitems.qtyorder, soitems.[identity]
From somaster INNER JOIN soitems ON somaster.sono = soitems.sono
WHERE (SOMaster.shipdate BETWEEN @startdate AND @enddate) and soitems.partno = @partno

END

BEGIN
INSERT INTO #DPLIST(partno, dpdate, ncase, dpno, machno, DPidentity)

Select dailyproddetail.partno, dailyprod.[date], dailyproddetail.ncase, dailyproddetail.dpno, dailyproddetail.machno, dailyproddetail.[identity]
From (dailyprod INNER JOIN dailyproddetail ON dailyprod.dpno = dailyproddetail.dpno) INNER JOIN InvMaster ON InvMaster.partno = dailyproddetail.partno
WHERE (dailyprod.[date] BETWEEN @startdate AND @enddate) and dailyproddetail.partno = @partno


END

BEGIN
INSERT INTO #InvTrans(TQtyShipped, ITpartno)

Select SUM(invtrans.qty), invtrans.partno
From Invtrans
WHERE (invtrans.transdate BETWEEN @startdate AND @enddate) and invtrans.partno = @partno and type = 'A7'
Group by Invtrans.partno

END

SELECT *, dbo.fn_TotalOnHandQtyByPartNo([#SOLIST].partno) AS qtyonhand, dbo.fn_TotalProducedByPartNo([#InvTrans].ITpartno) AS TotProduced, dbo.fn_TotNetCasesByPartNo([#DPLIST].partno, @startdate, @enddate) AS TotNetCases
FROM ((#DATELIST Left Outer JOIN #SOLIST ON #DateList.combdate = #SOList.shipdate) Left Outer JOIN #DPList ON #DPList.dpdate = #DateList.combdate) Left Outer JOIN #InvTrans ON
#InvTrans.ITpartno = #DateList.DLpartno


END
GO

 

[dgillz]

Don't need the SP code, we need your current results in crystal, and your desired results.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[DmikeR]

Here are the current results:
shipDate SO# DailyProdDate DP# NetCases
1/1/08 1 1/1/08 1 10
1/1/08 2 1/1/08 1 10
1/1/08 3 1/1/08 1 10
1/2/08 4 1/2/08 2 11
1/2/08 4 1/2/08 3 12
1/3/08 4 12
1/3/08 5 13
1/4/08 5
1/4/08 6

Desired Result is to eliminate duplicate lines. I tried concat. fields with Previous function and using Next function with limited results. The functions would either suppress sales info and not duplicate daily prod info.

 

[IanWaterman]

None of the lines you have shown are duplicates, each line has a different set of data.

show us how you want it to look.

Ian

 

[DmikeR]

shipDate SO# DailyProdDate DP# NetCases
1/1/08 1 1/1/08 1 10
1/1/08 2
1/1/08 3
1/2/08 4 1/2/08 2 11
1/2/08 3 12
1/3/08 4 12
1/3/08 5 13
1/4/08 5
1/4/08 6

 

[IanWaterman]

Just right click on each field object, select Format field and common tab.

In bottom of that tab check suppress if duplicated.

Ian

 

[DmikeR]

Thanks Ian for the tip. Unfortunately, I cannot provide all the heading information due to limited space of post. When I do what you suggested it would suppress information and suppress needed information.
The example below SO# 1-4 shipdate and activity are suppressed because they are same from line above. The desired result is to keep information but suppress duplicate line.

shipDate SO# Activity DailyProdDate DP# NetCases
1/1/08 1 A 1/1/08 1 10
2
3
4 D 1/2/08 2 11
1/2/08 3 12
1/3/08 4 12
1/3/08 5 13
1/4/08 5 E
1/4/08 6 F

 

[DmikeR]

Fixed my problem. Instead of Select I used Select Distinct in the store procedure. I then concat the sales order fields in one formula and daily production fields in another formula to suppress each formula using the Previous function.

 

[RoyDeWitt]

This is how I located duplicate records:

Code - Make formula "Counter" [WhilePrintingRecords; NumberVar counter := 0] and place on report header.

Make formula "Parse" [ ToText ({NIMRS2.FTYPE},0) & ToText ({NIMRS2.INCDATE}) & {@ClientName}]

Make formula "Details" [ WhilePrintingRecords; NumberVar counter; if {@PARSE} = Previous ({@PARSE})
then counter := counter + 1 else 0 ] and place in the details section.

Create Group "@Parse" and suppress group header and details sections.

Split Group footer into 2 sections and add suppress formula [OnFirstRecord or {@Details} = 0] to section "A"

then add suppress formula [{@Details} = 0] to section "B".

The code results in only records entered twice displayed in the report.