asked on

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?

Sharath S

Question -
do you have more than one record in SalesLeadsReply table for the combination of SalesLeadsBoardID and StatusTypeID?
can you provide some sample data?

erin027

ASKER

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!

Sharath S

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

jamesgu

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

erin027

ASKER

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'

Sharath S

Can you provide the result set of my query?

erin027

ASKER

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

erin027

ASKER

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

erin027

ASKER

Sorry for the delay... I am read to fix this.
I got an error:
No column was specified for column 3 of 'BA'

erin027

ASKER

Thank you. your coding was correct except I had to change the 'Min(RegisterDate)' to 'Min(RegisterDate) as RegisterDate'

Thank you So Much!!!