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

SQL Server Development (2000)

Corelated query

Author

Topic

mee
Starting Member

1 Post

Posted - 2002-05-24 : 01:57:45

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

And I need as output -
cumulated hits | USER | DATE | DAYS FROM PREVIOUS RECORD | TOTALHITS(2000+HITS) | TOTALHITS *DAYS FROM PREVIOUS RECORD | HITS*DAYS FROM PREVIOUS RECORD

RESULTING TABLE
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-24 : 08:23:26

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