Compare dates in two ongoing rows

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 2000 Forums

SQL Server Development (2000)

Compare dates in two ongoing rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-28 : 08:40:15

Jiri writes "Dear all,

being unhappy for few hours by now, I decide to try here to find nice solution for my answer:

I have table showing


HITS USER DATE
100   A    02/01/2002
150   A    04/01/2002
200   A    08/01/2002

hits	UCODE	date					
	A	1/01/2002		2000			
100,00	A	2/01/2002	1	2100	358,02	2000	
150,00	A	4/01/2002	2	2250	527800,58	4200	
200,00	A	8/01/2002  	4	2.450	62900	9000

The final answer is 7 days sum for average 15200 average hits 2171."

ToddV
Posting Yak Master

218 Posts

Posted - 2002-05-28 : 16:24:44

Jiri,

There was a nearly identical post on Friday to which I posted this response:

I think something like this will get you started:

Create table #temp (
Hits int,
UserName char(1),
Date Datetime)
Insert #TEMP Values(100, 'A', '02/01/2002')
Insert #TEMP Values(150, 'A', '04/01/2002' )
Insert #TEMP Values(200, 'A', '08/01/2002')

--Rank the records by user and date
SELECT A.UserName,
A.Date ,
A.Hits,
Count(*) AS Rank,
Sum(B.Hits) AS "HitsToDATE"
INTO #RANK
FROM #TEMP A
JOIN #TEMP B
ON A.UserName = B.UserName AND
A.date >= B.Date
GROUP BY A.UserName,
A.Date,
A.Hits

-Join Rank to Rank-1 to find how long from last period
SELECT A.HitsToDATE,
A.UserName,
A.Date,
ISNULL(DateDiff(dd, B.Date, A.Date),0)
FROM #RANK A
LEFT JOIN #Rank B
ON A.UserName = B.Username and A.Rank-1 = B.Rank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources