Help with duplicate row.....

[khekking]

I'm new to SQL and need help. The table below duplicates the row when there is a value for "open quotes."

I'm using SQL 2008 and accessing it with SQL server management studio by Microsoft.

Here's the query:

select distinct CUSTID_32, MonthlyRentals, DailyRentals, FreightCharges, Total,

case
when (ContactType like 'quo%'
and ActionDate >= DATEADD(month,-3,getdate())
and ActionDate <= DATEADD(month,5,getdate())
and WorkflowStatus not like 'Rej%'
and WorkflowStatus not like 'Real%')
then Synergy.dbo.AAASynergyWorkflow_vw.ActionDate else '' end as OpenQuote

from ExactMAXBunnell.dbo.TwelveMonthRentalRevenue_vw

left join synergy.dbo.AAASynergyWorkflow_vw
on ExactMAXBunnell.dbo.TwelveMonthRentalRevenue_vw.CUSTID_32 = Synergy.dbo.AAASynergyWorkflow_vw.Cust#

where Total >= 20000

group by CUSTID_32, MonthlyRentals, DailyRentals, FreightCharges, Total, ContactType, ActionDate, WorkflowStatus


Here are the results:

CUSTID_32 MonthlyRentals DailyRentals FreightCharges Total OpenQuote
BA300 14000 57702 12116.76 83818.76
BC020 36400 0 0 36400
BR430 0 19116 1668.22 20784.22
CA120 0 59649 731 60380
CH340 0 72924 13823.09 86747.09
CH340 0 72924 13823.09 86747.09 10/31/2013
CH360 0 129741 7276.07 137017.07
CH420 0 37524 5439.21 42963.21
CH600 0 23010 365.5 23375.5
FL100 0 119652 8312.17 127964.17
HI010 16800 9381 1215.57 27396.57
HI010 16800 9381 1215.57 27396.57 11/30/2013
HU590 0 58056 6048 64104
IL040 0 44250 3655 47905
IN070 25200 42834 2407.47 70441.47
IN070 25200 42834 2407.47 70441.47 10/31/2013
IN080 8400 22656 2277.67 34064.67
LA030 33600 0 0 33600
LE350 0 53631 1908.05 55539.05
MI010 0 19293 2193 21486
MI010 0 19293 2193 21486 10/31/2013
MI120 0 65667 7516.26 73183.26
MI520 0 111333 1827.5 113160.5
NC020 14000 16815 3560.04 34375.04
NV020 36400 28674 2565 68665
NV050 0 33984 2664.18 36648.18
PA030 25200 18231 3181.37 46612.37
RO120 0 32568 4447.68 37015.68
SC500 0 34161 2622.22 36783.22
SI400 11200 7434 5835.37 26086.55
SK010 0 45489 0 45489
ST530 0 144432 1208.76 145640.76
TX030 19600 60003 1296.5 80899.5
TX040 0 24426 7148.44 31574.44
TX060 33600 1239 5817.97 40656.97
TX120 5600 12390 2268.81 20258.81
VA370 0 32745 6268.13 39013.13
VA440 28000 7080 8411 43491
WI500 0 68743 955.2 69698.2
YO010 0 36462 4309.51 40771.51


As you can see any time there is a result for an open quote date the row returned is duplicated. Can someone explain this to me and provide a solution to remove the duplicate. As I am new to SQL any explanations that you can provide would be most helpful.

Many thanks in advance.

Cheers,

Ken

 

[gmmastros]

Well.... they are not exactly duplicates. The distinct keyword prevents duplicates. When the "duplicates" appear, the last column is different. One has a date and the other doesn't.

You are getting "dupes" when there are multiple rows in the result set where one (or more) rows matches your case expression and the other doesn't. We can help you fix this problem, but the question is, which row do you want to show, the one with the date or the one without the date?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Here's the issue, you are trying to use DISTINCT to remove 'duplicates'. For example, in this set of two rows you want the one with the date.

CH340 0 72924 13823.09 86747.09
CH340 0 72924 13823.09 86747.09 10/31/2013


But those aren't duplicates. Each one is distinct. One has the date and one doesn't; so they are different.

Right now, I'm not sure what the solution is but that should give you an idea of what the issue is. If you take DISTINCT out, do you get more rows returned or do you get the same results?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[khekking]

Gmmastros,

Thanks for the response. Sorry the jargon I used was incorrect. I want to keep the rows that have the date value.

Thank you in advance for your help.

Cheers,

Ken

 

[khekking]

SQLBill,

Thank you for the response. I did a poor job of explaining my issue. When distinct is removed I get more rows returned. I was trying to eliminate the "dupes" (I hope that is the correct jargon) by using distinct.

Thanks,

Ken

 

[gmmastros]

try this:

  select CUSTID_32, 
         MonthlyRentals, 
	     DailyRentals, 
	     FreightCharges, 
	     Total,
	     Min(case when (ContactType like 'quo%' 
                    and ActionDate >= DATEADD(month,-3,getdate()) 
                    and ActionDate <= DATEADD(month,5,getdate()) 
                    and WorkflowStatus not like 'Rej%' 
                    and WorkflowStatus not like 'Real%') 
              then Synergy.dbo.AAASynergyWorkflow_vw.ActionDate 
			  else NULL 
			  end) as OpenQuote
  from   ExactMAXBunnell.dbo.TwelveMonthRentalRevenue_vw
         left join synergy.dbo.AAASynergyWorkflow_vw
            on ExactMAXBunnell.dbo.TwelveMonthRentalRevenue_vw.CUSTID_32 = Synergy.dbo.AAASynergyWorkflow_vw.Cust#
  where Total >= 20000
  group by CUSTID_32, MonthlyRentals, DailyRentals, FreightCharges, Total

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[khekking]

Gmmastros,

After applying the suggested coding I basically get the same results but with NULL rather than '' in the OpenQuote Column. Additionally, removing the discrete function before CUSTID_32 has added what I believe are duplicate rows.

Here are the results:

CUSTID_32 MonthlyRentals DailyRentals FreightCharges Total OpenQuote
BA300 14000 57702 12116.76 83818.76 NULL
BA300 14000 57702 12116.76 83818.76 NULL
BC020 36400 0 0 36400 NULL
BC020 36400 0 0 36400 NULL
BC020 36400 0 0 36400 NULL
BR430 0 19116 1668.22 20784.22 NULL
BR430 0 19116 1668.22 20784.22 NULL
BR430 0 19116 1668.22 20784.22 NULL
BR430 0 19116 1668.22 20784.22 NULL
CA120 0 59649 731 60380 NULL
CA120 0 59649 731 60380 NULL
CH340 0 72924 13823.09 86747.09 NULL
CH340 0 72924 13823.09 86747.09 10/31/2013
CH360 0 129741 7276.07 137017.07 NULL
CH360 0 129741 7276.07 137017.07 NULL
CH420 0 37524 5439.21 42963.21 NULL
CH420 0 37524 5439.21 42963.21 NULL
CH420 0 37524 5439.21 42963.21 NULL
CH420 0 37524 5439.21 42963.21 NULL
CH600 0 23010 365.5 23375.5 NULL
CH600 0 23010 365.5 23375.5 NULL
FL100 0 119652 8312.17 127964.17 NULL
FL100 0 119652 8312.17 127964.17 NULL
HI010 16800 9381 1215.57 27396.57 NULL
HI010 16800 9381 1215.57 27396.57 NULL
HI010 16800 9381 1215.57 27396.57 11/30/2013
HI010 16800 9381 1215.57 27396.57 NULL
HU590 0 58056 6048 64104 NULL
IL040 0 44250 3655 47905 NULL
IL040 0 44250 3655 47905 NULL
IN070 25200 42834 2407.47 70441.47 NULL
IN070 25200 42834 2407.47 70441.47 NULL
IN070 25200 42834 2407.47 70441.47 NULL
IN070 25200 42834 2407.47 70441.47 10/31/2013
IN070 25200 42834 2407.47 70441.47 NULL
IN080 8400 22656 2277.67 34064.67 NULL
IN080 8400 22656 2277.67 34064.67 NULL
LA030 33600 0 0 33600 NULL
LA030 33600 0 0 33600 NULL
LA030 33600 0 0 33600 NULL
LE350 0 53631 1908.05 55539.05 NULL
LE350 0 53631 1908.05 55539.05 NULL
MI010 0 19293 2193 21486 NULL
MI010 0 19293 2193 21486 10/31/2013
MI120 0 65667 7516.26 73183.26 NULL
MI120 0 65667 7516.26 73183.26 NULL
MI120 0 65667 7516.26 73183.26 NULL
MI520 0 111333 1827.5 113160.5 NULL
MI520 0 111333 1827.5 113160.5 NULL
NC020 14000 16815 3560.04 34375.04 NULL
NC020 14000 16815 3560.04 34375.04 NULL
NV020 36400 28674 2565 68665 NULL
NV050 0 33984 2664.18 36648.18 NULL
PA030 25200 18231 3181.37 46612.37 NULL
RO120 0 32568 4447.68 37015.68 NULL
SC500 0 34161 2622.22 36783.22 NULL
SI400 11200 7434 5835.37 26086.55 NULL
SK010 0 45489 0 45489 NULL
ST530 0 144432 1208.76 145640.76 NULL
TX030 19600 60003 1296.5 80899.5 NULL
TX030 19600 60003 1296.5 80899.5 NULL
TX040 0 24426 7148.44 31574.44 NULL
TX040 0 24426 7148.44 31574.44 NULL
TX060 33600 1239 5817.97 40656.97 NULL
TX120 5600 12390 2268.81 20258.81 NULL
VA370 0 32745 6268.13 39013.13 NULL
VA370 0 32745 6268.13 39013.13 NULL
VA440 28000 7080 8411 43491 NULL
WI500 0 68743 955.2 69698.2 NULL
YO010 0 36462 4309.51 40771.51 NULL
YO010 0 36462 4309.51 40771.51 NULL

Ideas?