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-07-09 : 04:14:28

Hello,
I have a Start Time and End Time As Input parameters.
For Ex:
@StartTime = 500
@End Time = 2500

And I have MatchInfo Table with MatchID And PlayerID
ie
MatchID | PlayerID
-------------------
8 | 1
8 | 4
8 | 5
8 | 6
-------------------

Now for Each player in a match i have to generate time division like below
-------------------------------------
MatchID | PlayerID | StartTime | EndTime
-----------------------------------------
8 | 1 | 500 | 1100
8 | 1 | 1101 | 1700
8 | 1 | 1701 | 2300
8 | 1 | 2300 | 2500 here end time shoud come

the time division should come to all players.

The End Time should be @EndTime if @EndTime - @StartTime < 600

How to get like this.

Thanks
Ganesh

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 04:23:46

[code]
@StartTime = 500
@End Time = 2500
[/code]
Please explain what time does 2500 represent ?

[code]
MatchID | PlayerID | StartTime | EndTime
-----------------------------------------
8 | 1 | 500 | 1100
8 | 1 | 1101 | 1700
8 | 1 | 1701 | 2300
8 | 1 | 2300 | 2500 here end time shoud come
[/code]
The PlayerID should be 1, 4, 5, 6 correct ?

Also what is the time allocation logic ? equally among the 4 players ?

KH
[spoiler]Time is always against us[/spoiler]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-09 : 04:27:33

500 means Start second and 2500 means end second.
This should continue for all playerid'd

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

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-09 : 06:05:18

Hello i got solution for the above problem
Below is the query

SELECT m.MatchID,m.PlayerID,@StartSegment,@StartTime + (300* v.number) + CASE WHEN v.number<>0 THEN 1 ELSE 0 END
,Case when (@EndTime)-((@StartTime)+300*(v.number+1)) >300 then (@StartTime)+300*(v.number+1) else (@EndTime) end
FROM @matchInfo m

CROSS JOIN master..spt_values v
WHERE v.Type='p'
AND (@StartTime)+300*(v.number+1) <=(@EndTime)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources