Help in writing 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)

Help in writing query.

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-30 : 05:06:59

Hi,
I have a following Tables.
1. TrendSelected_Matches
-------------------------
SessionID MatchId
-------------------------
0 8
------------------------

2. trendSelected_Players
-------------------------
SessionID PlayerID
-------------------------
0 70
0 134
-------------------------

For each match we have Max(Time) in another table. The time is in seconds.
3. MaxTimeTable
-----------------------------
MatchID MaxTime
-----------------------------
8 5800
-----------------------------

My question is i have to insert matchId, playerid in another temporary table with Time division
of 900 sec each playerid.
ie., My output should be like
---------------------------------------------
MatchID Playerid TimeStart TimeEnd
---------------------------------------------
8 70 0 900
8 70 901 1800
8 70 1801 2700
8 70 2701 3600
8 70 3601 4500
8 70 4501 5400
8 70 5401 5800 i.e, max time

8 134 0 900
8 134 901 1800
8 134 1801 2700
8 134 2701 3600
8 134 3601 4500
8 134 4501 5400
8 134 5401 5800 i.e, max time

How can get like above
Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 05:23:58

[code]INSERT INTO YourTable
SELECT m.MatchID,p.PlayerID,900 * v.number+ CASE WHEN v.number<>0 THEN 1 ELSE 0 END,900*(v.number+1)
FROM TrendSelected_Matches m
INNER JOIN trendSelected_Players p
ON p.SesionID=m.SessionID
INNER JOIN MaxTimeTable t
ON t.MatchID=m.MatchID
CROSS JOIN master..spt_values v
WHERE v.Type='p'
AND 900*(v.number+1) <=t.MaxTime[/code]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-30 : 05:54:05

thank you visakh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources