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?
erin027Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.