• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1503
  • Last Modified:

MS SQL, Select top 1 data from two table to compare time difference

I am using MS-SQL 2005.
I have two tables called 'SalesLeadsBoard' and 'SalesLeadsReply'
And colums below

SalesLeadsBoard                     SalesLeadsReply
----------------------                    ------------------------
SalesLeadsBoardID                  SalesLeadsReplyID
                                                  StatusTypeID
                                                  RegisterDate

Select sl.SalesleadsBoardID,slr.RegisterDate as 'Assigned Date',con.RegisterDate as 'Contacted Date'
,CASE WHEN con.RegisterDate IS NULL THEN ''
                ELSE
                        CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)/3600)) AS VARCHAR) + ':' + CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)/60) AS VARCHAR) + ':' + CAST((((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)%60)%60) AS VARCHAR)
                END AS DurationTime
from salesleadsboard sl
Left Join SalesleadsReply slr ON slr.Salesleadsboardid=sl.salesleadsboardid and slr.statustypeid='12'
Left Join SalesleadsReply con ON con.Salesleadsboardid=sl.salesleadsboardid and con.statustypeid='14'


I was trying to get a time difference and it worked fine. The only problem is sometimes there are more than one 'Assign Date' and more than one 'Contacted Date' on each Salesleadsboard.
So I wanted to just select top 1 of 'Assign Date' and top 1 of 'Contacted Date'
How do I implement this to above query?
0
erin027
Asked:
erin027
  • 6
  • 4
1 Solution
 
SharathData EngineerCommented:
Question -
do you have more than one record in SalesLeadsReply  table for the combination of SalesLeadsBoardID and StatusTypeID?
can you provide some sample data?
0
 
erin027Author Commented:
Yes, sometimes it has more record or sometimes it has no record
Here are the current result:

SalesleadsBoardID      Assigned Date    Contacted Date       Duration Time
------------------------------------------------------------------------------------------
31                                2008-11-08          Null                            
33                                2008-11-09          2008-11-09             0:3:40
33                                2008-11-09          2008-11-09             0:3:35

Notice there are two Assigned Date?
I wanted to just select first Assigned Date and First Contacted Date record so the result will be like below:

SalesleadsBoardID      Assigned Date    Contacted Date       Duration Time
------------------------------------------------------------------------------------------
31                                2008-11-08          Null                            
33                                2008-11-09          2008-11-09             0:3:40

Thank you for your help!

0
 
SharathData EngineerCommented:
check this:

SELECT B.SalesleadsBoardID AS SalesleadsBoardID, B.Assigned_Date AS 'Assigned Date', B.Contacted_Date AS 'Contacted Date',B.DurationTime AS DurationTime
FROM

(Select sl.SalesleadsBoardID,slr.RegisterDate as 'Assigned_Date',con.RegisterDate as 'Contacted_Date'
,CASE WHEN con.RegisterDate IS NULL THEN ''
                ELSE
                        CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)/3600)) AS VARCHAR) + ':' + CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)/60) AS VARCHAR) + ':' + CAST((((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)%60)%60) AS VARCHAR)
                END AS DurationTime
from salesleadsboard sl
Left Join SalesleadsReply slr ON slr.Salesleadsboardid=sl.salesleadsboardid and slr.statustypeid='12'
Left Join SalesleadsReply con ON con.Salesleadsboardid=sl.salesleadsboardid and con.statustypeid='14') B

JOIN

(SELECT SalesleadsBoardID, Assigned_Date, MAX(DurationTime) AS Max_DurationTime FROM(
Select sl.SalesleadsBoardID, slr.RegisterDate as 'Assigned_Date',con.RegisterDate as 'Contacted_Date'
,CASE WHEN con.RegisterDate IS NULL THEN ''
                ELSE
                        CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)/3600)) AS VARCHAR) + ':' + CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)/60) AS VARCHAR) + ':' + CAST((((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)%60)%60) AS VARCHAR)
                END AS DurationTime
from salesleadsboard sl
Left Join SalesleadsReply slr ON slr.Salesleadsboardid=sl.salesleadsboardid and slr.statustypeid='12'
Left Join SalesleadsReply con ON con.Salesleadsboardid=sl.salesleadsboardid and con.statustypeid='14') A
GROUP BY SalesleadsBoardID,Assigned_Date) C

ON B.SalesleadsBoardID = C.SalesleadsBoardID AND B.Assigned_Date = C.Assigned_Date AND B.DurationTime = C.Max_DurationTime
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jamesguCommented:
did you try group by?

Select sl.SalesleadsBoardID
,min(slr.RegisterDate as 'Assigned Date')
,min(con.RegisterDate as 'Contacted Date')
,min(CASE WHEN con.RegisterDate IS NULL THEN ''
                ELSE
                        CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)/3600)) AS VARCHAR) + ':' + CAST(((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)/60) AS VARCHAR) + ':' + CAST((((DATEDIFF(ss,slr.registerdate,con.registerdate)%3600)%60)%60) AS VARCHAR)
                END AS DurationTime)
from salesleadsboard sl
Left Join SalesleadsReply slr ON slr.Salesleadsboardid=sl.salesleadsboardid and slr.statustypeid='12'
Left Join SalesleadsReply con ON con.Salesleadsboardid=sl.salesleadsboardid and con.statustypeid='14'
group by sl.SalesleadsBoardID

or use your result a derived table, and then use group by on it


0
 
erin027Author Commented:
Sharath_123:
Thank you, but nothing has been changed except that all the record that has NULL doesn't display.
I want all the record to show.

Current Record:
SalesleadsBoardID      Assigned Date    Contacted Date       Duration Time
------------------------------------------------------------------------------------------
30                                2008-11-11          Null    
31                                2008-11-08          Null                            
33                                2008-11-09          2008-11-09             0:3:40
33                                2008-11-09          2008-11-09             0:3:35
33                                2008-11-11          2008-11-12             27:3:35

I WANT THE RESULT TO BE LIKE THIS
SalesleadsBoardID      Assigned Date    Contacted Date       Duration Time
------------------------------------------------------------------------------------------
30                                2008-11-11          Null    
31                                2008-11-08          Null                            
33                                2008-11-09          2008-11-09             0:3:40



jamesgu:
I don't think it should group it. I just want to select SalesLeadsBoardID, and Time Differences Between  First 'Assigned Date' and First 'Contacted Date'

0
 
SharathData EngineerCommented:
Can you provide the result set of my query?
0
 
erin027Author Commented:
1st result is mine and 2nd one is your query.

SalesleadsBoardID Assigned Date           Contacted Date          DurationTime
--------------  ------------------------------   -----------------------
10                2008-01-31 20:50:33.000 NULL                    
11                NULL                                  NULL                    
12                2008-02-13 00:39:32.000 NULL                    
13                2008-02-01 23:19:00.000 2008-02-01 23:21:59.000 0:2:59
14                2008-02-01 21:00:17.000 NULL                    
14                2008-02-01 21:01:13.000 NULL                    
15                2008-02-01 20:59:48.000 2008-02-12 18:24:52.000 261:25:4
15                2008-02-01 20:59:48.000 2008-11-05 19:51:31.000 6670:51:43
18                NULL                    NULL                    
19                NULL                    NULL                    
20                NULL                    NULL                    
21                NULL                    NULL                    
22                NULL                    NULL                    
23                NULL                    NULL                    
24                NULL                    NULL                    
25                2008-11-08 00:03:18.000 NULL                    
26                NULL                    NULL                    
27                NULL                    NULL                    
28                NULL                    NULL                    
29                NULL                    NULL                    
30                NULL                    NULL                    
31                NULL                    NULL                    
32                NULL                    NULL                    
33                2008-11-09 15:06:49.000 2008-11-09 15:10:29.000 0:3:40
33                2008-11-09 15:06:54.000 2008-11-09 15:10:29.000 0:3:35



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SalesleadsBoardID Assigned Date           Contacted Date          DurationTime
----------------- ----------------------- ----------------------- --------------------------------------------------------------------------------------------
10                2008-01-31 20:50:33.000 NULL                    
15                2008-02-01 20:59:48.000 2008-11-05 19:51:31.000 6670:51:43
14                2008-02-01 21:00:17.000 NULL                    
14                2008-02-01 21:01:13.000 NULL                    
13                2008-02-01 23:19:00.000 2008-02-01 23:21:59.000 0:2:59
12                2008-02-13 00:39:32.000 NULL                    
25                2008-11-08 00:03:18.000 NULL                    
33                2008-11-09 15:06:49.000 2008-11-09 15:10:29.000 0:3:40
33                2008-11-09 15:06:54.000 2008-11-09 15:10:29.000 0:3:35
0
 
erin027Author Commented:
Maybe I am bad explaining this.
Let say I have following Data in Three Different Tables.
 I have to select each salesleadsBoardID, first AssignedDate, first ContactedDate, and Time Difference from those two first AssignedDate, and first ContactedDate

SalesLeadsBaord (Table)
------------------------
SalesLeadsBoardID
1
2
3

StatusType (table)
------------------------
StatusTYpeID           StatusTypeName
12                             AssignedDate
14                             ContactedDate

SalesLeadsReply (Table)
------------------------
SalesLeadsBoardID     RegisterDate                             StatusTypeID
1                                  2008-01-01 12:01:00                        12
1                                  2008-01-01 12:34:00                        12
1                                  2008-01-01 12:54:00                        14
1                                  2008-01-01 12:59:00                        14
3                                  2008-01-01 12:59:00                        12

0
 
SharathData EngineerCommented:
Check this query:
SELECT A.SalesLeadBoardID
       ,BA.RegisterDate as 'Assigned Date'
       ,BC.RegisterDate as 'Contacted Date'
       ,CASE WHEN BA.RegisterDate IS NULL OR BC.RegisterDate IS NULL THEN ''
             ELSE CAST(((DATEDIFF(ss,BA.RegisterDate,BC.RegisterDate)/3600)) AS VARCHAR) + ':' +
                  CAST(((DATEDIFF(ss,BA.RegisterDate,BC.RegisterDate)%3600)/60) AS VARCHAR) + ':' +                                   CAST((((DATEDIFF(ss,BA.RegisterDate,BC.RegisterDate)%3600)%60)%60) AS VARCHAR)
        END AS DurationTime
  FROM SalesLeadBoard A
  LEFT JOIN (SELECT SalesLeadBoardID,StatusTypeID,Min(RegisterDate)
               FROM SalesLeadsReply
              GROUP BY SalesLeadBoardID,StatusTypeID) BA
    ON A.SalesLeadBoardID = BA.SalesLeadBoardID
   AND BA.StatusTypeID = 12
  LEFT JOIN (SELECT SalesLeadBoardID,StatusTypeID,Min(RegisterDate)
               FROM SalesLeadsReply
              GROUP BY SalesLeadBoardID,StatusTypeID) BC
    ON A.SalesLeadBoardID = BC.SalesLeadBoardID
   AND BC.StatusTypeID = 14
0
 
erin027Author Commented:
Sorry for the delay...  I am read to fix this.
I got an error:
No column was specified for column 3 of 'BA'
0
 
erin027Author Commented:
Thank you. your coding was correct except I had to change the 'Min(RegisterDate)' to 'Min(RegisterDate) as RegisterDate'

Thank you So Much!!!
0

Featured Post

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!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now