Prev: Updating column values only if value is not null
Next: restricting access to sp_table_statistics2_rowset
From: LN on 25 Feb 2010 18:06
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
From: Plamen Ratchev on 25 Feb 2010 22:36
Here is one solution. You may want to add COALESCE(DATEDIFF(...), 0) depending on how you want to treat dates that have
no prior date.

WITH Ranked AS (
SELECT LoanId, EnterDate,
ROW_NUMBER () OVER (PARTITION BY LoanId ORDER BY EnterDate) AS rn
FROM #Temp)
SELECT A.LoanId, A.EnterDate,
DATEDIFF(HOUR, B.EnterDate, A.EnterDate) AS diff,
AVG(DATEDIFF(HOUR, B.EnterDate, A.EnterDate)) OVER(PARTITION BY A.LoanId) AS avg_diff
FROM Ranked AS A
LEFT JOIN Ranked AS B
ON A.LoanId = B.LoanId
AND A.rn = B.rn + 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 26 Feb 2010 00:17
Assuming you meant to post an actual table with a key and basic
constraints:

CREATE TABLE Loan_Entries
(loan_id INTEGER NOT NULL,
entry_date DATETIME NOT NULL
PRIMARY KEY (loan_id, entry_date));

>> Return the average time/hour difference between all records [sic: rows]. This is for each loan_id. <<

SELECT loan_id,
(DATEDIFF (HOUR, MAX(entry_date), MIN(entry_date) / COUNT(*))
AS entry_feq_avg
FROM Loan_Entries
GROUP BY loan_id;

Plamen probably gave the best you can do in SQL Server without a RANGE/
ROWS subclause in the OVER() clause.
From: LN on 26 Feb 2010 11:27
-- Plamen - many many THANKS to you for helping this challenge queries.
I have to 2 questions for you.

1. what is A.rn = B.rn + 1 does?
2. Is there a way to provide Total count for each LoanId. See desire
results below.

Again, I appreciated for your help.


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-02-07 20:30:37.760')
INSERT INTO dbo.#Temp (LoanId, Enterdate)
VALUES (110810, '2010-02-07 18: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


;WITH Ranked
AS (SELECT LoanId, EnterDate, ROW_NUMBER () OVER (PARTITION BY LoanId
ORDER BY EnterDate) AS rn
FROM #Temp
)
SELECT A.LoanId
,A.EnterDate
--COUNT(1) AS 'TotalCount',
,COALESCE(DATEDIFF(HH, B.EnterDate, A.EnterDate), '') AS diff
,AVG(DATEDIFF(HOUR, B.EnterDate, A.EnterDate)) OVER(PARTITION BY
A.LoanId) AS avg_diff
FROM Ranked AS A
LEFT JOIN Ranked AS B
ON A.LoanId = B.LoanId
AND A.rn = B.rn + 1;


Is there a way to provide total count of each each LoanId?

Desire results:

LoanId EnterDate diff avg_diff totalcount
----------- ----------------------- ----------- ----------- ----------
110810 2010-02-07 18:09:56.467 0 1 3
110810 2010-02-07 20:30:37.760 2 1
110810 2010-02-07 21:30:09.140 1 1
117467 2010-01-11 19:08:48.030 0 256 5
117467 2010-01-22 17:53:40.600 262 256
117467 2010-01-22 17:54:19.817 0 256
117467 2010-02-19 09:37:24.280 664 256
117467 2010-02-23 12:03:06.003 99 256

Warning: Null value is eliminated by an aggregate or other SET operation.



"Plamen Ratchev" wrote:

> Here is one solution. You may want to add COALESCE(DATEDIFF(...), 0) depending on how you want to treat dates that have
> no prior date.
>
> WITH Ranked AS (
> SELECT LoanId, EnterDate,
> ROW_NUMBER () OVER (PARTITION BY LoanId ORDER BY EnterDate) AS rn
> FROM #Temp)
> SELECT A.LoanId, A.EnterDate,
> DATEDIFF(HOUR, B.EnterDate, A.EnterDate) AS diff,
> AVG(DATEDIFF(HOUR, B.EnterDate, A.EnterDate)) OVER(PARTITION BY A.LoanId) AS avg_diff
> FROM Ranked AS A
> LEFT JOIN Ranked AS B
> ON A.LoanId = B.LoanId
> AND A.rn = B.rn + 1;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Plamen Ratchev on 26 Feb 2010 12:00
LN wrote:
> I have to 2 questions for you.
>
> 1. what is A.rn = B.rn + 1 does?

This predicate is used to join the current row to the prior date row.

> 2. Is there a way to provide Total count for each LoanId. See desire
> results below.
>

;WITH Ranked
AS (SELECT LoanId, EnterDate, ROW_NUMBER () OVER (PARTITION BY LoanId ORDER BY EnterDate) AS rn
FROM #Temp
)
SELECT A.LoanId
,A.EnterDate
,CASE WHEN B.LoanId IS NULL
THEN COUNT(*) OVER(PARTITION BY A.LoanId)
END AS TotalCount
,COALESCE(DATEDIFF(HH, B.EnterDate, A.EnterDate), 0) AS diff
,AVG(DATEDIFF(HOUR, B.EnterDate, A.EnterDate)) OVER(PARTITION BY A.LoanId) AS avg_diff
FROM Ranked AS A
LEFT JOIN Ranked AS B
ON A.LoanId = B.LoanId
AND A.rn = B.rn + 1;

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Updating column values only if value is not null
Next: restricting access to sp_table_statistics2_rowset