complicated 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)

complicated query

Author

Topic

michal
Starting Member

4 Posts

Posted - 2008-03-02 : 05:08:54

I am trying to use ssis (sql query or .net-script task)
to transfer data from one table to another.
I have difficulties to make the query:

i have one table tbl_games which fields are :
type ,startDateTime, EndDateTime, playerId.
I need to check every 1/2 hour according to the startDateTime,
How many times a person is playing and to show it in a new table
tbl_collectData like this:
Time playerId Games_0-30_seconds Games_30-50_seconds

8-8:30 A 3 2
8:30-9 B 2 10
9-:30 C 20 7

The length of the game is measured by the StartDateTime -EndDateTime,
and only games type # 2 is being collected.
The number 3 example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.

I tryed to start by doing something like this , but it is not a complete query:
INSERT INTO tbl_collectData
(Time, playerId, Games_0-30_seconds)
SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS numberOfCalls_0_30
FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND
(StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM')
GROUP BY playerId

I just don't know how to do it! Please help!!!!

sanoj_av
Posting Yak Master

118 Posts

Posted - 2008-03-03 : 06:50:57

insert into tbl_collectData
(
[Time],
playerId,
Games_0_30_seconds,
Games_30_50_seconds
)
Select
Convert (varchar(15),dateadd(mi,-30,GetDate()),108) + '-' + Convert (varchar(15),GetDate(),108),
playerId,
sum(Case when dateDiff(ss,startDateTime,EndDateTime) <= 30 then 1 else 0 end) as Games_0_30_seconds,
sum(Case when dateDiff(ss,startDateTime,EndDateTime) > 30 then 1 else 0 end) as Games_30_50_seconds
From
tbl_games
Where
startDateTime>=dateadd(mi,-30,GetDate())--If tbl_games is not truncating at each 30 minutes
group by
playerId

hope this will help you,
Sanoj

michal
Starting Member

4 Posts

Posted - 2008-03-05 : 04:36:18

Hi sanoj_av,

Exellent!!!!
Thank you very much!!!!
This was very helpful!!
It is exactly what i need!!!

Michal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources