How to omit repeated data. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to omit repeated data.

Author

Topic

basawareddy
Starting Member

11 Posts

Posted - 2009-04-14 : 02:51:01

Hi Friends,

I have written one stored procedure to generate report which contains 2 functions. When i run this stored Procedure i am getting following result set (Record set is big one so i am displaying sample data).

Water Treatment 217 217 0 0 NULL
Adjunct Cooking 204 148 0 56 TEMPERATURE
Adjunct Cooking 204 148 0 56 TOTAL GELATINISATION TIME
Milling and Mashing 416 387 0 29 TEMPERATURE
Lautering 261 261 0 0 NULL
Wort Boiling 290 290 0 0 NULL
Whirl Pool & Cooling 310 310 0 0 NULL
Yeast Handling 273 245 0 28 YEAST STORAGE TEMPERATURE (OC)
Fermentation 1104 1104 0 0 NULL
Lagering 652 378 102 172 CO2 counter pr during lagering
Lagering 652 378 102 172 Storage temperature
Lagering 652 378 102 172 OG
Lagering 652 378 102 172 ALCOHOL
Lagering 652 378 102 172 BITTERNESS
Lagering 652 378 102 172 COLOUR
Filtration 127 74 17 36 BBT STORAGE TEMPERTAURE
Filtration 127 74 17 36 NULL
BBT 799 674 51 74 BBT STORAGE TEMPERTAURE
BBT 799 674 51 74 Apparent Extract
Washing Machine 298 293 5 0 Hard pads
Filler 389 389 0 0 NULL
Pasteuriser 283 283 0 0 NULL

But I want this as below

Water Treatment 217 217 0 0 NULL
Adjunct Cooking 204 148 0 56 TEMPERATURE
TOTAL GELATINISATION TIME
Milling and Mashing 416 387 0 29 TEMPERATURE
Lautering 261 261 0 0 NULL
Wort Boiling 290 290 0 0 NULL
Whirl Pool & Cooling 310 310 0 0 NULL
Yeast Handling 273 245 0 28 YEAST STORAGE TEMPERATURE (OC)
Fermentation 1104 1104 0 0 NULL
Lagering 652 378 102 172 CO2 counter pr during lagering
Storage temperature
OG
ALCOHOL
BITTERNESS
COLOUR
Filtration 127 74 17 36 BBT STORAGE TEMPERTAURE
NULL
BBT 799 674 51 74 BBT STORAGE TEMPERTAURE
Apparent Extract
Washing Machine 298 293 5 0 Hard pads
Filler 389 389 0 0 NULL
Pasteuriser 283 283 0 0 NULL

Please find the following stored procedure and functions.
Store Procedure.
--drop procedure get_wrf_compiled_report_data 1,'2009-03-22','2009-03-28'
create procedure get_wrf_compiled_report_data
(@UnitCodes varchar(1000), @FromDate datetime,@ToDate datetime)
As
Begin
DECLARE @Units VARCHAR(1000)
SELECT @Units = (CASE WHEN @Units IS NULL THEN CONVERT(VARCHAR(50),um_code) ELSE @Units + ',' + cONVERT(VARCHAR(50),um_code) END)
FROM qc_unit_master where um_zone_unit_group =@UnitCodes

select f.TestDesc,f.Totobs,f.Green,f.Yellow,f.red,prmDtls.tpd_field2,prmDtls.tpd_field3,prmDtls.tpd_field7,prmDtls.tpd_field8
,prmDtls.tpd_field9,e.unitcode,e.Yellowcount,d.YellowRmarks,e.Redcount,d.RedRmarks--,e.TestCode,e.param
from (select * from [ConcatRemarks](@Units,@FromDate,@ToDate)) d
join
(select * from [ConcatUnits](@Units,@FromDate,@ToDate)) e on (e.TestCode = d.TestCode and e.param = d.param)
join
qc_test_parameter_detail prmDtls on ( e.param=prmDtls.tpd_code and e.testcode=prmDtls.tpd_test_code)
right join
(select th_description TestDesc,trd_test_code tstcode,th_sort_order,sum(trd_observations) TotObs,sum(trd_green) Green,sum(trd_yellow) Yellow,sum(trd_red) Red
from qc_transaction_detail,qc_transaction_header,qc_test_header
where trd_unit_code in (select * from fnSplit(@Units,','))
and trh_date >= @FromDate and trh_date <= @ToDate
and trh_code=trd_trn_header_code
and trd_test_code = th_code
group by th_description,trd_test_code,th_sort_order
)f on e.testcode=f.tstcode
order by f.th_sort_order
End
GO

Function 1:
--drop function [dbo].[ConcatRemarks]
CREATE FUNCTION [dbo].[ConcatRemarks](
@unitList varchar (1000)
, @fromDate datetime
, @toDate datetime
) RETURNS @RemarksTbl TABLE (TestCode int ,param int,YellowRmarks varchar(4000),RedRmarks varchar(4000))

BEGIN
DECLARE @strRemarks VARCHAR(8000)
DECLARE @paramCode int
DECLARE @paramCodeTemp int
DECLARE @yellowRemarks VARCHAR(500)
DECLARE @redRemarks VARCHAR(500)
DECLARE @testCode int
DECLARE @unitCode int
DECLARE @yellowRemarksConcat VARCHAR(8000)
DECLARE @redRemarksConcat VARCHAR(8000)

DECLARE @prevtestCode int
DECLARE @prevyellowRemarksConcat VARCHAR(8000)
DECLARE @prevredRemarksConcat VARCHAR(8000)

Declare csr cursor for
select trd_test_code,trd_par_detail_code,trd_unit_code,trd_yellow_remarks,trd_red_remarks
from qc_transaction_detail trnsDtl join qc_transaction_header trnsHdr
on trnsDtl.trd_trn_header_code=trnsHdr.trh_code
where trnsDtl.trd_unit_code in (select * from fnSplit(@unitList, ','))
and trnsHdr.trh_date >= @fromDate and trnsHdr.trh_date <= @toDate
and(trnsDtl.trd_yellow > 0 or trnsDtl.trd_red > 0)
order by trnsDtl.trd_par_detail_code

open csr

Fetch Next from csr into @testCode,@paramCode,@unitCode,@yellowRemarks,@redRemarks

while(@@fetch_status=0)
begin
if(@paramCodeTemp = 0)
set @paramCodeTemp = @paramCode
if(@paramCodeTemp = @paramCode)
begin
if(@yellowRemarks <> '')
Begin
set @yellowRemarksConcat=@yellowRemarks +','+ @yellowRemarksConcat
End
Else
Begin
set @yellowRemarksConcat=@yellowRemarksConcat
End

if(@redRemarks <> '')
Begin
set @redRemarksConcat=@redRemarks +','+ @redRemarksConcat
End
Else
Begin
set @redRemarksConcat=@redRemarksConcat
End

--set @yellowRemarksConcat=@yellowRemarks +','+ @yellowRemarksConcat
--set @redRemarksConcat=@redRemarks +','+ @redRemarksConcat

set @testCode = @prevtestCode
set @prevyellowRemarksConcat = @yellowRemarksConcat
set @prevredRemarksConcat = @redRemarksConcat

set @paramCodeTemp = @paramCode
end
else
begin
INSERT INTO @RemarksTbl SELECT @prevtestCode,@paramCodeTemp,@prevyellowRemarksConcat,@prevredRemarksConcat
set @yellowRemarksConcat=''
if(@yellowRemarks <> '')
Begin
set @yellowRemarksConcat=@yellowRemarks +','+ @yellowRemarksConcat
End
Else
Begin
set @yellowRemarksConcat=@yellowRemarksConcat
End
--set @yellowRemarksConcat=@yellowRemarks+','+@yellowRemarksConcat

set @redRemarksConcat=''
if(@redRemarks <> '')
Begin
set @redRemarksConcat=@redRemarks +','+ @redRemarksConcat
End
Else
Begin
set @redRemarksConcat=@redRemarksConcat
End
--set @redRemarksConcat=@redRemarks+','+@redRemarksConcat

set @prevtestCode = @testCode
set @prevyellowRemarksConcat = @yellowRemarksConcat
set @prevredRemarksConcat = @redRemarksConcat

set @paramCodeTemp=@paramCode
end
Fetch Next from csr into @testCode,@paramCode,@unitCode,@yellowRemarks,@redRemarks
End
INSERT INTO @RemarksTbl SELECT @prevtestCode,@paramCodeTemp,@prevyellowRemarksConcat,@prevredRemarksConcat
close csr
deallocate csr
RETURN
END

Function 2:
--drop function [dbo].[ConcatUnits]
create FUNCTION [dbo].[ConcatUnits](
@unitList varchar (1000)
, @fromDate datetime
, @toDate datetime
) RETURNS @UnitCountTbl TABLE (TestCode int ,param int,unitCode varchar(1000),YellowCount int , RedCount int)

BEGIN
DECLARE @paramCode int
DECLARE @paramCodeTemp int
DECLARE @yellow int
DECLARE @red int
DECLARE @testCode int
DECLARE @unitCode int
DECLARE @unitCount varchar (8000)
DECLARE @prevunitCount varchar (8000)
DECLARE @prevyellow int
DECLARE @prevred int
DECLARE @prevtestCode int
DECLARE @prevunitCode int

Declare csr cursor for
select trd_test_code,trd_par_detail_code,trd_unit_code,sum(trd_yellow) TotYellow,sum(trd_red) TotRed
from qc_transaction_detail trnsDtl join qc_transaction_header trnsHdr
on trnsDtl.trd_trn_header_code=trnsHdr.trh_code
where trnsDtl.trd_unit_code in (select * from fnSplit(@unitList, ','))
and trnsHdr.trh_date >= @fromDate and trnsHdr.trh_date <= @toDate
and(trnsDtl.trd_yellow > 0 or trnsDtl.trd_red > 0)
group by trd_test_code,trd_par_detail_code,trd_unit_code
order by trnsDtl.trd_par_detail_code

open csr

Fetch Next from csr into @testCode,@paramCode,@unitCode,@yellow,@red

while(@@fetch_status=0)
begin
if(@paramCodeTemp = 0)
set @paramCodeTemp = @paramCode
if(@paramCodeTemp = @paramCode)
begin
set @unitCount=Convert(varchar(100),@unitCode) + ' '+'Yellow - (' + Convert(varchar(100),@yellow) + '),' +' Red - (' + Convert(varchar(100),@red) + ')'
set @prevtestCode = @testCode
set @prevunitCount = @unitCount
set @prevyellow = @yellow
set @prevred = @red
set @paramCodeTemp=@paramCode
end
else
begin
INSERT INTO @UnitCountTbl SELECT @prevtestCode,@paramCodeTemp,@prevunitCount,@prevyellow,@prevred -- Put the last item in

set @unitCount=''
set @unitCount=Convert(varchar(100),@unitCode) + ' '+'Yellow - (' + Convert(varchar(100),@yellow) + '),' +' Red - (' + Convert(varchar(100),@red) + ')'
set @paramCodeTemp=@paramCode

set @prevtestCode = @testCode
set @prevunitCount = @unitCount
set @prevyellow = @yellow
set @prevred = @red

end
Fetch Next from csr into @testCode,@paramCode,@unitCode,@yellow,@red
End
close csr
deallocate csr
RETURN
END

Please help me to solve this issue.

Thanks,
Basawareddy M Gopsen.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-14 : 03:03:10

You are going to need to simplify this for us if you want free help. You've posted way too much code. Figure out exactly what query shows the duplicates and post just that small part.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

basawareddy
Starting Member

11 Posts

Posted - 2009-04-14 : 03:11:14

Sorry friends,

I thought it will help u to analyze.

I have written following query.

select f.TestDesc,f.Totobs,f.Green,f.Yellow,f.red,prmDtls.tpd_field2,prmDtls.tpd_field3,prmDtls.tpd_field7,prmDtls.tpd_field8
,prmDtls.tpd_field9,e.unitcode,e.Yellowcount,d.YellowRmarks,e.Redcount,d.RedRmarks--,e.TestCode,e.param
from (select * from [ConcatRemarks](@Units,@FromDate,@ToDate)) d
join
(select * from [ConcatUnits](@Units,@FromDate,@ToDate)) e on (e.TestCode = d.TestCode and e.param = d.param)
join
qc_test_parameter_detail prmDtls on ( e.param=prmDtls.tpd_code and e.testcode=prmDtls.tpd_test_code)
right join
(select th_description TestDesc,trd_test_code tstcode,th_sort_order,sum(trd_observations) TotObs,sum(trd_green) Green,sum(trd_yellow) Yellow,sum(trd_red) Red
from qc_transaction_detail,qc_transaction_header,qc_test_header
where trd_unit_code in (select * from fnSplit(@Units,','))
and trh_date >= @FromDate and trh_date <= @ToDate
and trh_code=trd_trn_header_code
and trd_test_code = th_code
group by th_description,trd_test_code,th_sort_order
)f on e.testcode=f.tstcode
order by f.th_sort_order

It will return result as same as

Water Treatment 217 217 0 0 NULL
Adjunct Cooking 204 148 0 56 TEMPERATURE
Adjunct Cooking 204 148 0 56 TOTAL GELATINISATION TIME
Milling and Mashing 416 387 0 29 TEMPERATURE

But i want in following manner.

Water Treatment 217 217 0 0 NULL
Adjunct Cooking 204 148 0 56 TEMPERATURE
Adjunct Cooking 204 148 0 56 TOTAL GELATINISATION TIME
Milling and Mashing 416 387 0 29 TEMPERATURE

Plz help me to solve.

Thanks
Basawareddy M Gopsen.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-14 : 03:17:20

I'm going to lock this topic since you posted the shorter query in a new topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

basawareddy
Starting Member

11 Posts

Posted - 2009-04-14 : 03:17:22

Sorry friends,

Forgot to copy result set which i need.

It will return result as same as

Water Treatment 217 217 0 0 NULL
Adjunct Cooking 204 148 0 56 TEMPERATURE
Adjunct Cooking 204 148 0 56 TOTAL GELATINISATION TIME
Milling and Mashing 416 387 0 29 TEMPERATURE

But i want in following manner.

Water Treatment 217 217 0 0 NULL
Adjunct Cooking 204 148 0 56 TEMPERATURE
TOTAL GELATINISATION TIME
Milling and Mashing 416 387 0 29 TEMPERATURE

Thanks,
Basawareddy M Gopsen,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources