SQL join by date

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.

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

Select allOpen in new window

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

but
That report layout does not seem to relate to the title.

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)

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

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

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.

Scott, that worked perfectly. I really appreciate your help on this.  (P.S. I would have never figured that out)

That assumes that the comments were entered in the same order that the phone calls were made.

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
;

Select allOpen in new window