Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Delete duplicate row based on one column, but need to retain rest of the values in tmp file Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 7:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Hi.

I have the following code which is correctly selecting my data, but it is giving me duplicates and I can't use a distinct, or Max to be able to eliminate the duplicates. Any ideas?
Thanks for all of your help.

G

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,([order_date]) as order_date
,MAX ([promise_date]) as promise_date
,[AvailableDate]
,MAX ([inv_date]) as inv_date
INTO #rptset
FROM @tmp_FullfillOrder
GROUP BY
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]
--9 Days to available - number of days between order date and date aXllocated.
,DATEDIFF(day,[order_date],[availabledate]) as 'DaysToAvailable'
--10 Ship days - number of days from order date to highest invoice date
,DATEDIFF(day,order_date,inv_date) as 'ShipDays'
--11 Reliable? - Yes if allocate date is less than or equal to Orig Promise date, otherwise leave blank
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
then 'Yes' else 'No' end as 'Reliable'
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
THEN 1 ELSE 0 end as 'ReliableCalc'

FROM #rptset








Partial Output:
name cust_num co_num item inv_num
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
ELLA WANG 74351 SF00008668 222-63-80 SF0000018266
JEFFRE VASCOE 74351 SF00008668 222-63-80 SF0000018266
UDITH YOUNG 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363
A-1 Movers 70221 SF00012876 222-13-10 SF0000017363
A-1 Movers 70221 SF00012876 222-13-16 SF0000017363

Want:
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363



Post #1524131
Posted Wednesday, December 18, 2013 7:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 2,929, Visits: 2,944
Gillian_Pappas2002 (12/18/2013)
Hi.

I have the following code which is correctly selecting my data, but it is giving me duplicates and I can't use a distinct, or Max to be able to eliminate the duplicates. Any ideas?
Thanks for all of your help.

G

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,([order_date]) as order_date
,MAX ([promise_date]) as promise_date
,[AvailableDate]
,MAX ([inv_date]) as inv_date
INTO #rptset
FROM @tmp_FullfillOrder
GROUP BY
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]
--9 Days to available - number of days between order date and date aXllocated.
,DATEDIFF(day,[order_date],[availabledate]) as 'DaysToAvailable'
--10 Ship days - number of days from order date to highest invoice date
,DATEDIFF(day,order_date,inv_date) as 'ShipDays'
--11 Reliable? - Yes if allocate date is less than or equal to Orig Promise date, otherwise leave blank
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
then 'Yes' else 'No' end as 'Reliable'
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
THEN 1 ELSE 0 end as 'ReliableCalc'

FROM #rptset








Partial Output:
name cust_num co_num item inv_num
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
ELLA WANG 74351 SF00008668 222-63-80 SF0000018266
JEFFRE VASCOE 74351 SF00008668 222-63-80 SF0000018266
UDITH YOUNG 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363
A-1 Movers 70221 SF00012876 222-13-10 SF0000017363
A-1 Movers 70221 SF00012876 222-13-16 SF0000017363

Want:
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363





Hi
Try this code:

;WITH cte as
(
SELECT *,
ROW_NUMBER() OVER(PARTITION by inv_num ORDER BY name) AS dupCnt
FROM #rptset
)
--Now Delete Duplicate Records
DELETE FROM cte
WHERE dupCnt > 1

select * from #rptset

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1524134
Posted Wednesday, December 18, 2013 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
From the sample output you posted you don't have any duplicates. There are unique values in one of those columns (I can't tell from the garbled display which one). What are the rules about deciding which row to return?

I will be happy to help but you need to first help me. Take a few minutes and read the first article in my signature about best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1524135
Posted Wednesday, December 18, 2013 7:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 2,929, Visits: 2,944
Sean Lange (12/18/2013)
From the sample output you posted you don't have any duplicates. There are unique values in one of those columns (I can't tell from the garbled display which one). What are the rules about deciding which row to return?

I will be happy to help but you need to first help me. Take a few minutes and read the first article in my signature about best practices when posting questions.


I think he was asking for duplicates based on inv_num.




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1524138
Posted Wednesday, December 18, 2013 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
IgorMi (12/18/2013)
Sean Lange (12/18/2013)
From the sample output you posted you don't have any duplicates. There are unique values in one of those columns (I can't tell from the garbled display which one). What are the rules about deciding which row to return?

I will be happy to help but you need to first help me. Take a few minutes and read the first article in my signature about best practices when posting questions.


I think he was asking for duplicates based on inv_num.


Yeah you posted at the same time I did. Once I saw your code the light bulb turned on. Gotta love that we have lots of people around here. At least somebody was able to figure out what they wanted.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1524141
Posted Wednesday, December 18, 2013 7:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thank you so much. That worked really well, but for some reason, there remain a few randon duplicate co_nums rows showing up. But I truly appreciate your help. Thanks. G
Post #1524146
Posted Wednesday, December 18, 2013 8:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 2,929, Visits: 2,944
Gillian_Pappas2002 (12/18/2013)
Thank you so much. That worked really well, but for some reason, there remain a few randon duplicate co_nums rows showing up. But I truly appreciate your help. Thanks. G


You have to identify what is unique in your data and/or and what columns to use in order to determine rows for deleting.




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1524153
Posted Wednesday, December 18, 2013 8:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
For some reason, my preview looks good but when I post it, everything looks garbled.

I've sent an attachment. Thank you.


  Post Attachments 
Remove duplicate rows based on co_num, but retain the rest of the tmp record.docx (8 views, 15.15 KB)
Post #1524155
Posted Wednesday, December 18, 2013 8:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
My posts look fine in preview then get messed up when I post it. Sorry. I have included an attachment.

Thanks for all of your help.


  Post Attachments 
Remove duplicate rows based on co_num, but retain the rest of the tmp record.docx (7 views, 15.15 KB)
Post #1524156
Posted Wednesday, December 18, 2013 8:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 2,929, Visits: 2,944
Gillian_Pappas2002 (12/18/2013)
For some reason, my preview looks good but when I post it, everything looks garbled.

I've sent an attachment. Thank you.


Cannot open the attachment...




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1524157
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse