December 18, 2013 at 7:27 am
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-80SF0000018266
ELLA WANG 74351 SF00008668 222-63-80SF0000018266
JEFFRE VASCOE 74351 SF00008668 222-63-80SF0000018266
UDITH YOUNG 74351 SF00008668 222-63-80SF0000018266
5D TRUCKING 787051 869123 222-63-13SF0000015676
A-1 Movers 70221 SF00012876 222-13-06SF0000017363
A-1 Movers 70221 SF00012876 222-13-10SF0000017363
A-1 Movers 70221 SF00012876 222-13-16SF0000017363
Want:
ANNA GAI 74351 SF00008668 222-63-80SF0000018266
5D TRUCKING 787051 869123 222-63-13SF0000015676
A-1 Movers 70221 SF00012876 222-13-06SF0000017363
December 18, 2013 at 7:41 am
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-80SF0000018266
ELLA WANG 74351 SF00008668 222-63-80SF0000018266
JEFFRE VASCOE 74351 SF00008668 222-63-80SF0000018266
UDITH YOUNG 74351 SF00008668 222-63-80SF0000018266
5D TRUCKING 787051 869123 222-63-13SF0000015676
A-1 Movers 70221 SF00012876 222-13-06SF0000017363
A-1 Movers 70221 SF00012876 222-13-10SF0000017363
A-1 Movers 70221 SF00012876 222-13-16SF0000017363
Want:
ANNA GAI 74351 SF00008668 222-63-80SF0000018266
5D TRUCKING 787051 869123 222-63-13SF0000015676
A-1 Movers 70221 SF00012876 222-13-06SF0000017363
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,My blog: www.igormicev.com
December 18, 2013 at 7:42 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2013 at 7:45 am
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,My blog: www.igormicev.com
December 18, 2013 at 7:48 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2013 at 7:59 am
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
December 18, 2013 at 8:04 am
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,My blog: www.igormicev.com
December 18, 2013 at 8:10 am
For some reason, my preview looks good but when I post it, everything looks garbled.
I've sent an attachment. Thank you.
December 18, 2013 at 8:13 am
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.
December 18, 2013 at 8:13 am
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,My blog: www.igormicev.com
December 18, 2013 at 8:18 am
I must not be having good luck today. I've added it as a docx, and a .txt
December 18, 2013 at 8:21 am
Gillian_Pappas2002 (12/18/2013)
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.
Displaying tabular information is not something html is good at. That is why there are html tables. UGH!!! It is generally best to just create a temp table for your results. It only takes a few more seconds.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2013 at 8:26 am
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
Hi,
Pay attention on this fragment of the above posted code: OVER(PARTITION by inv_num ORDER BY name) You've decided to remove duplicates based on inv_num and here you're telling us about some co_nums.
You can also specify more columns after PARTITION BY... as well as in after ORDER BY..
I think you'll manage to settle up your results.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
December 18, 2013 at 8:33 am
Thank you.
December 18, 2013 at 8:36 am
Yes, I was try to remove duplicates based on Co_num. Thank you.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply