Need help with the Query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with the Query.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-02-25 : 18:04:51

I need to construct a query base on the requirements below and get stuck. Any help would greatly appreciated.

IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
LoanId INT NULL,
Enterdate DATETIME NULL

)
GO

INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-02-07 21:30:09.140')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-01-29 18:31:37.760')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-01-13 08:09:56.467')

INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-02-23 12:03:06.003')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-02-19 09:37:24.280')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-22 17:54:19.817')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-22 17:53:40.600')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-11 19:08:48.030')
go

SELECT *
FROM #Temp
ORDER BY LoanId ASC, Enterdate ASC;
GO

LoanId Enterdate
----------- -----------------------
110810 2010-01-13 08:09:56.467
110810 2010-01-29 18:31:37.760
110810 2010-02-07 21:30:09.140

117467 2010-01-11 19:08:48.030
117467 2010-01-22 17:53:40.600
117467 2010-01-22 17:54:19.817
117467 2010-02-19 09:37:24.280
117467 2010-02-23 12:03:06.003

Business rules: 1. Return the time/hour between the last record and the record before it.
ex: 110810 (2010-02-07 21:30:09.140 - 2010-01-29 18:31:37.760) = Diff in hour.
2. Return the average time/hour difference between all records. This is for each LoanId.

-- SQL 2005.

-- Testing...

SELECT LoanId,
EnterDate,
ROW_NUMBER () OVER (PARTITION BY LoanId ORDER BY EnterDate DESC) AS 'rn'
FROM #Temp AS a
WHERE LoanId = '117467'
ORDER BY EnterDate DESC
GO

Sachin.Nand

2937 Posts

Posted - 2010-02-25 : 23:25:45

[code]
IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
id int identity(1,1),
LoanId INT NULL,
Enterdate DATETIME NULL

)
GO

INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-02-07 21:30:09.140')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-01-29 18:31:37.760')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-01-13 08:09:56.467')

INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-02-23 12:03:06.003')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-02-19 09:37:24.280')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-22 17:54:19.817')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-22 17:53:40.600')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-11 19:08:48.030')

select * from #Temp

--First Requirement
;with cte
as
(
select * from #temp t1 inner join

(
select max(t2.id)-1 as rn1,max(t2.id)as rn2 from #temp t2
group by t2.Loanid
)t2 on t2.rn1=t1.id or t2.rn2=t1.id
)

select convert(varchar(40),datediff(hh,MIN(enterdate),MAX(enterdate)))+ ' hours'as diff,LoanId
from cte
group by LoanId

--second Requirement
select convert(varchar(40),avg(DATEDIFF(HH,t3.Enterdate,t1.Enterdate)))+ ' hours' as avgdiff,LoanId from #Temp t1
cross apply(select top 1 Enterdate from #Temp t2 where t1.LoanId=t2.LoanId order by Enterdate)t3
group by LoanId
[/code]

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:50:14

[code]IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
LoanId INT NULL,
Enterdate DATETIME NULL

)
GO

INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-02-07 21:30:09.140')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-01-29 18:31:37.760')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-01-13 08:09:56.467')

INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-02-23 12:03:06.003')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-02-19 09:37:24.280')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-22 17:54:19.817')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-22 17:53:40.600')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (117467, '2010-01-11 19:08:48.030')
go

1.SELECT t.*, DATEDIFF(hh,t2.Enterdate,t.Enterdate)
FROM #Temp t
INNER JOIN (SELECT LoanId,MAX(Enterdate) AS latest
FROM #Temp
GROUP BY LoanId) t1
ON t1.LoanId=t.LoanId
AND t1.latest= t.Enterdate
CROSS APPLY (SELECT TOP 1 Enterdate
FROM #Temp
WHERE LoanId=t.LoanId
AND Enterdate< t.Enterdate
ORDER BY Enterdate DESC) t2

2.SELECT t.LoanId, AVG(DATEDIFF(hh,t2.Enterdate,t.Enterdate)*1.0)
FROM #Temp t
CROSS APPLY (SELECT TOP 1 Enterdate
FROM #Temp
WHERE LoanId=t.LoanId
AND Enterdate< t.Enterdate
ORDER BY Enterdate DESC) t2
GROUP BY t.LoanId

DROP TABLE #Temp

output
----------------------------------------
1.
LoanId Enterdate TimeDiff
110810 2010-02-07 21:30:09.140 219
117467 2010-02-23 12:03:06.003 99

2.
LoanId AvgTimeDiff
110810 306.500000
117467 256.250000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources