Solved

SQL join by date

Posted on 2013-11-14
11
269 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
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 48

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 48

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 48

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 48

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

831 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