June 19, 2013 at 11:35 am
sorry for the nature of the question, I just don't know how to write this query.
I have 2 tables, one is a user table and the other is phone data from my phone system, I am just trying to write a query to join the 2 tables together where I can get the user (fullname) from the User_data table to join each users from the phone column to both the callingparty and calledparty field from the CDR_Data table. Basically all the records from the CDR_Data table where both the callingparty field and the calledparty field are joined to the phone column on the User_data table so I can see both types of calls for each user. Any help is appreciated, thanks!
My 2 tables are as follows:
CDR_Data
ID
Datetimeorigination (datetime)
callingparty (int)
calledparty (int)
datetimeconnect (datetime)
datetimedisconnect (datetime)
duration (int)
User_data
fullname (char)
phone (int)
June 19, 2013 at 12:15 pm
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for 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/
June 19, 2013 at 12:27 pm
Ok, thanks. This is my call data table:
INSERT INTO [Cisco_CallManager].[dbo].[CDR]
([datetimeorigination]
,[callingpartynumber]
,[originalcalledpartynumber]
,[finalcalledpartynumber]
,[datetimeconnect]
,[datetimedisconnect]
,[duration])
VALUES
(<datetimeorigination, numeric(18,0),>
,<callingpartynumber, numeric(18,0),>
,<originalcalledpartynumber, numeric(18,0),>
,<finalcalledpartynumber, numeric(18,0),>
,<datetimeconnect, numeric(18,0),>
,<datetimedisconnect, numeric(18,0),>
,<duration, numeric(18,0),>)
GO
And this is my user table:
INSERT INTO [Cisco_CallManager].[dbo].[vw_journyxphones]
([user_login]
,[fullname]
,[Phone])
VALUES
(<user_login, nvarchar(500),>
,<fullname, nvarchar(500),>
,<Phone, float,>)
GO
My expected results would be all the data from the call data table grouped by fullname from the user table, where the users phone is either the callingpartynumber or the originalcalledpartynumber.
hope that helps, thanks again!
June 19, 2013 at 12:31 pm
NO we need to have actual DDL and sample data. As I said previously in the form of insert statements. I should be able to hit F5 and have a table populated with data. What you posted is a template for an insert statement. I need the data and the table to put it in.
_______________________________________________________________
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/
June 19, 2013 at 1:04 pm
Thanks, but the data has personal information in it (full names, and phone numbers) so I was trying to avoid putting that on the internet.
June 19, 2013 at 1:10 pm
ericb1 (6/19/2013)
Thanks, but the data has personal information in it (full names, and phone numbers) so I was trying to avoid putting that on the internet.
Well we certainly don't want actual data...that is why I said sample data. The idea here is that if you don't create some dummy data then I have to. Since we are all volunteers around here many people won't even consider looking at posts that don't have this.
_______________________________________________________________
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/
June 19, 2013 at 1:35 pm
OK I looked at your original post over and over and I think I figured out what you are asking. See if this query is close to what you are looking for.
select * from User_Data u
join CDR_DAta d on u.Phone = callingparty or u.Phone = calledparty
_______________________________________________________________
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/
June 21, 2013 at 3:26 pm
that worked great, thank you!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply