March 8, 2013 at 2:31 am
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.
March 10, 2013 at 3:15 pm
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.
March 10, 2013 at 3:34 pm
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.
March 10, 2013 at 4:28 pm
No problems its on its way, thanks!
March 10, 2013 at 6:06 pm
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'
March 11, 2013 at 3:16 am
Brad Marsh (3/10/2013)
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.
I was referring to the data type not the data
CaseID in HMD is nvarchar and Correspondences_All is int (note that it cannot be int as the case id numbers are too big)
The use of DISTINCT in the query you posted will not miss out on the users as it will give you distinct rows, as in
SELECT DISTINCT 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 ran this against the data in the first sheet your spreadsheet and it returned exactly the result in the second sheet.
'NULL' (with quotes) and NULL (without quotes) are not the same. If the column contains NULL then use NULL without quotes, if the column contains the text NULL then use quotes.
Far away is close at hand in the images of elsewhere.
Anon.
March 11, 2013 at 3:24 am
David - perfect thank you so very much for this!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply