Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL join by date

Posted on 2013-11-14
11
Medium Priority
?
282 Views
Last Modified: 2013-11-15
We have been asked to create a phone log report, in which I need to tie our system database to our phone system database. Both systems run Microsoft SQL 2005. I am not a SQL novice, but I am no expert either. Here is what I have...

A legal database with 2 tables

Table: Claims
ClaimID (PK)
FileNumber


Table Comments:
CommentID (PK)
ClaimID
Comment - The comments I will be pulling are associated with a phone call
Comment Date and Time

Our phone system has a CallLog table.
CallID
StartTime
StopTime
AccountCode  - the account code is populated with FileNumbers from the claim table above

My goal is to create a report that will look like this:
FileNumber
Comment
CallDuration

I have joined the Claim table to the CallLog table, and everything works great if there is only one 'call' comment per day (the report is daily). My problem is when there is more than one call on a Claim per day...

What I would like is this...(each line representing an individual call)
ClaimA   11/14 8:00AM  20 (duration)
ClaimA   11/14 10:00AM 100 (duration)
Claim A  11/14 12:00PM 150 (duration)

What I am getting is this...(appears to just grab the first time)
ClaimA   11/14 8:00AM  20 (duration)
ClaimA   11/14 10:00AM 20(duration)
Claim A  11/14 12:00PM 20 (duration)

I realize my join isn't ideal, but is there anyway to the data so I get results that I want?
0
Comment
Question by:ZS-Tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39650061
Well the answer is yes, but you will want more. So do I.

You could do this:

select * from Claims
inner join CallLog on Claims.FileNumber = CallLog.AccountCode  

or this:

select * from Claims
left join CallLog on Claims.FileNumber = CallLog.AccountCode  

or you could reverse the table positions and each variant could give you slightly different results (depending on data quality basically)

Is the primary focus claim or call? e.g.
Are you selecting for a particular Claim (or set of claims)?
or
Are you selecting over a range of dates and want to know what claims are involved?

I assume it does not matter that if no call is made on a date then that date is not listed.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39650088
I have deliberately setup sample data where:
a Claim exists with no calls
a call exists with no claim

Below are results of 4 query variants, the purpose being I'm trying to understand what you consider to be important
    	([ClaimID] int, [FileNumber] varchar(20))
    ;
    	
    INSERT INTO Claims
    	([ClaimID], [FileNumber])
    VALUES
    	(1, 'Claim A'),
    	(2, 'Claim B')
    ;
    
    CREATE TABLE CallLog
    	([CallID] int, [StartTime] datetime, [StopTime] datetime, [AccountCode] varchar(20))
    ;
    	
    INSERT INTO CallLog
    	([CallID], [StartTime], [StopTime], [AccountCode])
    VALUES
    	(1, '2013-10-23 09:00:00', '2013-10-23 09:20:00', 'Claim A'),
    	(2, '2013-10-23 09:00:00', '2013-10-23 09:20:00', NULL)
    ;
    
    CREATE TABLE Comments
    	([CommentID] int, [ClaimID] int, [Comment] varchar(200), [Comment_DT] datetime)
    ;
    	
    INSERT INTO Comments
    	([CommentID], [ClaimID], [Comment], [Comment_DT])
    VALUES
    	(1, 1, '- The comments I will be pulling are associated with a phone call', '2013-10-23 09:15:00')
    ;

**Query 1**:

    select
            Claims.FileNumber
          , Comments.Comment
          , datediff(minute,CallLog.StartTime,CallLog.StopTime) as Call_Duration_Min
          , convert(varchar,CallLog.StartTime,120) as Call_Start
    from Claims
    inner join CallLog on Claims.FileNumber = CallLog.AccountCode 
    left join Comments on Claims.ClaimID = comments.ClaimID
    
    

**[Results][2]**:
    
    | FILENUMBER |                                                           COMMENT | CALL_DURATION_MIN |          CALL_START |
    |------------|-------------------------------------------------------------------|-------------------|---------------------|
    |    Claim A | - The comments I will be pulling are associated with a phone call |                20 | 2013-10-23 09:00:00 |


**Query 2**:

    select
            Claims.FileNumber
          , Comments.Comment
          , datediff(minute,CallLog.StartTime,CallLog.StopTime) as Call_Duration_Min
          , convert(varchar,CallLog.StartTime,120) as Call_Start
    from Claims
    left join CallLog on Claims.FileNumber = CallLog.AccountCode 
    left join Comments on Claims.ClaimID = comments.ClaimID
    
    

**[Results][3]**:
    
    | FILENUMBER |                                                           COMMENT | CALL_DURATION_MIN |          CALL_START |
    |------------|-------------------------------------------------------------------|-------------------|---------------------|
    |    Claim A | - The comments I will be pulling are associated with a phone call |                20 | 2013-10-23 09:00:00 |
    |    Claim B |                                                            (null) |            (null) |              (null) |


**Query 3**:

    select
            Claims.FileNumber
          , Comments.Comment
          , datediff(minute,CallLog.StartTime,CallLog.StopTime) as Call_Duration_Min
          , convert(varchar,CallLog.StartTime,120) as Call_Start
    from CallLog
    inner join Claims on CallLog.AccountCode = Claims.FileNumber
    left join Comments on Claims.ClaimID = comments.ClaimID
    
    

**[Results][4]**:
    
    | FILENUMBER |                                                           COMMENT | CALL_DURATION_MIN |          CALL_START |
    |------------|-------------------------------------------------------------------|-------------------|---------------------|
    |    Claim A | - The comments I will be pulling are associated with a phone call |                20 | 2013-10-23 09:00:00 |


**Query 4**:

    select
            Claims.FileNumber
          , Comments.Comment
          , datediff(minute,CallLog.StartTime,CallLog.StopTime) as Call_Duration_Min
          , convert(varchar,CallLog.StartTime,120) as Call_Start
    from CallLog
    left join Claims on CallLog.AccountCode = Claims.FileNumber
    left join Comments on Claims.ClaimID = comments.ClaimID
    
    

**[Results][5]**:
    
    | FILENUMBER |                                                           COMMENT | CALL_DURATION_MIN |          CALL_START |
    |------------|-------------------------------------------------------------------|-------------------|---------------------|
    |    Claim A | - The comments I will be pulling are associated with a phone call |                20 | 2013-10-23 09:00:00 |
    |     (null) |                                                            (null) |                20 | 2013-10-23 09:00:00 |



  [1]: http://sqlfiddle.com/#!3/48114/9

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39650103
By the way, the question title "SQL join by date" might indicate something entirely different

but
My goal is to create a report that will look like this:
FileNumber
Comment
CallDuration
That report layout does not seem to relate to the title.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:mankowitz
ID: 39650117
zs, in order for us to help, I think you will need to post some sample data so that we can tell why the results you expect are not the results you are getting. In the example you give, I have no way of knowing why the numbers are wrong.

For example, I would use this query:

select c.filenumber, cm.comment, cl.starttime, (cl.stoptime-cl.starttime) duration
from CallLog cl join claims c on (cl.accountcode=c.filenumber)
join comments cm on (c.claimid=cm.claimID)
0
 

Author Comment

by:ZS-Tech
ID: 39651474
CREATE TABLE [dbo].[XCLAIMS](
      [ClaimID] [int] IDENTITY(1,1) NOT NULL,
      [FileNumber] [varchar](10) NULL,
 CONSTRAINT [PK_XCLAIMS] PRIMARY KEY CLUSTERED
(
      [ClaimID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[XCALLS](
      [CallID] [int] IDENTITY(1,1) NOT NULL,
      [StartTime] [datetime] NULL,
      [StopTime] [datetime] NULL,
      [AccountCode] [varchar](10) NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[XCOMMENTS](
      [CommentID] [int] IDENTITY(1,1) NOT NULL,
      [ClaimID] [int] NULL,
      [Comment] [varchar](50) NULL,
      [CommentDate] [datetime] NULL,
 CONSTRAINT [PK_XComment] PRIMARY KEY CLUSTERED
(
      [CommentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


insert into xclaims (filenumber)
values ('11-11111')
insert into xclaims (filenumber)
values ('12-22222')
insert into xclaims (filenumber)
values ('13-33333')

insert into xcomments (claimid,comment,commentdate)
values (1,'Made Contact','2013-11-14 08:00:00.000')
insert into xcomments (claimid,comment,commentdate)
values (1,'Made Contact Again','2013-11-14 10:00:00.000')
insert into xcomments (claimid,comment,commentdate)
values (1,'Made Contact Yet Again','2013-11-14 12:00:00.000')

insert into xcalls (starttime,stoptime,accountcode)
values ('2013-11-14 07:59:00.000','2013-11-14 08:05:00.000','11-11111')
insert into xcalls (starttime,stoptime,accountcode)
values ('2013-11-14 09:58:00.000','2013-11-14 10:06:00.000','11-11111')
insert into xcalls (starttime,stoptime,accountcode)
values ('2013-11-14 11:57:00.000','2013-11-14 12:07:00.000','11-11111')


I guess what I was getting at in my original post about 'joining by date', is that i'm trying to associate the  xcomment.commentdate to the closest xcalls.starttime.

ok...so my bad query and results...
select xclaims.filenumber,xcomments.comment,-(datediff(second,xcalls.stoptime,xcalls.starttime)) as duration
from xclaims inner join
xcomments on xclaims.claimid=xcomments.claimid inner join
xcalls on xclaims.filenumber = xcalls.accountcode

filenumber      comment      duration
11-11111      Made Contact      360
11-11111      Made Contact      480
11-11111      Made Contact      600
11-11111      Made Contact Again      360
11-11111      Made Contact Again      480
11-11111      Made Contact Again      600
11-11111      Made Contact Yet Again      360
11-11111      Made Contact Yet Again      480
11-11111      Made Contact Yet Again      600

What I want is
filenumber      comment      duration
11-11111      Made Contact      360
11-11111      Made Contact Again      480
11-11111      Made Contact Yet Again      600
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 39651970
how can you know which comment refers to which call? Both are linked to the claimID, but there is no direct linkage between the call and the comment. In this example, we know that there are only 3 calls, and we can figure out from context which belongs to which.

For example, how do you know that the comment

values ('2013-11-14 11:57:00.000','2013-11-14 12:07:00.000','11-11111')

refers to

values (1,'Made Contact Yet Again','2013-11-14 12:00:00.000')

and not to

values (1,'Made Contact Again','2013-11-14 10:00:00.000')

See http://sqlfiddle.com/#!6/13824/6
0
 

Author Comment

by:ZS-Tech
ID: 39652047
Yeah, that appears to be my roadblock.  I need to find an elegant way to get the commentid into the call log table, so that everything can be joined up properly. I guess, at best, I was just hoping to find a shortcut way around that.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39652123
SELECT
    xcl.filenumber,
    xco.comment,
    (SELECT TOP (1) -(DATEDIFF(SECOND, xca.stoptime, xca.starttime)) AS duration
    FROM dbo.XCALLS xca
    WHERE
        xca.AccountCode = xcl.filenumber AND
        xco.commentdate >= xca.starttime
    ORDER BY
        xca.starttime DESC
    ) AS duration
FROM dbo.XCOMMENTS xco
INNER JOIN dbo.XCLAIMS xcl ON
    xcl.claimid = xco.claimid

NOTE: For best performance (at least for this specific query, and quite possibly for nearly all other common queries as well), you'll want to cluster dbo.XCALLS on ( AccountCode, StartTime ), NOT on CallID.
0
 

Author Comment

by:ZS-Tech
ID: 39652185
Scott, that worked perfectly. I really appreciate your help on this.  (P.S. I would have never figured that out)
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 39652345
That assumes that the comments were entered in the same order that the phone calls were made.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39652654
and it does not account for calls made without comments recorded, add this to the sample:

insert into xcalls (starttime,stoptime,accountcode)
values ('2013-11-15 10:11:12.000','2013-11-15 10:15:23.000','11-11111')

The call and duration should be seen in the result:
| FILENUMBER |                COMMENT | DURATION |
|------------|------------------------|----------|
|   11-11111 |           Made Contact |      360 |
|   11-11111 |     Made Contact Again |      480 |
|   11-11111 | Made Contact Yet Again |      600 |
|   11-11111 |                 (null) |      251 |

-- produced by

SELECT
        filenumber
      , comment
      , duration
FROM (
      SELECT
              xcl.filenumber
            , xco.comment
            , (DATEDIFF(SECOND, xca.starttime, xca.stoptime))                    AS duration
            , row_number() over (partition BY xca.callid ORDER BY xco.CommentID) AS rn
      FROM dbo.XCALLS xca
      INNER JOIN dbo.XCLAIMS xcl
              ON xca.AccountCode = xcl.filenumber
      LEFT JOIN XCOMMENTS xco
             ON xca.AccountCode = xcl.filenumber
            AND xco.commentdate between xca.starttime and xca.stoptime
     ) x
WHERE rn = 1
;

Open in new window

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question