July 29, 2010 at 5:13 am
hi all,
i have a query which gives the percentage of time against each user.
I have 6 records in total. I must now select top 3 records from it in the last 30 days. i.e top 3 users in the last 30 days.
there is a timeuploaded column which gives this last 30 days.
this is my original query:
select tbl3.Worlds, CONVERT(FLOAT,tbl3.WorldSessionLength)/CONVERT(FLOAT,tbl4.TotalSessionLength) AS Perc from
(select (CASE WHEN world LIKE '%sunset%valley%' THEN 'SunsetValley'
WHEN world LIKE '%River%view%' THEN 'RiverView'
WHEN world LIKE '%China%' THEN 'China'
WHEN world LIKE '%France%' THEN 'France'
WHEN world LIKE '%Twin%Brook%' THEN 'TwinBrook'
WHEN world LIKE '%Egypt%' THEN 'Egypt'
ELSE 'Miscellaneous' END) AS Worlds,
SUM(tbl1.sessionlength) as WorldSessionLength from
(select world, sessionlength, telemetry_id from world inner join world_telemetry
on (world.id = world_telemetry.world_id)) as tbl1
group by (CASE WHEN world LIKE '%sunset%valley%' THEN 'SunsetValley'
WHEN world LIKE '%River%view%' THEN 'RiverView'
WHEN world LIKE '%China%' THEN 'China'
WHEN world LIKE '%France%' THEN 'France'
WHEN world LIKE '%Twin%Brook%' THEN 'TwinBrook'
WHEN world LIKE '%Egypt%' THEN 'Egypt'
ELSE 'Miscellaneous' END)) as tbl2,
(select SUM(sessionlength) as TotalSessionLength from sessioninfo) as tbl3
**********************************************************************
The TimeUploaded column comes from table sessioninfo.
now i need top 3 * from this query where timeuploaded in (dateadd(day,-30,timeuploaded))
i tried and got the result set but now when i am trying to tune it further i am not getting.
it is bit urgent. please help me.
July 29, 2010 at 6:52 am
you say you got the answer, but now you're trying to tune it further and not getting it?
are you not getting your correct result, or are you not getting it to be more efficient?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 29, 2010 at 7:19 am
malavika.ramanathan (7/29/2010)
hi all,i have a query which gives the percentage of time against each user.
I have 6 records in total. I must now select top 3 records from it in the last 30 days. i.e top 3 users in the last 30 days.
there is a timeuploaded column which gives this last 30 days.
this is my original query:
select tbl3.Worlds, CONVERT(FLOAT,tbl3.WorldSessionLength)/CONVERT(FLOAT,tbl4.TotalSessionLength) AS Perc from
(select (CASE WHEN world LIKE '%sunset%valley%' THEN 'SunsetValley'
WHEN world LIKE '%River%view%' THEN 'RiverView'
WHEN world LIKE '%China%' THEN 'China'
WHEN world LIKE '%France%' THEN 'France'
WHEN world LIKE '%Twin%Brook%' THEN 'TwinBrook'
WHEN world LIKE '%Egypt%' THEN 'Egypt'
ELSE 'Miscellaneous' END) AS Worlds,
SUM(tbl1.sessionlength) as WorldSessionLength from
(select world, sessionlength, telemetry_id from world inner join world_telemetry
on (world.id = world_telemetry.world_id)) as tbl1
group by (CASE WHEN world LIKE '%sunset%valley%' THEN 'SunsetValley'
WHEN world LIKE '%River%view%' THEN 'RiverView'
WHEN world LIKE '%China%' THEN 'China'
WHEN world LIKE '%France%' THEN 'France'
WHEN world LIKE '%Twin%Brook%' THEN 'TwinBrook'
WHEN world LIKE '%Egypt%' THEN 'Egypt'
ELSE 'Miscellaneous' END)) as tbl2,
(select SUM(sessionlength) as TotalSessionLength from sessioninfo) as tbl3
**********************************************************************
The TimeUploaded column comes from table sessioninfo.
now i need top 3 * from this query where timeuploaded in (dateadd(day,-30,timeuploaded))
i tried and got the result set but now when i am trying to tune it further i am not getting.
it is bit urgent. please help me.
This query cannot run without errors - the table alias [tbl3] doesn't contain the column [Worlds] referenced in the output.
Here's a gently-formatted version of the above query:
select
tbl3.Worlds,
CONVERT(FLOAT,tbl3.WorldSessionLength)/CONVERT(FLOAT,tbl4.TotalSessionLength) AS Perc
from (
select (CASE WHEN world LIKE '%sunset%valley%' THEN 'SunsetValley'
WHEN world LIKE '%River%view%' THEN 'RiverView'
WHEN world LIKE '%China%' THEN 'China'
WHEN world LIKE '%France%' THEN 'France'
WHEN world LIKE '%Twin%Brook%' THEN 'TwinBrook'
WHEN world LIKE '%Egypt%' THEN 'Egypt'
ELSE 'Miscellaneous' END) AS Worlds,
SUM(tbl1.sessionlength) as WorldSessionLength
from (
select world, sessionlength, telemetry_id
from world
inner join world_telemetry
on (world.id = world_telemetry.world_id)
) as tbl1
group by (CASE WHEN world LIKE '%sunset%valley%' THEN 'SunsetValley'
WHEN world LIKE '%River%view%' THEN 'RiverView'
WHEN world LIKE '%China%' THEN 'China'
WHEN world LIKE '%France%' THEN 'France'
WHEN world LIKE '%Twin%Brook%' THEN 'TwinBrook'
WHEN world LIKE '%Egypt%' THEN 'Egypt'
ELSE 'Miscellaneous' END)
) as tbl2,
(select SUM(sessionlength) as TotalSessionLength from sessioninfo) as tbl3
I wouldn't try to go any further with this. The core idea of using derived tables is fine, but it really needs a bit of a re-write.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2010 at 7:30 am
now i'm actually at my desk and reading through the sql, chris is absolutely correct - this sql aint gonna work (which you obviously know).
I'd recommend rewriting it to be perfectly honest.. keep what you have saved off, but start over, its not that complex a statement and shouldn't take you too long to do, but by starting over instead of modifying, you're less likely to miss something.
if you continue to have issues after your rewrite, I'm sure someone can help
good luck 🙂
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 29, 2010 at 7:32 am
Hi all,
thanks for all your replies. Yes as you said, the tbl3.world was not correct.
m working on it. i will surely get back to you once i am done.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply