Solved

SQL join by date

Posted on 2013-11-14
11
254 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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:
ScottPletcher 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now