July 7, 2016 at 9:41 am
Is there a way to quickly wrap a string of text in the 'where in' statement?
I have 200+ numbers, that are changing, I need to quickly wrap.. ('500','404', etc etc.. is there a program that does this? or a trick? I've been doing it in excel - using =C3&D2&"','" - just want to know if there's a better way, thank you.
July 7, 2016 at 9:49 am
Why not do it like this?
WHERE MyCol IN (
SELECT MyNumber FROM MyOtherCol
)
John
July 7, 2016 at 9:51 am
Maybe instead of building up a big string of values maybe a table valued parameter would work? There is not a lot of detail in your question so it is hard to know what might be the best approach for your situation.
_______________________________________________________________
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/
July 7, 2016 at 9:53 am
I have to pull data, from a phone system, but I have to pull the data on a specific group of phone extensions -
SELECT
CONVERT (varchar,TimeStamp,1) AS 'DATE'
,[AgentLogin] as Phone_EXT
,[AgentSurName]+', '+[AgentGivenName] as 'Agent Name'
,[SupervisorSurName]+', '+[SupervisorGivenName] as 'TeamLeader'
,[BusyOnDNTime]
,sum([CallsOffered]) as Calls_Off
,sum([CallsAnswered]) as Calls_Ans
,sum([HoldTime]) as HoldTime
,sum([TalkTime]) as TalkTime
,sum([PostCallProcessingTime]) as ACW
,sum([DNInExtCalls]) as Ext_In_Calls
,sum([DNInExtCallsTalkTime]) as Ext_In_Calls_TalkTime
,sum([DNInIntCalls]) as Int_In_Calls
,sum([DNInIntCallsTalkTime]) as Int_In_Calls_TalkTime
,sum([DNOutExtCalls]) as OutBound_Calls
,Sum([DNOutExtCallsTalkTime]) as OutBound_TalkTime
,sum([DNOutIntCalls]) as Interncal_OB_Calls
,sum([DNOutIntCallsTalkTime]) as Internal_OB_Calls_TalkTime
,sum([LoggedInTime]) as LoggedInTime
,sum([NotReadyTime]) as NotReadyTime
,[Time] as Interval
,sum([DNOutExtCallsHoldTime]) as OB_Calls_Hold_Time
,sum([DNOutIntCallsHoldTime]) as Internal_calls_hold_time
,sum([NumberTimesNotReady]) as Time_Entered_NotReady
--,sum([ContactTalkTime]) as Total_Contact_TalkTime **ContactTalkTime related to the talk time for multimeida contacts, example, Phone, Email, Chat would be sumed here as totalk contact time**
--,sum([ContactHoldTime]) as Total_Contact_HoldTime
FROM [LDW_Avaya].[dbo].[dAgentPerformanceStat] --use [iAgentPerformanceStat] for interval data
where [AgentGivenName] is not null
and [AgentLogin] in ('447','536','574','456','487','537','523','554','579','548','378','494','611','571','572','539','612','546','486','477','522','538','541','582','540','573','514','465','389','543','564','301','504','462','549','617','435','396','581','534','547','457','502','567','559','578','557','483','568','479','535','569','337','616','274','339','461','558','516','553','511','530','338','614','563','570','513','613','532','618','550','615','520','518')
--and [AgentSurName] = 'Pham'
group by [AgentLogin]
,[AgentSurName]
,[AgentGivenName]
,[SupervisorSurName]
,[SupervisorGivenName]
,[BusyOnDNTime]
,[TimeStamp]
,[Time]
order by 1,3
July 7, 2016 at 9:54 am
John Mitchell-245523 (7/7/2016)
Why not do it like this?
WHERE MyCol IN (SELECT MyNumber FROM MyOtherCol
)
John
Not sure how do this, haven't done it.... how does it work?
July 7, 2016 at 9:56 am
bcrockett (7/7/2016)
I have to pull data, from a phone system, but I have to pull the data on a specific group of phone extensions -SELECT
CONVERT (varchar,TimeStamp,1) AS 'DATE'
,[AgentLogin] as Phone_EXT
,[AgentSurName]+', '+[AgentGivenName] as 'Agent Name'
,[SupervisorSurName]+', '+[SupervisorGivenName] as 'TeamLeader'
,[BusyOnDNTime]
,sum([CallsOffered]) as Calls_Off
,sum([CallsAnswered]) as Calls_Ans
,sum([HoldTime]) as HoldTime
,sum([TalkTime]) as TalkTime
,sum([PostCallProcessingTime]) as ACW
,sum([DNInExtCalls]) as Ext_In_Calls
,sum([DNInExtCallsTalkTime]) as Ext_In_Calls_TalkTime
,sum([DNInIntCalls]) as Int_In_Calls
,sum([DNInIntCallsTalkTime]) as Int_In_Calls_TalkTime
,sum([DNOutExtCalls]) as OutBound_Calls
,Sum([DNOutExtCallsTalkTime]) as OutBound_TalkTime
,sum([DNOutIntCalls]) as Interncal_OB_Calls
,sum([DNOutIntCallsTalkTime]) as Internal_OB_Calls_TalkTime
,sum([LoggedInTime]) as LoggedInTime
,sum([NotReadyTime]) as NotReadyTime
,[Time] as Interval
,sum([DNOutExtCallsHoldTime]) as OB_Calls_Hold_Time
,sum([DNOutIntCallsHoldTime]) as Internal_calls_hold_time
,sum([NumberTimesNotReady]) as Time_Entered_NotReady
--,sum([ContactTalkTime]) as Total_Contact_TalkTime **ContactTalkTime related to the talk time for multimeida contacts, example, Phone, Email, Chat would be sumed here as totalk contact time**
--,sum([ContactHoldTime]) as Total_Contact_HoldTime
FROM [LDW_Avaya].[dbo].[dAgentPerformanceStat] --use [iAgentPerformanceStat] for interval data
where [AgentGivenName] is not null
and [AgentLogin] in ('447','536','574','456','487','537','523','554','579','548','378','494','611','571','572','539','612','546','486','477','522','538','541','582','540','573','514','465','389','543','564','301','504','462','549','617','435','396','581','534','547','457','502','567','559','578','557','483','568','479','535','569','337','616','274','339','461','558','516','553','511','530','338','614','563','570','513','613','532','618','550','615','520','518')
--and [AgentSurName] = 'Pham'
group by [AgentLogin]
,[AgentSurName]
,[AgentGivenName]
,[SupervisorSurName]
,[SupervisorGivenName]
,[BusyOnDNTime]
,[TimeStamp]
,[Time]
order by 1,3
Where do you get the list of values you need to use? Could you write a query instead?
Also, you really should stop ordering by ordinal position. It is dangerous (and confusing). If you change the column list and add a new column in the second position you order is going to be all screwy. You should order by the name of the column instead.
_______________________________________________________________
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/
July 7, 2016 at 9:57 am
Where does that list of phone extensions come from - another table, a spreadsheet, somewhere else?
John
July 7, 2016 at 10:00 am
John Mitchell-245523 (7/7/2016)
Where does that list of phone extensions come from - another table, a spreadsheet, somewhere else?John
I get them from an excel file -
July 7, 2016 at 10:03 am
Sean Lange (7/7/2016)
bcrockett (7/7/2016)
I have to pull data, from a phone system, but I have to pull the data on a specific group of phone extensions -Where do you get the list of values you need to use? Could you write a query instead?
Also, you really should stop ordering by ordinal position. It is dangerous (and confusing). If you change the column list and add a new column in the second position you order is going to be all screwy. You should order by the name of the column instead.
thanks for the info on the group by..
The phone exts, I get are from a spread sheet - every phone etx exist in the tables, but I am only pulling the phone exts, for a particular department, and we don't have department specific tables/columns, I could just join on - so I have to filter by phone ext.. if that makes sense?
July 7, 2016 at 10:16 am
bcrockett (7/7/2016)
Sean Lange (7/7/2016)
bcrockett (7/7/2016)
I have to pull data, from a phone system, but I have to pull the data on a specific group of phone extensions -Where do you get the list of values you need to use? Could you write a query instead?
Also, you really should stop ordering by ordinal position. It is dangerous (and confusing). If you change the column list and add a new column in the second position you order is going to be all screwy. You should order by the name of the column instead.
thanks for the info on the group by..
The phone exts, I get are from a spread sheet - every phone etx exist in the tables, but I am only pulling the phone exts, for a particular department, and we don't have department specific tables/columns, I could just join on - so I have to filter by phone ext.. if that makes sense?
So you are manually editing this query based on the contents of your spreadsheet?
_______________________________________________________________
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/
July 7, 2016 at 10:19 am
So your excel file should be containing the department I guess. The proper way is to import the excel to a table and then you can use it as a join or a sub query as already suggested. If you are allowed to, probably you might want to add the dept column in your model.
But if it is just a one time analysis, if you have not figured it by now, one way is to copy all values to the query analyzer text editor and using regular expression, replace /n with ,
July 7, 2016 at 10:30 am
Sean Lange (7/7/2016)
bcrockett (7/7/2016)
Sean Lange (7/7/2016)
bcrockett (7/7/2016)
I have to pull data, from a phone system, but I have to pull the data on a specific group of phone extensions -Where do you get the list of values you need to use? Could you write a query instead?
Also, you really should stop ordering by ordinal position. It is dangerous (and confusing). If you change the column list and add a new column in the second position you order is going to be all screwy. You should order by the name of the column instead.
thanks for the info on the group by..
The phone exts, I get are from a spread sheet - every phone etx exist in the tables, but I am only pulling the phone exts, for a particular department, and we don't have department specific tables/columns, I could just join on - so I have to filter by phone ext.. if that makes sense?
So you are manually editing this query based on the contents of your spreadsheet?
To some extent yes - usually just what's in the where in statement - this query I have, i use for more adhoc reports.
July 7, 2016 at 10:31 am
Sean Lange (7/7/2016)
bcrockett (7/7/2016)
Sean Lange (7/7/2016)
bcrockett (7/7/2016)
I have to pull data, from a phone system, but I have to pull the data on a specific group of phone extensions -Where do you get the list of values you need to use? Could you write a query instead?
Also, you really should stop ordering by ordinal position. It is dangerous (and confusing). If you change the column list and add a new column in the second position you order is going to be all screwy. You should order by the name of the column instead.
thanks for the info on the group by..
The phone exts, I get are from a spread sheet - every phone etx exist in the tables, but I am only pulling the phone exts, for a particular department, and we don't have department specific tables/columns, I could just join on - so I have to filter by phone ext.. if that makes sense?
So you are manually editing this query based on the contents of your spreadsheet?
sorry for double posting..
July 8, 2016 at 2:09 am
OK, so import the extensions using SSIS , bcp, BULK IMPORT, OPENROWSET or however else is easiest, as Joe suggested, and then use a query like the one I post earlier to do the filtering.
John
July 9, 2016 at 2:00 am
I do this every day - Excel sheets are sent to me and I must use a column as part of an IN statement.
Here is what I do - takes me 30 seconds:
1) I take the column I need and I paste it into a new worksheet at A2 - so my data is in A2 to A102 (for example if there were 100 lines)
2) I put the following easy formula into B2
=B1&"','"&A2
3) I copy the formula down
4) I take the contents of B102 (still using my example of 100 lines) and copy it into the IN statement
5) There is a small tidy up - remove the ', at the start and add a ' at the end
DONE 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply