Search code examples
t-sqlviewranking

T-SQL how to calculate palmaresand create a view based on one table and a view?

Hy,

I have these objects in a bridge card game:

Tabble: Series SerieID Title DateAdded

View: BoardCounter BoardID SerieID Counter

The counter is the number of time each board has been played. Now I need to establish a ranking based on the counter divided by the number of days of presence.

I figured out easily how to calculate the days:

select serieid, date, datediff(day, DateAdded, GETDATE()) Days, Title from auboardseries

But not being that much a T-SQL geek (mostly used to LINQ), I need help to create the new view.

Thanks

Solution

  • Here's my stab at this based on a whole bunch of assumptions I've made about your question.

    Here's my bootstrapping code to get sample data:

    Your series table

    DECLARE @series table
(
SeriesID int,
Title varchar(10),
DateAdded datetime
)
INSERT @series
SELECT 1,'series 1','10-10-2011'
UNION
SELECT 2,'series 2','8-01-2011'
UNION
SELECT 3,'series 3','11-30-2011'

    Your BoardCounter View (it's a table here but that shouldn't matter and you said you've got that figured out already)

    DECLARE @BoardCounter table
(
BoardID INT,
SeriesID INT,
[COUNTER] INT
)
INSERT @BoardCounter
SELECT 1,1,1000
UNION
SELECT 1,2,800
UNION
SELECT 1,3,600
UNION
SELECT 2,1,2000
UNION
SELECT 2,2,1600
UNION
SELECT 2,3,1200
UNION
SELECT 3,1,500
UNION
SELECT 3,2,400
UNION
SELECT 3,3,300

    Okay, the following should be the guts of the view you say you want. It's just a join of the table and view above. Again, this is fine because once a view is created, in this situation, it's just like a table. Nothing too fancy. I divided the counter by the days elapsed like you indicated. You'll notice that I've converted both values to FLOATs first and rounded the result to two decimal places. That's just an assumption on my part that you'll want a fine grained ranking. You can adjust that as you like. 

    select S.SeriesID, DateAdded as [Date], ROUND(CAST([COUNter] AS FLOAT)/CAST(datediff(day, DateAdded, GETDATE()) AS FLOAT),2) AS Ranking,S.Title
from @series s
INNER JOIN @BoardCounter bc
ON s.seriesid=bc.seriesid

    So, you should be good to just change a few table/view/column names in this and slap a CREATE VIEW AS on it.