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 ««123»»

remove duplicates IF matches Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 9:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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
Post #1428359
Posted Thursday, March 7, 2013 9:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 20,798, Visits: 32,714
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.



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)
Post #1428365
Posted Thursday, March 7, 2013 9:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Point taken will do over the weekend - thanks guys.
Post #1428373
Posted Thursday, March 7, 2013 10:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
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.


  Post Attachments 
Data.xlsx (12 views, 12.06 KB)
Post #1428382
Posted Thursday, March 7, 2013 11:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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
Post #1428402
Posted Friday, March 8, 2013 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:02 AM
Points: 6,997, Visits: 7,155
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.

Post #1428456
Posted Sunday, March 10, 2013 3:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
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.


Post #1429025
Posted Sunday, March 10, 2013 3:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 20,798, Visits: 32,714
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.



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)
Post #1429027
Posted Sunday, March 10, 2013 4:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
No problems its on its way, thanks!
Post #1429029
Posted Sunday, March 10, 2013 6:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
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'



Post #1429040
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse