Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


remove duplicates IF matches


remove duplicates IF matches

Author
Message
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
Brad Marsh (3/7/2013)
Thanks Lynn I can appreciate what your saying but the reason I gave you that dummy data is because the data is confidential, I can not simply throw it up.

I also appreciate the fact that you say simply reuse the query provided, but I just don't see how that is possible to scale with. As the data is dynamic it is driven from a form that is completed and then submitted into the database. there could be hundreds of additions to this per day, so I am not sure that this can scale moving forward.

Any ideas on how I can achieve the outcome, with something that can better scale for these requirements? If you need read data, I can give it to you, but I am afraid i will have needed to blank some items out (which may make the data rather useless).

Cheers,
Brad


Even after all this info you provided about your problem....we still haven't completely understood what is it that you actually want.......to b really blunt...it is just a complete waste of time. Instead of doing this you can always utilize the time to create a sample data set that looks like your actual data.

Just throw in some dummy data but give us a picture of what the problem is.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24198 Visits: 37963
vinu512 (3/7/2013)
Brad Marsh (3/7/2013)
Thanks Lynn I can appreciate what your saying but the reason I gave you that dummy data is because the data is confidential, I can not simply throw it up.

I also appreciate the fact that you say simply reuse the query provided, but I just don't see how that is possible to scale with. As the data is dynamic it is driven from a form that is completed and then submitted into the database. there could be hundreds of additions to this per day, so I am not sure that this can scale moving forward.

Any ideas on how I can achieve the outcome, with something that can better scale for these requirements? If you need read data, I can give it to you, but I am afraid i will have needed to blank some items out (which may make the data rather useless).

Cheers,
Brad


Even after all this info you provided about your problem....we still haven't completely understood what is it that you actually want.......to b really blunt...it is just a complete waste of time. Instead of doing this you can always utilize the time to create a sample data set that looks like your actual data.

Just throw in some dummy data but give us a picture of what the problem is.



To be honest, NO ONE on this site wants you to post confidential data, NO ONE.

You are the only one who understands your problem domain, therefore it is up to you to put together a sample data set that models your problem domain without using confidential data.

You also have to show us what it is you are trying to achieve. Without the sample data and expected results based on that sample data, we can't put together anything that may be able you help you. All you will get are shots in the dark.

Help us help you.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 132
Point taken will do over the weekend - thanks guys.
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 132
Ok was able to get this done now:

Here is the table structures

CREATE TABLE [dbo].[HMD](
[WeekNum] [float] NULL,
[MonthName] [nvarchar](255) NULL,
[Response1_Num] [float] NULL,
[Response2_Num] [float] NULL,
[Response3_Num] [float] NULL,
[Response4_Num] [float] NULL,
[Customer Id] [nvarchar](255) NULL,
[Email_Queue_Name] [nvarchar](255) NULL,
[Session Id] [nvarchar](255) NULL,
[Time_stamp] [float] NULL,
[CaseID] [nvarchar](255) NULL,
[Response] [nvarchar](255) NULL,
[Response1] [nvarchar](255) NULL,
[Response2] [nvarchar](255) NULL,
[Response3] [nvarchar](255) NULL,
[Response4] [nvarchar](255) NULL,
[comments] [nvarchar](255) NULL,
[Language] [nvarchar](255) NULL,
[resolver] [nvarchar](255) NULL,
[Product] [nvarchar](255) NULL,
[Agent_TSCS] [nvarchar](255) NULL,
[SITE] [nvarchar](255) NULL,
[YYYY_MM] [nvarchar](255) NULL,
[YYYY_WK] [nvarchar](255) NULL,
[CS_Queue] [nvarchar](255) NULL,
[EN_JP] [nvarchar](255) NULL,
[No_Queue] [nvarchar](255) NULL,
[Product_Other] [nvarchar](255) NULL
) ON [PRIMARY]



the other tables structure:

CREATE TABLE [dbo].[Correspondences_All](
[UserLogin] [varchar](max) NULL,
[comm_id] [bigint] NULL,
[CaseID] [int] NULL,
[Creation_Date] [datetime] NULL,
[Supervisor] [varchar](max) NULL,
[YearWeek] [int] NULL
) ON [PRIMARY]




here is the tSQL used to pull the data I need:

SELECT top 50 ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor]
FROM HMD hm
join Correspondences_All ca on hm.[caseid]=ca.caseid
where [comments] not in ('null','')



I have attached an XLS, this shows the output of the query there are 2 sheets (sanatised of course)
1. What I am getting
2. what I hope to get out of the query

As you can see with the second set of results, its filtered out the duplicate comments that are the same, unless the 'userlogin' is different.

I hope this is what you are after, if I am still missing something please let me know I will do my best to get it up here.
Attachments
Data.xlsx (12 views, 12.00 KB)
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
Good job with the DDL and the required output, we are only missing the sample data now.....take your time and make a dummy sample data set that looks like or is closest to the actual data you have(we do not want the actual data....just something that gives us a picture of the actual data) over the weekend.....post it as a string of insert statements.....that would definitely get you the best solution. :-)

Enjoy your weekend!!:-)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7965 Visits: 9417
Adding DISTINCT to the query will give you the results required as per the spreadsheet.

Two observations.
1. CaseID has different data type in the two tables, presume this is a typo!
2. The query has no ORDER BY. The TOP 50 you slelect could be any 50 rows, is this correct?

p.s. Also noticed your WHERE clause is is ignoring comments containing the text 'null', is this correct or are you really trying to ignore comments containing null, the two are not the same.


Far away is close at hand in the images of elsewhere.

Anon.


Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 132
Hi Vinu,

the excel sheet has both the data output and the expected result from what I am trying to achieve. There are 2 sheets on the excel document. Is this what you needed or I am misunderstanding you?

David to answer you:
1. CaseID has different data type in the two tables, presume this is a typo!

Case ID will vary, if you see 2 caseID's that are the same on the second sheet (what I need tab), its because the user ID is different, for this reason I cant't use a distinct on the comments, as this will give me just the single comment and miss out on the users.


2. The query has no ORDER BY. The TOP 50 you slelect could be any 50 rows, is this correct?
Yes this is correct.


p.s. Also noticed your WHERE clause is is ignoring comments containing the text 'null', is this correct or are you really trying to ignore comments containing null, the two are not the same.

This is also right oddly enough the data has both a NULL and blank, I need these filtered out as they have no relevant data I need.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24198 Visits: 37963
Brad Marsh (3/7/2013)
Ok was able to get this done now:

Here is the table structures

CREATE TABLE [dbo].[HMD](
[WeekNum] [float] NULL,
[MonthName] [nvarchar](255) NULL,
[Response1_Num] [float] NULL,
[Response2_Num] [float] NULL,
[Response3_Num] [float] NULL,
[Response4_Num] [float] NULL,
[Customer Id] [nvarchar](255) NULL,
[Email_Queue_Name] [nvarchar](255) NULL,
[Session Id] [nvarchar](255) NULL,
[Time_stamp] [float] NULL,
[CaseID] [nvarchar](255) NULL,
[Response] [nvarchar](255) NULL,
[Response1] [nvarchar](255) NULL,
[Response2] [nvarchar](255) NULL,
[Response3] [nvarchar](255) NULL,
[Response4] [nvarchar](255) NULL,
[comments] [nvarchar](255) NULL,
[Language] [nvarchar](255) NULL,
[resolver] [nvarchar](255) NULL,
[Product] [nvarchar](255) NULL,
[Agent_TSCS] [nvarchar](255) NULL,
[SITE] [nvarchar](255) NULL,
[YYYY_MM] [nvarchar](255) NULL,
[YYYY_WK] [nvarchar](255) NULL,
[CS_Queue] [nvarchar](255) NULL,
[EN_JP] [nvarchar](255) NULL,
[No_Queue] [nvarchar](255) NULL,
[Product_Other] [nvarchar](255) NULL
) ON [PRIMARY]



the other tables structure:

CREATE TABLE [dbo].[Correspondences_All](
[UserLogin] [varchar](max) NULL,
[comm_id] [bigint] NULL,
[CaseID] [int] NULL,
[Creation_Date] [datetime] NULL,
[Supervisor] [varchar](max) NULL,
[YearWeek] [int] NULL
) ON [PRIMARY]




here is the tSQL used to pull the data I need:

SELECT top 50 ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor]
FROM HMD hm
join Correspondences_All ca on hm.[caseid]=ca.caseid
where [comments] not in ('null','')



I have attached an XLS, this shows the output of the query there are 2 sheets (sanatised of course)
1. What I am getting
2. what I hope to get out of the query

As you can see with the second set of results, its filtered out the duplicate comments that are the same, unless the 'userlogin' is different.

I hope this is what you are after, if I am still missing something please let me know I will do my best to get it up here.



Now, if you will take your sample data from the Excel spreadsheet and create a series of INSERT INTO statements to load your tables, I would be willing to work on this some more.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 132
No problems its on its way, thanks!
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 132
Hopefully this is what you are after?

is this what you are after?

drop table testdata
Create table testdata (
Caseid [Varchar](30) NULL,
weeknum [float] NULL,
[monthName][nvarchar](255) NULL,
Response1_Num [float] NULL,
Response2_Num [float] NULL,
Response3_Num [float] NULL,
Response4_Num [float] NULL,
comments [nvarchar](max) NULL,
userlogin [varchar](max) NULL,
supervisor [varchar](max) NULL )


insert into TestData
Select 42372993071,3,'January',5,5,5,5,'We are still experiencing a problem with our job','User1','manager1'
Union ALL
select 42372993071,3,'January',5,5,5,5,'We are still experiencing a problem with our job','User1','manager1'
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User3','manager1' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User3','manager1' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User3','manager1' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23176661271,8,'February',3,1,1,1,'This issue required a lot of pushing from customers and executives to get any answer and was pending for many months. I would like to see better response to such a critical issue in the future.','User2','manager2' Union ALL
Select 23178234921,1,'January',3,3,1,1,'The problems cleared up on their own','User4','manager3' Union ALL
Select 12279616281,2,'January',3,2,1,1,'I just wanted more info on the issue that','User5','manager4' Union ALL
Select 45579639411,2,'January',5,5,5,5,'took some time to address the issue, but it wasnt supports fault I would guess. Thanks. Hoping to get this done faster in future','User5','manager4' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User7','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 67580187871,7,'February',5,5,5,5,'Prieur is awesome!','User6','manager5' Union ALL
Select 45680356651,1,'January',5,5,5,5,'Excellence follow up!','User8','manager6' Union ALL
Select 45680356651,1,'January',5,5,5,5,'Excellence follow up!','User8','manager6' Union ALL
Select 45680356651,1,'January',5,5,5,5,'Excellence follow up!','User8','manager6' Union ALL
Select 45680356651,1,'January',5,5,5,5,'Excellence follow up!','User8','manager6' Union ALL
Select 42380383401,1,'January',4,3,4,3,'lacks the functionality to have resolved the issue. Instead, we had to workaround the limited functionality and delete all our content and re-upload it to resolve the permissions issue.','User9','manager6' Union ALL
Select 42380383401,1,'January',4,3,4,3,'lacks the functionality to have resolved the issue. Instead, we had to workaround the limited functionality and delete all our content and re-upload it to resolve the permissions issue.','User9','manager6' Union ALL
Select 42380383401,1,'January',4,3,4,3,'lacks the functionality to have resolved the issue. Instead, we had to workaround the limited functionality and delete all our content and re-upload it to resolve the permissions issue.','User9','manager6' Union ALL
Select 42380383401,1,'January',4,3,4,3,'lacks the functionality to have resolved the issue. Instead, we had to workaround the limited functionality and delete all our content and re-upload it to resolve the permissions issue.','User9','manager6' Union ALL
Select 12380387151,4,'January',5,5,5,5,'I could browse http://xxxxx','User10','manager7' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User12','manager9' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8' Union ALL
Select 45280540011,8,'February',5,5,5,5,'xxxxx handled this case and even though there was little he personally could do in relation to the resolution, he worked to ensure it was placed at the right level for assistance and continued to follow up and ensure a relaxed client. For this, I would','User11','manager8'




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search